OnTime statement problems 1

[LSIGuy]

For some reason, when I put the command...

Application.OnTime Now + TimeValue("00:30:00&quot, "Runme"

into my program, it can not find the Runme macro. This macro is not visable in the the run menu, but it is there. I am attempting to use this function to have a program run endlessly every 30 minutes. Could it be a problem that this statement lies within the Runme macro?

Please help!!

 

[xlbo]

Not quite sure I understand you. Are you saying that the line of code you posted sits within the macro called Runme ??
Rgds
Geoff

 

[LSIGuy]

Yeah, the line of code above sits within the macro Runme()

 

[xlbo]

Well, AFAIK, you can't call a macro from itself - you need to have a reference point to go to eg
Sub Runme()
restart:
code goes here
----
---
---
---
---
Application.OnTime Now + TimeValue("00:30:00&quot, goto restart
End sub
HTH
Geoff HTH
~Geoff~

 

[uberpudge]

Hello LSIGuy,

When I use OnTime and it cannot find the macro I want to run (Sub or Function - it doesn't matter, you can run both with OnTime), all I usually have to do is fully qualify the reference to the Sub or Function.

For example:
Application.OnTime Now + TimeValue("00:30:00&quot, "Module1.RunMeEvery30Minutes"

Also, you can call a macro from itself.
The process is called recursion.
If you include the exact same line in the "RunMeEvery30Minutes" macro, it will schedule itself to be run.

Also, I'm assuming this is a VBA project. If this is the case, you may want to include something similiar to the following line in the document close event.

Application.OnTime strNextRun, , , Schedule:=False

where strNextRun is the string representation of the next scheduled time to run (you may have to make it a public variable). This will cancel the RunMe macro that was scheduled for the future.

By doing this, the host application will not re-open your project just to run the macro.

Hope this helps,
Pete

 

[xlbo]

Pete (Uberpudge) - coool - u have taught me summat new - which is odd for a friday. Thx - have a star
Geoff HTH
~Geoff~

 

[rmikesmith]

Pete,

I just want to throw something else out here. Isn't there a potential problem setting this up as an open-ended recursive procedure; i.e. running out of stack space, since there doesn't appear to be an exit condition? Any thoughts?

M. Smith

 

[Guest_imported]

There should be no problem with stack space as OnTime starts a seperately threaded timer object thus allowing the runme procedure to exit. Also, as far as I know Excel only supports one background timer per application so a second call to OnTime removes the previous timer.