Scheduling Excel to Run a Procedure at periodic intervals or at a specific time of day, with the OnTime Method. Automatically run macros.
Excel Application.OnTime Method – Scheduling OnTime Events
Use the Application.OnTime Method to run a procedure at specified intervals or at a specific time of day. Syntax: ApplicationObject .OnTime(EarliestTime, ProcedureName, LatestTime, Schedule). Using this method you can schedule to run a procedure in the future. You can either fix specific intervals, starting from now, when the procedure will run, or you can fix a specific time of day. The (Excel) Application Object represents the entire Excel application, and is the top-most object in the Excel object model. The EarliestTime and ProcedureName arguments are required to be specified while the other arguments are optional. The EarliestTime argument specifies the time when the procedure is to be run. The ProcedureName argument specifies the name of the procedure you want to be executed. With the LatestTime argument you can set the time limit for running the procedure viz. if you set the LatestTime to “EarliestTime + 20” and if meanwhile another procedure is being executed and Excel is not in ready mode within 20 seconds, this procedure will not run. Omitting the LatestTime argument will make Excel wait and run the procedure. Omitting the Schedule argument will default to True, which sets a new Ontime procedure. To cancel an existing OnTime procedure set earlier, specify False.
To fix specific intervals starting from now, to run the procedure, use “Now + TimeValue(time)”. To fix a specific time of day for the procedure to run, use “TimeValue(time)”. See below examples on using these.
Stop or Cancel a Running Procedure (using the OnTime method)
If you attempt to close the workbook while a procedure is being run using Application.Ontime, Excel will re-open the workbook, and leave it open post completion of the procedure. Hence, you will need to cancel the procedure at a certain point or time.
To cancel a running procedure (using the OnTime method), the precise time of its scheduled run is required. Note that if you don’t pass the time to a variable, Excel will not know which OnTime method to cancel, as Now + TimeValue(“00:00:03”) is not static, but becomes static when passed to a variable. This means that the time when the procedure is to run (EarliestTime argument) should be assigned to a variable (use a Public variable to make the variable available to all Procedures in all modules) and then use it to cancel the OnTime.
Example 1: This procedure uses the OnTime Method to auto increment cell value at specific time intervals, and Stops the procedure on crossing a specific cell value.
The procedure should be entered in a Standard Module (select Insert>Module, in VBE code window).
‘Dim as a Public variable and it will be available to all Procedures in all modules.
Public rTime As Date
_____________________________________
Sub CellValueAutoIncr1()
‘This procedure uses the OnTime Method to auto increment cell value at specific time intervals, and Stops the procedure on crossing a specific cell value.
‘The procedure should be entered in a Standard Module (select Insert>Module, in VBE code window).
‘To run a procedure at a specific time, use TimeValue(time) viz. TimeValue(“20:30:00”) will run a procedure at 8.30 pm. To run a procedure at specified time intervals (say, from now), use Now + TimeValue(time) viz. Now + TimeValue(“00:00:05”) sets the time interval at 5 seconds, at which interval the procedure will run.
‘set the time interval at 3 seconds, at which interval the procedure will run.
rTime = Now + TimeValue(“00:00:03”)
‘procedure named CellValueAutoIncr1 will autmatically run, at the sheduled time interval, with the OnTime Method.
Application.OnTime EarliestTime:=rTime, Procedure:=“CellValueAutoIncr1”, schedule:=True
‘increment the value in cell A1 (in Active Worksheet) by 5, for each time the Macro is repeated:
Cells(1, 1).Value = Cells(1, 1).Value + 5
‘If you attempt to close the workbook while a procedure is being run using Application.Ontime, Excel will re-open the workbook, and leave it open post completion of the procedure. Hence, you will need to cancel the
procedure at a certain point or time.
‘stop the procedure at a specified point – the procedure will stop after cell A1 value crosses 25 for the first time.
If Cells(1, 1).Value > 25 Then
‘If you need to cancel an OnTime, you must provide the exact time that the event was schedule to take place.
‘Therefore, you need to store the time at which the procedure is to run in a Public variable and use that variable’s value in calls to OnTime.
‘To cancel a running procedure (using the OnTime method), the precise time of its scheduled run is required. Note that if you don’t pass the time to a variable, Excel will not know which OnTime method to cancel, as
Now + TimeValue(“00:00:03”) is not static, but becomes static when passed to a variable. This means that the time when the procedure is to run (EarliestTime argument) should be assigned to a variable (Public
variable rTime in this example) and then use it to cancel the OnTime.
‘cancel the procedure by setting the Schedule argument to False:
Application.OnTime rTime, “CellValueAutoIncr1”, , False
End If
End Sub
Example 2: This procedure uses the OnTime Method to auto increment cell value at specific time intervals, and Stops the procedure after it runs for a specific number of times.
The procedure should be entered in a Standard Module (select Insert>Module, in VBE code window).
Public eTime As Date
Dim count As Integer
__________________________
Sub CellValueAutoIncr2()
‘This procedure uses the OnTime Method to auto increment cell value at specific time intervals, and Stops the procedure after it runs for a specific number of times.
eTime = Now + TimeValue(“00:00:03”)
Application.OnTime eTime, “CellValueAutoIncr2”, , True
Cells(1, 1).Value = Cells(1, 1).Value + 5
count = count + 1
‘stop the procedure after it runs for 5 times:
If count = 5 Then
Application.OnTime eTime, “CellValueAutoIncr2”, , False
count = 0
End If
End Sub
Example 3: Start the OnTime procedure automatically when the workbook is opened; stop the OnTime procedure automatically on closing the workbook. This Application.OnTime procedure sets reminders at specific times and auto-closes workbook at a specified time.
For live code, click to download excel file.
Add code (workbook procedures) to the Workbook module (ThisWorkbook):
Private Sub workbook_open()
‘to start the procedure automatically when the workbook is opened – add this code to the Workbook module (ThisWorkbook)
‘call SetReminder procedure:
SetReminder
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
‘to stop the procedure automatically on closing the workbook, add this code to the Workbook module (ThisWorkbook)
On Error Resume Next
‘call StopSetReminder procedure to stop the Application.OnTime:
StopSetReminder
‘save workbook before closing:
ThisWorkbook.Save
End Sub
The procedures to be entered in a Standard Module:
Public dTime As Date
______________________
Sub SetReminder()
‘This Application.OnTime procedure sets reminders at specific times and auto-closes workbook at a specified time:
On Error Resume Next
dTime = Now + TimeValue(“00:00:01”)
‘procedure named SetReminder will autmatically run, at the sheduled time interval, with the OnTime Method.
Application.OnTime dTime, “SetReminder”
‘Close the workbook at the specified time:
If Time = TimeSerial(18, 30, 0) Then
CloseWorkBook
End If
‘set OfficeClose reminder:
If Time = TimeSerial(17, 30, 0) Then
Application.OnTime dTime, “OfficeClose”
End If
‘set LunchBreak reminder:
If Time = TimeSerial(13, 0, 0) Then
Application.OnTime dTime, “LunchBreak”
End If
‘set CoffeeBreak reminder:
If Time = TimeSerial(11, 15, 0) Then
Application.OnTime dTime, “CoffeeBreak”
End If
End Sub
Sub CoffeeBreak()
Dim obj As Object
Dim strMsg As String
Set obj = CreateObject(“WScript.Shell”)
‘play the Beep sound, you can customize the sound / message you wish to play:
Beep
‘the Popup Message Box will automatically close by itself, without user action of clicking Ok:
strMsg = obj.Popup(“Coffee Break Sir!”, vbOKCancel)
End Sub
Sub LunchBreak()
Dim obj As Object
Dim strMsg As String
Set obj = CreateObject(“WScript.Shell”)
Beep
strMsg = obj.Popup(“Lunch Break Sir!”, vbOKCancel)
End Sub
Sub OfficeClose()
Dim obj As Object
Dim strMsg As String
Set obj = CreateObject(“WScript.Shell”)
Beep
strMsg = obj.Popup(“Office Closing Sir!”, vbOKCancel)
End Sub
Sub StopSetReminder()
‘Stop the Application.OnTime procedure named SetReminder
Application.OnTime dTime, “SetReminder”, , False
End Sub
Sub CloseWorkBook()
‘close the workbook
On Error Resume Next
‘call StopSetReminder procedure to stop the Application.OnTime:
StopSetReminder
‘save workbook before closing:
ThisWorkbook.Save
‘close workbook:
ThisWorkbook.Close
End Sub