title: Bloomberg data doesn't populate until Excel VBA macro finishes
body: <p>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.</p>
  
  <p>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.  <em>wb</em> is a global-level workbook and <em>ws1</em> is a global level worksheet.</p>
  
  <pre><code>Public Sub Run_Me()
  
      'Application.DisplayAlerts = False
      'Application.ScreenUpdating = False
  
      Call Populate_Me
      Call Format_Me
  
      'Application.DisplayAlerts = True
      'Application.ScreenUpdating = True
  
  End Sub
  </code></pre>
  
  <pre><code>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 &lt;&gt; "" 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
  </code></pre>
  
  <pre><code>Sub HardCode()
  
      Range("C2").Select
      ActiveCell.FormulaR1C1 = "=BDP($A2,C$1)"
      BloombergUI.RefreshAllStaticData
  
  End Sub
  </code></pre>