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):
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.
This sample hasn't been published anywhere except here on this website, but first rights to such publication are jealously guarded - you have been warned. <g>
This sample uses the following API calls:
Module Library Function CTimer.cls user32 KillTimer
Don't see what you're looking for? Here's a complete API cross-reference.
Please, enjoy and learn from this sample. Include its code within your own projects, if you wish. But, in order to insure only the most recent code is available to all, I ask that you don't share the sample by any form of mass distribution.
Download TimerObj.zip, 165Kb, Last Updated: Friday, June 17, 2005