Dear Friends,

In this article I am going to teach you about Application.OnTime. I will also explain you the usage of this method with two examples:

This method is used to execute a procedure in Excel VBA at a specific time or after certain time has elapsed.
Lets have a look at the syntax of this Excel VBA method:

Syntax:

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

Where:

EarliestTime: This is a mandatory parameter and this is the time when you want to run your procedure. It accepts a variant data type.
Procedure: This is also a mandatory parameter. It is the name of the procedure/macro you want to run. It is a string type parameter.
LatestTime : This is an optional parameter. This is a maximum waiting time set by programmer for Excel to complete any ongoing activity like Cut, Copy, Paste, Save etc. and then start this procedure. Note that, if other ongoing activities are not complete within the specified waiting time then the procedure will not run. Since this is an optional parameter, if omitted, Application.OnTime method will wait till excel does not finish all its activities.
Schedule : This is an optional parameter. This is a boolean type parameter. True is used to schedule new procedure. False is used to clear out previously scheduled procedure.

Example 1# Schedule to save and close workbook

Follow the below steps to achieve this:

Step 1#

Copy and paste the following code in your ThisWorkbook Code window

Private Sub Workbook_Open()
    On Error Resume Next
    ' Schedule the Save&CloseWorkbook procedure at 5:30 PM
    Application.OnTime VBA.TimeValue("17:30:00"), "SaveAndCloseWorkBook", , True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    'Before closing the workbook, remove the schedule
    Application.OnTime VBA.TimeValue("17:30:00"), "SaveAndCloseWorkBook", , False
    On Error GoTo 0
End Sub

Step 2#

Last but not the least step, copy paste the Save and Close workbook function which will be triggered at scheduled time in any of the regular module in your VBE project.

Sub SaveAndCloseWorkBook()
    ThisWorkbook.Close SaveChanges:=True
End Sub

Example 2# Save and Close workbook, if left idle

Before we proceed further, let see – How to check if excel was left idle?
Here I am checking following two events to determine if excel was left idle-
SelectionChange in Workbook Event
Any Change across workbook Event

If any of the above events are happening with workbook, idle duration will be reset back to zero again.

Step 1#

Copy and paste the following code in any module in your VBE Project. Read basic tutorials – how to add modules in VBE.
Note that the following 2 lines must be added at the top of the module. It should not be pasted after any function/procedure in the module.

Public runTime As Double
Public Const DURATION_IN_HOURS = 0
Public Const DURATION_IN_MINUTES = 30
Public Const DURATION_IN_SECONDS = 0

You can use this duration according to your need. In this function I have used it for 30 minutes.

Step 2#

Copy and paste the following code in your ThisWorkbook Code Window.

Private Sub Workbook_Open()
    On Error Resume Next
    ' Set the runTime public varibale with the specified duration
    ' To change the time you can change in the public Constants
    ' defined in one of your module
    runTime = Now + TimeSerial(DURATION_IN_HOURS, DURATION_IN_MINUTES, DURATION_IN_SECONDS)
    
    ' Now schedule the Save&CloseWorkbook procedure with the specified duration
    Application.OnTime runTime, "SaveAndCloseWorkBook", , True
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

On Error Resume Next
    ' As explained above to reset the scheduled time for this save and close procedure
    ' Scheduled - False parameter is used
    Application.OnTime runTime, "SaveAndCloseWorkBook", , False
    
    ' Now reset the runTime variable again based on the new time
    runTime = Now + TimeSerial(DURATION_IN_HOURS, DURATION_IN_MINUTES, DURATION_IN_SECONDS)
    
    'Now re-schedule the Save&Close function with the new calculated time.
    Application.OnTime runTime, "SaveAndCloseWorkBook", , True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
    ' As explained above to reset the scheduled time for this save and close procedure
    ' Scheduled - False parameter is used
    Application.OnTime runTime, "SaveAndCloseWorkBook", , False
    
    ' Now reset the runTime variable again based on the new time
    runTime = Now + TimeSerial(DURATION_IN_HOURS, DURATION_IN_MINUTES, DURATION_IN_SECONDS)
    
    'Now re-schedule the Save&Close function with the new calculated time.
    Application.OnTime runTime, "SaveAndCloseWorkBook", , True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    
    'Before closing the workbook, remove the schedule
    Application.OnTime RunWhen, "SaveAndCloseWorkBook", , False
    On Error GoTo 0
End Sub

Step 3#

Last but not the least step – write your own procedure which you want to schedule it. Make sure that the name of the procedure exactly same as you have provided in the Application.OnTime method in above code.

Now Paste this SaveAndCloseWorkbook function code any where in any of the regular Modules (not in worksheet or workbook module)

Sub SaveAndCloseWorkBook()
    ThisWorkbook.Close SaveChanges:=True
End Sub