Application.OnTime not being followed properly

[Andy2016]

Hello,

Do any of you know a solution for the following:-

If you have an application.ontime statement that calls another procedure, and then the next statement in the same sub is calling a procedure, VBA ignores the application.ontime delay and starts running the next statement before the application.ontime statement has finished. I have some example code below to illustrate this.

So in this example when I run macro1, VBA starts macro3 before macro 2 has finished. So effectively the sequence is macro1,macro3,macro2 rather than the desired macro1, 5 second delay and then macro2, macro3. Effectively VBA runs macro 3 whilst the 5 second delay is running and then runs macro2

Now I know I could call macro3 from macro2. And that would give me the desired order, but for more sophisticated structured programs this is not an acceptable solution. I have tried using application.enableevents thinking this might be the solution but to no success so far.

Please can anyone help?

Sub macro1() 
Dim n As Integer


n = n + 1


Application.OnTime Now + TimeValue("00:00:05"), "macro2"


Call macro3




End Sub


Sub macro2()


Dim n As Integer


n = n + 1


MsgBox ("macro2")


End Sub
Sub macro3()


Dim n As Integer


n = n + 1


MsgBox ("Macro 3")
End Sub

 

[steve the fish]

How about application.wait?

Sub macro1()
Dim n As Integer

n = n + 1

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

macro2
macro3

End Sub

 

[Andy2016]

Hi Steve

Thanks for the reply but that doesnt work, as application.wait also prevents the other data from populating (this is the data that needs a timer to allow it to populate).

I also tried

Do Until Now > endtime
DoEvents
Loop


But this also prevents the data populating.

The data is Bloomberg data if that helps other people who work in finance and have these issues

 

[steve the fish]

Cant see anything that needs populating from what you have given?

 

[Andy2016]

No i was trying to keep it simple. In reality Macro2 would be doing something with the Bloomberg API to draw data into a spreadsheet

 

[joeu2004]

If you have an application.ontime statement that calls another procedure, and then the next statement in the same sub is calling a procedure, VBA ignores the application.ontime delay and starts running the next statement before the application.ontime statement has finished. I have some example code below to illustrate this.

So in this example when I run macro1, VBA starts macro3 before macro 2 has finished. So effectively the sequence is macro1,macro3,macro2 rather than the desired macro1, 5 second delay and then macro2, macro3.
[....]

Sub macro1() 
[....]
Application.OnTime Now + TimeValue("00:00:05"), "macro2"
Call macro3
End Sub
[....]

Application.OnTime simply schedules macro2 to execute in 5 sec. But macro2 will not execute until you exit VBA code.

Then VBA continues execution of macro1 with the next statement. In this case, it calls macro3.

If you want to ensure that macro3 executes after macro2, yes, macro2 should call macro3.

Alternatively, perhaps you want to schedule macro3 to execute later as well. For example:

Application.OnTime Now + TimeSerial(0,0,5), "macro2"
Application.OnTime Now + TimeSerial(0,0,6), "macro3"

Caveat: If you use Now+TimeSerial(0,0,5) for both Application.OnTime statements, I'm not sure the order is guaranteed. In one experiment, macro3 executed before macro2.