I am trying to write a simple VBA in excel to run through a loop of stock tickers and update certain data from Bloomberg API, but the code seems not to be working appropiately.
Sub RunMacroForDropdown()
Dim r As Range
For Each r In Worksheets("Selector").Range("B6:B42")
Range("B1").Value = r.Value
RefreshData
Next r
End Sub
Sub RefreshData()
Application.Run "RefreshEntireWorkbook"
Application.Run "RefreshAllStaticData"
'Wait until it Downloads the information
Application.OnTime (Now + TimeValue("00:00:03")), "ProcessData"
End Sub
Sub SaveWorkbook()
Dim FileName As String
Dim Path As String
Application.DisplayAlerts = False
Path = "\ClientC$CitrixExchange"
FileName = Range("B1").Value & ".xlsm"
ActiveWorkbook.SaveCopyAs Path & FileName
Application.DisplayAlerts = True
End Sub
Sub ProcessData()
MsgBox ("Processing Data for" & Range("B1").Value)
'Wait for a while until it processes data
Application.Wait (Now + TimeValue("00:00:03"))
ActiveWorkbook.RefreshAll
SaveWorkbook
End Sub
When debugging, the OnTime process returns to the ‘Next r’ in the RunMacroForDropdown, and does not enter the ProcessData function.
Thanks for your help!
Imanol Landa is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
When you call RefreshData in the first sub, it schedules a run of ProcessData in 3 seconds and returns immediately. Your sub then continues to loop.
Your best option would be to use the VBA API and call the BB API synchronously from VBA instead of waiting for the asynchronous data population in the spreadsheet.
But if you want to stick to your approach, you should only run refreshData for the first cell. Once ProcessData completes, you can rerun refreshData for the next cell etc.
Also note that waiting 3 seconds may or may not work, so in ProcessData you should check if the data has updated, and if not use an OnTime to run again a bit later.