Scheduling Events With OnTime And Windows Timers

This page describes the Application.OnTime method and Windows Timers.

You may have the need to run some code periodically and automatically, such as code to pull data from an external data store. With some simple VBA code, you can call upon the the Application's OnTime method to automatically run a procedure. This pages describes how to do this. It also includes a section on using the timers provided via the Windows API functions.

Introduction

As parameters, the OnTime method takes a specific data and time at which it should run the procedure and the name of the procedure to run. It is important to remember that you provide the date and time to run the procedure, not an offset from the current time. If you need to cancel an OnTime, you must provide the exact time that the event was schedule to take place. There is no way to tell Excel to cancel the next OnTime event or to cancel all the pending OnTime events. Therefore, you need to store the time at which the procedure is to run in a Public variable and use that variable's value in calls to OnTime.

Note that if the workbook containing the procedure to be executed by OnTime is closed before the procedure is run, Excel will open the workbook before running the procedure and will leave the workbook open after the procedure is complete.

For example, declare Public variables in a standard code module, outside of and before any procedure (Sub or Function) declaration:

Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "TheSub"  ' the name of the procedure to run

Starting A Timer

To start a repeatable timer, create a procedure named StartTimer as shown below:

Sub StartTimer()
    RunWhen = Now + TimeSerial(0,0,cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=True
End Sub

This stores the time to run the procedure in the variable RunWhen, two minutes after the current time.

Next, you need to write the procedure that will be called by OnTime. For example,

Sub TheSub()
    ''''''''''''''''''''''''
    ' Your code here
    ''''''''''''''''''''''''
    StartTimer  ' Reschedule the procedure
End Sub

This procedure executes whatever code you include in it, and then at the end calls the StartTimer procedure to schedule another OnTime event. This is how the periodic calls are implemented. Note that if you close the workbook while an OnTime event is pending, Excel will re-open that workbook to execute the procedure and will not close the workbook after the OnTime event is finished.

Stopping A Timer

At some point, you or your code will need to terminate the OnTime schedule loop. To cancel a pending OnTime event, you must provide the exact time that it is scheduled to run. That is the reason we stored the time in the Public variable RunWhen. You can think of the RunWhen value as a unique key into the OnTime settings. (It is certainly possible to have multiple OnTime events pending. In this, you should store each procedure's scheduled time in a separate variable. Each OnTime event needs its own RunWhen value.)  The code below illustrates how to stop a pending OnTime event.

Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen,Procedure:=cRunWhat, _
        Schedule:=False
End Sub

Using Windows Timers

In addition to Excel's OnTime method, you can use the Windows Timer functions provided via Window API. Windows Timers are automatically rescheduled and will continue to "pop" until you terminate the timer with the KillTimer API function. With a Windows Timer, you provide the interval, in milliseconds, that the timer will "pop". The timer will "pop" at that interval until terminated with KillTimer.

These procedures require Excel 2000 or later, since we use the AddressOf operator. The code will not work in Excel 97 or earlier versions.

A NOTE OF CAUTION: If the code executed by the timer changes a cell value, and you are presently in edit mode in Excel (e.g., entering data in a cell), Excel will likely crash completely and you will lose all unsaved work. Use Windows timers with caution.

To use Windows timers, paste the following code into a standard code module:

Public Declare Function SetTimer Lib "user32" ( _
    ByVal HWnd As Long, _
    ByVal nIDEvent As Long, _
    ByVal uElapse As Long, _ 
    ByVal lpTimerFunc As Long) As Long

Public Declare Function KillTimer Lib "user32" ( _
    ByVal HWnd As Long, _
    ByVal nIDEvent As Long) As Long

Public TimerID As Long
Public TimerSeconds As Single

Sub StartTimer()
    TimerSeconds = 1 ' how often to "pop" the timer.
    TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub

Sub EndTimer()
    On Error Resume Next
    KillTimer 0&, TimerID
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
        ByVal nIDEvent As Long, ByVal dwTimer As Long)
    
    ''''''
    ' This procedure is called by Windows. Put your
    ' code here.
    ''''''
End Sub

Run the procedure StartTimer to begin the periodic timer. The variable TimerSeconds indicates how often, in seconds, the timer is to "pop". The SetTimer function takes a value in milliseconds, so the code multiplies TimerSeconds by 1000.When the timer "pops" Windows will call the procedrue TimerProc. You may name this procedure anything you want, but it must be declared with the parameters exactly as shown above. If the parameters differ from what is shown above, Excel will crash. When Windows calls TimerProc, it passes the following parameters:

To terminate a Windows timer, use the EndTimer procedure shown above, which calls KillTimer to actually terminate the timer.

This page last modified: 23-Oct-2008.