Using a Timer in Office VBA Macros

For those of us who have our minds deeply into VB.NET, the journey back to VB6 can be a confusing trip. Using a Timer in VB6 is like that. At the same time, adding timed processes to your code is not obvious to new users of VBA Macros.

Timers For Newbies

Coding a Word VBA macro to automatically time a test that was written in Word is a typical reason for using a timer. Another common reason is to see just how much time is being taken by different parts of your code so you can work on optimizing the slow sections. Sometimes, you might want to see if anything is happening in the application when the computer seems to be just sitting there idle, which can be a security problem. Timers can do that.

Start a Timer

You start a timer by coding an OnTime statement. This statement is implemented in Word and Excel, but it has different syntax depending on which one you're using. The syntax for Word is:

expression.OnTime(When, Name, Tolerance)

Read More

Ten Tips for Coding Excel VBA Macros

By Dan Mabbutt

The syntax for Excel looks like this:

expression.OnTime(EarliestTime, Procedure, LatestTime, Schedule)

Both have the first and second parameter in common. The second parameter is the name of another macro that runs when the time in the first parameter is reached. In effect, coding this statement is like creating an event subroutine in VB6 or VB.NET terms. The event is reaching the time in the first parameter. The event subroutine is the second parameter.

This is different from the way it is coded in VB6 or VB.NET. For one thing, the macro named in the second parameter can be in any code that is accessible. In a Word document, Microsoft recommends putting it in the Normal document template. If you put it in another module, Microsoft recommends using the full path: Project.Module.Macro.

The expression is usually the Application object. The Word and Excel documentation states that the third parameter can cancel the execution of the event macro in case a dialog or some other process prevents it from running within a certain time. In Excel, you can schedule a new time in case that happens.

Code the Time Event Macro

This code in Word is for the administrator who wants to display a notification that the testing time has expired and print the result of the test.

Public Sub TestOnTime()
Debug.Print "The alarm will go off in 10 seconds!"
Debug.Print ("Before OnTime: " & Now)
alertTime = Now + TimeValue("00:00:10")
Application.OnTime alertTime, "EventMacro"
Debug.Print ("After OnTime: " & Now)
End Sub
Sub EventMacro()
Debug.Print ("Executing Event Macro: " & Now)
End Sub

This results in the following content in the immediate window:

The alarm will go off in 10 seconds!
Before OnTime: 12/25/2000 7:41:23 PM
After OnTime: 12/25/2000 7:41:23 PM
Executing Event Macro: 2/27/2010 7:41:33 PM

Option for Other Office Apps

Other Office applications don't implement OnTime. For those, you have several choices. First, you can use the Timer function, which simply returns the number of seconds since midnight on your PC, and does your own math, or you can use Windows API calls. Using Windows API calls has the advantage of being more precise than Timer. Here's a routine suggested by Microsoft that does the trick:

Private Declare Function getFrequency Lib "kernel32" _
Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" _
Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
Sub TestTimeAPICalls()
Dim dTime As Double
dTime = MicroTimer
Dim StartTime As Single
StartTime = Timer
For i = 1 To 10000000
Dim j As Double
j = Sqr(i)
Next
Debug.Print ("MicroTimer Time taken was: " & MicroTimer - dTime)
End Sub

Function MicroTimer() As Double
'
' Returns seconds.
'
Dim cyTicks1 As Currency
Static cyFrequency As Currency
'
MicroTimer = 0
' Get frequency.
If cyFrequency = 0 Then getFrequency cyFrequency
' Get ticks.
getTickCount cyTicks1
' Seconds
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency
End Function