VBA for smarties: Application.Ontime

Application.Ontime

0 What is application.ontime

1 Unique ontime action

1.0 Four arguments
1.1 EarliestTime
1.1.1 absolute time
1.1.2 relative time

1.2 Procedure
1.2.1 in same workbook
1.2.2 in another workbook
1.2.3 in a closed workbook
1.2.4 macro contents

1.3 LatestTime

1.4 Schedule

1.5 The calling macro

2 'self calling' macro
2.1 Time storing variable
2.1.1 Worksheet
2.1.2 Workbook
2.1.3 Macromodule
2.1.4 Event

2.2 Application property storing
2.2.1 Application 'Name'
2.2.2 Statusbar
2.2.3 Customlist

2.3. Workbook property
2.3.1 Workbook 'name'
2.3.2 Custom documentproperty
2.3.3 Cell in worksheet

3. Simultaneous ontime actions
3.1 Unique actions
3.2 Recurring actions

What is application.ontime

Application.Ontime is a method in Excel with which you can trigger the execution of a macro in a workbook (Excel) at a predetermined time

Ontime is part of the application (Excel) and therefore runs independent of any workbook.
As long as the application (Excel) is active the method ontime stays active too.
Even if a workbook has been closed the Application will start the macro after (re)opening the workbook.
As soon as the application (Excel) has been shut all ontime actions will be removed.

1 Unique ontime action

EG. start macro M_snb at 12:45 hour

Sub M_snb_ontime_start()
Application.OnTime TimeSerial(12, 45, 0), "M_snb"
End Sub

Sub M_snb()
MsgBox "Illustration"
End Sub

1.0 The four arguments of the method ontime

The method OnTime has 4 arguments: EarliestTime, Procedure, LatestTime, Schedule

1.1 EarliestTime: the moment to start the macro

1.1.1 an absolute time value

example: at 12:45 hour

Sub M_snb_ontime_start()
Application.OnTime TimeSerial(12, 45, 0), "M_snb"
End Sub

Sub M_snb_ontime_start()
Application.OnTime TimeValue("12:45:00"), "M_snb"
End Sub

Sub M_snb_ontime_starten()
Application.OnTime CDate("12:45:00"), "M_snb"
End Sub

A string, that Excel is able to interpret as a time value.

Sub M_snb_ontime_start()
Application.OnTime "12:45", "M_snb"
End Sub

Sub M_snb_ontime_start()
Application.OnTime "12.45", "M_snb"
End Sub

Since Excel isn't always handling time values consistently (sometimes it uses the American system, sometimes it conforms to the international setting in Windows) the use of functions like cdate, timeserial and timevalue is the most reliable thing to do.

1.1.2 a relative time value

example: four hours from now

Sub M_snb_ontime_start()
Application.OnTime DateAdd("h", 4, Time), "M_snb"
End Sub

Sub M_snb_ontime_start()
Application.OnTime DateAdd("h", 4, Now), "M_snb"
End Sub

Sub M_snb_ontime_start()
Application.OnTime Time + 4 / 24, "M_snb"
End Sub

Sub M_snb_ontime_start()
Application.OnTime Now + 4 / 24, "M_snb"
End Sub

1.2 Procedure: the name of the macro that will be triggered

A macro can reside in different codemodules: the workbook, a worksheet, a macromodule, a userform or a class
I will not discuss macros in a userform or in a class.

The name of the macro will be written as a string (see the examples), just like in the method Application.Run.
If a macro resides in a macromodule and it's not a Private Sub you don't need to specify the codemodule's name, unless another macromodule contains a macro with an identical name.
Nevertheless it's always sensible to add the 'codename' to the macro name to prevent any confusion.

1.2.1 The macro resides in the same workbook as the macro that contains the ontime instruction

The macro resides in macromodule 'Module2'

Sub M_snb_ontime_start()
Application.OnTime DateAdd("h", 4, Time), "Module2.M_snb"
End Sub

The macro resides in the codemodule of 'ThisWorkbook'

Sub M_snb_ontime_start()
Application.OnTime DateAdd("h", 4, Time), "ThisWorkbook.M_snb"
End Sub

The macro resides in the codemodule of Sheet3

Sub M_snb_ontime_start()
Application.OnTime DateAdd("h", 4, Time), "Sheet3.M_snb"
End Sub

1.2.2 The macro is in another workbook

In the application all macros in open workbooks (visible workbooks, hidden workbooks (like e.g. personal.xlsb ) or loaded Add-Ins) are available.
So you'd better use the filename to call the macro as well. The macro 'M_evenaar' in the codemodule of 'sheet2' in workbook 'Example.xlsb'

