vba Appliation.onTime Running twice

[davewatson86]

Morning/Evening all

i have the below code set to update the data in a file every 5 min but it is running the Update sub twice one immediately after the other. i do not have the code (Application.OnTime mTimerTime, "my_Procedure") anywhere else in any other subs so im a bit stumped.

where am i going wrong?

Public Sub timer_on()


    WS_Summary.Range("f21").Value = "On"
    timer_off
    my_Procedure


End Sub


Public Sub my_Procedure()


   ' Do timer work here
   Call update


   ' Reset the timer
   mTimerTime = Now() + TimeSerial(0, 5 - Minute(Now()) Mod 5 - 1, 60 - Second(Now()) Mod 60 + 1)
   Application.OnTime mTimerTime, "my_Procedure"


End Sub


Public Sub timer_off()


   On Error Resume Next
   Application.OnTime mTimerTime, "my_Procedure", Schedule:=False
   WS_Summary.Range("f21").Value = "Off"
   On Error GoTo 0


End Sub

thank you in advance
Dave

 

[MoshiM]

Morning/Evening all

i have the below code set to update the data in a file every 5 min but it is running the Update sub twice one immediately after the other. i do not have the code (Application.OnTime mTimerTime, "my_Procedure") anywhere else in any other subs so im a bit stumped.

where am i going wrong?


thank you in advance
Dave

Is Timer_off or on or myProcedure called from anywhere else? Is mTimerTime a public variable inside "thisworkbook"? You should consider storing the value either in such a variable or on a sheet. Also five minutes from now can just be written as Now + Timeserial(0,5,0).

 

[DanteAmor]

Add the declaration of the mTimerTime variable to the beginning of the code.
I guess you start with the timer_on macro.


To stop the execution you use timer_off. It is not necessary On Error Resume Next, In my tests it works without On Error Resume Next, so the line is disabled, but if you have problems then enable the line.

Dim mTimerTime


Public Sub timer_on()
    WS_Summary.Range("f21").Value = "On"
    my_Procedure
End Sub


Public Sub my_Procedure()
   ' Do timer work here
   Call update
   ' Reset the timer
   mTimerTime = Now() + TimeSerial(0, 5, 0)
   Application.OnTime mTimerTime, "my_Procedure"
End Sub


Public Sub timer_off()
   'On Error Resume Next
   Application.OnTime mTimerTime, "my_Procedure", Schedule:=False
   WS_Summary.Range("f21").Value = "Off"
   'On Error GoTo 0
End Sub

Try and tell me.

 

[joeu2004]

@dave.... If you close the Excel application, then restart it and execute timer_on just once, I think you will see that your code runs as intended.

The problem is: since mTimerTime is a local variable in timer_off and initialized to zero, the OnTime Schedule:=False fails to stop any currently scheduled timer.

So if you manually run timer_off, then re-run timer_on, you will have two timers running, possibly scheduled to expire at the same time.

The solution is: declare Dim mTimerTime as global variable at the top of the module.

Caveat: If VBA is "reset", either by you manually or sometimes VBA does it automatically (!), even the global variable will be reset to zero, and timer_off will not abort a running timer as expected. (Sigh, VBA reset does not stop all timers.)

-----
@moshi.... Simply doing Now+TimerSerial(0,5,0) is not a correct alternative. Dave's method starts and reschedules the time one sec after each 5-min epoch (00, 05, 10, 15,..., 50, 55), not literally every 5 min.

-----
@Dante.... On Error Resume Next is absolutely necessary and prudent.

It is necessary when that timer is not yet running and when mTimerTime is zero for any other reason.

Usually, no timer is running when timer_on is executed, because either the workbook was just opened (after restarting Excel) or timer_off had been executed successfully before.

As I noted, even the global variable mTimerTime might be unintentionally zero if VBA reset is performed, either manually or automatically.

(I am not sure of the conditions under which VBA reset is performed automatically. Certain edits in VBA cause that to happen, especially when a VBA procedure is excecuting.)

OTOH, On Error Then GoTo 0 is superfluous in this context, since we are exiting the procedure immediately afterwards. But there is no harm, no foul. Some people argue for doing it as a matter of "good form", just in case code is added below later.

 

[joeu2004]

Dave's method starts and reschedules the time[r] one sec after each 5-min epoch (00, 05, 10, 15,..., 50, 55), not literally every 5 min.

More correctly, after the next 5-min epoch (plus one sec).

 

[joeu2004]

On Error Resume Next is absolutely necessary and prudent. It is necessary when that timer is not yet running and when mTimerTime is zero for any other reason.

Also, there is a (unlikely) race condition wherein the timer might expire while we are executing timer_off, but just before we execute the OnTime Schedule:=False statement.

If that happens, even the global mTimerTime value will no longer matches a scheduled timer, causing OnTime Schedule:=False to raise an error condition.

It is easy to demonstrate by putting a breakpoint on the first executable statement in timer_off, then executing it after running timer_on and before the next 5-min epoch.

Sit at the breakpoint until after the 5-min epoch (plus 1 sec). VBA will display an error that code -- the OnTime procedure -- cannot be executed in break mode. Then continue execution of timer_off (press f5).

Without On Error Resume Next, we get a runtime error. With On Error Resume Next, the VBA variable Err is set to non-zero.

 

[DanteAmor]

-----
@DanteAmor.... On Error Resume Next is absolutely necessary and prudent.

Hi @joeu2004,
That is not quite right.
In my tests with the code indicated in post #3 it is not necessary On Error Resume Next.


However, I made the clarification, because in some versions or because I really don't know what it has in the update code. That can cause the value of the mTimerTime variable to reset.

---------------------
Note:
A small detail, if you are going to mention a person, you must put the full name: DanteAmor.

 

[davewatson86]

Thank you all for your reply's.

i have changed the mtimertime to a global as @joeu2004 suggested and so far everything seems to me working as expected.

Regards
Dave

 

[joeu2004]

i have changed the mtimertime to a global as @joeu2004 suggested and so far everything seems to me working as expected

Actually, DanteAmor suggested it first. I merely provided some explanation, and I corrected some mistaken suggestions.

 

[DanteAmor]

Actually, DanteAmor suggested it first. I merely provided some explanation, and I corrected some mistaken suggestions.

Thanks Joeu for the clarification, the important thing is that it works for the OP. Good luck