The OnTime Method
The OnTime method can be used to schedule a procedure to run at a later time. The syntax of this method, from help, is
Application.OnTime(EarliestTime, Procedure, LatestTime, Schedule)
Generally, the EarliestTime argument is set to a certain number of minutes or seconds from now. This line causes the procedure named MyProc to run in 5 minutes.
Application.OnTime Now + TimeSerial(0, 5, 0), “MyProc”
Notice how the argument isn’t called ExactTime. MyProc will run no earlier than EarliestTime, but that’s no assurance that it will start then, such as when another macro is running. That’s actually beneficial, because you can use OnTime to run directly after the current procedure, like this:
Application.OnTime Now, “MyProc”
Now is the earliest it will run, so as soon as the current procedure is finished, it will run. Why not just call it directly? Well, sometimes you have to trick Excel into doing what you want, like when you need to get around a bug. Other times, like hooking commandbarbutton events, you need the user to do whatever it is they’re going to do before your procedure runs.
The Schedule argument determines whether you’re setting up a procedure to run or cancelling a previously scheduled procedure. The default is True (setting up a procedure to run) so it’s usually omitted in that instance. To cancel a scheduled procedure, you have to know the exact time that it’s scheduled (its EarliestTime argument). For that reason, if you will potentially need to cancel an OnTime procedure, it’s best to store the EarliestTime in a variable that you can access to cancel it.
Dim dEtime As Date
dEtime = Now + TimeSerial(1, 1, 5)
Application.OnTime dEtime, “MyProc”
‘If something happens and you need to cancel
Application.OnTime dEtime, “MyProc”, , False
‘End If
Andy Miller:
People in forums often ask (especially when coming from VB to VBA) where the Timer control is in Excel VBA. I normally stear them toward the Application.OnTime procedure, but sometimes it seems to not work as expected. I wonder if it’s because, as you mentioned, some other processes were still running and it was waiting its turn.
30 July 2004, 3:12 pmColo:
With regards to Application.OnTime Now, as Dick wrote it will run, so as soon as the current procedure is finished.
It’s an interesting method. The following code would Reboot the current workbook.
Sub RebootMe()
Application.OnTime Now, “OpenMe”
ThisWorkbook.Close SaveChanges:=False
End Sub
Private Sub OpenMe()
31 July 2004, 12:02 amWorkbooks.Open ThisWorkbook.FullName
End Sub
Chuck:
I am trying to print an Excel worksheet at midnight each night. I need to leave the workbook open all the time because it is pulling OPC data from another application.
My script works the first time I call OnTime(), how do I get it to run again for the next day.
Here is what I have so far.
Sub OnTime()
Application.OnTime (#12:01:00 AM#), “Print”
End Sub
Sub Print()
Workbooks(”Test.xls”).Worksheets(”Sheet1″).Printout
Call OnTime
End Sub
26 August 2004, 9:10 amHomer:
Chuck,
The trick is to write a subroutine that activates itself each time. This subroutine will restart each second:
Option Explicit
Dim NextTick
Sub update()
‘ Call your application here
‘ Set up the next event one second from now
NextTick = Now + TimeValue(”00:00:01″)
Application.OnTime NextTick, “update”
End Sub
Getting it to shut up:
Sub StopChart()
‘ Cancels the OnTime event (stops the clock)
On Error Resume Next
Application.OnTime NextTick, “update”, , False
End Sub
13 October 2004, 9:59 amAlice:
I need help about the application on time.
27 May 2005, 11:13 amI need to run a formula every last day of the month
Is there anyway to make this run without opening the Excel.
Thanks
Alice
ross:
without opening excel? i do think so. Look at task Schedules within windows. - maybe with a on open even in you ss, and some time/date cheching just in case?
28 May 2005, 3:32 pmGareth:
Am having truoble stopping a procedure once started. here is what i have so far.
Sub StartTimer()
Application.OnTime Now, “Update”
End Sub
Sub EndTimer()
Application.OnTime EarliestTime:=Now, _
Procedure:=”Update”, Schedule:=False
End Sub
Sub Update()
9 September 2005, 4:29 amApplication.Workbooks(1).Worksheets(1).Cells(6, 15).Value = Application.Workbooks(1).Worksheets(1).Cells(6, 15).Value + 1
Application.OnTime Now + TimeValue(”00:00:01″), “Update”
End Sub
Rob van Gelder:
Gareth,
When you run EndTimer, the time you supply should be the exact same time that you supplied to StartTimer.
What you are doing is supplying Now() to both, which will be different by the time EndTimer runs.
You’ll need store Now() in a global variable and use that for both StartTimer and EndTimer.
I have an example on my website: Schedule a macro to run.
Cheers,
9 September 2005, 3:48 pmRob
Max:
Can you specify an argument after the procedure?
18 November 2005, 5:10 amThank you!
sam:
I assigned a ontime macro to a shape, it will run normally once shape is clicked. but if click many
24 November 2005, 7:42 amtimes on the shape,then the scheduled time become uncontrollable ! Anyone knows how to prevent this from
happening?
Mustafa:
Hi
I am using application.ontime function but I have a problem.
When I schedule an event it starts the Procedute more then once. Sometimes it doesnt stop and again and again restarts it.
Can ıt be because of the reloading of Form that the application.ontime function code is written..Lets say the code is in MAİNMENU so after the macro is started the MainMenu.Frm is accessed lots of times and may be it is scheduling the same Procedure everytime the MainMenu is started? THis i silly but I cant really figure out why it doesnt function properly.
Private Sub UserForm_Activate()
If seansuyarı = 1 Then Application.OnTime TimeValue(”09:29:00″), “seans1″
End sub
10 December 2005, 10:00 amdusc:
Just a note on that logic:
Private Sub UserForm_Activate()
If seansuyarı = 1 Then Application.OnTime TimeValue(”09:29:00″), “seans1″
End sub
TimeValue() will return a value of that time, with the date of Jan 01, 1900. Every time you call this, you’re scheduling a task to run over a century ago. From what you’ve said, I’m guessing it’s just queueing them up, and trying to execute them sequentially.
It may sounds trite, but most of the issues on this thread are RTFM material.
7 April 2006, 9:52 pmTushar Mehta:
It’s been a while since Max posed the question about parameters to the procedure specified in the OnTime method.
See
8 April 2006, 6:40 amUsing procedures with arguments in non-obvious instances:
macros associated with shapes, forms, and commandbar elements, and called by the OnTime and OnKey methods
http://www.tushar-mehta.com/excel/vba/xl%20objects%20and%20procedures%20with%20arguments.htm
Byron:
Is there a way to use OnTime to schedule a procedure with passing parameters? For example: MyProc(Counter).
24 April 2006, 1:56 pmMasum:
Any reply to Byron’s email?
Tushar, i had a look at your website (and a very good one it is, too), but it only mentions passing static arguments, eg, you can only call myProc(7) rather than myProc(Counter) where Counter = 7
23 August 2006, 5:33 amTushar Mehta:
Byron, Masum: As I wrote in my reply to Masum’s email query, no, I don’t believe so. At the time it processes the OnAction specification, Excel doesn’t know anything about the VBA variables you may have declared.
23 August 2006, 4:25 pmksm:
this pasge was kinda idle…hopefully i’ll get an answer..
im tryin to use the on time method…
it simply doesn;t work.
the sub is printing something out. some days it works, some days it doesn’t.
makes me crazy…
any ideas?
2 August 2007, 4:35 amflee01:
I found this but I can’t get it to work.
6 August 2007, 9:50 pmflee01:
Sorry. Here’s the website. http://www.thecodenet.com/articles.php?id=10
6 August 2007, 9:51 pmid:
Hi,
When use OnTime method, how to specify a time of a particular day to call a procedure “Schedule”?
For example, I created the following.
Application.OnTime EarliestTime:=TimeValue(”8/19/2007 01:01:06″), _
Procedure:=”Schedule”
On 08/18/2007, 01:00:05, I executed the above. Since I specified that the OnTime method to be run on 08/19/2007 01:01:06, I was not expecting nothing to happen at 08/18/2007 01:01:06. However, the excel macro actually run at 08/18/2007 01:01:06, and procedure “Schedule” was executed.
What should I do in order to make excel macro to call 08/19/2007 01:01:06, not at 08/18/2007 01:01:06?
18 August 2007, 2:43 amThanks.
8/18/2007 1:43am
Tushar Mehta:
id: Use datevalue()+timevalue() That will definitely create the date+time you want. Whether OnTime will honor the date specification is something I don’t know.
For obvious reasons I don’t intend to test my suggestion. {grin}
19 August 2007, 9:32 amid:
Tushar, it works. Thanks. I did a test with the following:
Application.OnTime DateValue(”8/22/2007″) + TimeValue(”02:30:06″), “Schedule”
Application.OnTime DateValue(”8/23/2007″) + TimeValue(”00:01:06″), “Schedule”
The code work perfectly.
23 August 2007, 9:55 pmBill:
Hello,
I'm really struggling with getting the application.ontime command to work for me.
I have
startTimer
end sub
sub startTimer()
Application.OnTime Now + TimeValue("00:00:2"), "nextorders"
end sub
It seems like the application.ontime command doesn't actually go back to the beginning of the nextorders sub.
I really appreciate your help.
Bill
18 October 2007, 4:23 pmDick Kusleika:
Bill: It works fine for me as posted.
19 October 2007, 1:53 pmMike Ward:
In "This Workbook", I have the code:
Private Sub Workbook_Open()
Application.OnTime TimeValue("07:46:00"), "MakeSyncRequest"
End Sub
and in a module I have the code:
Public Sub MakeSyncRequest()
Application.OnTime TimeValue("07:46:00"), "MakeSyncRequest"
' my code here
End Sub
however, this doesn't work - at least not consistently. Any ideas? Thanks in advance...
23 October 2007, 5:57 amBryan Schmidt:
Any ideas?...
I am attempting to use OnTime to run a subroutine, except if another book is open at the time, it won't find the subroutine. I've been trying something like this:
Application.ontime now+timeserial(0,0,10), "'Data File.xls'!Functions.waitingForSheet ""sheet1"", ""runThisSub"""
The waitingForSheet subroutine requires two string arguments. I don't see what I am doing wrong, any help?
29 October 2007, 5:04 pmkeepItcool:
Bryan,
the commandline requires apostrophes to encapsulate the arguments, but the filename should not be encapsulated: file.xls!'module.proc 12.3#,""arg""'"
Note the modulename is optional. Type declaration characters can be used to force
data types
to be able to stop the timed proc when you close the workbook you'll need to store both the next timeserial AND the 'commandline'(since it contains arguments)
[vba]
Option Explicit
Dim dtNext As Date
Dim szNext As String
Sub StartTimed()
30 October 2007, 4:46 amdtNext = Now + TimeSerial(0, 0, 5)
szNext = ThisWorkbook.Name & "!'module1.TimedProc True,""text"",123,empty,null'"
Application.OnTime dtNext, szNext
End Sub
Sub StopTimed()
On Error Resume Next
If dtNext 0 Then
Application.OnTime dtNext, szNext, , False
dtNext = 0
End If
End Sub
Sub TimedProc(bReRun As Boolean, ParamArray vArgs())
Dim i As Integer
If Not IsMissing(vArgs) Then
For i = LBound(vArgs) To UBound(vArgs)
Debug.Print TypeName(vArgs(i)),
Next
Debug.Print
End If
If bReRun Then
StartTimed
End If
End Sub
[/vba]
George:
Hello,
i have a similar trouble than some of above, but the fact is that it doesn´t work. This is the code in a single module:
Private Sub CommandButton1_Click()
Application.OnTime Now + TimeValue("00:00:02"), "Auto"
End Sub
Sub Auto()
MsgBox ("¡BINGO!")
End Sub
When i click on CommandButton1, after 2 secs, an error message tells that can´t find the macro Auto.
Please, could anyone help me?
Thanks a lot.
10 December 2007, 3:51 ammarc:
sub nextorders()
startTimer
end sub
sub startTimer()
Application.OnTime Now + TimeValue("00:00:2"), "nextorders"
end sub
This doesn't work for me!!!
CANNOT RUN THE MACRO "D:\BOOK1.XLSM'!NEXTORDERS'. THE MACRO MAY NOT BE AVAILABLE IN THIS WORKBOOK OR ALL MACROS MAY BE DISABLED.
wth?? All macros are enabled, what's the problem??
29 March 2008, 8:04 pmbrajesh:
yes it working
5 May 2008, 5:36 amthanks
sed:
Hi,
The ontime codes that I have below call the functions I specified twice sometimes even thrice depite having specified the time up to the second. May I know how I can limit the function to run only once?
Sub ontime()
Dim nexttick
Application.ontime TimeValue("21:31:01"), "FIRST", TimeValue("21:31:01")
Application.ontime TimeValue("21:22:40"), "Second", TimeValue("21:22:40")
Application.ontime TimeValue("21:22:59"), "Third", TimeValue("21:22:59")
nexttick = Now + TimeValue("00:00:15")
Application.ontime nexttick, "ontime"
End Sub
Thanks in advance for the assistance
10 July 2008, 7:59 amRudi:
Hi,
I am using function to forse Excel Macro everyday at 10:11.
Everything works fine if the day is one of the workdays.
On Saturday and Sunday it happens simply nothing.
I tried much to find out the reason for that, no success.
I don't use any statement like "Weekday", just daily repeating.
Thanks in advance for any suggestions,
20 November 2008, 12:13 amRudi