my company makes extensive use of a data provider using a (closed source) VBA plugin. In principal, every query follows follows a certain structure:
Fill one cell with a formula, where arguments to the formula specify the query
the range of that formula is extended (not an arrray formula!) and cells below/right are filled with data
For this to work, however, a user has to have a terminal program installed on the machine, as well as a com-plugin referenced in VBA/Excel.
My Problem
These Excelsheets are used and extended by multiple users, and not all of them have access to the data provider. While they can open the sheet, it will recalculate and the data will be gone. However, frequent recalculation is required. I would like every user to be able to use the sheets, without executing a very specific set of formulas.
Attempts
remove the reference on those computers where I do not have terminal access. This generates a NAME error i the cell containing the query (acceptable), but this query overrides parts of the data (not acceptable)
If you allow the program to refresh, all data will be gone after a failed query
Replace all formulas with the plain-text result in the respective cells (press a button and loop over every cell...). Obviously destroys any refresh-capabilities the querys offer for all subsequent users, so pretty bad, too.
A theoretical idea, and I'm not sure how to implement it: Replace the functions offered by the plugin with something that will be called either first (and relay the query through to the original function, if thats available) or instead of the original function (by only deploying the solution on non-terminal machines), which just returns the original value.
More specifically, if my query function is used like this:
=GETALLDATA(Startdate, Enddate, Stockticker, etc)
I would like to transparently swap the function behind the call.
Do you see any hope, or am I lost?
I appreciate your help.
PS: Of course I'm talking about Bloomberg...
Some additional points to clarify issues raise by Frank:
The formula in the sheets may not be changed. This is mission-critical software, and its way too complex for any sane person to try and touch it.
Only excel and VBA may be used (which is the reason for the previous point...)
It would be sufficient to prevent execution of these few specific formulas/functions on a specific machine for all excel sheets to come
This looks more and more like a problem for stackoverflow ;-)