Sub M_snb_ontime_start()
Application.OnTime DateAdd("s", 1, Time), "Example.xlsb!sheet2.M_evenaar"
End Sub

The macro 'M_equator' in the codemodule of 'sheet1' in workbook 'Personal.xlsb'

Sub M_snb_ontime_start()
Application.OnTime DateAdd("s", 1, Time), "Personal.xlsb!sheet1.M_equator"
End Sub

The macro 'M_equator' in the codemodule 'Module1' in AddIn 'snb_addin.xlam'

Sub M_snb_ontime_start()
Application.OnTime DateAdd("s", 1, Time), "snb_addin.xlam!Module1.M_equator"
End Sub

1.2.3 The macro is in a closed workbook

A macro in a closed workboek can also be triggered by the method Ontime.
The workbook will be opened first and subsequently the macro will be executed.
E.g. the macro "M_equator" in macromodule Module1 in the closed workbook "G:\OF\__ontime_closed_snb.xlsb"

Sub M_start()
Application.OnTime DateAdd("s", 1, Time), "G:\OF\__ontime_closed_snb.xlsb!Module1.M_equator"
End Sub

1.2.4 The contents of a to-be-triggered macro

The macro will be triggered at a moment that different workbooks could be openend / active/ have the focus than was the case when the ontime method was started.
If the code that will be executed is independent of the active workbook it poses no real problem, e.g.

Sub M_snb()
MsgBox "Illustration"
End Sub

That's quite a different story in this case

Sub M_snb()
Range("A1:C10").copy range("K1")
End Sub

The macro is being executed in the active worksheet.
Most of the time that's not what is being intended.
To ensure that the macro adapts the intended workbook and worksheet it's necessary to specify them both e.g.

Sub M_snb()
workbooks("illustration.xlsx").sheets("data").range("A1:K10").clearcontents
End Sub

1.3 LatestTime

Sometimes the macro can't be executed because the user is editing a cell, another macro is running etc.
Unlike the title of this argument suggests, this argument is meant to indicate how long the application will try to start the macro if it fails to do so the first time.
If this argument is empty the macro will be executed as soon as possible.

1.4 Schedule

This argument activates (True) or cancels (False) the specifically indicated (by time and macro name) method ontime.
Since the default is 'True', only the option 'False' is worth discussing. Activate 'ontime':

Sub M_snb_ontime_start()
Application.OnTime TimeSerial(12, 45, 0), "M_snb"
End Sub

Sub M_snb()
MsgBox "illustration"
End Sub

Cancel 'ontime'

Sub M_snb_ontime_cancel()
Application.OnTime TimeSerial(12, 45, 0), "M_snb", , False
End Sub

NB. Only if the 'earliest time' is still in the future the canceling has relevance.
The macro that cancels the ontime action requires the exact same time and macro name as used in the activating macro.

Do not use Alt-F8 to run the 'cancel' macro; it often results in Error 400.
Link the macro to a worksheet event, a workbook event, an ActiveX-control, a picture, etc. instead to avoid this error message.

1.5 The calling macro

The calling macro can be placed anywhere:
- in the codemodule of a workbook
- in the codemodule of a worksheet
- in the codemodule of a userform
- in the codemodule of a class

The calling macro can be a separate one (as in the examples above)

The calling macro can be part of the eventcode of a:

- workbook

Private Sub Workbook_Open()
Application.OnTime DateAdd("s", 4, Time), "ThisWorkbook.M_snb"
end sub

- worksheet

Private Sub Worksheet_BeforeDoubleClick()
Application.OnTime DateAdd("s", 4, Time), "ThisWorkbook.M_snb"
end sub

- ActiveX-control

Private Sub Commandbutton1_Click()
Application.OnTime DateAdd("s", 4, Time), "ThisWorkbook.M_snb"
end sub

2 A 'self calling' application.ontime containing macro

A macro that contains an ontime instruction can call itself. The macro will be executed every day at 12.45 o'clock

Sub M_snb_ontime_start()
MsgBox "illustration"
Application.OnTime TimeSerial(12, 45, 0), "M_snb_ontime_start"
End Sub

If this macro has been executed it will execute itself over and over again as long as the application - Excel - hasn't been quit.
If you don't want that you will have to start a macro that will cancel the ontime method.
The only way to do so is to cancel the ontime method using the exact same macro name and time and setting the argument 'Schedule' to FALSE.
Stop the planned action before it's 12.45 o'clock

