HOWTO Time VBA code

[spilly39]

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

 

[GS]

I'm not a whiz-kid (I'm in my 60s) but here's a simple way I find
useful...

Sub TimeEvents()
' use to time procedures or functions
Dim Starttime As Date, EndTime As Date
Starttime = Timer

'Run procedure

EndTime = Timer
MsgBox Format(EndTime - Starttime, "0.0000")
End Sub

Note that you can set whatever result format you want. (The format
above displays seconds to 4 decimal places.)

 

[spilly39]

Hi Garry
ISTR that resolution for Timer() & Now() is far less than the ticker inside
the RTC. Does that ring a bell?
In fact writing this brings back a vague memory that 'Tick' is the right
search term for what I remember. I'll have a go...
....Found it!

The keywords are GetTickCount and/or QueryPerformanceCounter
KB Article 172338 talks v simply about the various timers and gives sample
code
Also look at the bottom post on this page
http://answers.microsoft.com/en-us/...in-cells/37880163-6112-43e1-97aa-bd6d75e42bf6
There is some code you can paste into a module and it runs fine.
It has some nice random data generation in it too
The data is aimed at a rather specific problem, but the timer technique is
the bees knees

As I'm worried about inner loop performance, I think that Timer might not
get down to the resolution I wd like.

spilly

 

[Ron Rosenfeld]

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

 

[GS]

spilly39 wrote :

Hi Garry
ISTR that resolution for Timer() & Now() is far less than the ticker inside
the RTC. Does that ring a bell?
In fact writing this brings back a vague memory that 'Tick' is the right
search term for what I remember. I'll have a go...
...Found it!

The keywords are GetTickCount and/or QueryPerformanceCounter
KB Article 172338 talks v simply about the various timers and gives sample
code
Also look at the bottom post on this page
http://answers.microsoft.com/en-us/...in-cells/37880163-6112-43e1-97aa-bd6d75e42bf6
There is some code you can paste into a module and it runs fine.
It has some nice random data generation in it too
The data is aimed at a rather specific problem, but the timer technique is
the bees knees

As I'm worried about inner loop performance, I think that Timer might not get
down to the resolution I wd like.

spilly

Yes, you're correct about this alternative. I have code that uses
GetTickCount but that was more complex. Ron's offering also looks good
for what you're after and so I'd recommend going with that. I'll be
adding Ron's code to my collection, for sure.

You both have been doing this much longer than I have so I'm the novice
here. I started reading my first book on VBA Thanksgiving weekend of
2003. It was John Walkenbach's "Excel 2003 Power Programming with VBA".
-Been hooked on VB[A} ever since.<g> I met Rob Bovey in May 2004 and he
has been my mentor ever since. Between Rob's & John's books along with
studying what I've learned in the NGs, I'm gradually gaining knowledge
and experience from the pros. Hopefully, I'm able to give some back to
others seeking help. Your insight and wisdom is always appreciated...

 

[GS]

