Use the OnTime Method in Excel VBA

The OnTime method of the Application object in Excel VBA occurs at a specific time of the day.

It can be used to schedule procedures to be run at specified times of the day, or at specific time intervals.

OnTime Method Parameters

The OnTime method requires the following parameters.

.OnTime(EarliestTime, Procedure, LatestTime, Schedule)

EarliestTime – The time that you want the procedure to run (required)

Procedure – The name of the procedure to be run, entered as a string (required)

LatestTime – The latest time that the procedure should be run

Schedule – Set to True to schedule a new OnTime procedure, or False to cancel the OnTime procedure. The default is set to True

Use the OnTime Method to Run a Procedure

Let’s use the OnTime method to run a procedure every day at 9 am. The procedure will display a message box to let the user know that it is time for their morning coffee.

The first thing we need is a way of initiating the method. The best way to do this is to use the Workbook Open event.

1. Press Alt + F11 to open the Visual Basic Editor window.
2. Double click on the ThisWorkbook object in the Project Explorer to open its Module Window.
3. Click on the Object list arrow and select Workbook.
4. Click on the Procedure list arrow and select Open.
5. Enter the code Application.OnTime TimeValue(“09:00:00”), “message” within the procedure as shown below.

6. This code will initiate the message procedure and schedule it to run at 9 am. The TimeValue function is used to convert the text string to a time value.
7. The message procedure resides in a general module and looks like below.

Sub message()
MsgBox "Time for your morning coffee!!"
End Sub

Cancel the OnTime Method

Procedures scheduled by the OnTime method will run at their specified time every day, even if the workbook is closed.

If you only want a procedure to be run if the workbook is open, then the OnTime method can be cancelled by using the Workbook BeforeClose event.

The following code can be entered in the BeforeClose event to cancel the

OnTime method.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime TimeValue("09:00:00"), "message", , False
End Sub