Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation Context available inside an Excel VBA function?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation Context available inside an Excel VBA function?
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 "David Boden" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying symbol from inside a calculation | Excel Worksheet Functions | |||
Can I use the result of an array calculation inside a regular formula? | Excel Programming | |||
Launching context-sensitive HTML Help for a Function in Excel Add-In | Excel Programming | |||
Using a range variable inside a excel function | Excel Discussion (Misc queries) | |||
Adding help context to personal function | Excel Programming |