As posted on my blog a few days earlier,
Back in 2005, I noticed something that worried me.
You may know already that switching Application.Calculation to xlCalculationManual can make various code run faster. It can be a big time saver.
The problem, as I see it, is not switching it back to xlCalculationAutomatic. Given that some people for whatever reasons might use Manual Calculation all the time, most people don’t, especially the vast majority of average users who probably haven’t heard of this setting. With Calculation still set to Manual, they might be looking at values that haven’t be updated. Even experienced programmers might be temporarily confused until they figure out what’s going on. Imagine someone in a sales department quoting incorrect pricing to a customer or doing a faulty presentation at an important meeting. Not good.
And now for what really worries me – saving files with this setting. Let’s try something. Close all Excel files, except one to use for testing. Now switch to Manual Calculation. If you don’t know how to do it in code, you can click Calculation Options on the Formulas tab, then select Manual. Now save in that setting, close Excel, and reopen the file. If that file is the first one to be opened, Excel Calculation will be set to Manual by default, and all other files opened thereafter will be affected too. Save any of them with this setting, and the same thing will happen if they happen to be the first file opened…
So, how do you know Calculation is set to Manual without specifically checking?
You can’t. (Actually there is a way to make it more obvious. See the comment from Jake Collins)
Now, imagine sending one of these files to colleagues or customers, then realizing something is amiss days later. Again, not good. In fact, downright scary.
So, also in 2005(?), I made an addin called Calculation Checker. It checks Calculation when you save and prompts you to do so as Automatic if set as otherwise (including Automatic Except for Data Tables).
I’ve found it useful, but since then I’ve thought there’s room for improvement, so I made something new.
As you can see there’s 3 menu items. The bottom 2, when toggle to “On”, check files when opened/saved for the following settings.
Application.DisplayFormulaBar
Application.DisplayStatusBar
Application.Calculation
Application.ReferenceStyle
If any of those settings are not at their default, the Application Settings form will be displayed. Non-default settings are displayed in red. (Yes, the first 3 should be obvious, but easy enough to miss if you’re busy, tired or both!)
Click the form’s controls to reset them individually, or just click the Reset Everything button, then the Save File and Exit button if you choose to. Alternatively, click the X button not to save the file. Note that any other file that are open will also be saved with these settings (unless you change them later), because they are Application settings, not Workbook settings.
And because the form can be opened directly from the Ribbon, you can easily change any of the settings at any time for whatever reason. Click the Show Settings button and you can see other settings that can also be reset when clicking the Reset Everything button, if the Include other settings checkbox is ticked.
Note
Keep in mind that these additional settings aren’t checked automatically. The form only resets them if you click the Reset Everything button as mentioned above. Also, if Application.EnableEvents is set to False by VBA code, my addin won’t check files when opening or saving as these are the events that trigger it. In fact, you should be setting this False if any of your code does open or save workbooks to prevent my code from running, then set it back to True before the code ends.
Hopefully this tool will be of use. You can download it here.
PS. I’m going on holidays for a few days so I’ll reply to comments (if there be any!) when I get back.
Nice!
As a trick to ensure things get set back regardless whether the user clicks the End button on a runtime error, I have a small routine called ResetAppSettings which I schedule to be run after my entry sub has ended, like so:
Application.OnTime Now, “ResetAppSettings”
This scheduled routine will always be called even if the VBAproject is reset by whatever reason.
For those that use (willingly or not) circular references or goal seek, the following settings are also worth mentioning:
– Application.Iteration (TRUE or FALSE)
– Application.MaxChange (Level of precision)
– Application.MaxIterations (Typically 100 iterations)
The first one is only when you use circular references willingly (which I don’t, but sometimes you’re stuck with someone else’s model)
The other 2 are used with circular references and also with goal seeks. Having too many iterations or too much precision might mean a really long to converge goal seek on a complex model.
Jan, that sounds like good insurance! Care to post an example?
Sébastien, thanks for the information.
I don’t use those settings (much) myself, but I’ll give some thought to adding them to the next version!
Andrew:
Sure.
Sub SomeEntryRoutine()
‘Declarations
‘Schedule the restore of app settings.
‘Always runs, even if a runtime error resets your project
‘even if you use an End statement (don’t!)
Application.OnTime Now, “RestoreAppSettings”
‘This is where you might store current app settings
‘Now fiddle with app settings as much as you like
‘and do lots of complicated stuff
End Sub
Sub RestoreAppSettings()
‘ Restore Application settings here
‘ This is a simple version that does not cater for
‘ user settings. If there are user settings you want to restore
‘ you need a way to save them outside of VBA first and retrieve them here
‘ Examples: In a (hidden) worksheet, in a textfile, in the registry
‘ All these have their own merits and drawbacks. Up to you to decide
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
‘Application.Iteration (TRUE or FALSE)
‘Application.MaxChange (Level of precision)
‘Application.MaxIterations (Typically 100 iterations)
End Sub
As fas as I know Application.Screenupdating and application.displayalerts have only local scope.
More important to me (in the workbook_open event in personal.xlsb) seems Application.visible=true
@snb: You are correct (in theory), but there are bugs and situations where for example Application.screenupdating does not reset itself when the VBAproject is reset. DisplayAlerts is so important that I set it back to True just in case MSFT introduces a new bug that causes it to not automatically reset itself.
I like your bug-buster mentality ! ;)
A minor tweak – the macro errors on Show Application Settings Form if a spreadsheet is not open. Just a quick error trap needed.
Otherwise I like a lot of the suggestions above.
Jan,
Thanks! I’ll definitely be trying this with future projects!
snb,
I’ve heard that about ScreenUpdating, but always clean up anyway out of habit. DisplayAlerts, I’ve never been brave enough to not reset it :-)
GMF, thanks for picking that up! I’m actually testing a new version right now and will upload it on the weekend with error trapping for no workbooks open. Sorry for any inconvenience.
My approach to tell if calculation is set to manual is to include the command to set it automatic in the QAT
Jake, didn’t think of that… good idea!
Hi Jan,
By ‘ This is a simple version that does not cater for user settings’, what do you mean by user settings? Please let me know.
I think he means it doesn’t try to restore those setting back to what they were before the sub ran, but rather restores them to a hardcoded value. If your ‘Restore’ procedure sets iteration to True and the user at some points sets it to False, any time your procedure runs it will set it to True possibly against the users wishes.
The alternative is that you store what Iteration is set to at the start of your procedure then restore it back to that value rather than a hardcoded value.
@Karthick it means that my example code does not take into account that the user might have a different default than one of the settings I “restore” back. For example: if the user has Iteration turned on for a particular file but your code defaults to off, the user will not be pleased.
So you’ll have to write the original settings (for example) to the registry before your code changes them and retrieve them before restoring them.