Thanks for pointing me in the right direction. With your help I have
constructed a very useful function for testing the speed of code
execution which I'm posting below to help anyone else who might be
interested in doing the same...
Place this code in a new module:
Option Compare Database
Option Explicit
Public Declare Function GetTickCount Lib "kernel32" () As Long
Public tstart As Single
Private Enum ElapsedTimeType
ttMilliseconds = 1
ttSeconds = 2
ttMinutes = 3
End Enum
Private Function GetElapsedTime(dwTimeType As ElapsedTimeType) As
Single
Dim divisor As Long
Select Case dwTimeType
Case ttMilliseconds: divisor = 1
Case ttSeconds: divisor = 1000
Case ttMinutes: divisor = 60000
End Select
GetElapsedTime = (GetTickCount() - tstart) / divisor
End Function
Function TimeItUsingGetTickCount() As String
On Error GoTo myErr
'PURPOSE: Times a process in milliseconds from start to finish
Dim sngElapsed As Single
Dim sngAverage As Single
Dim sngTotal As Single
Dim LoopCount As Long
Dim LoopMax As Long
'set the number of times you want to run the code
'to find the total time and average time per loop:
LoopMax = 100
Do Until LoopCount = LoopMax
'reset the timer:
tstart = GetTickCount()
'run the code or function you wish to test here:
'******TEST CODE START*******
'******TEST CODE END*******
sngElapsed = GetElapsedTime(ttMilliseconds)
LoopCount = LoopCount + 1
sngTotal = sngTotal + sngElapsed
sngAverage = (sngAverage + sngElapsed) / LoopCount
Loop
TimeItUsingGetTickCount = "Total time for " & LoopMax & "
loops:" & vbCrLf & vbCrLf & FormatNumber(sngTotal, 0) & " milliseconds"
& vbCrLf & vbCrLf & _
"Average time = " & FormatNumber(sngAverage, 0) & "
milliseconds"
myExit:
Exit Function
myErr:
MsgBox Err.Number & " " & Err.Description
Resume myExit
End Function
pietlinden@hotmail.com wrote:[color=blue]
> How about this....
> (taken from vbnet.mvps.org... sorry, for some weird reason I can't[/color]
get[color=blue]
> the real page)
>
> '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
> ' Copyright ©1996-2005 VBnet, Randy Birch, All Rights Reserved.
> ' Some pages may also contain other copyrights by the author.
> '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
> ' Distribution: You can freely use this code in your own
> ' applications, but you may not reproduce
> ' or publish this code on any web site,
> ' online service, or distribute as source
> ' on any media without express permission.
> '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
>
> Private Declare Function GetTickCount Lib "kernel32" () As Long
>
> Private tstart As Single
>
> Private Enum ElapsedTimeType
> ttMilliseconds = 1
> ttSeconds = 2
> ttMinutes = 3
> End Enum
>
>
> Private Function GetElapsedTime(dwTimeType As ElapsedTimeType) As
> Single
>
> Dim divisor As Long
>
> Select Case dwTimeType
> Case ttMilliseconds: divisor = 1
> Case ttSeconds: divisor = 1000
> Case ttMinutes: divisor = 60000
> End Select
>
> GetElapsedTime = (GetTickCount() - tstart) / divisor
>
> End Function
>
>
> Private Sub Form_Load()
>
> 'Assign the current tick count
> 'as the tick start point. Failure
> 'to do this will return the number
> 'of ticks since Windows was last
> 'rebooted. (Note: elapsed time is
> 'stored as a DWORD value in Windows.
> 'Therefore, the time will wrap around
> 'to zero if the system is run continuously
> 'for 49.7 days. If a higher resolution
> 'timer is required, use a multimedia
> 'or high-resolution timer.
> tstart = GetTickCount()
>
> End Sub
>
> Calling Syntax
>
> Print FormatNumber(GetElapsedTime(ttMilliseconds), 0) &
> "milliseconds"
> Print FormatNumber(GetElapsedTime(ttSeconds), 2) & "
> seconds"
> Print FormatNumber(GetElapsedTime(ttHours), 2) & "
> minutes"
>
> If you search VBNet for "GetTickCount" you'll find all this fun[/color]
stuff...
Timer Function for Determining Code Execution Speed 
