Using a Timer in MS Office VBA Macros

Using a Timer in MS 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 youre using.  The syntax for Word is: 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  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, EventMacroDebug.Print (After OnTime: Now)End SubSub 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 PMAfter OnTime: 12/25/2000 7:41:23 PMExecuting Event Macro: 2/27/2010 7:41:33 PM Option for Other Office Apps Other Office applications dont 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. Heres a routine suggested by Microsoft that does the trick: Private Declare Function getFrequency Lib kernel32 _Alias QueryPerformanceFrequency (cyFrequency As Currency) As LongPrivate Declare Function getTickCount Lib kernel32 _Alias QueryPerformanceCounter (cyTickCount As Currency) As LongSub TestTimeAPICalls()Dim dTime As DoubledTime MicroTimerDim StartTime As SingleStartTime TimerFor i 1 To 10000000Dim j As Doublej Sqr(i)NextDebug.Print (MicroTimer Time taken was: MicroTimer - dTime)End SubFunction MicroTimer() As Double Returns seconds.Dim cyTicks1 As CurrencyStatic cyFrequency As CurrencyMicroTimer 0 Get frequency.If cyFrequency 0 Then getFrequency cyFrequency Get ticks.getTickCount cyTicks1 SecondsIf cyFrequency Then MicroTimer cyTicks1 / cyFrequencyEnd Function