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.
Excel and UDF Performance Stuff 
Well Charles,
That’s a million dollar question 😉
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?
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
Hi Charles. Question on the below quote in regards to FastExcel:
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.
I take it that FastExcel’s recalc timer uses Range.Calculate or similar? Reason I ask is that I’m getting some timings out of FastExcel for a very, very large block that are something like 10 times longer than what I suspect they should be. FastExcel tells me something like 6 seconds, but I suspect it’s more like 0.6 seconds given what I actually see onscreen.
My test data is this:
In A:A I’ve got consecutive integers from 1 to 1048576
In B:D I’ve got the results of =ADDRESS(ROW(),COLUMN()) which I’ve then cut and pasted as values
I’m then doing some VLOOKUPS on this:
In F2:F101 I have the number 1000000. These are my lookup values
In G2 copied down I have =VLOOKUP($F2,$A$2:$D$1048576,2,FALSE)
In H2 copied down I have =VLOOKUP($F2,$A$2:$D$1048576,3,FALSE)
In I2 copied down I have =VLOOKUP($F2,$A$2:$D$1048576,4,FALSE)
I’ve got calculation turned off. If I change F2:F101 to a new number (say 1000001) and then select the VLOOKUP formulas in G2:I101 and push F9, it takes approximately 3 seconds for Excel to update all those lookups, measured by my wall clock (i hear it ticking in the background).
Whereas if I use FastExcel to measure, it tells me it takes 6.3 seconds.
Is the time difference due to the fact that FastExcel is using the single-threaded Range.Calculate, whereas when I trigger the change manually Excel is doing a multi-threaded calculation?
Whoops, those timings in the second paragraph should have read 6 seconds for FastExcel vs 3 seconds for a manual change with visual check.
Depends which FastExcel button you are using to time the calculation:
Calc Range is single threaded, Recalc Books and Full Calculation are multi-threaded.
Timing also depends on which RangeCalc option you choose (FastExcel->FXL V3 Calculation Options->FastExcel Settings)
Ahh. Was pressing Calc Range. I take it that this will overstate the calculation time?
Depends what you mean by overstate. The problem is that the efficiency of multi-threading is very dependent on the dependency tree structures. If you want to compare the pure execution speed of 2 formulas then RangeCalc is usually the best method because it ignores multi-threading. If you want to compare the calculation speed of 2 workbooks then Recalc or Full Calc is best because you want to take the relative effectiveness of multi-threading into account.
Very helpful clarification. I’m comparing execution speed of 2 formulas for a presentation I’m giving today.
I see that if I’m in Excel’s Manual Calc Mode, then the Recalc Sheet timer gives me the answer that matches what I see…3 seconds recalc time after changing my 100 input cells while in Manual. But if in Excel’s Automatic Calc Mode, none of those 100 input cells are flagged as dirty by the time I change them and then push the Recalc Sheet button, meaning it doesn’t tell me what I want to measure in this case. (It gives me a time of 0.41 milliseconds).
So I take it that if I am in in Excel’s Automatic Calc Mode, I’d probably only ever use the Recalc Sheet if I wanted to measure the calculation chains of volatile formulas, because those will be the only cells flagged as dirty?
in this specific test-case, given my sheet consists solely of the formulas I’m interested in timing, then if I’m in Excel’s Automatic Calc Mode then I take it that the FullCalc Sheet option will also give me the actual recalc time of those VLOOKUP formulas, given those formula are the only formula in my test sheet?
Yes – FullCalc Sheet would do what you want in Auto mode (but be aware that it dirties all the formulas on the sheet and does not clean them: only Recalc Books and Fullcalc books clean up dirtied cells)
Cool. And just to clarify my understanding, I take it Recalc Sheet works out the calculation time of any dirty cells, without cleaning them in the process? i.e. if I push the Recalc Sheet button twice after changing my 100 input cells while in Excel’s Manual Calc mode, I get approximately the same answer each time, meaning that as far as Excel is concerned, those 100 input cells are still dirty after FastExcel has recalculated them?
Yes: neither of the sheet calcs clean up dirty cells.