Sub M_snb_ontime_cancel()
Application.OnTime TimeSerial(12, 45, 0), "M_snb_ontime_start", , False
End Sub

In this case it's simple because it's a fixed time value.

It's less simple in the case of a variable time value, e.g. dateadd("s",10,time).
In such a case you will have to register the time value to be able to execute the 'cancel' macro.

You can register the time value in 2 ways:
- use a variable to store the time value
that variable has to be available/accessible in both macros (the start macro and the cancel macro)
- use a property of the application (Excel), of a workbook or a worksheet or one of the objects in the application, a workbook or a worksheet

2.1 Variable to store the time value

To give access to a variable in different macros it needs to be declared as Private or Public.
To reduce any interference with other code I'd prefer to declare such a variable as Private.
That means that all macros in the same codemodule (the workbook's, a worksheet's, a userform's), can use this variable.

2.1.1 In a worksheet

The code is in the codemodule of worksheet sheet1.
Every 5 seconds the code will be executed.

Dim t

Sub M_snb_ontime_start()
MsgBox "illustration"

t = DateAdd("s", 5, Time)
Application.OnTime t, "sheet1.M_snb_ontime_start"
End Sub

Sub M_snb_ontime_cancel()
Application.OnTime t, "sheet1.M_snb_ontime_start", , False
End Sub

2.1.2 In the workbook

The code is in the codemodule of 'ThisWorkbook'.
Every 5 seconds the code will be executed.

Dim t

Sub M_snb_ontime_start()
MsgBox "illustration"

t = DateAdd("s", 5, Time)
Application.OnTime t, "Thisworkbook.M_snb_ontime_start"
End Sub

Sub M_snb_ontime_cancel()
Application.OnTime t, "ThisWorkbook.M_snb_ontime_start", , False
End Sub

2.1.3 In a macromodule

The code is in a 'normal' macromodule.
Every 5 seconds the code will be executed.

Dim t

Sub M_snb_ontime_start()
MsgBox "illustration"

t = DateAdd("s", 5, Time)
Application.OnTime t, "M_snb_ontime_starten"
End Sub

Sub M_snb_ontime_sluiten()
Application.OnTime t, "M_snb_ontime_start", , False
End Sub

2.1.4 In the BeforeClose event of the workbook

If you intend to cancel the ontime action at the moment the workbook will be closed (but not the application) it's obvious to use the 'BeforeClose' event.
It's practical to put the variable that stores the time value into the same codemodule where the 'BeforeClose' event resides: the workbook.

the code will look like this in the codemodule of 'ThisWorkbook':

Dim t

Sub M_snb_ontime_start()
MsgBox "illustration"

t = DateAdd("s", 5, Time)
Application.OnTime t, "Thisworkbook.M_snb_ontime_start"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime t, "ThisWorkbook.M_snb_ontime_start", , False
End Sub

2.2 Excel (Application) property / objectproperty to store the execution time value

2.2.1 An Application 'Name'

Add a 'name' to store the execution time value.
An existing name will be overwritten if a new 'name' with the same name will be added.
The value in the 'name' has to be evaluated in Excel; e.g. the value 12:00:00 wil be stored as "=0.5".
Excel evaluates between brackets [...] or with Evaluate("..".).

Sub M_snb_ontime_start()
Sheet1.TextBox1.Text = Time

Application.Names.Add "n_time", DateAdd("s", 1, Time)
Application.OnTime [n_time], "Thisworkbook.M_snb_ontime_start"
End Sub

Sub M_snb_ontime_cancel()
Application.OnTime [n_time], "Thisworkbook.M_snb_ontime_start", , False
End Sub

2.2.2 The Statusbar

The StatusBar is available to store a starttime regardless its visibility.
The Statusbar can contain strings only; a stored time value has to be converted to a time before applying it in the ontime method.

Sub M_snb_ontime_start()
Sheet1.TextBox1.Text = Time

Application.StatusBar = DateAdd("s", 1, Time)
Application.OnTime CDate(Application.StatusBar), "Thisworkbook.M_snb_ontime_start"
End Sub

Sub M_snb_ontime_cancel()
Application.OnTime CDate(Application.StatusBar), "Thisworkbook.M_snb_ontime_start", , False
End Sub

2.2.3 A customlist

Sub M_snb_ontime_start()
Sheet1.TextBox1.Text = Time

If IsDate(Application.GetCustomListContents(Application.CustomListCount)(1)) Then Application.DeleteCustomList Application.CustomListCount
Application.AddCustomList Array(DateAdd("s", 1, Time))
Application.OnTime Application.GetCustomListContents(Application.CustomListCount)(1), "Thisworkbook.M_snb_ontime_start"
End Sub

Sub M_snb_ontime_cancel()
Application.OnTime Application.GetCustomListContents(Application.CustomListCount)(1), "Thisworkbook.M_snb_ontime_start", , false
End Sub

2.3. A workbookproperty / workbook object property to store the time value

2.3.1 A workbook 'name'

An existing name will be overwritten if a new 'name' with the same name will be added.
The value in the 'name' has to be evaluated in Excel; e.g. the value 12:00:00 wil be stored as "=0.5".
Excel evaluates between brackets [...] or with Evaluate(" ... "),

Sub M_snb_ontime_start()
Sheet1.TextBox1.Text = Time

Thisworkbook.Names.Add "wn_time", DateAdd("s", 1, Time)
Application.OnTime [wn_time], "Thisworkbook.M_snb_ontime_start"
End Sub

Sub M_snb_ontime_cancel()
Application.OnTime [wn_time], "Thisworkbook.M_snb_ontime_start", , False
End Sub

2.3.2 A customdocumentproperty

A documentproperty can contain a time value.
Therefore converting its value to a time value isn't necessary.
Adding a custom documentproperty will not replace the existing one.
So it's necessary to check whether the custom documentproperty exists or not.

Sub M_snb_ontime_start()
On Error Resume Next
Sheet1.TextBox1.Text = Time

ThisWorkbook.CustomDocumentProperties("c_time") = DateAdd("s", 1, Time)
If Err.Number <> 0 Then ThisWorkbook.CustomDocumentProperties.Add "c_time", 0, 3, DateAdd("s", 1, Time)
Application.OnTime ThisWorkbook.CustomDocumentProperties("c_time"), "Thisworkbook.M_snb_ontime_start"
End Sub

Sub M_snb_ontime_cancel()
Application.OnTime ThisWorkbook.CustomDocumentProperties("c_time"), "Thisworkbook.M_snb_ontime_start", , False
End Sub

2.3.3 A cell in a worksheet

Any time value can be stored in a cell in a workbook.
Any user might change / remove that value; so it's not a very safe location.

3. Running ontime actions simultaneously

Excel can run several ontime instructions simultaneously.
These might be actions that run only once, but also repeatedly.

3.1 Simultaneous one-off actions

Sub M_snb_ontime_start()
Application.OnTime DateAdd("s", 1, Time), "sheet1.M_equator_1"

Application.OnTime DateAdd("s", 4, Time), "sheet1.M_equator_2"

Application.OnTime DateAdd("s", 10, Time), "sheet1.M_equator_3"
End Sub

Sub M_equator_1()
MsgBox "equator_1"
End Sub

Sub M_equator_2()
MsgBox "equator_2"
End Sub

Sub M_equator_3()
MsgBox "equator_3"
End Sub

3.2 Recurring simultaneous actions

Dim sn

Sub M_snb_ontime_start_0()
sn = Array(DateAdd("s", 1, Time), DateAdd("s", 3, Time), DateAdd("s", 6, Time))
Application.OnTime sn(0), "M_snb_ontime_start_1"
Application.OnTime sn(1), "M_snb_ontime_start_2"
Application.OnTime sn(2), "M_snb_ontime_start_3"
End Sub

Sub M_snb_ontime_cancel_0()
Application.OnTime sn(0), "M_snb_ontime_start_1", , False
Application.OnTime sn(1), "M_snb_ontime_start_2", , False
Application.OnTime sn(2), "M_snb_ontime_start_3", , False
End Sub

Sub M_snb_ontime_start_1()
ThisWorkbook.Sheets("sheet1").TextBox1.Text = Time

sn(0) = DateAdd("s", 1, Time)
Application.OnTime sn(0), "M_snb_ontime_start_1"
End Sub

Sub M_snb_ontime_start_2()
ThisWorkbook.Sheets("sheet1").TextBox2.Text = Time

sn(1) = DateAdd("s", 3, Time)
Application.OnTime sn(1), "M_snb_ontime_start_2"
End Sub

Sub M_snb_ontime_start_3()
ThisWorkbook.Sheets("Sheet1").TextBox3.Text = Time

sn(2) = DateAdd("s", 6, Time)
Application.OnTime sn(2), "M_snb_ontime_start_3"
End Sub