I am trying to extract bloomberg data into excel using excel vba. I have a list of tickers which I copy paste in another sheet (one ticker at a time), extract data related to that ticker, do some mathematical operations and then paste the result in another sheet. This process is repeated for the next ticker and so on using a for loop.
The problem is that the for loop runs ahead even before bloomberg can refresh the data. I tried inserting pause command in vba code but though that pauses the code, it doesn't result into refreshing the excel to extract bloomberg data. What command to pass in excel vba for bloomberg data to get refreshed and only then the code to move ahead.
Badly stuck on this. Bloomberg helpdesk says that they don't give support for excel vba queries.
The reason you face this problem is that bloomberg data fetch functions are asynchronous. The vba code will move forward after requesting the data, instead of blocking for the data fetch to complete. I do not think bloomberg API has any support for callbacks for vba. One thing you can do is to use the for loop to request data as you do, and then create a separate polling loop in a different sub. That will periodically check the cell where you triggered the data fetch to see if the operation is complete. Typically, the cell will have "#N/A Requesting Data…" instead of a number if the operation is not complete. Once the cells are all filled with number, you can proceed to your calculation part.