Timing Macros
VBA includes a built-in Timer function. Timer returns the number of seconds since 12:00 AM. Windows users get some decimals, but Mac users have to settle for the nearest second. I’ve never been all that convinced of Timer’s accuracy anyway. If a fraction of a second is important, I’d be testing Timer before I used it to test my application.
Inspired by a previous comment, here’s an example of how you might use Timer
Sub test()
Dim stime As Single
Dim i As Long
stime = Timer
For i = 1 To 1000
Range(”a1″).Formula = “1234″
Next i
Debug.Print “Formula”, Timer - stime
stime = Timer
For i = 1 To 1000
Range(”a1″).FormulaR1C1 = “1234″
Next i
Debug.Print “FormulaR1C1″, Timer - stime
stime = Timer
For i = 1 To 1000
Range(”a1″).Value = “1234″
Next i
Debug.Print “Value”, Timer - stime
stime = Timer
For i = 1 To 1000
Range(”a1″).Value2 = “1234″
Next i
Debug.Print “Value2″, Timer - stime
End Sub
And the results
My analysis of this is that it doesn’t matter which one you use. For me, I use the Value property for values and the Formula property for formulas. Call me old fashioned.
Rob van Gelder:
I use the Multimedia Timer for timing - Somewhere I read this a high resolution timer.
Declare Function timeGetTime Lib “winmm.dll” () As Long
Dim lngStart As Long
Sub Start()
lngStart = timeGetTime()
End Sub
Function Finish()
Finish = timeGetTime() - lngStart
End Function
Sub test()
Dim i As Long, lngLastRow As Long, lngTemp As Long, rng As Range
With ActiveSheet
’set up test data
For i = 1 To 10000: .Cells(i, 1).Value = i: Next
‘do the 3 speed tests
Start
For i = 1 To 10000
lngTemp = Range(”A” & i).Value
Next
Debug.Print “Test 1: ” & Finish
Start
For i = 1 To 10000
lngTemp = .Cells(i, 1).Value
Next
Debug.Print “Test 2: ” & Finish
Start
Set rng = .Range(”A1″)
For i = 0 To 10000 - 1
lngTemp = rng.Offset(i, 0).Value
Next
Debug.Print “Test 3: ” & Finish
End With
End Sub
29 June 2004, 2:42 amJamie Collins:
I use GetTickCount, the simplest of Win32 APIs and probably the first one I knowingly used. It returns milliseconds elapsed since Windows was started:
Option Explicit
Private Declare Function GetTickCount _
Lib “kernel32″ () As Long
Sub test1()
Dim lngStart As Long
Dim lngDuration As Long
lngStart = GetTickCount
‘
lngDuration = GetTickCount - lngStart
MsgBox Format(lngDuration / 24 / 60 / 60 / 1000, _
“hh:nn:ss”)
End Sub
Jamie.
–
29 June 2004, 4:31 amross:
But which is the best gents???
29 June 2004, 4:38 amJamie Collins:
I’ve discovered TimeGetTime has a 1 millisecond resolution, whereas GetTickCount is ‘only’ approx 10 ms.
Jamie.
–
29 June 2004, 11:08 amRob van Gelder:
MSKB 172338 has a good voerview of Operating System timers
http://support.microsoft.com/default.aspx?scid=kb;en-us;172338
30 June 2004, 1:04 amJamie Collins:
“MSKB 172338 has a good voerview of Operating System timers”
I discovered wrong! According to the article, GetTickCount and TimeGetTime are the same resolution (10 milliseconds). So I choose GetTickCount because it has a nicer… I mean, more meaningful name.
Jamie.
–
1 July 2004, 7:03 am