1

I am trying to grab RTD output in VBA. The problem is it takes a couple seconds sometimes for the data to come back so when I run a loop half the time it incorrectly returns 0. Apparently RTD is running asynchronously. How can I set it to run synchronously so it waits for a correct output?

If I put the formula in a cell it usually returns instantly sometimes it takes a second. If I try to loop in VBA it goes to the next value too quickly and returns false positives because it is not waiting for the data to return. I have tried RTD.RefreshData and it is not loading fast enough to keep up with my loop. (My goal is to loop through thousands of fields using RTD which is why I need to do this)

CodeCamper
  • 6,609
  • 6
  • 44
  • 94
  • There are a couple of Google things floating around on this: http://stackoverflow.com/questions/12856979/how-to-refresh-load-rtd-bloomberg-function-bdh-in-excel-in-vba – Chrismas007 Nov 26 '14 at 15:51
  • @Chrismas007 I have read all of them, I am getting some ideas, but I haven't found a link with a real answer to this question. I am thinking of a few hacky workarounds but the issue here is speed. The reason I need to do it in VBA is specifically because I want to loop thousands of fields. – CodeCamper Nov 26 '14 at 16:12

1 Answers1

1

What about waiting in loop for condition to be met:

Do Until Cells(1, 1) <> "" ' use plausible condition
    Application.Wait (Now + TimeValue("0:00:01"))
Loop
Radek
  • 241
  • 2
  • 6
  • If he has 1000's this would be the last resort. – Automate This Nov 26 '14 at 18:20
  • @PortlandRunner Exactly, 1 second will take too long. I really am cycling through thousands of fields. Sometimes it is only 100 but other times it has been as high as 10,000. It is a dynamic system I am building. Sadly I have no access to the database itself and only have access via RTD no API is available to me. So it would be nice if there was some hack around. Can I somehow override IRTDUpdateEvent to accomplish this? I am not even sure. At this point I am thinking of putting one RTD formula in 1 sheet and just detecting the change and throwing out the blanks. *Crosses fingers* – CodeCamper Nov 26 '14 at 21:25