Part 1: Introduction – Part 2: Timer – Part 3: Volatile formulas – Part 4: Lookup formulas – Part 5: Sorting source data – Part 6: Table or List? – Part 7: Multiple criteria lookups – Part 8: Sum with multiple criteria – Part 9: Other factors – Part 10: Summary
Part 2: Timer
Microsoft’s – MicroTimer VBA code
The code for the MicroTimer can be found in this article Excel 2010 Performance: Improving Calculation Performance. Find the two sections of code half way down the document and copy them both into a new VBA module.
To create a new VBA module:
- Press ALT + F11. The VBA window will open.
- Insert -> Module
- Copy the code into this module, close the VBA window.
Testing the MicroTimer
To test the MicroTimer, enter any data and formulas into one of the worksheets. Press ALT+F8. The Macro window will open. You should see the 4 options.
- FullcalcTimer – Time the calculation of every formula in the entire workbook
- RangeTimer – Time the calculation of the selected cells only
- RecalcTimer – Time the calculation of any changes since the last recalculation, including any volatile formulas
- SheetTimer – Time all the calculations on the active sheet
Select the option you wish and click Run.
The message box will tell you how long it will take to calculate.
Recording calculation times
You will notice that when you run the MicroTimer over and over on the same workbook, sheet or range the calculation time is not the same. Therefore it is best to run it multiple times (say 5-10 times) and then average the result.
Finding Bottlenecks in Calculations
Now comes the fun part. Finding out what is causing the worksheet to calculate slowly.
- Reduce the interference from other applications and formulas.
- Close everything, except the workbook you wish to test.
- Set the calculation mode to manual (Formulas -> Calculation -> Calculation Options -> Manual)
- Run the FullcalcTimer. Make a note of the average time – this is the slowest recalculation as it recalculates everything.
- Run the RecalcTimer. Make a note of the average time – as there have been no changes to the workbook there should not be any new calculations to perform. Excel will evaluate all the formulas. The volatile formulas will be recalculated along with any formulas dependent on those volatile formulas. This should be the fastest full recalculation. If this re-calculation time is slow then you may be able to speed up the calculation be removing some volatile formulas (more on that next time)
- Activate each sheet and run the SheetTimer. This will identify which worksheets are running slowly.
- Once the slow running worksheets have been identified use the RangeTimer on various columns, rows, ranges and cells to identify exactly which cells are causing the problems.
Where do you go from here?
Hopefully you will now know which formulas are causing your workbooks to calculate slowly. Next time we will start looking at what changes we might make to our formulas to optimize the calculation speed.