Application.Wait information request

[Michelle]

Hello all!

I have a question about the Wait function in VBA. I currently am using it
to pause my macro before continuing through a do/ while loop. In this loop
I'm checking a cell value. In this cell is an add-in function from
Bloomberg. When I first enter the function the end result doesn't appear
right away. Instead a "requesting data" value shows in the cell first. It
takes a while before the actual answer appears. In the do/ while loop I keep
checking the value over and over again because I can't do the next step until
the true value shows. My question is if I have the application wait will it
also stop the function from being able to pull the data from Bloomberg by
pausing the connection with it as well? I only ask because I can't seem to
get a value past the initial "requesting data" output, and I can if I simply
run the function in a cell without using the macro. (I'm really hoping this
all makes sense.) I have looked all over these discussion groups, and can't
quite come up with an answer. When I looked at the msdn site I found this
answer, "However, background processes such as printing and recalculation
continue," but am uncertain if this pertains to functions that are add in's
from another software.

I originally had the do/ while loop continually running without a pause, but
I couldn't seem to get past the "requesting data" doing that either. If I
just continually run the loop will Excel still be also continually accessing
Bloomberg or will it pause that?

Any help would be greatly appreciated. If you have any questions regarding
this don't hesitate to post. I think this might be a convoluted question.
Thank you in advance.

 

[JLGWhiz]

You could try this. It allows everything else to run while its macro is
paused.

Public Function TwoSecDly()
s = Timer + 2
Do While Timer < s
DoEvents
Loop
End Function

Paste this in the top of your module and call it by simply typing TwoSecDly
as a line in your code, no quote marks. You can set it for as many seconds
as you need or even tenths of seconds. Just change the 2 to the desired
number. To be consistent, you might want to change to macro name accordingly.