Thanks, Ron! -Very nice! (I'll be adding this to my lib for sure...)

 

[spilly39]

Ron
That's JUST what I'm looking for
And as a Class too!
Thanks greatly

spilly

 

[GS]

Ron Rosenfeld formulated on Thursday :

You're most welcome to both you and Gary. I wish I could recall where I got
this from (I did not originate it).

Much appreciated, Ron!

<FYI>
I believe this is what VBers call a "high resolution" counter that's
used on systems that support that. The next best thing down from this
is "GetTickCount", which is what I also use for highly granular
testing. It's slightly less complex than this one, but I mostly use
Timer for 'on-the-fly' tests.

I've seen several examples of this hi-res timer on DevX (FreeVBcode),
but didn't think anything I was doing warranted using it.

 

[joeu2004]

The next best thing down from this is "GetTickCount",
which is what I also use for highly granular testing.
It's slightly less complex than this one, but I mostly
use Timer for 'on-the-fly' tests.

Actually, GetTickCount and Timer have the same granularity on MSWin
systems (in contrast to Mac systems). Both are updated at each
"system tick", which is normally every 15.625 msec.

But in fact, Timer has greater precision than GetTickCount because
Timer is a floating-point number result with microsecond precision,
whereas GetTickCount is an integer number of milliseconds (rounded).

This is demonstrated by the following macro.

Declare Function GetTickCount Lib "Kernel32" () As Long

Sub doit()
Dim x As Double, x0 As Double, y As Long, y0 As Long
x0 = Timer: y0 = GetTickCount
Do: y = GetTickCount: Loop Until y <> y0
x = Timer
MsgBox Format(y - y0, "0 msec") & _
Format((x - x0) * 1000, " 0.000000 msec")
End Sub

Timer is indeed the easiest function to use since it is built into VBA
and does not require any special declaration.

But both are misleading if we are near the end of a system tick
interval when we first call Timer and/or GetTickCount. See the
example below.

Of course, just how significant that is depends on the total time for
the algorithm being measured.

I have been using the QueryPerformance functions for many years,
albeit without the elegance and overhead of a class structure. I
simply import the following VBA module (BAS file):

Public Declare Function QueryPerformanceFrequency _
Lib "kernel32" (ByRef freq As Currency) As Long
Public Declare Function QueryPerformanceCounter _
Lib "kernel32" (ByRef cnt As Currency) As Long

Private freq As Currency, df As Double

Function myTimer() As Currency
QueryPerformanceCounter myTimer
End Function

Function myElapsedTime(dt As Currency) As Double
'* return seconds
If freq = 0 Then QueryPerformanceFrequency freq: df = freq
myElapsedTime = dt / df
End Function

The following demonstrates the usage. Compare with the results of
GetTickCount and Timer, especially with and without syncing with the
system tick.

Declare Function GetTickCount Lib "Kernel32" () As Long

Sub doit()
Dim x As Double, x0 As Double, y As Long, y0 As Long
Dim dt As Currency '<--- *****
'* uncomment next line to sync with system tick
'x0 = Timer: Do: x = Timer: Loop Until x <> x0
dt = myTimer '<--- *****
x0 = Timer: y0 = GetTickCount
Do: y = GetTickCount: Loop Until y <> y0
x = Timer
dt = myTimer - dt '<--- *****
MsgBox Format(y - y0, "0 msec") & Chr(10) & _
Format((x - x0) * 1000, "0.000000 msec") & Chr(10) & _
Format(myElapsedTime(dt) * 1000, "0.000000000 msec")
End Sub

 

[GS]

Thanks for that info! I didn't know all those details about
GetTickCount. I've resorted to using Timer because of its ease of use
AND the fact that I can set the degree of decimal precision.

More added to my learning process. -Much appreciated...

 

[joeu2004]

Re: QueryPerformance functions

That's JUST what I'm looking for

But remember: the accuracy of the timer functions is meaningless if
you do not take proper steps to control the environment while
performance is being measured. Are you interested in some tips?

 

[Jim Cone]

The VBA Developers Handbook by Ken Getz states that the Timer function "...can measure time only to
1/18 second accuracy because of the particular internal timer it's using."
Also, "It turns over as midnight, so if you happen to be running a test over the bewitching hour,
your test results will be meaningless."

Strangely, KB172338 states that the Timer resolution is 1 second vs. 10ms for TimeGetTime and
GetTickCount.
(the summer intern strikes again).<g>

 

[GS]

After serious thinking Jim Cone wrote :

The VBA Developers Handbook by Ken Getz states that the Timer function
"...can measure time only to 1/18 second accuracy because of the particular
internal timer it's using."
Also, "It turns over as midnight, so if you happen to be running a test over
the bewitching hour, your test results will be meaningless."

Strangely, KB172338 states that the Timer resolution is 1 second vs. 10ms for
TimeGetTime and GetTickCount.
(the summer intern strikes again).<g>

Thanks, Jim!
I've been using 4 decimal places which means it's been returning
results to the ten-thousandth of a second. It actually does return any
number of decimals regardless of the accuracy. While accuracy may have
significance in some areas of eval, it's not really an issue for me
when timing running code. As I said, I just use it because it's 'quick
& easy'. If I need greater accuracy I use GetTickCount, though I'm
studying the benefit of using Ron's suggestion in it's place.

Your input is greatly appreciated...

 

[joeu2004]

The VBA Developers Handbook by Ken Getz states that the
Timer function "...can measure time only to 1/18 second
accuracy because of the particular internal timer it's
using."

A statement that is easily debunked by running the macro that I
provided.

1/18 seconds is about 55.556 msec, whereas I demonstrated that Timer
has a granularity of 15.625 msec (1/64 second), at least on Intel-
compatible MSWin XP systems.

(I suspect it true for all WinNT-based systems, which includes Win7
and Vista.)

Also, "It turns over as midnight, so if you happen to
be running a test over the bewitching hour, your test
results will be meaningless."

That gibes with the VBA Timer help page. And I confirmed it with my
own testing ;-).

It is true that GetTickCount does not suffer from that deficiency.
According to the GetTickCount "manpage", it rolls over about every
"49.7 days" -- actually about 49d 17h 2m 47.295s.

But note that that means GetTickCount will return a negative number
after about 24d 20h 31m 23.647s, since the VBA type is Long -- signed,
not unsigned as the system DWORD type is.

Probably not a concern for most users with personal computers. But
"always-on" shared and networked computers might be not rebooted
before then. At least, that is usually their goal.

In any case, the loss of precision can be significant in some cases of
time measurements.

I use Timer for quick-and-dirty time measures, being mindful not to
measure time across midnight ;-).

I use the QueryPerformance functions when I want precision, better
granularity or reliability.

Strangely, KB172338 states that the Timer resolution is
1 second vs. 10ms for TimeGetTime and GetTickCount.
(the summer intern strikes again).<g>

I never take for granted anything that MS documentation states, at
least not when it comes to very technical details (timers, binary
floating-point, numerical boundaries and limitations of functions,
etc).

 

