Excel Bloomberg API callback in macro

Question

I have a cell utilizing a Bloomberg API function and looping through inputs using a macro, but I want the loop to wait for the Bloomberg function to produce a result before proceeding (basically a callback). Is there a straightforward way of accomplishing this?

Answer 1

You can try something like this:

Sub Main()

'Write API Formulas
Range("B1:C10000").Formula = "=bdp(""MSFT Equity"",""SECURITY_NAME"")"

'Check to see if it filled
Call Check_API

End Sub


Sub Check_API()

If Application.WorksheetFunction.CountIfs(Range("B1:C10000"), "#N/A Requesting Data...") > 0 Then
    'Check every 3 seconds
    Application.OnTime Now + TimeValue("00:00:03"), "Check_API"
Else
    'Do what you want after api has loaded
    MsgBox "Done"
End If

End Sub