1

i am trying to create an innocuous loop while bloomberg refreshes a large amount of data. After the data refreshes I want that printed (not an issue). The problem is, wait and other VBA delay commands actually stop the data from refreshing for some reason. Seems like an issue w bloomberg.

I'm looking for a simple Do (something?) loop until a range is all numbers (isnumber). I just need the program to keep running until all the data is updated so then it can print and move on. Is there a simple do loop until a range is all numbers I could use? Thanks

jpm351
  • 11
  • 1
  • Is there a status that is returned from Bloomberg when it is finished? Maybe you could just try a do loop n times with DoEvents. Vary n or check for a status? – Wayne G. Dunn Dec 04 '14 at 19:13
  • There is no status or pop up when it finishes, i just need a loop to check that all the refreshing cells have actually returned data. I'm a novice, so any examples would be great. – jpm351 Dec 04 '14 at 20:10
  • Can you post the code you are using to refresh the data? – tospig Dec 04 '14 at 21:49
  • Application.Calculate Application.Run "BLPLinkReset" Application.Calculate Application.ScreenUpdating = False Application.StatusBar = " Processing ticker " & arTickers(i) & " (ticker # " & i & " of " & nTickers & ")...waiting 20 seconds" Application.Wait (Now + TimeValue("0:00:20")) – jpm351 Dec 04 '14 at 22:20
  • wait does not work, it stops the link reset – jpm351 Dec 04 '14 at 22:21
  • Do AllNumbers = True Dim Data As Range Set example = Data("AG3:Av200") For Each c In Range(Data) If Not IsNumeric(c.Value2) And Len(Trim(c.Value2)) Then AllNumbers = False End If Next c Loop Until AllNumbers = True – jpm351 Dec 04 '14 at 23:09
  • Thanks Rodge, i think this is what I need, but I get an error on above, "object variable or with block variable not set. What did i miss? – jpm351 Dec 04 '14 at 23:10
  • http://www.vonixx.com/tips/tips34.htm – Larry Dec 05 '14 at 03:40
  • I will paypal someone $100 if they can fix this. Larry, your solution might be correct, but can't get it quite right. – jpm351 Dec 10 '14 at 21:21

1 Answers1

1

There are two options I know of,

One is to loop through each cell in the range, check if it's numeric, and if no, continue containing Do loop:

Do
AllNumbers = True
For each c in Range(Range of what you want to be all numbers, ideally dynamic named range)
   If Not IsNumeric(c.Value2) And Len(Trim(c.Value2)) Then
       AllNumbers = False
   End If
Next c
Loop Until AllNumbers = True

The other is to execute an Excel formula inside VBA like so:

Do
   ActiveWorkbook.Names.Add Name:="blahblahblah",RefersToR1C1:="=SUM(IF(NOT(ISNUMBER(Sheet1!R1C1:R9C2)),1,0))"
Loop Until Application.Evaluate("blahblahblah") = 0
ActiveWorkbook.Names("blahblahblah").Delete

Be careful though, both of these could create infinite loops if the Bloomberg data didn't ever retrieve or if it did but there was some non-numeric data in it.

SirRodge
  • 594
  • 5
  • 8