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.
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
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.
But which is the best gents???
I’ve discovered TimeGetTime has a 1 millisecond resolution, whereas GetTickCount is ‘only’ approx 10 ms.
Jamie.
MSKB 172338 has a good voerview of Operating System timers
http://support.microsoft.com/default.aspx?scid=kb;en-us;172338
“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.