Need Help to Control the UDF from re-calculation

[Steve C.]

Hi Excel Experts,
I’ve developed an add-in function in Excel to assist peers to pull out
accounting data from our database according to criteria like account group
number, ledger code and accounting period. This function is basically
constructed using Windows query connected to a MS Access database. It works
fine when the function is not heavily used in the spreadsheet. However, in
the case of building a profit/loss by month report, the matrix of 12 columns
(month) x 100 A/c rows (total 1200 cells) of this add-in function could be a
nightmare! It takes more than half an hour to complete the calculation on my
PC (Windows XP; CPU 3.00GHz 2GB RAM).
This time-consuming calculation process repeats every time the file is
re-opened. To avoid this function being evaluated upon opening the file, I
tried to keep the active cell value by inserting codes inside the function to
bypass the time-consuming SQL portion, but the active cell value is fact
empty and does not contain the visible value last returned by the function!

I therefore request for expertise advice in the forum. Anyone out there
wishing to help me to resolve this issue would be much appreciated.

Regards,
Steve C.

 

[Charles Williams]

There is no simple way to keep the previous cell value in a VBA UDF.

Best thing to do would be to speed up your UDF so that it runs much faster.
I suggest you post the code of the UDF and ask for help in speeding it up.

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Steve C.]

Hi Charles,
many thanks for your suggestion. I appended below is the code of my UDF for
your reference. Hoping that you or other Excel experts can give me advice on
how to make it run faster. Thankyou in advance for your further expertise
advice!


Public Const G_Connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Finance\Voucher.mdb;"
Public rs As ADODB.Recordset
'*******************************
Private Function LG(ByVal LedgerCode As String, ByVal ACGrp As String, ByVal
StartMonth As String, ByVal EndMonth As String) As Double
Dim ws As Worksheet
Dim MTBL, STRSQL As String
Dim numberOfRows
Dim x, y As Long

Set ws = ActiveSheet

MTBL = "V2008"
Set rs = New ADODB.Recordset

STRSQL = "SELECT SUM([" & MTBL & ".EquvAmt]) AS AMOUNT FROM " & MTBL & " "
STRSQL = STRSQL & "WHERE ("
'************* Selection criteria ********************************
STRSQL = STRSQL & MTBL & ".Ledger = '" & LedgerCode & "' AND " & MTBL &
".Grouping= '" & ACGrp & "' "
STRSQL = STRSQL & " AND " & MTBL & ".ACPeriod >='" & StartMonth & "' AND "
& MTBL & ".ACPeriod <='" & EndMonth & "') ; "

rs.Open STRSQL, G_Connect, adOpenForwardOnly, adLockReadOnly, adCmdText
LG = IIf(IsNull(rs.Fields(0).Value), 0, rs.Fields(0).Value)
Set rs = Nothing

End Function

'------------------------------------------------------------------------------------------------

 

[Charles Williams]

Hi Steve,

Looks like the time is being used making the connection and executing the
query to get the recordset.

I would suggest that you get the complete recordset (without the filters,
using GROUP BY) once (or once every hour or so if the information in the DB
may change).

Do this by getting the recordset only if it is Nothing, and do not set the
recordset to nothing at the end -

if rs is nothing then
'
' get recordset
'
Set rs = New ADODB.Recordset

STRSQL = "SELECT ... FROM ... GROUP BY ..."
rs.Open STRSQL, G_Connect, adOpenStatic, adLockBatchOptimistic, adCmdText

end if

and then use RS.FIND to get the particular record you want using the Where
Criteria

You may also want to use a disconnected recordset
(RS.CursorLocation=adUseClient before getting the recordset and Set
rs.ActiveConnection = Nothing after getting it)

regards
Charles
_________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Steve C.]

Hi Charles,
many thanks for your suggestion. Have a nice day!!