1. Technology

Using a Timer in Office VBA Macros

Coding a VBA macro that makes things happen on the clock.


Stopwatch appearing out of Laptop computer screen
Dimitri Otis/Digital Vision/Getty Images
Updated June 09, 2014

VBA - Visual Basic for Applications - is the only remaining VB6 "COM" level technology in the Microsoft universe. So, 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, how to add timed processes to your code is often not obvious to new users of VBA Macros. This article explains Timers in VBA for both groups.

Timers For Newbies

This article was inspired by an office administrator who was responsible for giving timed tests to applicants. She wanted a way to code a Word VBA macro to automatically time a test that was written in Word. This is a very 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 optomizing the slow sections. Sometimes, you might want to see if anything is happening in the application or if the computer seems to be just sitting there idle (which can be a security problem). Timers can do that.

The basic methodology for implementing a timer in a VBA program is as follows:

Start a timer.

You do this by coding an OnTime statement. Currently, this statement is only implemented in Word and Excel, and it has different syntax depending on which one you're using. (We'll look at alternatives for the others just a little later.) The syntax for Word is as follows:

expression.OnTime(When, Name, Tolerance)

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 quite different from the way it would be 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. (It's possible to instantiate another copy of Word or Excel and use that as "expression".) 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. (I wasn't able to make "Tolerance" work in Word, even though "LatestTime" seems to work just fine in Excel. I'm calling it a bug. If anyone can make it work in Word, let me know.)

Code the time event macro.

This can be any code. The adminstrator used this event to display a notification that the testing time had expired and print the result of the test.

A complete procedure (in Word) looks like this:

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

Note that the complete TestOnTime macro completed before EventMacro started, but that EventMacro kicked off exactly ten seconds after the OnTime statement was executed.

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 do your own math. Or you can use Windows API calls. Using Windows API calls has the advantage of being more precise than Timer. (Some people have complained that the precision of Timer isn't good enough.) 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)
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

On my system, I got this result:

MicroTimer Time taken was: 1.75951356945734

  1. About.com
  2. Technology
  3. Visual Basic

©2014 About.com. All rights reserved.