Starting and stopping code

[Mitch_Morgan]

I am new to VBA coding and I have read all the threads that seem to cover this but I am doing something wrong. I have built a time loop simulation that I would like to start with a command button and stop. The simulation code all resides in one module "Main". To create the loop I have the following

Sub Timer()
    Dim Time_loop
    Time_loop = Now() + TimeValue("00:00:01")
    Application.OnTime Time_loop, "Main"


End Sub

The last line in the last Sub I have Timer to cause it to loop. This works fine. I have inserted a form command button and assigned the macro "Timer".

The stop code is

Public Sub StopTimer()
    Application.OnTime Time_loop, "Timer", schedule:=False
End Sub

I receive a Run-time error '1004' Method 'OnTime of object'_Application Failed

I have read this same error message occuring in other threads but the fixes I have tried do not work

 

[JLGWhiz]

OK, got the answer from one of the Great Gurus of VBA, Chip Pearson, now deceased.

To use the Schedule:=False argument to cancel an OnTime
procedure, you must provide OnTime with the *exact* time that the
event was scheduled. See www.cpearson.com/excel/ontime.htm for
details and example code.

since your procedure uses a non specific and varying time as the Earliest Time, you cannot use the Schedule:= False to kill your macro.

 

[Mitch_Morgan]

OK, got the answer from one of the Great Gurus of VBA, Chip Pearson, now deceased.



since your procedure uses a non specific and varying time as the Earliest Time, you cannot use the Schedule:= False to kill your macro.

I really appreciate your help on this. At least I know what I can't use and that is a help

 

[JLGWhiz]

OK, Got it!

We have been using the wrong procedure name in the Schedule:=False statement. Amazing how this stuff works. Try it like this:
Note that the Time_Loop variable is declared as a public variable at the top of the code module.

Public Time_Loop As Double

Sub Timer()
    Time_Loop = Now + TimeValue("00:00:03")
    Application.OnTime Time_Loop, "Main"
    'Application.OnTime Time_Loop, "Main", Schedule:=False
End Sub

Sub StopTimer()
    Application.OnTime Time_Loop, "Main", Schedule:=False
End Sub

Sub Main()
MsgBox "OK"
Timer
End Sub

Here is a link to Chip's web site where you can read everything for yourself. Pearson Software Consulting (cpearson.com)

BTW, he has articles on just about everything that Excel does.

 

[Mitch_Morgan]

THAT ABSOLUTELY DID IT!!!! Thank you for your persistence in this. Truly great!!!

 

[JLGWhiz]

THAT ABSOLUTELY DID IT!!!! Thank you for your persistence in this. Truly great!!!

You're welcome, helped refresh my understanding also.
regards, JLG