Timer() variability

[gototcm]

Simple question regarding a procedure that takes different amounts of time to run for the exactly same code each time it is run.

Simplified code:

Please Login or Register  to view this content.

Each time I run the procedure I get a slightly different execution time: 0.277 sec, 0.285 sec, 0.307 sec, etc.

I suspect the cause is the way Windows schedules or handles the execution of Excel VBA but I am simply curious if anyone knows the answer. And if anyone knows an answer to the deeper question of what is the expected time variability associated with Windows if that is in fact, the cause?

Thanks

[Kenneth Hobson]

I usually take an average of 3 for time tests. Due to multi-processors and memory sharing, the times will never be the same. There are many programs running in the background. I find that sometimes a first run will take longer than subsequent runs.

There are more detailed discussions and papers on the topic. e.g. https://drops.dagstuhl.de/opus/vollt...CET-2016-6.pdf

[Pepe Le Mokko]

@gotocm
Administrative note
Are you still using XL2003? If not perhaps update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to? Members tailor answers based on your Excel version.
Thanks

[gototcm]

Thanks much Ken. That's what I figured and I certainly review the link you sent me to get a bit smarter.

[gototcm]

Oh geez, thanks for the alert. I will make the change to my profile.

Thanks

[joeu2004]

[.... deleted by me ....]

[joeu2004]

what is the expected time variability associated with Windows

"Time" [sic] variability, like the variability of your measurement?

Or "Timer" variability, referring to the VBA Timer function, as your title states?

-----

No one could intelligently discuss any variability of your measurements, since you provide no details about the code.

However, the numbers that you posted might not reflect any significant measurement variability at all(!), within the limited precision of your measurements -- notwithstanding the fact that we certainly do expected some variability.

(Of course, if you have other numbers that do indeed reflect significant variability, the "explanation" again is the unexplainable for lack of details.)

-----

Although the VBA Timer result is precise to 2^-7 to 2^-22 sec, depending on the time of day, the default resolution (update frequency) is 15.625 msec.

That is, the system clock is updated every 15.625 msec (a "tick"), by default.

(Theoretically, some applications might change the frequency of the clock interrupt. So it is prudent to close all other windows when you measure.)

So you can expect an apparent measurement "variability" of at least 16 msec, even if there were no actual variability at all.

Your posted numbers (0.277 to 0.307 sec) differ by 30 msec -- about 2 ticks.

If your actual elapsed time is nearly 0.281250 sec (18 ticks), the difference of 30 msec could be explained by a shift of the measured interval with respect to clock interrupts, as well as the VBA Timer variability described below.

That is, if the measured interval starts just before a clock interrupt and ends just after the last clock interrupt, and if the last clock interval appears to be longer for the reasons described below, the measured interval might appear to be about 2 ticks longer than it actually is.

-----

Time between VBA Timer calls is typically 15.625 msec.

But some intervals appear to be higher and lower; for example, 11.718750 and 19.531250 msec (YMMV). Typically, they occur in pairs that sum to 2*15.625 msec.

However, note that 11.718750 is 3.906250 less than 15.625, and 19.531250 is 3.906250 more.

Also note that the data was collected data when the system time was between 9:06:08 and 18:12:15, when the precision of VBA Timer (type Single) is only 3.906250 msec (2^-8).

So 11.718750 and 19.531250 represent differences in the least-significant bit of the representation the time of day (since midnight), which is probably due to binary rounding to the limited precision of VBA Timer.

This can be demonstrated by collecting data soon after midnight.

The following data was collected when the system time was between 0:01:04 and 0:02:07, when the precision of VBA Timer (but not necessarily system time) is 2^-17 (0.007629394531250 msec).

(Internally, system time is probably maintained at a fixed degree of precision that is independent of, and probably better than, even the best precision of VBA Timer.)

Please Login or Register  to view this content.

With the actual system time on the left (columns A:C), VBA Timer difference varies between 0.0159988403320312 (*) and 0.0160064697265625, a difference of -0.0006256103515625 to 0.0003814697265625 from 0.015625.

(*) The display of values is limited to 15 significant digits, due to the formatting limitation of Excel. For example, what appears to be 0.0159988403320312 is actually 0.01599884033203125.

But when the same system time is rounded on the right (columns D:F) to the precision of VBA Timer at a later time of day, VBA Timer difference appears to vary between 0.011718750 and 0.019531250, a difference of -0.003906250 to 0.003906250 from 0.015625.

Obviously, the difference in system time did not change. The apparent greater difference is an illusion caused by binary rounding of system time to the limited precision of VBA Timer at different times of the day.

-----

As to the actual variability in the difference between VBA Timer calls....

My theory is: occassionally, the clock interrupt is delayed while that CPU is processing other interrupts (e.g. LAN traffic?). When the clock interrupt finally runs, it updates system time by the actual elapsed time, which is more than 15.625 msec, effectively compensating for the delay. The next clock interrupt might occur on time, 15.625 msec after the previous one.

Consequently, the time difference between consecutive clock interrupts appears to be more or less than 15.625 msec.