VBA: Waiting for Bloomberg BDP calls to finish

4
votes

I have a script that imports some external data into the worksheet, which in turn affects some =BDP(...) formulas. Optimally, I'd like to do some checks on the BDP results immidiately after copying the data.

The Bloomberg Excel Add-in updates asynchronously - how do I wait for the results and then resume the script? It seems that the results are only imported after the VBA script finishes, no matter how long it runs.

Thanks in advance Martin

1
vbaexcelbloomberg

1 Answers

7
votes

I built something similar using BDH. I had to release control so that the Bloomberg add-in can go and collect the data, then resume my code. I was able to do this using Application.OnTime.

For example, if you have a 'CheckForData' function that affects the =BDP calls, and another function called 'ProcessData' that checks the results, make an aynchronous call to to 'ProcessData' within 'CheckForData', e.g.

Sub CheckForData
  ' Your code here
  Application.OnTime Now + TimeValue("00:00:05"), "ProcessData"
End Sub

Then within 'ProcessData' perform your checks.