ontime code

[Steve]

Morning all.
I'm interested in doing an "autorun" macro that will run every few minutes,
to every couple of hours.
I've looked around here on the newsgroup, and the various forums, and found
some examples, but in the application of them, I don't seem to get what I
want. See code....

Sub AutoRunMe()
application.ontime now + timeserial(0, 0, 10), "MyMacro"
end sub

In my reading, it states that the above code will run every 10 seconds,
following an initial start time of "now."

If however I do this:

application.ontime timeserial( 0, 0, 10), "MyMacro"

it'll only run at 10 seconds past midnight.

I also tried now + timevalue("00:00:10"); it's not repeating every 10
seconds either.


What I'm finding is that the first one does not run every ten seconds-- I
tried it just to make sure it'll do what I need-- and I cannot find anything
else that will allow me to run the macro every few minutes or hours, as I
decide.

I realize that I don't entirely understand it, so I wanted to ask-- how do I
create a macro that'll run every few minutes or so?

Thank you.

 

[Jacob Skaria]

Hi Steve

Insert a module and paste the below procedures and try.

Sub AutoRunMe()
Application.OnTime Now + TimeSerial(0, 0, 10), "MyMacro"
End Sub

Sub MyMacro()
MsgBox Now
Call AutoRunMe
End Sub

If this post helps click Yes

 

[Steve]

Thanks Jacob.

I found something that Chip Pearson had made a while back too, and that did
the trick.

 

[Chip Pearson]

Sub AutoRunMe()
Application.OnTime Now + TimeSerial(0, 0, 10), "MyMacro"
End Sub

Sub MyMacro()
MsgBox Now
Call AutoRunMe
End Sub

That code isn't a good idea, because it doesn't allow you to cancel
the pending on time event. The timer rescheduling will turn off only
if you completely quit Excel. Closing the workbook isn't enough -Excel
will reopen it when the timer pops.

Instead, create a module level variable and assign to it the value
when the code should run:

Public RunWhen As Double

Sub RunMe()
' code
RunWhen = Now + TimeSerial(0, 0, 10)
Application.OnTime RunWhen, "RunMe", , True
End Sub

To cancel a timer, you must specify the *exact* time that it is
schedule to pop. You can do this only if you have stored the time
value in a module-scope variable.

Sub StopTimer()
Application.OnTime RunWhen, "RunMe", , False
End Sub

I have extensive notes about OnTime at
http://www.cpearson.com/excel/OnTime.aspx

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Jacob Skaria]

Thanks Steve/Chip

You can adjust the code to run a number of iterations or upto a a certain
time keeping Ontime within a loop and specifying the iterations or the end
time as a global variable.

If this post helps click Yes