Bloomberg BDH Refresh Via Macro Loop Application.Ontime

**firefly2k8** · 12-10-2009, 09:38 AM

Hi,

Firstly you don't need to know anything about Bloomberg to answer this query, only that Bloomberg has various functions that take time to update.

The code Application.Run "RefreshEntireWorkbook" will update these functions but Application.OnTime (Now + TimeValue("00:00:25")), "RunList" is required to allow them time to get the data from the feed. However this only works if the refresh and wait commands are in a seperate function. So in simplified terms it must go like this in vba:

Sub Main Rountine
  Change bloomberg formula
  Call Refresh Routine
End

Sub Refresh Rountine
  Refresh and Wait
  Call Main Rountine
End

BUT I want to do this operation within a loop (see my code below). So what I want is a way to call the refresh sub-rountine and then go back to where the call was made with the count variable holding the same value as before. Now it is the Runlist part of Application.OnTime (Now + TimeValue("00:00:25")), "RunList", Count that calls back to the main routine and I have tried to send the Count. This is where I am stuck. Basically I want to know how to use Application.Ontime to send the Count Variable and a GoTo instruction,

Cheers,

Martin

---------------------------------------------------------

Sub RefreshSheet(Count)
    Application.Run "RefreshEntireWorkbook"
    Application.OnTime (Now + TimeValue("00:00:25")), "RunList", Count
End Sub
Sub RunListGo()
   RunList (0)
End Sub

Sub RunList(Count)
NoRows = Application.WorksheetFunction.CountA(Sheets("Interface").Range("H9:H65536")) ' get number of lines

Offset = 8 'Input starts on row 8+1=9
Holdtime = 25 'Pause 5sec between counts to allow Bloomberg formulae to update

Do While Count < NoRows
    Range("N6").Value = (NoRows - Count) * Holdtime & "s"
   
    Count = Count + 1
    Cells(Count + Offset, 8).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("C2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Cells(Count + Offset, 9).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Cells(Count + Offset, 10).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("C4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Cells(Count + Offset, 11).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("C5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    RefreshSheet (Count)
        
    Range("C27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Cells(Count + Offset, 12).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("D27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Cells(Count + Offset, 13).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("E27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Cells(Count + Offset, 14).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Loop
        
End Sub

**ossiam** · 08-06-2014, 08:23 AM

I had been working on this (seemingly easy) issue for hours !

Thanks a lot for posting, you made my day !

**firefly2k8** · 12-17-2009, 01:11 PM

I have solved the issue with something like the following.

Public count As Integer
Public limit As Integer
Sub main()
  count = 0
  'set limit
  limit = 4
  'time delay warning
  load
End Sub

Sub load()
   'signal processing and time expectation
   'drop in details
   MsgBox count
   Application.OnTime Now + TimeValue("00:00:05"), "export"
End Sub

Sub export()
    If count < limit Then
      'copy out details
      count = count + 1
      load
    Else
      End
    End If
End Sub