D
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.
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.