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)
The time (Date variable) when the Procedure is to be executed
A string with the name of the Procedure which is to be executed
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
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!