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


"joeu2004" wrote in message
...
On Apr 14, 5:27 pm, I wrote:
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.