Excel 2013 SDI Bug: “Calculate” in Status Bar strikes again

In the old days (I’m talking Excel 5 to Excel 2003 here) there was a worrying situation you could find yourself in where, no matter what you did, Excel would show you “Calculate” in the statusbar.
Even when actually nothing needed calculating.
You could press F9, Ctrl/Alt/F9 or even Shift/Ctr/Alt/F9 until the cows came home, but you could not get rid of that pesky “Calculate”.

Excel 2007 to the rescue

Then along came Excel 2007 and solved the problem.

For details of the circumstances causing “Calculate” to appear in the statusbar see http://www.decisionmodels.com/calcsecretsf.htm

Excel 2013 and Calculate in the statusbar

Having just spent the best part of 2 days sorting out what looked like a problem with a lot of complicated VBA FastExcel code I have just discovered that its not a FastExcel problem at all: its an Excel 2103 SDI Bug (Single Document Interface – each window is separate from the others. As opposed to MDI -Multiple Document Interface where all the windows are within a parent window – as in all previous Excel versions).

Basically what should happen is very simple:

• When Excel detects that something needs to be calculated it puts the “Calculate” message in the statusbar. (This corresponds to VBA Application.CalculationState=xlPending)
• Whilst Excel is calculating it shows “Calculating x%” in the statusbar (Application.CalculationState=xlCalculating)
• When Excel has finished calculating it removes the messages from the statusbar (Application.CalculationState=xlDone)

There are a few exceptions to this:

• If Workbook.ForceFullCalculation is true then the statusbar always shows Calculate
• If there are circular references and Iteration is switched on then the statusbar always shows Calculate
• If your VBA has taken control of the statusbar then you don’t see this happening until you relinquish control
• If you are using Automatic Calculation mode then the switch into and out of “Calculate” status does not happen.

Unfortunately the Excel 2013 SDI implementation currently only does all this to the active window: so if you have multiple workbooks open things can rapidly get in a mess:
just follow this simple recipe:

Start Excel 2013 with an empty workbook (Book1) and in automatic calculation mode.

Add a (non-volatile) formula to cell A1 (=99)

Open another empty workbook (Book2)

Switch to Manual calculation Mode

Add a (non-volatile) formula to cell a1 in Book2 (=55)

The statusbar in book2 now shows “calculate” but the one in Book1 does not (Bug)

Now switch to the Book1 window and press F9. The Calculate message in Book2 should go away: but it does not (Bug)

Now you can go back to the Book2 window and press F9 again, but that pesky “calculate” will NOT go away.
If you change the formula in cell A1 (or do anything else that makes the workbook uncalculated) and then press F9 the “calculate” goes away!

You can even make this happen in Automatic mode.

Repeat the steps above, but when you have switched to Book1 with book2 showing calculate, instead of pressing F9 switch to Automatic calculation mode.
Now switch back to Book2 and no matter what you do you cannot get rid of “Calculate” (except by going back to Manual, dirtying Book2 and recalculating).

Conclusion

Moving Excel 2013 to SDI is the only way to get sensible support for multiple screens, and so was probably neccessary.
But it has caused quite a lot of breakage of existing applications (Toolbars, Modeless Forms etc)

I hope Microsoft can fix this Calculate problem in the forthcoming SP1 release before it bites too many people!