excel - VBA to refresh Bloomberg data not running in proper order

Question

my purpose is to update Bloomberg data and do some calculatations with different tickers. But it seems that VBA will run all the calculations without waiting the data to be updated. Here is the code:

Application.Calculation = xlCalculationAutomatic

For i = 1 To 3

    Call Worksheets("Sheet1").Range("data1").Select 'the cells "data1" contains the function =BDH(ticker, field, start date, end date) to get the information from Bloomberg'

    Call Application.Run("RefreshCurrentSelection")

    Worksheets("sheet1").Range("d3").Value = Worksheets("sheet1").Range("sum") 'the cells "sum" takes the sum of all BB info'     

Anyone know how to fix it?

Answer 1

Bloomberg formulae update is asynchronous so your code won't wait until the update is over. You need to schedule the rest of your code to a later time, check if the data has been updated.

It would look like this:

Application.Run "RefreshCurrentSelection"
update

Sub update()
  If (check if the links have been updated) Then
    Worksheets("sheet1").Range("d3").Value = Worksheets("sheet1").Range("sum")
  Else
    Application.OnTime earliestTime:= Date + Time + timeserial(0, 0, 1), _
                   procedure:="update", Schedule:=True
  End if
End Sub

That would ask the Bloomberg API to refresh the data, then wait 1 second, check if the data is updated, wait another second if not etc. until the data is updated and then it would run the range assignment.