Bloomberg data doesn't populate until Excel VBA macro finishes

Question

I'm running a macro in a blank Excel 2007 workbook on a PC with a Bloomberg license. The macro inserts Bloomberg functions into sheet1 that pull yield curve data. Some additional functions' results are dependent on the first functions finishing and correctly displaying the Bberg data. When I step through the program it only displays '#N/A Requesting Data . . .' instead of the results of the query, no matter how slowly I go. Because some of the functions are dependent on string and numeric field results being populated, the program hits a run-time error at that code. When I stop debugging -- fully ending running the program -- all the Bberg values that should have populated then appear. I want these values to appear while the program is still running.

I've tried using a combination of DoEvents and Application.OnTime() to return control to the operating system and to get the program to wait for a long time for the data update, but neither worked. Any ideas would be helpful. My code is below. wb is a global-level workbook and ws1 is a global level worksheet.

Public Sub Run_Me()

    'Application.DisplayAlerts = False
    'Application.ScreenUpdating = False

    Call Populate_Me
    Call Format_Me

    'Application.DisplayAlerts = True
    'Application.ScreenUpdating = True

End Sub

Private Sub Populate_Me()

    Dim lRow_PM As Integer
    Dim xlCalc As XlCalculation

    Set wb = ThisWorkbook
    Set ws1 = wb.Sheets(1)

    'clear out any values from previous day
    If wb.Sheets(ws1.Name).Range("A1").Value <> "" Then
        wb.Sheets(ws1.Name).Select
        Selection.ClearContents
    End If


    xlCalc = Application.Calculation
    Application.Calculation = xlCalculationAutomatic

    Range("A1").Value = "F5"
    Range("B1").Value = "Term"
    Range("C1").Value = "PX LAST"

    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=BDS(""YCCF0005 Index"",""CURVE_MEMBERS"",""cols=1;rows=15"")"
    BloombergUI.RefreshAllStaticData

    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=BDS(""YCCF0005 Index"",""CURVE_TERMS"",""cols=1;rows=15"")"
    BloombergUI.RefreshAllStaticData

    Application.OnTime Now + TimeValue("00:00:10"), "HardCode"

    '******more code*******'
    End Sub

Sub HardCode()

    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=BDP($A2,C$1)"
    BloombergUI.RefreshAllStaticData

End Sub

Answer 1

A way to get around this issue is to put all subs, etc that you want to run after pulling the bloomberg data into a different sub. You must do this each time you call Bloomberg information. If you call another sub in the "master" sub after the Application.OnTime Now +TimeValue("00:00:15"), it will fail- you must put all subs following into a new master sub.

For example: Instead of

Sub Master1()
Application.Run "RefreshAllStaticData"
Application.OnTime Now + TimeValue("00:00:15"), "OtherSub1"
'This will cause the Bloomberg Data to not refresh until OtherSub2 and 3 have run
OtherSub2
OtherSub3
End Sub

It should be

Sub Master1()
Application.Run "RefreshAllStaticData"
Application.OnTime Now + TimeValue("00:00:15"), "Master2"
End Sub

Sub Master2()
OtherSub1
OtherSub2
OtherSub3
End Sub

Hope that helps