Contents
Introduction to the Timer class
Properties, Methods, and Events
Examples
Distribution and Pricing
The add-in exposes one
class, clsTimer. This class supports the properties,
methods, and events described below. All that the
developer has to do is set the appropriate properties, start
the timer, and handle the events the add-in raises.
Once the developer sets the
various properties and starts the timer, all s/he has to do is
respond to the events the Timer class raises. One event,
TimerTick occurs every so often (controlled by the developer
specified TickIntervalMilliSecs) and the other occurs in
countdown mode when the counter expires (CountdownComplete).
For those instances where the developer cannot handle events,
the Timer class can also run a developer-specified subroutine
(specified through the TimerTickProcName and
CountdownDoneProcName properties, respectively).
The Timer class uses the
Windows APIs SetTimer and KillTimer to raise internal events.
That means it can work with millisecond specification (unlike
Excel's OnTime method that has a 1 second minimum).
However, one should note that no event is actually accurate to
the expected millisecond. Various factors, ranging from
global issues such as a busy system to issues local to Excel
such as the application being in edit mode, can delay the time
at which an event occurs.
In countdown mode, the
Timer class uses the VBA Timer() function to measure elapsed
time. Since the system resets the Timer() value at
midnight, this add-in is unsuitable for use as a
countdown timer across midnight.
Note that only one timer
can be active at any given time.
Can the developer do without this
add-in? Of course. Thousands, if not millions have
for so long. However, it has always required
coordination between different parts of the program using
global variables and is far from elegant. Based on the
number of people who ask for help on this subject, it is not
the easiest thing to do. This add-in, on the other hand,
makes using timed events as simple as using any other object.
Properties
TimerType As Integer
The timer supports one of
two modes. A countdown mode in which it counts down
from a developer specified time (CountdownDurationMilliSecs)
at a developer specified rate (TickIntervalMilliSecs) until
it gets to zero. The other is a perpetual mode where
it simply raises an event at a developer-specified rate (TickIntervalMilliSecs).
Each time the
TickIntervalMilliSecs passes, the class raises an event,
TimerTick.
When in countdown mode,
the class also raises an event when the countdown timer
expires.
The possible values are
TimerTypeCountdown and
TimerTypePerpetual
There is no default
value. The developer must set this property before
calling startTimer.
TickIntervalMilliSecs As Integer
This is the rate at which
the timer class will raise the event TimerTick.
Note that the time is
specified in milliseconds. The default
is 1000 milliseconds (i.e., 1 second).
CountdownDurationMilliSecs As Long
If the TimerType is
specified as TimerTypeCountdown, this property indicates the
countdown duration.
Note that the time is
specified in milliseconds. The default
is zero milliseconds
CurrentMilliSecsLeft As Long
Read-only. In
countdown mode, contains the remaining time in
milliseconds. Since the timer accuracy is not
guaranteed -- the system may be busy, handling other events,
the Excel program may be in edit mode, etc. -- the value in
CurrentMilliSecsLeft may be negative.
TimerTickProcName As String
A developer specified
subroutine name that is called each time the TimerTick event
occurs. Note that this must be a well-formed name that
allows the Timer class to run a subroutine in
another file (the file
containing the developer's code).
CountdownDoneProcName As String
A developer specified
subroutine name that is called in count down mode when the
timer expires. Note that this must be a well-formed
name that allows the Timer class to run a subroutine in
another file (the file
containing the developer's code).
Methods
startTimer
Call this after the
relevant properties are set to start the timer
cancelTimer
Call this to cancel the
timer. Optional in countdown mode. However, must
be called in perpetual mode; otherwise there will be no way
to stop the timer.
resetCountdown
In countdown mode, resets
the timer to the beginning, i.e., to the value specified in
CountdownDurationMilliSecs.
Events
TimerTick(TimerVal As Single)
This event is raised by
the Timer class after each TickIntervalMilliSecs. The
client application may, if it wishes, respond to it.
For those cases where the developer cannot respond to the
event, s/he can specify a subroutine that the Timer class
will also call (TimerTickProcName)
TimerVal is the value of
the VBA Timer function.
This event is also raised
when the Timer is started and when the timer expires (in
countdown mode). This allows the developer to simplify
her/his code development.
CountdownComplete()
In countdown mode, this
event is raised when the timer expires. Since the
timer accuracy is not guaranteed (the system may be busy,
handling other events, the Excel program may be in edit
mode, etc.), the value in CurrentMilliSecsLeft may be
negative.
Referring to the addin
The developer can choose to
connect to the add-in either by using Application.Run
or by establishing a reference to the add-in.
To create the Timer object:
Direct call to the add-in
(i.e., without establishing a reference) and assuming the
add-in is installed in the user library location:
set TimerObj = Application.Run("'" & Application.UserLibraryPath _
& "tm vba timer.xla'!createTimer")
With a reference to the
TMTimer project in the TM
VBA Timer.xla file (in the VBE, use Tools |
References...), the code would be:
set TimerObj = TMTimer.createTimer
A splash
form
A userform that closes after a
predetermined time (unless the user resets the countdown)
A
modeless userform that displays the current time
Use the Timer class in a standard
module
Add a reference to the
TMTimer add-in XLA file (Tools | References...)
Create a userform that will
act as the splash form. Name it SplashScreen. Add
one subroutine (startCounter) to set the properties of the
Timer object and start it. Since this is a splash form,
we only need to know when the time has expired. So, for
a 10 second splash form, set both CountdownDurationMilliSecs
and TickIntervalMilliSecs to 5*1000. Set the TimerType
to TimerTypeCountdown and invoke the startTimer method.
We also need to know when
the timer expires. For that add the CountdownComplete
event procedure. All that it needs to do is unload the
userform.
The code specific to the
Timer object is modularized in the startCounter subroutine.
The userform's Activate event procedure calls this subroutine
(of course, it might also have other code but not in this
example).
Also, because this is a
splash form, we don't want the user to be able to cancel the
form. So, we add the QueryClose event procedure.
The procedure rejects the user's attempts to close the splash
form but correctly handles an application or a system request
to quit.
In the code module of the
userform, add:
Option Explicit
Dim WithEvents CountdownTimer As TMTimer.clsTimer
Private Sub CountdownTimer_CountdownComplete()
Me.Hide
End Sub
Private Sub startCounter()
Set CountdownTimer = TMTimer.createTimer
With CountdownTimer
.CountdownDurationMilliSecs = 5 * 1000
.TickIntervalMilliSecs = 5 * 1000
.TimerType = .TimerTypeCountdown
.startTimer
End With
End SubPrivate Sub UserForm_Activate()
startCounter
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
'Reject user attempt to close splash screen
Cancel = 1
Else
'Excel or OS asking us to shutdown
CountdownTimer.cancelTimer
Unload Me
End If
End Sub
To show the splash form, in a standard
module use code such as
SplashScreen.Show
This is basically like a
splash screen except that the user has the opportunity to
interact with it and reset the countdown. Suppose the
developer already has a form that s/he wishes to close if
there is no activity for some amount of time. Such a
form should have an OK button, a Cancel button (the Cancel
property of this button set to True) and some other controls
that the developer has added.
A good and friendly design
would not surprise the user by closing the form without
warning. We should periodically inform her/him about how
long before the form closes on its own. And, of course,
if the user really needs more time, s/he should be able to
reset the countdown clock. To that effect, add two more
controls: a label, called Label1, and a button, named Reset.
In the userform code
module, we will have code similar to the above code with a few
refinements. The countdown duration is still 10 seconds,
but this time we want to inform the user every 3 seconds about
how long s/he has. To process the tick event raised by
the Timer class, add the CountdownTimer_TimerTick event
procedure. In this procedure we use Label1 to inform the
user of the state of the countdown.
In addition, as part of the
user interface, the user has three options that were
unavailable for a splash form. S/he can OK the form,
cancel it, or request that the timer be reset. That code
goes into the OK_Click, Cancel_Click, and Reset_Click
procedures. They, of course, are linked to the OK,
Cancel, and Reset buttons respectively.
Option Explicit
Dim WithEvents CountdownTimer As TMTimer.clsTimer
Private Sub CountdownTimer_CountdownComplete()
Unload Me
End Sub
Private Sub CountdownTimer_TimerTick(TimerVal As Single)
Me.Label1.Caption = "This form will close in " _
& Round(CountdownTimer.CurrentMilliSecsLeft / 1000, 0) & " seconds"
End Sub
Private Sub startCounter()
Set CountdownTimer = TMTimer.createTimer
With CountdownTimer
.CountdownDurationMilliSecs = 10 * 1000
.TickIntervalMilliSecs = 3 * 1000
.TimerType = .TimerTypeCountdown
.startTimer
End With
End SubPrivate Sub Cancel_Click()
CountdownTimer.cancelTimer
Unload Me
End Sub
Private Sub OK_Click()
MsgBox "OK"
CountdownTimer.cancelTimer
Me.Hide
End Sub
Private Sub Reset_Click()
CountdownTimer.resetCountdown
End Sub
Private Sub UserForm_Activate()
startCounter
End Sub
A userform can be shown
modeless only in Excel 2000 or later.
Create a userform with no
controls. Name it Clock. Shrink it so that it is
as small as possible. The caption of this form will be
the current time in a hh:mm:ss format. Since there are
no controls, the user can close the form only by clicking the
'X' in the userform's title bar. We will update the time
every second, which also happens to be the default value of
TickIntervalMilliSecs.
Clearly, this is a
perpetual timer. There is no 'countdown expired' event.
At the same time, we want to know each time 2 seconds expire.
Finally, when the user closes the form, we rely on code in the
QueryClose event procedure to cancel the active timer.
Add the following code to
the userform's code module.
Option Explicit Dim WithEvents EasyTimer As TMTimer.clsTimer
Private Sub EasyTimer_TimerTick(TimerVal As Single)
Me.Caption = Format(Now(), "hh:mm:ss")
End Sub
Private Sub startCounter()
Set EasyTimer = TMTimer.createTimer
With EasyTimer
.TickIntervalMilliSecs = 2 * 1000
.TimerType = .TimerTypePerpetual
.startTimer
End With
End SubPrivate Sub UserForm_Activate()
startCounter
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
EasyTimer.cancelTimer
Unload Me
End Sub
To start the timer, use
code in a standard module:
Clock.Show vbModeless
In the above examples, we relied on
(1) a reference to the add-in's XLA file and (2) event
procedures to respond to the Tick and CountdownComplete
events. In this, the final, example, we will do neither.
Suppose we want to create a timer in a standard module that
counts down from 2.8 seconds and informs us of the countdown
every 0.5 seconds. Since this is in a standard module,
we cannot declare a 'WithEvents' object, and, consequently, we
have no way to use the event procedures.
Put the code below in a standard
module. It initiates the Timer object after setting the
appropriate time properties as in the examples above. In
addition, it specifies the subroutines that should be called
each time the TimerTick event and the CountdownComplete event
would have occurred.
It is important to note that the
developer must use complete filenames together with the
subroutine names.
Option Explicit
'Dim CountdownTimer As TimerClass.clsCountdown
Dim CountdownTimer As Object
Sub testInStdModule()
'Set CountdownTimer = TimerClass.createCountdownTimer
Set CountdownTimer = Application.Run( _
"'" & Application.UserLibraryPath _
& "TM VBA Timer.xla'!createTimer")
With CountdownTimer
.CountdownDurationMilliSecs = 2.8 * 1000
.TickIntervalMilliSecs = 500
.TimerType = .TimerTypeCountdown
.TimerTickProcName = "'" & ThisWorkbook.FullName & "'!CountdownTick"
.CountdownDoneProcName = "'" & ThisWorkbook.FullName & "'!CountDownDoneProc"
.startTimer
End With
End Sub
Public Sub CountdownTick()
Debug.Print "Test CountdownTick: " & CountdownTimer.CurrentMilliSecsLeft
End Sub
Public Sub CountDownDoneProc()
Debug.Print "Test CountDownDoneProc"
End Sub
Simply include the XLA file
(after acquiring a license to do so) in your normal
installation process.
For use by a developer
for development and testing of her/his own software the add-in is free.
For distribution with the
software that uses the add-in, the cost is $29.95. This
entitles the developer to distribute the add-in. It does
not give her/him access to the source code.
Since this is a beta
version, contact me and we can negotiate an appropriate
discount.
For the source code, the
cost is $49.95.
|
