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.

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx

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.


"Michelle" wrote:

> 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.
>
> --
> Cheers,
> Michelle
> "Anyone who says he can see through women is missing a lot." Groucho Marx