A Quantum of Time – measuring time accurately.

If you want to do work on Excel performance stuff you need a method of timing things so that you can see whether one way of doing things is faster than another.

Typically this requires 2 things:

  • A method for reading some kind of clock.
  • A method of executing the thing you want to time.

There are several clocks you can use with VBA.

  • the built-in Time() function
  • the timeGetTime Windows API
  • the QueryPerformanceCounter and QueryPerformanceFrequency Windows APIs

But each clock has different sized “ticks” (the clock’s Quantum of Time, more usually called its resolution), and the size of the tick is the smallest amount of time you can measure.

The VBA built-in Time() function has a very large tick: about half a second on my system which makes it fairly useless for anything except long-running macros.
timeGetTime is much better: the tick is about a millisecond (one thousandth of a second).
But the winner is QueryPerformanceCounter and QueryPerformanceFrequency: I get about 0.3 microseconds (millionths of a second).

You can download VBA code using these 2 timers from here.

So now we need a method of executing the thing we want to time, which in my case is usually the calculation of a block of Excel formulas or UDFs. The most convenient method for this is to use Range.Calculate. This method has some peculiarities that need handling, and the actual calculation method used varies somewhat depending on the Excel version you are using. I have packaged all this together into a small Excel addin file RangeCalc, which gives you a button to click which shows you the time taken to calculate the currently selected formulas.

The RangeCalc times are not completely consistent, because Windows is a multi-tasking operating system, tends to move stuff in and out of the high-speed caches and also because Excel itself will try to cache recently used stuff. So its best to do several successive clicks on RangeCalc and take an average of the times.

Some more things to bear in mind when timing formula calculation are:

  • Multi-threaded Recalculation
  • Excel’s smart Recalculation engine
  • Volatile Functions

Excel 2007 introduced Multi-threaded recalculation. This allows Excel to split the calculation into multiple calculation chains and execute each of them simultaneously if you have a multi-core PC. But Range.Calculate does not use Multi-Threading, so will be misleading if you compare a multi-threaded Function with a non-multithreaded Function. (All VBA UDFs are non multi-threaded).

Excel’s smart Recalculation engine minimises the number of formulae that it recalculates by tracking changes to cells and formulae. Only changed cells, cells containing Volatile functions such as Rand() or Now(), and the cells that are dependent on these cells will be calculated in a normal Recalculation (F9). But RangeCalc calculates ALL the selected cells rather than just the ones that need recalculating. So it measures the speed of a Full Calculation of all the selected formulas rather than telling you the time for only the cells that need calculation.

The RangeCalc addin gives you an excellent starting tool for measuring and comparing the calculation time of a formula or a block of formulas. You can extend the approach to measure the time taked to Calculate a worksheet or a workbook. And once you can measure calculation time accurately you can start comparing the alternatives so that you can find faster solutions.

This entry was posted in Calculation, VBA. Bookmark the permalink.

3 Responses to A Quantum of Time – measuring time accurately.

  1. Well Charles,

    That’s a million dollar questionđŸ˜‰

  2. Vijay Verma says:

    Hi Charles,

    Yours is the only website which I have read from cover to cover, each and every article. This has made me a better Excel professional.

    I have used your timer published on https://msdn.microsoft.com/en-us/library/ff700515%28v=office.14%29.aspx

    I have a particular question which has been reported by some of my friends with whom I had shared the above timer. They have 64 bit of Excel and the above doesn’t work. I have 32 bit of Excel, hence I have no issue. Since, I don’t have 64 bit of Excel, I am not able to check their claim that above timer doesn’t work on 64 bit of Excel.

    If this is true, do you have a timer code for 64 bit?

  3. fastexcel says:

    If you download the RANGECALC.XLA addin from the link in this blog post it has been modified to handle both 63-bit and some complexities of timing array formulas etc. If you want to look at the code the password is dm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s