Ever needed a timer, but didn't want to add a form to your project? Were
you positively dumbstruck the first time you realized that the MSForms package
offered by Visual Basic for Applications (VBA) doesn't offer a Timer control?
Wish the Timer control's Interval property supported durations of several
minutes? Well, here's a totally code-based (two module) drop-in ready solution for
those situations, and many more.
This package includes not only a Classic VB sample, but also DOC, XLS,
and PPT files (within a /vba folder in the ZIP) with the same sample embedded.
But wait! That's not all. On the kind advice of some of my favorite Office (ab)
users, I've also compiled the timer into an OCX (within the /ocx folder in the
ZIP), for an additional edge of
safety in the IDE. The OCX includes not only a control that replicates (only
better!) the Classic VB timer control, but also a public class that can be
used to sink timer events in either form or class modules.
Code-Based Timer Objects
To get an idea of how to use this code-based timer object, open any one of the
provided examples. To use the timer object within your own projects, just
include the following two modules (from the root folder in the ZIP):
- CTimer.cls
- MSharedTimer.bas
Create module-level instances of the CTimer object, using WithEvents of
course, and set their properties as needed. The necessary BAS file is for
support of the CLS only, and you never need to directly interact with it. For
example, to update a label control to show the current date and time, you
could use code like this:
Option Explicit
Private WithEvents Timer1 As CTimer
Private Const defClockUpdate As Long = 500 'milliseconds
Private Sub UserForm_Initialize()
' Create new timer object instance.
Set Timer1 = New CTimer
' Set very short interval for first, to get
' current time immediately.
Timer1.Interval = 10
Timer1.Enabled = True
End Sub
Private Sub Timer1_Timer()
' Update caption to show current time.
Label1.Caption = Now
' Make sure interval is set appropriately.
' If we needlessly set the Interval property
' directly, the timer will be needlessly
' killed and restarted.
With Timer1
If .Interval <> defClockUpdate Then
.Interval = defClockUpdate
End If
End With
End Sub
The only modification you will ever need to make within the BAS module
would be to toggle the conditional constant declaration, if you move the
module between Classic VB and VBA:
#Const VBA = True
Deep background: In order to pass information into the API-based timer
callbacks, a window handle (on the same thread) needs to be associated with
the data. I chose to use the top-level "application" window in VBA
and the hidden top-level window in Classic VB. Obviously, detection methods
for these quite different window types varies, and Office object model
references in Classic VB would prevent compilation. So, in the end, you may
have to toggle this one constant declaration, as appropriate.
Improved Basic Timer Control
The OCX that's included with this project may be used in place of Classic
VB's standard Timer control. It provides essentially the same functionality,
but enhances the standard with the following features.
- Use of a Long value for the Interval property, allowing intrinsic
support far greater than one minute.
- Number of milliseconds elapsed since last Timer event passed to verify
whether the events are occurring in a timely <g> manner. (Timer
events are of extremely low priority in the Windows scheme of things!)
- CTimer object for use where a form isn't needed or wanted.
- Full support for VBA UserForms.
- Marked "Safe for Scripting" and "Safe for
Initialization" through implementation of the IObjectSafety
interface.
- Requires the VB6 runtime, as VB5
doesn't offer the ability to export both public controls and public
classes from the same ActiveX component. (Note: Office 2000 and higher
ship with msvbvm60.dll)
The issue of what window to associate with our timer callbacks was a bit
more complex in the OCX. I could have chosen to use the UserControl.hWnd
within the control, but what to use within the creatable class? I decided the
easiest thing to do was to simply create a hidden window on the fly. This is
done once at library startup, and undone at library shutdown, and this single
window is then associated with all callbacks.
So Cool You Might Not Notice It, Dept
The folks who really enjoy tricking VB into doing what they want it to,
rather than the other way around, owe it to themselves to take a close look
at the timer callback procedure:
Public Sub TimerProc(ByVal hWnd As Long, _
ByVal uMsg As Long, _
ByVal oTimer As CTimer, _
ByVal dwTime As Long)
' Alert appropriate timer object instance.
oTimer.RaiseTimer
End Sub
Notice that third parameter? How does Windows "know" what
instance of your CTimer class to hand you? That's a very little known trick.
If a procedure is passed an ObjPtr as one of its parameters, you can declare
that parameter as the object itself. VB does the cast for you. Normally, this
parameter would be a Long value which identifies which particular timer
instance was being fired. I decided to make it totally simple by using
ObjPtr(Me) in the SetTimer call within the CTimer class:
' Pass pointer to Me so we can return event to this instance.
m_TmrID = SetTimer(m_hWnd, ObjPtr(Me), m_Interval, AddressOf TimerProc)
This allows the callback to be passed back into the appropriate
CTimer instance with a simple one-line call. This concept has been expanded
greatly in the vbaTimer.ocx project, by implementing an ITimerObject interface
within both the usercontrol and class module. Now, a single callback can route
to the appropriate instance of the appropriate object with a single line of
code. Too cool, huh? Check it out.
