Calculation Context available inside an Excel VBA function?

[David Boden]

Our Excel VBA Add-In hits our server one or more times every time the user
hits F9 to recalculate their values. Sometimes, the users who have written
the spreadsheets have organised things in such a way that the function is
called 5 or more times which results in 5 or more calls to our server. The
users won't tolerate a timeout or delay in getting data into their
spreadsheet when they hit F9 and they need up-to-the-second data so the
server must be hit each time F9 is pressed, but we need to avoid making 5
unnecessary server calls.

Inside the VBA function we *need* to be able to work out the context in
which the function is being called. At an absolute minimum, we should be able
to query a unique ID for the user event that caused the recalculate, be that
either an F9 keypress or the change of a cell's value. The cascading changes
should all be part of an identifiable transaction. More information about the
transaction would be great, specifically whether it was an F9 or an autocalc
that caused the transaction.

We could then cache the server values only for the life of the calculation
transaction. This would allow users to get up-to-the-second data, but would
allow the Excel Add-In to be smart and not make 5 server calls.

At its most basic this would be Application.getCalculationID() that could be
called within a function.

Is there a way of getting this information inside a VBA function in any
Office version? This is a real-world problem in an investment bank.

 

[Charles Williams]

David,

Some information about the calculation is available inside a function, but
not exactly as you have specified, also there are many more things that can
cause a recalc than F9 or a change in Automatic.

You should make sure your function is not being called multiple times
because of uncalculated cells or the function wizard.
(see http://www.DecisionModels.com/calcsecretsj.htm)

Then if there are multiple instances of the function being called per
calculation event you could set a switch from the first execution of the
function so that subsequent executions of the function would know to get the
cached value, and the reset the switch in a Calculate event (which fires
after the calculation).
If neccessary you could have separate cached values each with its own
identifier.

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