Macro Time Delay

[GeneralShamu]

I am trying to create a macro that will need to run a list of Tickers through a model with many other macros. To do this I created a for loop that works properly but before anything cna be run there must be a time delay. The model needs about 20 seconds for all Bloomberg data to be populated. To try and solve this I entered the following line of code:

Application.Wait (Now + TimeValue("00:00:20"))

but this seemed to only stop the macro and everything else from running...so no data could be populated.

To fix this I cahnged the code to:

Application.OnTime Now + TimeValue("00:00:20"), "CopyStockInfoDynamicToStatic"

The name in " " is just the name of the macro. When I run the macro with this line of code however, it seems to go passed this line and wait 20 seconds before it runs that macro.

How do I get my macro to stop for 20 seconds, let Excel continue to populate data from Bloomberg, and than, after 20 seconds, run the CopyStock... macro?

 

[Jonmo1]

I'm assuming your Bloomberg Data is a Web Query right?

You could right click the query that get's the data, Data Range Properties, and Uncheck Enable BackGround Refresh.

This will make everything else wait until the query is completed.

 

[HalfAce]

Hello GeneralShamu, welcome to the board.
I do exactly what you're asking about quite often every day.
(Waiting for PLC updates before continuing the code.)
The way I handle this is:
Run all the code you want to run before your 20 second waiting period as a separate macro
and have the very last line of that macro be:
Application.OnTime Now + TimeValue("00:00:20"), "CopyStockInfoDynamicToStatic"
Then have all the code you want to run after the waiting/updating either be one continuous
macro, or if you like, have each separate macro call the next.

Does that help?

 

[GeneralShamu]

Your idea helps HalfAce but see all this is enclosed in a for loop and I do not know how to go about properly changing it...

 

[HalfAce]

Hi General,
It's hard to come up with a meaningful example without knowing the code in all those other
routines being called by this one, but my guess is you should be able to use a public variable
to get it all done.
I'm thinking something like, instead of running all these through a loop, using the public
variable to deterine how many times you want it to run (one at a time) and reset the
value of your variable after each running, using an If statement at the beginning to
determine the value of the variable.
I imagine it would require re-writing some of your routine, but it seems to me it could be done.
I don't know if I'm explaining it well. (Makes sense to me but then I already know
what I mean.)