How to Use Application.OnTime VBA for Quick Results in 3 Minutes (Excel)

Written by Kasper Langmann

The Application.OnTime method in Visual Basic for Applications (VBA) is an incredibly powerful tool that can automate and schedule tasks in Excel. This method allows you to run a procedure at a specific time or after a certain amount of time has passed. Whether you’re looking to automate data updates, generate reports at specific intervals, or perform any other time-based task, mastering the Application.OnTime method can significantly boost your productivity in Excel.

Understanding the Application.OnTime Method

The Application.OnTime method is a part of Excel’s VBA programming language. VBA, or Visual Basic for Applications, is a powerful programming language that allows you to automate tasks in Excel and other Microsoft Office applications. The Application.OnTime method is a specific function within VBA that allows you to schedule a procedure to run at a specific time.

The syntax for the Application.OnTime method is as follows:

Application.OnTime(EarliestTime, Procedure, LatestTime, Schedule)

Each of these parameters plays a crucial role in defining when and how the procedure will run.

EarliestTime

This parameter specifies the earliest time at which the procedure can start. It must be a string or a numeric expression representing a valid time. For example, if you want the procedure to run at 2 PM, you can set EarliestTime to “14:00:00”.

Procedure

This is the name of the procedure that you want to run. It must be a string. For example, if you have a procedure named UpdateData, you would set Procedure to “UpdateData”.

LatestTime

This optional parameter specifies the latest time at which the procedure can start. If the procedure hasn’t started by this time, it won’t run. Like EarliestTime, it must be a string or a numeric expression representing a valid time.

Schedule

This optional parameter is a Boolean value that determines whether the procedure should be scheduled. If it’s set to True, the procedure is scheduled. If it’s set to False, any existing schedule for the procedure is cancelled.

Using the Application.OnTime Method

Now that you understand the syntax and parameters of the Application.OnTime method, let’s look at how you can use it in practice. The following steps will guide you through the process of creating a simple procedure and scheduling it to run at a specific time.

Step 1: Create a Procedure

First, you need to create a procedure that you want to run. This can be any valid VBA procedure. For example, you might create a procedure that updates a data table in your Excel workbook.

Step 2: Schedule the Procedure

Once you have a procedure, you can schedule it to run at a specific time using the Application.OnTime method. To do this, you need to write a separate procedure that calls the Application.OnTime method and specifies the time and procedure name.

Step 3: Run the Scheduling Procedure

After you’ve written the scheduling procedure, all you need to do is run it. Once it’s run, the procedure you specified will be scheduled to run at the time you specified.

Examples of Using the Application.OnTime Method

To help you better understand how to use the Application.OnTime method, let’s look at a few examples.

Example 1: Running a Procedure at a Specific Time

Suppose you have a procedure named UpdateData that you want to run at 2 PM. You could schedule this procedure using the following code:

Sub ScheduleUpdate()
    Application.OnTime "14:00:00", "UpdateData"
End Sub

Example 2: Running a Procedure After a Certain Amount of Time

You can also use the Application.OnTime method to run a procedure after a certain amount of time has passed. For example, if you want to run a procedure named RefreshData five minutes from now, you could use the following code:

Sub ScheduleRefresh()
    Application.OnTime Now + TimeValue("00:05:00"), "RefreshData"
End Sub

Conclusion

The Application.OnTime method is a powerful tool that can automate and schedule tasks in Excel. By understanding its syntax and parameters, and learning how to use it effectively, you can significantly boost your productivity and efficiency in Excel. Whether you’re a beginner or an experienced VBA programmer, mastering the Application.OnTime method is a valuable skill that can help you take your Excel projects to the next level.