There is a known issue with Excel’s calculation when switching out of Automatic calculation mode. For those who don’t know, let me recap the issues:
- The first workbook opened will use the calculation mode of its last save
- Any workbook created will inherit the calculation state of the first opened workbook (or current application calculation setting)
- The calculation mode should be saved with the last saved workbook
Here is some information pertaining to this known issue:
There is plenty of information in those links, so I won’t re-hash them. Even knowing this, sometimes there are issues. If you are opening Excel and you know the last saved workbook was in Automatic calculation mode, you may want to look at your add-ins, to see if any are in manual calculation mode. A place to start looking would be the ThisWorkbook module of those add-ins. So here are some other culprits of this cause:
- A file in your XLSTART folder is in manual calculation mode
- Open event code setting calculation to manual
- Add-in setting the calculation to manual
A Bing (Google, whatever) search will yield many results of the same problem. However, there exists another, more devious cause for this problem. It can really sneak up on you, and cause even the most seasoned Excel pro’s to cower before it. Yes, I speak of myself, and yes, I got my butt kicked by this problem tonight. Let me set the scene…
Enter Excel user stage left.
Every time you open it, regardless of any fixes or saves you’ve done, it’s in manual calculation mode.
Excel user bangs head against desk.
Wash. Rinse. Repeat.
As much as I like beating my head against the desk, it’s not the way I like to spend my time. The sinister cause of content here – my Personal.xlsb file. In all of its glory, somehow it was saved in manual calculation mode.
You would think that saving the last open file in Automatic calculation mode would be enough to fix things – and in most cases you would be correct. The fix, however, is an easy one, once you know how to do it. It does require you to open the Visual Basic Editor (VBE) and get your hands dirty with a little code though. If you’ve never done this before, fret not, here’s a step-by-step guide to assist you.
- Ensure you have no open files (it’s just easier this way)
Press Alt + F11 to open the VBE
- Alternatively you can go to the Developer tab (if it’s visible, if not you’ll have to customize the ribbon by right-clicking it and going to customize ribbon) and click on Visual Basic
- Press Ctrl + R to open the Project Explorer
Ensure your PERSONAL.XLSB is the active project *
- If you don’t have a PERSONAL.XLSB file, well, I guess this solution doesn’t mean much to you…
- Press Ctrl + G to open the Immediate pane
- Paste the below code in it, make sure your cursor is on the line of code and press Enter to run it
- Close the VBE
- Close Excel
To ensure your PERSONAL.XLSB file is the active project, look in the title bar of the VBE, like at the top of this picture:
If it isn’t, you can double-click any object in your PERSONAL.XLSB file and it should become the active project and show its name in the title bar. You must have this as your active file if this is your problem and you want to fix it this way.
The code to run in the Immediate window is this:
[sourcecode language=”vb”] workbooks(“Personal.xlsb”).Parent.calculation = xlcalculationautomatic: workbooks(“Personal.xlsb”).Save[/sourcecode]
Another option would be to set a workbook close event in your PERSONAL.XLSB file which sets the calculation to Automatic, but that seems like a lot of overhead for something which can be fixed in one fell swoop.
While this is a known problem, and I’ve dealt with it for years, I’ve never had it affect my PERSONAL.XLSB file, so it was a new variant for me.