[joeu2004]

As I said, I just use it [Timer] because it's 'quick
& easy'. If I need greater accuracy I use GetTickCount

And yet I demonstrated that GetTickCount has less, not greater
accuracy than Timer. Don't believe me. Look with your own eyes!

 

[GS]

joeu2004 wrote on 4/14/2011 :

As I said, I just use it [Timer] because it's 'quick
& easy'. If I need greater accuracy I use GetTickCount

And yet I demonstrated that GetTickCount has less, not greater
accuracy than Timer. Don't believe me. Look with your own eyes!

I'm not disputing what you say. I merely stated that I currently use it
over Timer when I want to, but now I'm considering sticking with Timer
for 'quick&easy' stuff and using Ron's code for more precision. IOW,
GetTickCounter is out!

 

[GS]

GS formulated the question :

IOW, GetTickCounter is out!

The above should read...

IOW, GetTickCount is out!

 

[spilly39]

If you're offering more tips in addition to the batch of postings above, I'm
listening avidly...

spilly


Re: QueryPerformance functions

That's JUST what I'm looking for

But remember: the accuracy of the timer functions is meaningless if
you do not take proper steps to control the environment while
performance is being measured. Are you interested in some tips?

 

[joeu2004]

But remember: the accuracy of the timer functions is meaningless
if you do not take proper steps to control the environment while
performance is being measured. Are you interested in some tips?

Of course, it depends on what you want to measure. Typically, we want
to measure a particular algorithm implemented in VBA or some
calculation in Excel. For the latter, we usually compute the time to
execute something like Range("a1").Calculate in VBA.

In my experience, the execution times in both cases can vary widely
for a number of reasons. I take the following steps to try to
minimize the variability.


1. Boot the system in safe mode without networking.

This eliminates a huge source of variability, namely many extraneous
interrupts and competing processes.

Of course, this is useful only if what you want to measure does not
require networking and the "extraneous" processes.

But keep in mind that we cannot eliminate all interrupts. Certainly,
the process clock will continue to interrupt the system at least every
15.625 msec, probably more frequently. There are probably other
minimal system interrupts that I'm not aware of.


2. If feasible, loop at least twice around the VBA code region to be
measured, ignoring the first iteration.

I have found that VBA introduces significant overhead (many msec on my
system) the first time each code branch is executed after a module
(any module?) is edited.

Of course, the significance of this overhead depends on the total
execution time for a code path. I am often measuring code regions
that complete in less than 16 msec. In that case, the first-time
overhead creates significant variability.


3. If feasible, loop many times around the code region to be measured,
keeping track of the time for each iteration and computing some
summary statistics such as average, avg or std dev, and min and max.

I look for a small relative avg or std dev (i.e. dev/avg). If I judge
it to be "too high", I suspect that extraneous system overhead has
tainted my results.

It is not uncommon to measure the total loop time, then divide by the
number iterations. That might provide a reasonable average time. But
it gives no insight into the variability of each iteration.

Usually, the granularity of QueryPerformanceCounter is good enough to
permit the measurement of each iteration individually.


4. If you are measuring an Excel formula, if feasible, replicate the
formula in many (1000s?) of cells and measure
Range("a1:a1000").Calculate instead of measuring a loop of 1000
iterations of Range("a1").Calculate, for example.

There is significant interprocess communication and process management
overhead when executing Range("a1").Calculate. If you iterate the
calculation of a single formula, typically you are measuring the
process management overhead, not the Excel processing time so much
unless it is very long relatively.

Arguably, the alternative -- Range("a1:a1000").Calculate -- incurs
increased Excel overhead: more memory management, a larger process
working set, etc. It's a trade-off and a judgment call.


5. If you are measuring an Excel formula, usually it is best to
disable ScreenUpdating and to set manual calculation mode. I also
disable EnableEvents, unless that is part of the measurement.

I disable ScreenUpdating and EnableEvents even if I am not measuring
an Excel formula. My hope is that that eliminates competion with the
Excel process. I have observed a significant difference in some
cases.


6. If you have a multicore system, I would disable all but one core if
the hardware allows for that.

I suspect that QueryPerformanceCounter is unreliable if the process/
thread migrates from one core to another for some reason.

However, Timer should not be affected. Usually the process clock
interrupt runs only one CPU. So Timer has the same value on all CPUs.


I hope this is helpful.

I should note that some of these precautions are debatable. It's a
judgment call. Performance measurement is as much an art as it is a
science, IMHO.

 

[spilly39]

This post goes into my local folder in its entirety.
Good stuff - as all well thought out common sense is.
Thanks on behalf of all the curious.

The responses to this thread made me sort out the main code loop which
worried me. It's now hugely simplified, much shorter, much simpler and much
more easily understood.
My gut feel is that it runs a lot better too, which I shall prove when I've
tidied up some loose ends

Thanks again

spilly
BTW I've already observed your "first iteration" delay - just reboot and
time using the clipboard for the first vs all the other times