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)

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

Statusbar2Now 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).


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!

This entry was posted in Calculation, VBA. Bookmark the permalink.

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

  1. Harold Petrich says:

    After spending 3 week porting my 2010 workbooks to 2013 to resolve the disappearing cells drama, recalculation issues, conditional formatting rules not working etc. I am now on the “CALCULATE” ghost!. Your input probably will save me at least two weeks of problem solving! I have a complete system of several work books opening via DOS batch control with text documents controlling what excel workbooks to open! So life has been fun🙂
    Few more things to consider as you work with the Calculate issue (Note that I have large volumes of data with a ton of macros which will make calculation slow):
    – If you have lots of data the “Conditional Formatting” rules will not complete (nor the data is some cells). Possible resolution press F-9 till the cows come home😦
    – If a range of cells have been selected and you’re using “Conditional Formatting” your screen may hang! Select a single cell, scroll up and down with your mouse, and select a single cell again then wait a couple of seconds. This should bring excel back to life!

    NOTE: In all instances of (Even after pressing F-9 a thousand times) the “CALCULATE” disappears and my resulting data appears OK

    Once this “CALCULATE” problem is resolved I will be VERY VERY Happy🙂


  2. Harold Petrich says:

    Currently applying & testing conditional formatting posts.
    Regarding the “CALCULATE” drama, I have discovered that if you delete all data rows except for the first row (not my data headers which has meta-data information about the data rows). Then paste the first data row to the number of data rows desired (thousands). Full calculation (F-9 or shift Ctrl Alt F-9) CALCULATE disappears and the worksheet now says it is READY!🙂

  3. Jack says:

    I noticed in my case Application.Volatile is causing excel 2013 to calculate extremely slow if the workbook is on auto calculation mode.

  4. Hans Polak says:

    I have the weirdest problem in Excel 2013 which may, or may not, be related to this, but IMO it’s a bug. I’m writing a small unit test workbook which opens a template and writes data to it. The first line of code is, obviously, to set screenupdating to false and calculation to manual.
    When I want to retreive the value of a cell and use cell.calculate, this doesn’t result in the calculation of the value.
    Only after I finish the tests and set the calculation to automatic, does the cell reflect the expected value.
    The cell I’m checking is in a table (I don’t know if that matters) and the formula uses named ranges.

  5. Les Firth says:

    If you use the camera tool, Calculate will always display on at least 2010 and 2013.

  6. Graham Griggs says:

    I have 2 machines, both Brand new with Office 2013 on them, neither has ever had any previous versión on it.
    Machine 1 is a 2.14GHz machine with 2Gb of RAM
    Machine 2 is a 2.4 GHz machine with 12Gb of RAM

    If I take a new blank spreadsheet and type in =2*2 (answer comes out correctly as 4)
    On Machine 1 it is instantaneous
    On Machine 2 it takes 3-4 seconds

    I know of absolutely no differences in the two machines other than the difference in hardware spec

    Any ideas ‘ the speed makes machine 2 almost unuseable

  7. Varun says:

    Hi, I have a VBA application running flawlessly in MS Excel 2010. After migrating to Excel 2013, I am facing many issues. The predominant issue is a run-time error while trying to open another workbook ( Book 2) through the parent workbook (Book 1).
    Book 2 contains macros, which was saved as .xls format. Could this be the cause? Book 2 macros are triggered through Book 1. I tried saving Book 2 as .xlsx and .xlsm format, but it did not solve the error.
    I am aware of the changes in Excel 2013 compared to Excel 2010. But I am not able to relate them to this problem. I had also read somewhere that there are specific tools that are required for changing Excel formats. I am not sure if such tools are required. Any suggestions?

  8. Olivier Hollender-Lutz says:

    Hi, you just saved my day!
    For me deleting all conditionnal formats helps – unfortunately I found your blog only after 3 days of debugging, rewriting macros etc…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s