none
Excel arbitrarily changing calculation to manual

    Question

  • This behavior has started just a few days ago. Out of 5 workstations, 3 have suddenly started doing this. We have been working with the same workbooks for years and they had no recent code changes and are not setting the calculation to manual programmatically. It seems to affect both Excel 2003 and Excel 2010 and both Win7 and WinXP machines. The workbooks that trigger this are located on the server. I will set calculation to automatic, then open a workbook and it will have switched to manual. I can then switch it back to automatic, save the workbook and then it will stick once the workbook is re-opened. But then it will happen again later or on other, random workbooks. It happens on some files that haven't been opened in years! They contain no code whatsoever.

    Now, to be clear: I realize that calculation can be set and saved per workbook, but I don't see where this is happening. None of the users are changing this setting by hand and it isn't done programmatically. I have created and written the code for all of them myself. We've been working with them for years without EVER encountering anything like this. Besides, it only happens on SOME workstations, but not on others. I have absolutely NO CLUE what could be causing this.

    There is actually one single factor that has changed recently and that I've been suspecting as the culprit (although I cannot explain how and why): our network infrastructure. We recently had several permission and user changes, the firewall settings have changed and some server/folder locations. This hasn't really caused any major problems. But this strange Excel calculation behavior did start at about the same time... although I'm puzzled as to how these two things could be related, as all documents still open and save just fine without any kind of restriction.

    ANY ideas?

    Wednesday, October 5, 2011 11:59 AM

Answers

  • Calculation mode is an Application level property that is initially set from the first non-addin, non-template workbook opened. It is NOT really a Workbook property, although (confusingly) the current Calculation mode is stored in a workbook when it is saved.

    So if you start up Excel, set the calculation mode to Automatic, then open a Workbook that has been saved in Manual then it will NOT change back to automatic.

    If you then open a second workbook that was saved in Automatic mode Excel will still be in Manual mode (the second workbook does not change anything).

    If you then save the second workbook it will be saved in Manual mode. So if people are not careful Manual Calc mode spreads like a virus.


    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/
    • Marked as answer by Liliane Teng Wednesday, October 12, 2011 9:56 AM
    Wednesday, October 5, 2011 9:42 PM

All replies

  • When you find a file wrong then check the owner property of the file to see who was the last person to write the file.  It is probably something to do with one of the accounts and not all the accounts or a "PC".  A computer may have a startup macro that is automatically changing the calculation mode.  When you find the person look in their folder for any workbooks that may contain a macro.

    C:\Program Files\Microsoft Office\OfficeXX\XLStart

    This file will be common for every user on a single PC.

     


    jdweng
    Wednesday, October 5, 2011 12:13 PM
  • I will remember to check for the owner property next time this happens and see who saved the file last and then check that computer, thanks.

    There are no startup macros on any of the workstations, however. I would know. But I still double checked to make sure.

    Wednesday, October 5, 2011 12:19 PM
  • Are you sure those old workbooks weren't previously saved with calculation manual. Might be an idea to open them all, if calculation changes to manual, reset to auto and resave the workbook. You could make a little macro to do that as a batch process.

    Peter Thornton

    Wednesday, October 5, 2011 1:36 PM
    Moderator
  • Calculation mode is an Application level property that is initially set from the first non-addin, non-template workbook opened. It is NOT really a Workbook property, although (confusingly) the current Calculation mode is stored in a workbook when it is saved.

    So if you start up Excel, set the calculation mode to Automatic, then open a Workbook that has been saved in Manual then it will NOT change back to automatic.

    If you then open a second workbook that was saved in Automatic mode Excel will still be in Manual mode (the second workbook does not change anything).

    If you then save the second workbook it will be saved in Manual mode. So if people are not careful Manual Calc mode spreads like a virus.


    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/
    • Marked as answer by Liliane Teng Wednesday, October 12, 2011 9:56 AM
    Wednesday, October 5, 2011 9:42 PM
  • Hi!

    We have recently got this problem at my office.

    It happened after we misstakenly ereased a bunch of xls files and restored them from our backup.

    The automatic calculation is checked for all new documents, but when we open the specific files from this bunch they are broken (manual).  I have tried to change it manually to automatic and save, but when I return to the file it goes back to automatic.  Do you have any suggestions to what I should do?

    Thanx!

    //David

    Wednesday, September 3, 2014 11:36 AM
  • Have you looked through the suggestions previously given (incl my own). Probably one of your workbooks was saved while calculation was set to manual. If that is opened as the first workbook in a new session it will change the calculation mode for subsequent workbooks that are opened (irrespective of how they were saved). So look at that one, change calculation at application level to automatic and resave the workbook.
    Wednesday, September 3, 2014 3:02 PM
    Moderator
  • If it is absolutely necessary that this workbook be in Automatic calculation mode, add it to a Workbook_Open macro in ThisWorkbook:

    Private Sub Workbook_Open()
        Application.Calculation = xlCalculationAutomatic
    End Sub
    

    Wednesday, September 17, 2014 8:52 PM