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]

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

The variable Time_loop in this code is an uninitialized variable. It has no value, so it throws an error when you attempt to use it in the statement. You can probably omit it from the statement altogether.

 

[Mitch_Morgan]

I removed Time_loop and replaced with Now() + TimeValue("00:00:01") in both Subs but stil have the same error

 

[JLGWhiz]

I removed Time_loop and replaced with Now() + TimeValue("00:00:01") in both Subs but stil have the same error

You need it in the first sub, you don't need it in the second sub.
Try the second sub like this:

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

 

[Mitch_Morgan]

Getting closer but this time I got a Compile error: Argument not optional. When I hit the Debug it points tp Public Sub StopTimer()

 

[JLGWhiz]

Well, it has been quite a while shince I used OnTime, so I did a look back and it seems you do need the variable in both codes. Try it like this.

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

Public Sub StopTimer()  
    Dim Time_loop
    Time_loop = Now() + TimeValue("00:00:01")
    Application.OnTime Time_loop, "Timer", schedule:=False
End Sub

 

[Mitch_Morgan]

IS there something better than using OnTime, I used that code and I went back to the original error message

 

[JLGWhiz]

IS there something better than using OnTime, I used that code and I went back to the original error message

Actually, this works very well when you get et set up right. Like I said, it has been a while since I last used it, and I have not really tested any of this code. Just doing what I think might work. I will take a closer look and see if I can bet it to work in a test set up. If I do, I will post back to this thread.

 

[Mitch_Morgan]

Actually, this works very well when you get et set up right. Like I said, it has been a while since I last used it, and I have not really tested any of this code. Just doing what I think might work. I will take a closer look and see if I can bet it to work in a test set up. If I do, I will post back to this thread.

Thanks so much, this has been driving me crazy for about six days

 

[JLGWhiz]

I tried it like this and still could not get it to work without throwing the error. I have checked and rechecked the syntax and it is not the syntax that is the problem. It does not like something about the time. I have tried several thinks related to the time and still get the same OnTime object failed message. But the basic code to loop the '"Main" macro works like it should. You can always use the Crrl + Break keys to stop it. Sorry I could not be more help.

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

Sub StopTimer()
    Application.OnTime Now + TimeValue("00:00:03"), "Timer", schedule:=False
End Sub

Sub Main()
MsgBox "OK"
Timer
End Sub