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.
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
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.
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()
Workbooks.Open ThisWorkbook.FullName
End Sub
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
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
I need help about the application on time.
I need to run a formula every last day of the month
Is there anyway to make this run without opening the Excel.
Thanks
Alice
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?
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()
Application.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
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,
Rob
Can you specify an argument after the procedure?
Thank you!
I assigned a ontime macro to a shape, it will run normally once shape is clicked. but if click many
times on the shape,then the scheduled time become uncontrollable ! Anyone knows how to prevent this from
happening?
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
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.
It’s been a while since Max posed the question about parameters to the procedure specified in the OnTime method.
See
Using 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
Is there a way to use OnTime to schedule a procedure with passing parameters? For example: MyProc(Counter).
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
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.
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?
I found this but I can’t get it to work.
Sorry. Here’s the website. http://www.thecodenet.com/articles.php?id=10
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?
Thanks.
8/18/2007 1:43am
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}
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.
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
Bill: It works fine for me as posted.
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…
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?
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)
Dim dtNext As Date
Dim szNext As String
Sub StartTimed()
dtNext = 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
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.
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??
yes it working
thanks
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
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,
Rudi
Is there a way to get a list of all active OnTime procedures in a given workbook?
Hi
This won’t give you a list of programs, but if you have a list of possible programs, this will allow you to nominate your program name, and, if it has been scheduled, show when it was scheduled. Just run the program for each of your possibles by changing the value of the variable thisprog. In the current code, it looks for a program called “bbb”
HTH
'nominate the name of the program you want to check
thisprog = "bbb"
On Error Resume Next
For h = 0 To 23
For i = 0 To 59
For j = 0 To 59
'remove the scheduled ontime activity
Application.OnTime TimeValue(Format(h, "00") & ":" & Format(i, "00") & ":" & Format(j, "00")), thisprog, , False
If Err.Number = 0 Then
'record the name and time of the program
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = TimeValue(Format(h, "00") & ":" & Format(i, "00") & ":" & Format(j, "00"))
Cells(Rows.Count, 1).End(xlUp).Offset(0, 1).Value = thisprog
'restate the ontime program
Application.OnTime TimeValue(Format(h, "00") & ":" & Format(i, "00") & ":" & Format(j, "00")), thisprog
End If
Err.Clear
Next j
Next i
Next h
On Error GoTo 0
End Sub
But, there all codes to be written plz specify someone
I would like to use OnTime to schedule an cancel events. But in my application, I can not trust that VBA will remember global variables. VBA may crash or be stopped and lose the global values. So I may need to cancel events with out knowledge of when they were scheduled.
Is there some DLL that exposes an interface to the Windows event queue that would allow me to inspect all currently-scheduled events?