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.