Forum Discussion

Mark_Kaplowitz's avatar
Mark_Kaplowitz
Copper Contributor
Dec 29, 2023
Solved

Shift+F9 is now cleaning dirty cells (after an update from version 2305 to 2308)

Hi,   In the past, Shift+F9 would leave dirty cells on the sheet dirty (assuming Manual calculation mode), as mentioned here:   https://www.decisionmodels.com/calcsecretsg.htm Sheet Calculate do...
excel
  • Mark_Kaplowitz's avatar
    Mark_Kaplowitz
    Jan 23, 2024

    Just to update on this, we have confirmed (via Microsoft Premier Support) that this is indeed a deliberate change to the Shift+F9 behavior. We'll have to consider our options for users relying on the old behavior.

    I guess Charles should eventually update the decisionmodels page, and also this one:

    https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-improving-calculation-performance#calculating-workbooks-worksheets-and-ranges

Mark_Kaplowitz's avatar
Mark_Kaplowitz
Copper Contributor
Jan 18, 2024
If anyone from the Excel team could at least confirm if the change was intentional or not it would be much appreciated. We have affected users who have been relying on the old behavior. Please feel free to message me directly. Thanks.
Mark_Kaplowitz's avatar
Mark_Kaplowitz
Copper Contributor
Jan 23, 2024

Just to update on this, we have confirmed (via Microsoft Premier Support) that this is indeed a deliberate change to the Shift+F9 behavior. We'll have to consider our options for users relying on the old behavior.

I guess Charles should eventually update the decisionmodels page, and also this one:

https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-improving-calculation-performance#calculating-workbooks-worksheets-and-ranges

  • fastexcel's avatar
    fastexcel
    Brass Contributor
    Jan 25, 2024

    Mark_Kaplowitz 

    I think this is a bug - looks like off-sheet dependencies do not get dirtied when they should.

    • Mark_Kaplowitz's avatar
      Mark_Kaplowitz
      Copper Contributor
      Jan 26, 2024

      fastexcel 

      Thanks very much for replying Charles.

       

      I'm not seeing that behavior with cross-sheet dependencies (but perhaps don't understand exactly what you mean).

       

      Fyi, the https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-improving-calculation-performance#calculating-workbooks-worksheets-and-ranges page has now been updated to reflect the new behavior.

       

      What used to read:

       

      You can also recalculate only the selected worksheets by using Shift+F9. This does not resolve any dependencies between worksheets, and does not reset dirty cells as calculated.

       

      is now:

       

      You can also recalculate only the selected worksheets by using Shift+F9. This resolves dependencies for the worksheet and resets all previously uncalculated (dirty) cells as calculated.

       

      In previous versions of Excel, the behavior was different and dirty cells were not set as calculated after the calculation completed. If user-defined functions relied on this behavior, these functions should be made volatile instead, as explained in the Volatile functions section in this article.

      • fastexcel's avatar
        fastexcel
        Brass Contributor
        Jan 26, 2024
        Wow - I originally wrote that article 15 years ago - great to see that it is being updated.
        Done some more testing - there is a calc bug but its not new..

        Sheet 1 has a volatile function (I used NOW() )
        Sheet 2 has a formula that is downstream dependent on the volatile function.
        Calc is Automatic

        Using sheet calc on sheet 1 calcs the volatile function but the sheet 2 formula does not recalc (which it should) and the status bar shows Calculate

        Pressing F9 resolves the bug.
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jan 25, 2024
    Thanks for sharing this with us Mark.

Resources