Nick,
From the Chip Pearson site:
http://www.cpearson.com/excel/ontime.htm
You may need to design your Excel workbooks to run a procedure
periodically, and automatically. For example, you may want to refresh data
from a data base source every few minutes. Using VBA, you can call upon the
OnTime method of the Excel Application object to instruct Excel to run a
procedure at a given time. By writing your code to call the OnTime method
by itself, you can have VBA code automatically execute on a periodic basis.
This page describes the VBA procedures for doing this.
Introduction
As arguments, the OnTime method takes a specific date and time, and a
procedure to run. It is important to remember that you tell Excel
specificially when to run the procedure, not an offset from the current
time. In order to cancel a pending OnTime procedure, you must pass in the
exact time that the procedure is scheduled to run. You can't tell Excel to
cancel the next scheduled procedure. Therefore, it is advisable to store
the time that the procedure is schedule to run in a public (or global)
variable, which is available to all your code. Then, you can use the time
stored in that variable to schedule or cancel the event. The example code
will also store the name of the procedure to run and the reschedule interval
in public constants, although this is not required.
Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "The_Sub"
Starting A Timer Process
To start the process, use a procedure called StartTimer, similar to
the code shown below.
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub
This stores the date and time two minutes from the current time in the
RunWhen variable, and then calls the OnTime method to instruct Excel when to
run the cRunWhat procedure.
Since is a string variable containing "The_Sub", Excel will run that
procedure at the appropriate time. Below is a sample procedure:
Sub The_Sub()
'
' your code here
'
StartTimer
End Sub
Note that the last line of The_Sub calls the StartTimer procedure.
This reschedules the procedure to run again. And when the The_Sub procedure
is called by OnTime the next time, it will again call StartTimer to
reschedule itself. This is how the periodic loop is implemented.
Stopping A Timer Process
At some point, you or your code will want to stop the timer process,
either when the workbook is closed or when some condition is met. Because
the OnTime method is part of the Application object, simply closing the
workbook which created the event will not cancel a call to OnTime. As long
as Excel itself remains running, it will execute the OnTime procedure,
opening the workbook if necessary.
To stop an OnTime procedure, you must pass the exact scheduled time to
the OnTime method. This is why we stored the time in the RunWhen public
variable . Otherwise, there would be no way of knowing exactly what time
the process was schedule for. (The scheduled time works like a "key" to the
OnTime method. Without it, there is no way to access that event.)
Below is a procedure called StopTimer which will stop the pending
OnTime procedure.
Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub
This procedure uses the same OnTime syntax used in the StartTimer
procedure, except that it has the schedule parameter set to False, which
tells Excel to cancel the procedure. You may want to include a call to
this procedure from the Auto_Close macro or Workbook_BeforeClose event
procedure. The StopTimer procedure uses an On Error Resume Next statement
to ignore any error that might be generated if you attempt to cancel a
non-existent procedure.
Using Windows Timers
In addition to Excel's OnTime method, you can use the Timer functions
provided by the Windows API library. In some ways, using the API procedures
are easier than OnTime. First, you tell Windows the interval at which you
want to the timer to "pop" rather than a specific time of day. And next,
the API procedure will automatically reschedule itself. The timer will
"pop" every interval until you tell it to stop.
These procedures require that you are using Office 2000 or later,
because we use the AddressOf function. They will not work in Excel 97 or
earlier.
To use Windows timers, put the following code in 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)
'
' The procedure is called by Windows. Put your
' timer-related code here.
'
End Sub
Execute the StartTimer procedure to begin the timer. The variable
TimerSeconds indicates how many seconds should be between timer "pops". This
value may be less than 1. Note that the SetTimer procedure takes a value in
milliseconds, so we multiply TimerSeconds by 1000 when we call SetTimer.
The procedure TimerProc will be called by Windows every time the timer pops.
You can name this procedure anything you want, but you must declare the
argument variables exactly as shown in the example. If you change the name
of the procedure, be sure to change the name in the call to SetTimer as
well. Windows will pass the following values to the TimerProc procedure:
HWnd The Windows handle of the Excel application.
Generally, you can ignore this.
uMsg The value 275. Generally, you can ignore this.
nIDEvent The value returned by SetTimer to the TimerID variable. If
you have made more than one call to SetTimer, you can examine the nIDEvent
argument to determine which call SetTimer to resulted in the procedure being
called.
dwTimer The number of milliseconds that the computer has been
running. This is the same value that is returned by the GetTickCount
Windows procedure.
To stop the timer loop, call the EndTimer procedure. This procedure
calls KillTimer, passing it the value returned by SetTimer.
There are two significant differences between the API timer and
Excel's OnTimer procedure. First, the API timer is much more accurate at
time intervals of 1 second or less. Second, the API timer will execute even
if Excel is in Edit Mode (that is, when you are editing a cell).
