What is VBA OnTime Function?

The VBA OnTime function is used to schedule a procedure (subroutine or function) to run at a specified time in the future. This function is handy for automating tasks or triggering actions after a certain delay. Consider the following example:

VBA OnTime Function 1

In this example, a message box in VBA is scheduled to pop up after 1 second. It is done by calling that subprocedure after 1 second with the VBA Now function.

VBA OnTime Function 1-1
Key Takeaways
  • The OnTime function in VBA is used to schedule a procedure to run at a specified time in the future. Use OnTime to schedule a procedure to run at a specific time in the future.
  • For the opposite, use OnTime with Schedule:=False to cancel a previously scheduled procedure.
  • OnTime is accurate to within one second.
  • Scheduled procedures are associated with the application instance where they are scheduled. If Excel is closed, the scheduled procedures are not executed.
  • To schedule a procedure to run periodically, you need to reschedule it each time it runs.


The syntax to using VBA Ontime with parameters is as shown.

Application.OnTime(earliestTime, procedure, [latestTime], [schedule])


  • earliestTime: This is the time when you want the procedure to run. It should be specified as a Date value.
  • procedure: This is the name of the procedure (subroutine or function) that you want to run.
  • latestTime: It is an optional parameter. This specifies the latest time at which the procedure can be run. If the current time exceeds this value, the procedure won’t run. If omitted, the procedure will run regardless of the current time.
  • schedule: This is an optional parameter. This specifies whether the procedure should run only once (False) or repeatedly (True) at the specified time. If omitted, the default is False, meaning the procedure will run only once.

How to Use VBA OnTime?

If you’re interested in learning how to use the VBA OnTime event parameters to your advantage, you can follow the steps below.

Step 1: Select the “Developer” option in the Excel Application once you open it.

How to use VBA OnTime Step 1

Under the tool bar, select “Visual Basic” to gain access to the Excel VBA (Visual Basic for Applications) Editor.

How to use VBA OnTime Step 1-1

In the currently empty VBA Editor, select “Insert” on the VBA Toolbar, then in the drop-down, select “Module.” You can follow the rest of the steps in this new module you’ve created.

How to use VBA OnTime Step 1-2

Step 2: Define a subroutine that will define the time when the code will be run.

How to use VBA OnTime Step 1-3

Step 3: The VBA Ontime event is set to 6:43 pm or 18:43 using the VBA TimeSerial function. The subroutine “Print_Code” will run then.

How to use VBA OnTime Step 1-4

Step 4: Define the subroutine scheduled to run at 6:43 pm.

How to use VBA OnTime Step 1-5

Step 5: Print the message using a Message box function. Additionally, you can add icons such as “vbInformation.”

How to use VBA OnTime Step 1-6

Step 6: Similarly, format the current date value, which will print the month name, day, and year.


Sub Ontime_Start()

    Application.OnTime TimeSerial(18, 43, 0), “Print_Code”

End Sub

Sub Print_Code()

    MsgBox “It’s 6:43pm!”, vbInformation

End Sub

Step 7: Click “F5” or the “Run” icon on the activity bar in the Editor to run this subroutine. It prints a Message Box when the Excel time strikes 6:43 pm.

How to use VBA OnTime Step 1-7

With this, you’ve learned how to schedule subprcedures except using the VBA Ontime milliseconds. The Application object in Excel VBA handles only up to the seconds denomination.


See the different interesting examples possible using the VBA OnTime function.

Example #1

Suppose you want to create a subroutine to print the current date and time in the Immediate tab in between certain intervals. You can do that using the VBA OnTime function to schedule the time being printed after certain intervals of time.

Step 1: Define two global variables in VBA, one being a date variable and the other being the variable interval after the schedule should run all the time.

VBA OnTime Example 1

Step 2: Define the subprocedure to print the current time.

VBA OnTime Example 1-1

Step 3: Print the current date and time using the VBA Now function. Then, call the other subroutine to execute this subroutine once every 1 second.

VBA OnTime Example 1-2

It is done so that both the subroutines work recursively so these subroutines will loop infinitely.

Step 4: Create a subroutine to set the subroutine defined to run every 1 second.

VBA OnTime Example 1-3

Step 5: Set the interval as 2 seconds using the VBA TimeSerial (h,m,s) format. The earliest time is set as 2 seconds.

VBA OnTime Example 1-4

Step 6: Use VBA OnTime to schedule the earliest time as two seconds and run the procedure with its name defined as a string variable.

VBA OnTime Example 1-5


Public SchTime As Date

Public Const Interval = 2

Sub MyPro()

    Debug.Print Now()


End Sub

Sub SetTime()

    SchTime = Now + TimeSerial(0, 0, Interval)

    Application.OnTime SchTime, “MyPro”

End Sub

Step 7: Press “F5” to run the code. The output is displayed, as shown below:

VBA OnTime Example 1-6

This subroutine must run infinitely. You need to provide additional parameters to make it stop running.

Example #2

You can create a running clock in your Excel worksheet and stop running it before you close your application. You can use the concept of the VBA Ontime stop functionality to stop a VBA Ontime schedule.

Step 1: Define a subroutine to create a custom clock to be displayed in your worksheet.

VBA OnTime Example 2

Step 2: Select cell “A1” of the worksheet to print the current date and time.

VBA OnTime Example 2-1

Note: Sometimes, the cell will show a value error. To prevent this, you can format the cells as shown.

VBA OnTime Example 2-2

Select your preferred option or create your custom option to see VBA Now.

VBA OnTime Example 2-3

Step 3: Recursively run the subroutine every second using the VBA Now and VBA TimeValue set as 1 second.

