Excel Retains Manual Calculation

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

 


You can easily change Excel’s Calculation Settings on the Formula Ribbon.

 

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 Issue w/Excel Retaining Manual Calculation Mode

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 Reason Excel is having the Issue

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.

Fixing Excel’s Retaining Manual Calculation Mode Problem

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.

  1. Ensure you have no open files (it’s just easier this way)
  2. Press Alt + F11 to open the VBE
    1. 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
  3. Press Ctrl + R to open the Project Explorer
  4. Ensure your PERSONAL.XLSB is the active project *
    1. If you don’t have a PERSONAL.XLSB file, well, I guess this solution doesn’t mean much to you…
  5. Press Ctrl + G to open the Immediate pane
  6. Paste the below code in it, make sure your cursor is on the line of code and press Enter to run it
  7. Close the VBE
  8. Close Excel

 

Need Professional Excel Consulting Help?

If you still need help, and are unable to resolve these issues on your own, or team of Excel consultants are here to help. We offer free consultations to discuss your needs live, via Zoom.  Our team of top notch Excel MVP’s are masters at everything 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.