Download the XLA file. By downloading the file you agree that you understand the software is copyright © Tushar Mehta and you will make no attempt to reverse engineer or in any other way access the source. You also understand this is a beta version of the add-in and may have problems that are not present in a later 'final release' version.
Are you looking for an easy way to add VBA capability that relies on timed events?
A splash screen, perhaps?
A countdown timer that closes a userform after a pre-determined inactivity duration?
A floating form that displays the current time?
A way to close a workbook that hasn't been used in a while?
The applications are endless. And, this VBA component makes it easy for you to do so.
The add-in exports a class, called clsTimer. The properties, methods, and events of the class make it very easy for the developer to add time-based features to her/his application. See the documentation and examples below.
After downloading the file, unzip it and save the XLA file in a folder of your choice. The recommended, though not mandatory, folder would be same place where other user add-ins are stored. This folder is identified by the Application object's UserLibraryPath property.
Introduction to the Timer class
Properties, Methods, and Events
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.
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).
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.
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.
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 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.