VBA OnTime Example 2-4

Step 4: To perform the VBA OnTime stop functionality, define another subroutine.

VBA OnTime Example 2-5

Step 5: Stop the scheduling by setting the VBA OnTime with parameters schedule as ‘false’ and the procedure set as the subroutine defined earlier..

VBA OnTime Example 2-6


Sub UpdateClock()

    Sheets(“Sheet1”).Range(“A1”).Value = Now

    Application.OnTime EarliestTime:=Now + TimeValue(“00:00:01″), Procedure:=”UpdateClock”

End Sub

Sub StopClock()

    Application.OnTime EarliestTime:=Now + TimeValue(“00:00:01″), Procedure:=”UpdateClock”, Schedule:=False

End Sub

Step 6: Click the green arrow button when you want to run the code. It will pop up a Message Box once run.

VBA OnTime Example 2-7
VBA OnTime Example 2-8

After stopping the scheduling.

VBA OnTime Example 2-9

With that, you’ve made your custom clock in the Excel workbook.

Example #3

Let us create a custom reminder where the user gives both the reminder message and the time when the remainder pops up while also being able to stop it when they need it. You can use the VBA OnTime function. Follow the steps to see how you can do it.

Step 1: Define the subroutine with which you’ll set the custom reminder.

VBA OnTime Example 3

Step 2: Define an Input Box function to take the time when the reminder will be set. Specify the input format you want the user to provide. Provide an appropriate input box title.

VBA OnTime Example 3-1

Step 3: Get the reminder message from the user as a string.

VBA OnTime Example 3-2

Step 4: Call the VBA OnTime function with its EarliestTime setting set to the time you want. Then, run the procedure that reminds you of using the message box function.

VBA OnTime Example 3-3

Step 5: Set the reminder message at a random cell in the worksheet. Here, it is set as cell “C3.”

VBA OnTime Example 3-4

Step 6: Define the subroutine called in Step 4.

VBA OnTime Example 3-5

Step 7: Set the reminder message string variable as the value set in cell “C3”.

VBA OnTime Example 3-6

Step 8: Print the reminder message in a message box. Set the icon as “vbInformation” and the title of the message box.

VBA OnTime Example 3-7

Step 9: Define another subroutine to perform the VBA OnTime stop of the schedule started in Step 4.

VBA OnTime Example 3-8

Step 10: Use the VBA On Error module to perform error handling.

VBA OnTime Example 3-9

Step 11: Perform the VBA OnTime stop by setting the Schedule parameter as “False.”

VBA OnTime Example 3-10

Step 12: Reset the Error to default, that is, 0.

VBA OnTime Example 3-11


Sub SetReminder()

    Dim ReminderTime As Date

    ReminderTime = InputBox(“Enter the reminder time (format: hh:mm:ss)”, “Reminder Time”)

    Dim ReminderMessage As String

    ReminderMessage = InputBox(“Enter the reminder message”, “Reminder Message”)

    Application.OnTime EarliestTime:=ReminderTime, Procedure:=”ShowReminder”, Schedule:=True

    Sheets(“Sheet1”).Range(“C3”).Value = ReminderMessage

End Sub

Sub ShowReminder()

    Dim ReminderMessage As String

    ReminderMessage = Sheets(“Sheet1”).Range(“C3”).Value

    MsgBox ReminderMessage, vbInformation, “Reminder”

End Sub

Sub CancelReminder()

    On Error Resume Next

    Application.OnTime Procedure:=”ShowReminder”, Schedule:=False

    On Error GoTo 0

End Sub

Step 13: Run the SetReminder subroutine. It will run the subroutine, ShowReminder.

VBA OnTime Example 3-12
VBA OnTime Example 3-13

It will print “Hello” in the cell “C3” on the worksheet.

VBA OnTime Example 3-14

At 7:50 pm, the reminder message box pops up.

VBA OnTime Example 3-15

It will keep showing reminders until you run the “CancelReminder” subroutine.

Important Things To Note

  • Always cancel scheduled tasks using OnTime with Schedule:=False when they are no longer needed to prevent unintended execution.
  • Be aware that OnTime is accurate to within one second. Account for this limitation when scheduling tasks that require precise timing.
  • Recognize that scheduled tasks are associated with the application instance where they are scheduled. Ensure that the application remains open when tasks need to be executed.
  • Avoid scheduling an excessive number of tasks or tasks with short intervals, as this can lead to performance issues and resource consumption.
  • Ensure to cancel scheduled tasks when closing the application to prevent tasks from executing unexpectedly or causing errors upon application restart.
  • While OnTime can be useful for automating tasks, avoid relying solely on it for critical functionality. Implement alternative mechanisms or redundancies where necessary.

Frequently Asked Questions (FAQs)

1) Can I cancel a scheduled procedure using OnTime in VBA?

Yes, you can cancel a scheduled procedure using OnTime in VBA by calling OnTime with Schedule:=False.

2) Is it possible to schedule a recurring procedure with OnTime in VBA?

Yes, it is possible to schedule a recurring procedure with OnTime in VBA by rescheduling the procedure each time it runs.

3) What is the difference between the OnTime function and the OnTime method in VBA?

The OnTime function schedules a procedure to run at a specific time, while the OnTime method cancels a scheduled procedure or timer.

4) What are some OnTime function alternatives for scheduling tasks in VBA?

Some alternatives to the OnTime function for scheduling tasks in VBA include using Windows API timers or creating custom timers using looped delays.

This has been a Guide to VBA OnTime. Here we explain How to use VBA OnTime using syntax along with examples & downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *