VBA ONTIME

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:

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.

Syntax

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

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

Where,

• 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.

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

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.

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

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.

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

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

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

Code:

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.

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.

Examples

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.

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

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.

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.

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

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.

Code:

Public SchTime As Date

Public Const Interval = 2

Sub MyPro()

    Debug.Print Now()

    SetTime

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:

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.

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

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

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

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

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

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

Code:

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.

After stopping the scheduling.

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.

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.

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

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.

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

Step 6: Define the subroutine called in Step 4.

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

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

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

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

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

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

Code:

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.

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

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

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)

Recommended Articles

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 –

• VBA Evaluate
• VBA PowerPoint
• VBA YEAR