Hi Excel Experts,
Ive 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.

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." wrote in message
...
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.

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

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


"Charles Williams" wrote:

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