This post is about monitoring the workbook environment when working on key workbooks or workbook applications especially if they involve VBA, which is my approach to developing key workbooks for clients. You wouldn’t do this for an ad hoc or quick and dirty workbook. But for workbooks you or your clients are going to be relying upon, and especially during the development phase of the workbook, I think it’s wise to regularly monitor:
1) workbook size
2) calculation speed
3) calculation mode especially if you have VBA with the workbook.
I say especially through the development phase of the workbook because if your approach in structure of the worksheets, choice of formulas, use (or over use) of conditional formatting or cell formatting is resulting in a deterioration of workbook size or calculation speed, then common sense says to me I want to know about these dark clouds on the horizon sooner rather than later so I can modify my approach. For instance, you might want to use more helper columns rather than load thousands of formulas with the same lookup function. You may have used lots of array formulas and can look for alternatives such as helper cells. Or you may have used a lot of volatile formulas and need to look for alternatives using non volatile formulas. When the workbook is done it’s often too hard to make these changes and you don’t want the client or your boss saying yeah it’s great except no one will use it because the recalc time is driving them nuts.
So back to the 3 key areas to be monitoring…
1) Your workbook may hang or fail at the worst time due to it’s bloating size like 10 minutes before the CFO needs a report to take to a meeting. Workbook size can bloat for a number of reasons – it only takes a keystroke in some far flung region of a worksheet and your workbook becomes much slower to open and save. Or you may be developing the workbook and your approach is unsustainable and needs to be reviewed. Maybe you have images that need to be compressed. There can be a host of reasons beyond the scope of this post and it’s a topic in itself to research yourself – but the best place to start is Charles Williams’ site specialising in work book performance decisionmodels.com. Whatever the reason, you want to know it’s becoming a problem before it’s a problem and Murphy’s Law kicks in, so it’s wise to continually monitor workbook size.
2) Recalculation time – this is something you should be naturally aware of as you use the spreadsheet, so I don’t think you necessarily need a specific tool to measure this. When a workbook takes more than a second to calculate, it’s becoming impractical to use. So be aware of the automatic calculation speed and if it deteriorates think about the last work you have done which has impacted this. Perhaps you have just used a large number of volatile formulas or array formulas and if so look for alternatives. If you want a more scientific approach or tool to help with this, you can buy Jan Karel Pieterse’s excellent tool RefTreeAnalyser for about Euro 30 at his site which primarily is a formula auditing Add-In tool, but it also will time recalculation by workbook, worksheet and even selected range to help zero in on a problem area causing excessive recalculation time. Or you can buy Charles Williams’s add-in Fast Excel for a complete suite of tools for everything about Excel performance – see the above link.
3) If you have VBA in your workbook it’s good practice in VBA coding for speed of processing to amongst other things turn calculation to manual mode at the start of the process, then do the things the VBA needs to do and then turn back on automatic recalculation. But if this macro is fails or is interrupted for some reason leaving your calculation mode as manual, you may not be aware of this but need to be. If a workbook is in manual calculation mode and you don’t know this you might report false data or you might be spending time solving problems that aren’t actually there once the workbook recalculates. Of course the workbook mode in the Status bar (bottom left hand side) will tell you this but it’s not so obvious.
So in an application spreadsheet, I like to have a worksheet named Control, where I put the following formulas and macro buttons to monitor at least size and recalculation mode and give users some buttons to reset the workbook environment so they don’t have to call you.
My steps are:
- I add a UDF or Function to a VBA module so that the formula detailed in B23 will report the closing workbook size.
- This standard Excel formula in B26 will show the calculation mode which we want to return as Automatic. But if it does return Manual some simple conditional formatting in B26 turns the cell red. (see below image).
- In case other strange behaviours hard to put your finger on are occurring probably also due to a previous macro error the Reset Environment code & button will reset calculation to automatic plus reset other common issues outside of the scope of this post.
- This code simply resets the calculation mode to Automatic – as above this is also achieved via the button in #3 but users understand this more direct fix for a clear cut instance of the calculation being in Manual Mode and turning red with conditional formatting in B26.
Note the macro button shapes are assigned to the 2 VBA subs in #3 & #4 above.
