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:
http://support.microsoft.com/kb/214395
http://www.decisionmodels.com/calcsecretse.htm
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…
The scene
Enter Excel user stage left.
Open Excel.
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.
The cause
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.
The fix
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.
In essence, the above article correctly found the culprit of the default Manual Mode issue.. and the problem lied with the Personal.xlsb workbook. However,
The above code (given in the article) did not work for me. It prompted some error about “calculation” in vba.
However.. this simple technique worked… without any code.
Open Excel… Go to View Tab… click unhide… and choose unhide workbook.. of PERSONAL.XLSB
once it is opened.. go to Formulas Tab.. and set calculation options to “Automatic”
Save the workbook… then “hide it”.. then save again.
Close Excel.. and Open it again… and you will have automatic option by default now on.
Thanks for the article for the pointer.. and now it saved me frustration for several weeks now.
I too had an issue with the above code. I used svcitian’s suggestion and it only partially worked. While after re-saving my Personal workbook, whenever I open a new excel sheet, the automatic setting did remain, which is great. However if I open a file from an outside source, it resorts back to the manual setting. Very frustrating! Any ideas?
Hi Andy,
This is the official stance Excel takes on setting the calculation state:
http://support.microsoft.com/kb/214395
Basically the calculation mode is set by the first file opened, then any change in any open file sets all files to that calculation state. The problem usually lies in a situation where multiple files are open, the setting gets changed to manual for some reason, all files are saved, some are closed, then the calculation is set back to automatic. In that case, those files which were closed before calculation was set to automatic have retained manual calculation status.
I’m not sure what you mean exactly by “outside source”, but I would try changing the calculation state, then saving it, closing and re-opening it.
Thank you for your quick reply. I understand how excel chooses to either open as manual or automatic. For clarification, the “outside source” is from a website that allows me to obtain different sets of data that are delivered as an excel file.
I have noticed that if i open excel, with a blank sheet, the automatic calculation mode is set the way I had last saved it. But if I do not enter anything in any cell, then open the file from the internet link, the blank sheet goes away and the only open file is from the link. Once that occurs and I check the setting, it is back in manual. I have to assume that the problem lies with the original formatting of the file from the link. I just find it odd, because I have been using the same data website for over a year and this is the first issue I have had with this.
Thanks again for your help. Let me know if you can think of any other possible solution.
That is interesting. I do wonder if the calculation of the online file was saved as manual. I’m assuming you can’t save back to the website? If you can it should be a relatively easy check.
Another check I would do, is with another file open (not a blank workbook), ensure calculation is set to automatic, then open the online file. I’m assuming in this case it would still retain the automatic setting. ?
No I cannot save files on the website I am going to try to find out today if they changed something with their process.
You are correct in assuming about having another file set to automatic open.
Thanks for getting back Andy. I’d be curious about the state of the file on the website too.
Have a good day!