VBA: Bloomberg Requesting Data

[nicolehalliday]

Hi everyone,

So I have a macro that first changes the dates in a cell, which in turn changes the values in a range. The values in this range are supported by a Bloomberg add-in and are the historical high price of the day for some stocks. The values in this range change when I change the dates in cell EF1. I then copy the new values and paste them into a table.

The macro runs properly, but when I change the date Excel needs to request the data from Bloomberg, and sometimes it can take a while to load. The speed of my macro is much faster, and so I end up copying the cells while they are loading and it says "#N/A Requesting Data...". I have put some Application.Updates into the macro but it doesn't make a difference. What I believe is happening is that the Bloomberg data can't be recieved while a macro is running... Is that true? Here is my code, any suggestions or ideas would be great, thank you!!!

Sub highs()

Application.ScreenUpdating = False
If Sheets("highs").Range("EE1") = Range("evaldate").Value Then
    MsgBox "Updated"
    
Else
    'enters date into cell
    Sheets("highs").Select
    Range("EF1").Formula = "=WORKDAY(EE1,1)"
       Sheets("lows").Select
    Range("EF1").Formula = "=WORKDAY(EE1,1)"
    
   Sheets("update").Select
   'range of cells is referenced to EF1 in 'highs'
   Application.Calculate
   'selects range of cells and copy/pastes into the main table
   Range("d2").Select
   Application.Calculate
   Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("highs").Select
    Application.Run "BLPLinkReset"
    Range("ef2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'deletes least recent date range (so table has a rollover effect)
    Columns("Z:Z").Select
    Selection.delete Shift:=xlToLeft
    Range("DZ1").Select
        
End If
End Sub