The Essential Correct Approaches to Refresh Bloomberg Formula in VBA

Normally, you use "Calculate" method to calculate a sheet/range. Like this:

ActiveSheet.Calculate
or
Range("A1:D10").Calculate
or
Application.Calculate

However, for Bloomberg formula, this won't work properly. It takes time for Bloomberg formulas to refresh. When an BBG formula calculates, it will show "#N/A Requesting Data..." (see picture below) for a while, ranging from a second to a few minutes, depending on the amount of data you're requesting.

It means, you macro needs to be intelligent enough to "wait" for the bloomberg formulas to be fully refreshed before proceeding.

In order to achiveve this, here I'm introducing a "standard" approach in VBA, making use of the "OnTime" method.

The macro "run_part1" is the macro you kick off. That If-Then statement fires a little function to test whether the activesheet has been fully refreshed. If yes (true), it'll proceed to do the work inside the IF-Then statment. If no (false), it'll calculate the activesheet again in 5 seconds.

 

You cannot calculate Bloomberg formula with UserForm activated

When you managed to calculate your Bloomberg worksheet, in my experience, your very creative users will ask you for another thing:
With a UserForm activated, calculate bloomberg formulas to drag real-time data from Bloomberg into the UserForm.

And your user will tell you that "they used to do this previously in every bank they worked in".

You can tell them: with a UserForm displayed, they CANNOT refresh the bloomberg formulas with macros. (This is because you cannot use OnTime while having a UserForm displayed.)

A solution will be to Hide the form in "run_part1" before the IF-THEN statement, and Show the form after the calculations have been completed.

(And your user will then teach you "they never had to dismiss a UserForm to have bloomberg formulas refreshed". Well, tell them "this is not possible".)