Timing comparisons

[Jerry W. Lewis]

Is there any way to get elapsed time for a VBA routine to better
resolution than seconds?

When I want to compare alternate algorithms, I currently do something like

Function tryit()
st = Time
...
tryit = Time - st
End Function

but the resolution of values returned by Time is too rough to give
meaningful comparisons in most cases.

Jerry

[mows]

Jerry,

You could perhaps try something like this

Declare Function QueryPerformanceCounter Lib "Kernel32" _
(X As Currency) As Boolean
Declare Function QueryPerformanceFrequency Lib "Kernel32" _
(X As Currency) As Boolean
Sub DoSomething()
Dim Count1 As Currency, Count2 As Currency, Freq As Currency
QueryPerformanceFrequency Freq
QueryPerformanceCounter Count1

'Do whatever here
For i = 1 To 100000
c = i ^ 2 / i ^ 2
Next
'stop

QueryPerformanceCounter Count2
Debug.Print (Count2 - Count1) / Freq * 1000 & " milliSec"
End Sub

mows
ExcelXP / WinXP

Here is some more info
'**********************************************************
'Declare Function QueryPerformanceFrequency Lib "kernel32.dll" _
' (lpFrequency As LARGE_INTEGER) As Long

'lpFrequency
'Receives the 64-bit frequency of the computer's high-performance
'timer, if one exists.

'QueryPerformanceFrequency obtains the frequency of the system's
'high-performance timer. This value indicates the number of times
'the high-performance timer increments itself every second.
'Knowing this frequency, you can use QueryPerformanceCounter
'to measure precise time intervals. However, not all systems
'support a high-performance timer.
'**********************************************************
'Declare Function QueryPerformanceCounter Lib "kernel32.dll" _
' (lpPerformanceCount As LARGE_INTEGER) As Long

'lpPerformanceCount
'Receives the current 64-bit value of the computer's
'high-performance timer, if one exists.

'QueryPerformanceCounter obtains the current reading of the
'system's high-performance timer. The high-performance timer is a
'counter that the system increments many times a second. To find
'out how quickly the timer is incremented, use
'QueryPerformanceFrequency. If you know its frequency, you can use
'the high-performance timer to measure small intervals of time
'precisely. However, keep in mind that not all computers support
'a high-performance timer.

'Return Value :- If the system contains a high-performance timer,
'the functions returns a non-zero value. If the system does not
'contain such a timer, the function returns zero.
'**********************************************************
'Windows 95/98/ME/NT3.1+/2000/XP: Supported.
'Windows CE: Not Supported
'http://www.vbapi.com/ref/q/queryperformancecounter.html

[Tom Ogilvy]

If you want high precision and only need elapsed time.

http://support.microsoft.com/?kbid=172338
HOWTO: Use QueryPerformanceCounter to Time Code

http://support.microsoft.com/?kbid=189/8/62
HOWTO: Do 64-bit Arithmetic in VBA

Here is some timing code Charles Williams posted a while ago:

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 testit()
Dim j As Long
Dim str1 As String
Dim dTime As Double
dTime = MicroTimer
For j = 1 To 20000
str1 = strColid(255)
Next j
dTime = (MicroTimer - dTime) * 1000
MsgBox dTime
End Sub

Function MicroTimer() As Double
' returns seconds
' uses Windows API calls to the high resolution timer
'
Dim cyTicks1 As Currency
Static cyFrequency As Currency
'
strWhere = "MicroTimer"
'
MicroTimer = 0
'
' get frequency
'
If cyFrequency = 0 Then getFrequency cyFrequency
'
' get ticks
'
getTickCount cyTicks1
'
' calc seconds
'
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency

End Function

--------------

From: Jim Rech (jar...@kpmg.com)
Subject: Re: API Timer
View: Complete Thread (14 articles)
Original Format
Newsgroups: microsoft.public.excel.programming
Date: 2001-07-26 05:23:39 PST

>>I really do not want to use the timer control because that bogs down CPU
time.

Excel VB does not have a timer control. Bill's suggestion of using the
Timer function is the way to go. Per Help: "In Microsoft Windows the Timer
function returns fractional portions of a second." Here's an example:

Sub TestTimer()
Dim StartTime As Single
Dim EndTime As Single
Dim Counter As Integer
StartTime = Timer
''Procedure to time
For Counter = 1 To 10000
DoEvents
Next
''End procedure to time
EndTime = Timer
MsgBox "Time: " & EndTime - StartTime
End Sub

If you want to go crazy you could use the high performance timer that most
processors support:

Declare Function QueryPerformanceFrequency Lib "kernel32" _

(lpFrequency As LARGE_INTEGER) As Long

Declare Function QueryPerformanceCounter Lib "kernel32" _

(lpPerformanceCount As LARGE_INTEGER) As Long

Type LARGE_INTEGER
lowpart As Long
highpart As Long
End Type

Sub TestHighResolutionTimer()
Dim FirstCount As LARGE_INTEGER
Dim SecondCount As LARGE_INTEGER
Dim Counter As Integer
QueryPerformanceCounter FirstCount
''Procedure to time
For Counter = 1 To 10000
DoEvents
Next
''End procedure to time
QueryPerformanceCounter SecondCount
MsgBox "Timer counts: " & Format(SecondCount.lowpart -
FirstCount.lowpart, "#,##0")
''To convert to seconds divide by result of
GetHighResolutionTimerFrequency
End Sub

Sub GetHighResolutionTimerFrequency()
'I'm ignoring the "highpart" because it will be zero unless the
frequency
'exceeds 2^32 or 4,294,967,267 which I don't believe is likely.
Dim Freq As LARGE_INTEGER
If QueryPerformanceFrequency(Freq) = 0 Then
MsgBox "Your computer does not support the high performance timer"
Else
MsgBox "Your computer's high resolution timer frequency is " &
Format(Freq.lowpart, "#,##0") & " counts per second"
End If
End Sub

--
Jim Rech
Excel MVP

----------------------------------------------------------------------------
----

Regards,
Tom Ogilvy

"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:3E03104E.3030001@no_e-mail.com...