ThreeWave Scheduling Events With OnTime And Windows Timers

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

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

SectionBreak

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.

SectionBreak

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

SectionBreak

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.

caution 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:

Parameter Meaning
HWnd This is the HWnd (Windows Handle) of the Excel application. You can ignore this parameter.
uMsg The message ID of a timer message, value of 275. You can ignore this parameter.
nIDEvent This value indicates which timer is being "pop" if you have more than one Windows timer in effict. This is the value that was returned from the SetTimer API function.
dwTimer The number of milliseconds that Windows has been running. This is the same value that you would get from the GetTickCount Windows API function.

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

ShortFadeBar

This page last modified: 23-Oct-2008.