One of the many useful features of Excel and other MS Office applications, of which little users know of is the VBA OnTime function. It allows you to execute any Excel VBA procedure after the specified amount of time (delay). Using it recursively allows you to create a recurring / repeating event which lets you schedule periodic VBA Macros to run when working on Excel Workbooks.

Application OnTime Syntax

The syntax of the VBA OnTime function:

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

EarliestTime
The time (Date variable) when the Procedure is to be executed

Procedure
A string with the name of the Procedure which is to be executed

LatestTime
Optional. This is the latest time when the Procedure should be executed. This allows Excel for some flexibility when prioritizing Procedure execution together with other Excel (or other MS Office applications) Events such as Cut, Copy or Find. It makes sense to use this variable when your user is working on the file, so as not to interrupt any activities

Schedule
Optional. Defaults to True. If True schedules a new OnTime procedure. If False clears a previously set procedure. Useful when you want to cancel a previously scheduled Procedure

Excel VBA Alarm Clock

The code below will schedule the HelloWorld Sub to run in 1 seconds.

Sub SetAlarm()
    Dim timeout As Date
    timeout = TimeValue("20:40:50")
    Application.OnTime timeout, "WhatHour"
End Sub
 
Sub WhatHour()
    Call MsgBox("The time is " & Format(Now(), "HH:MM"))
End Sub

From above the following are equivalent:

Application.OnTime timeout, "WhatHour"
Application.OnTime EarliestTime:=timeOut, Procedure:="WhatHour", Schedule:=True

Wait 1 second and expect a Message Box to open with the message “Hello World!”.

Cancelling Application OnTime

You can also use the Application OnTime function to Cancel Scheduled Procedures.

Sub ScheduleAndCancelHelloWorld()
    Dim timeout As Date
    timeout = Now + TimeValue("00:00:10")
    Application.OnTime timeout, "HelloWorld"
    '...
    Application.OnTime timeout, "HelloWorld", False
End Sub

Sub HelloWorld()
    Call MsgBox("Hello World")
End Sub

Example 1: Automatic worksheet recalculate using OnTime

The Timer can come in handy when we need to refresh our worksheet(s) periodically and do not want to do this manually. See the example below:

Let us add this procedure in any VBA module within the workbook:

Sub Refresh()
    Application.CalculateFull
    Dim timeout As Date
    timeout = Now + TimeValue("00:00:10")
    Application.OnTime timeout, "Refresh"
End Sub

Next add this to the Workbook in the “Microsoft Excel Objects” section of the VBA project:

Private Sub Workbook_Open()
    Refresh
End Sub

That is it! Once you open the workbook the whole workbook will be recalculated every 10 seconds. In order to stop it simply close the workbook or debug the Refresh procedure.

Example 2: Automatic workbook versioning using OnTime

Working on a large Excel solution and afraid that you workbook will crash? Want to have a separate backup copy of your file every now and then. Why not use automatic file versioning then?

Add this code to any VBA module:

Sub SaveNewVersion()
    Dim fileName As String, index As Long
    If InStr(ActiveWorkbook.Name, "_v") = 0 Then
        fileName = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) & "_v1.xlsm"
        ActiveWorkbook.SaveAs (fileName)
    Else
        index = CInt(Split(Right(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - InStr(ActiveWorkbook.Name, "_v") - 1), ".")(0))
        index = index + 1
        fileName = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, "_v") - 1) & "_v" & index & ".xlsm"
    End If
    ActiveWorkbook.SaveAs (fileName)
End Sub

Next replace the Refresh function with this:

Sub Refresh()
    SaveNewVersion
    Dim timeout As Date
    timeout = Now + TimeValue("00:10:00")
    Application.OnTime timeout, "Refresh"
End Sub

Voila! Your workbook will be saved as a new version every 10 minutes. Great isn’t? There are probably a ton of ways you can further tweak this to work better e.g. add a pop-up asking to confirm the creation of the next version etc.

Hope that this will prove useful to you! Let me know!