Refreshing Bloomberg requests in a for loop VBA

Question

I have a for loop that iterates over a list of securities and obtains the historical data as follows:

diter = 0
field = "px_last"
For Each d In dates
    diter = diter + 1

    For s = 1 To numb_sec

        bbticker = securities(s)
        wsSec.Range(cl & diter).Formula = _
            "=BDH(""" & bbticker & """,""" & field & """,""" & d & """,""" & d & """)"

        wsSec.Calculate


    Next s
Next d

Obviously, this results that the data is not loaded before the next security kicks in.

Since I save the file after this loop, I only get #N/A Requesting Data for each cell.

So I am looking for a way to wait until the fetching is done before proceeding to the next security and date.

I looked a bit online and found that you can use:

Application.OnTime Now + TimeValue("00:00:01"), "NextFunction"

The problem with this: this method only kicks in another function after 1 second.

• How could this be applied to my code? Perhaps put it at the end of the loop or are there better ways to Fetch data from Bloomberg in VBA?

• Also, what do I do if I don't know how long the fetching takes, now I put 1 second to wait, but perhaps it should be more?

Answer 1

You are trying to calculate the sheet every time you insert BDH formula.

Another approach (untested because I don't have the Bloomberg library) is to do the following:

• Disable events
• Do the loop inserting BDH formulas but don't calculate yet...
• Re-enable events
• Call Application.Calculate which will do the sheet calculation
• Wait till calculations are done - see here
• Save the sheet

Sample code would go like this (again, untested):

' disable events
Application.EnableEvents = False

' your code etc (but don't calculate)
diter = 0
field = "px_last"
For Each d In dates
    diter = diter + 1
    For s = 1 To numb_sec
        bbticker = securities(s)
        wsSec.Range(cl & diter).Formula = _
            "=BDH(""" & bbticker & """,""" & field & """,""" & d & """,""" & d & """)"
    Next s
Next d

' re-enable events
Application.EnableEvents = True

' don't just calculate the sheet - call Application.Calculate
Application.Calculate

' wait till calculation complete
' https://stackoverflow.com/questions/11277034/wait-until-application-calculate-has-finished
If Not Application.CalculationState = xlDone Then
    DoEvents
End If

' do save etc
' code...