On Thu, 14 Apr 2011 03:44:23 +0100, "spilly39" wrote:

Hi again

Years ago, when motherboards might not have a real time clock (yes, I'm that
old) I wrote a routine to time execution to the millisecond or so.
At the time I was researching and comparing different sorting and searching
techniques. But that was a long time ago and I no longer now have the code
(or the memory cells)
Even a fast routine is slow if you loop through it enough times, so "good"
code can be deceptively expensive in the wrong place.
ISTR some declarations of system DLLs were needed.


You whizz-kid developers must surely have some off the shelf functions for
returning the real time clock value to optimise execution speed. I could
imagine this as a handy developer's Class tool with methods like StartTimer,
StopTimer and an easy way of getting the difference in millisecs between the
two.

Anyone got anything they are willing to share?

My reason:
I'm developing an app, which I'd like to be generic, for Club and Community
Organisation membership. I want users to be allowed to move columns around
and insert new columns wherever they need to make it **their** Membership
page (e,g. some may want an emailAddress column; others not etc etc).
I'd like my app to cope on the fly with such interference - provided the
users do not remove my Red Letter Columns.

Named Ranges help to do this, but one wd have to be very careful to get the
correct column offset if users are to be allowed to do that kind of thing.

That's a specific reason why I want to measure the CPU cost of all this
flexibility, but one ought to use such a tool anyway.

spilly


Insert a new Class Module and name it: CHiResTimer

Enter this code into the module:

===============================
Option Explicit

'How many times per second is the counter updated?
Private Declare Function QueryFrequency Lib "kernel32" _
Alias "QueryPerformanceFrequency" ( _
lpFrequency As Currency) As Long

'What is the counter's value
Private Declare Function QueryCounter Lib "kernel32" _
Alias "QueryPerformanceCounter" ( _
lpPerformanceCount As Currency) As Long

'Variables to store the counter information
Dim cFrequency As Currency
Dim cOverhead As Currency
Dim cStarted As Currency
Dim cStopped As Currency


Private Sub Class_Initialize()
Dim cCount1 As Currency, cCount2 As Currency

'Get the counter frequency
QueryFrequency cFrequency

'Call the hi-res counter twice, to check how long it takes
QueryCounter cCount1
QueryCounter cCount2

'Store the call overhead
cOverhead = cCount2 - cCount1

End Sub

Public Sub StartTimer()
'Get the time that we started
QueryCounter cStarted
End Sub

Public Sub StopTimer()
'Get the time that we stopped
QueryCounter cStopped
End Sub

Public Property Get Elapsed() As Double

Dim cTimer As Currency

'Have we stopped or not?
If cStopped = 0 Then
QueryCounter cTimer
Else
cTimer = cStopped
End If

'If we have a frequency, return the duration, in seconds
If cFrequency 0 Then
Elapsed = (cTimer - cStarted - cOverhead) / cFrequency
End If

End Property
===============================

Then, in a regular module, use this routine:

===========================
Option Explicit
Sub TimeMacro()
Dim oTimer As New CHiResTimer

oTimer.StartTimer
MacroToBeTimed ' or other code
oTimer.StopTimer

MsgBox "That macro took " & Format(oTimer.Elapsed, "#.000000") & " seconds."

End Sub