VBA Tip: OnTime

The OnTime method allows you to execute code after a specified duration or at a predefined time.

Usage:

Application.OnTime execution_time, "procedure_to_execute"

For example, executing the procedure named "hello" at 10:30:

Application.OnTime TimeValue("10:30:00"), "hello"

Example: Execute a procedure in 10 seconds

The OnTime method requires a precise execution time.

To execute code in 10 seconds, you need to start by determining the current time with Now and then add the 10-second delay + TimeValue("00:00:10"), which gives:

Application.OnTime Now + TimeValue("00:00:10"), "wait"

In this example, OnTime triggers the "wait" procedure after 10 seconds:

Sub example()
    Application.OnTime Now + TimeValue("00:00:10"), "wait"
End Sub

Sub wait()
    MsgBox "10 seconds have elapsed!"
End Sub

Canceling an execution

If necessary, you can cancel a scheduled execution by adding a fourth argument with the value False:

Sub example()

    'Time
    executionTime = Now + TimeValue("00:00:10")
    
    'Scheduling
    Application.OnTime executionTime, "wait"
    
    'Cancellation
    Application.OnTime executionTime, "wait", , False
    
    '=> the "wait" procedure will not be executed
	
End Sub

Executing a procedure with arguments

Often, the procedure that needs to be executed by OnTime requires one or more arguments.

For example, to schedule the execution of the following procedure, the number of seconds needs to be provided:

Sub wait(seconds)
    MsgBox seconds & " seconds have elapsed!"
End Sub

In this case, instead of entering "wait" as in the previous examples, you need to start with single quotes 'wait' and then add the argument(s) (separated by commas if there are multiple arguments) 'wait 5', which gives:

Sub example()
    Application.OnTime Now + TimeValue("00:00:05"), "'wait 5'"
End Sub

If the argument is a text value, don't forget to include double quotes:

Sub example()
    Application.OnTime Now + TimeValue("00:00:05"), "'wait ""Five""'"
End Sub

If the argument is a variable, you should enter it like this:

Sub example()
    myValue = 5
    Application.OnTime Now + TimeValue("00:00:05"), "'wait " & myValue & "'"
End Sub

Don't forget the double quotes if it's a text value:

Sub example()
    myValue = "Five"
    Application.OnTime Now + TimeValue("00:00:05"), "'wait """ & myValue & """'"
End Sub