1

my company makes extensive use of a data provider using a (closed source) VBA plugin. In principal, every query follows follows a certain structure:

  1. Fill one cell with a formula, where arguments to the formula specify the query

  2. 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 ;-)

1
  • I'm afraid you are trying to use Excel as though it were an actual application, which is never a good idea (at least you are not using access, I guess). can you wrap your query call in an conditional, and only execute it when the user has access? what about just giving the user access to the datasource? – Frank Thomas Aug 19 '14 at 14:29
3

I don't doubt the truth of this sentence but I struggle to follow the logic: "This is mission-critical software, and its way too complex for any sane person to try and touch it.".

Anyway ...

I would try to separate this into two Excel files. One would gather the data using the VBA plugin, and would only be updated on machines with "terminal access". The content would just be tables of data.

The second file would copy data from the first file (I would use the Power Query Add-In for this) and would be used for analysis on any machine.

1
  • Yeah, I'd do if I could... unfortunately, formulas must not be touched (and cannot be, as there are literally thousands of formulas in dozens of workbooks with dozens of sheets.) Wait... I'd not use excel for this in the first place :D – zuiqo Aug 20 '14 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.