Difference between F9 and Shift+F9

[Schizoid Man]

Hello,

What exactly is the difference in the order of calculations in a workbook
with a single sheet when I hit F9 or Shift+F9?

I have a complicated spreadsheet that uses a custom add-in. A quick rough
estimate would be 1500-2000 function calls with on average 10 parameters per
function.

If I hit Shift+F9 a couple of times, the spreadsheet eventually calculates.
However when I hit F9 the calculation order seems to get corrupted and it
seems to just die. The only way to get it work again is to restart Excel and
go the Shift+F9 route.

Any pointers to the calculation dependency, order of calculations, etc would
be very helpful.

Thank you,
Schiz

 

[Charles Williams]

F9 recalculates (dirtied and volatile cells and their dependents) all
worksheets in all open workbooks and their dependencies and resets all
dirtied cells.
Shift F9 recalculates the active worksheet and within-sheet dependencies,
but ignores other worksheets and workbooks and does not reset any dirtied
cells.

I would suggest you look at the functions in the custom add-in to make sure
that every cell referenced (directly or indirectly) is contained in the
function parameter list.
See http://www.decisionmodels.com/calcsecretsj.htm

You could also explore using Ctrl/Alt/F9 (Full Calculate) which calculates
ALL the formulae in all the worksheets in all the open workbooks
See http://www.decisionmodels.com/calcsecretsg.htm

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Schizoid Man]

F9 recalculates (dirtied and volatile cells and their dependents) all
worksheets in all open workbooks and their dependencies and resets all
dirtied cells.
Shift F9 recalculates the active worksheet and within-sheet dependencies,
but ignores other worksheets and workbooks and does not reset any dirtied
cells.

I would suggest you look at the functions in the custom add-in to make
sure that every cell referenced (directly or indirectly) is contained in
the function parameter list.
See http://www.decisionmodels.com/calcsecretsj.htm

You could also explore using Ctrl/Alt/F9 (Full Calculate) which calculates
ALL the formulae in all the worksheets in all the open workbooks
See http://www.decisionmodels.com/calcsecretsg.htm

Hi Charles,

Thanks for the reply. I am wondering whether this has anything to do with
whether the functions in the add-in are marked as Volatile/Non-volatile.

The add-in forces Excel's calculation mode to Manual, so I would surmise
that whether the function is Volatile or not, it won't make any difference.
I just wanted to confirm.

Regards,
Schiz

 

[Charles Williams]

Manual calc does not have anything to do with Volatile functions.

If the Functions reference cells that are not in the parameter list it
usually works if they are flagged as Volatile, but IMHO thats a very bad
solution.

Try Ctrl/Alt/F9 - it will bypass a number of UDF Function problems

Can you post the code for one of the functions?

 

[Schizoid Man]

Manual calc does not have anything to do with Volatile functions.

If the Functions reference cells that are not in the parameter list it
usually works if they are flagged as Volatile, but IMHO thats a very bad
solution.

Try Ctrl/Alt/F9 - it will bypass a number of UDF Function problems

Can you post the code for one of the functions?

Hi Charles,

I unfortunately can't post the code for a sample UDF, but I have made a bit
more progress. I have collapsed my 10-sheet workbook to a single worksheet -
it's about 2500 rows, with 996 custom UDF calls, and about 3500 other cell
references (simply pointing from one to another).

If I open a fresh session of Excel and hit F9 I get a calculation error. If
I hit Ctrl + Alt + F9, the spreadsheet works.

I am confused though. According to what I've read on the web, F9 will do a
full recalc of all the cells that Excel deems need recalculation. However if
I've opened a fresh session then everything will need recalculation, right?
So why does F9 differ from Ctrl+Alt+F9 in this case?

Thank you,
Schiz

 

[Charles Williams]

If I open a fresh session of Excel and hit F9 I get a calculation error.
If I hit Ctrl + Alt + F9, the spreadsheet works.

OK: that makes it 95% certain that the problem is in badly coded UDFs in the
Addin

I am confused though. According to what I've read on the web, F9 will do a
full recalc of all the cells that Excel deems need recalculation. However
if I've opened a fresh session then everything will need recalculation,
right? So why does F9 differ from Ctrl+Alt+F9 in this case?

Nope, a saved workbook remembers its calculation state from the last save so
F9 does a recalc rather than a full calc.

(OK there are some exception to this when a different Excel version was used
to do the Save etc.)


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Schizoid Man]

Hi Charles,

OK: that makes it 95% certain that the problem is in badly coded UDFs in
the Addin

I was mucking about with the spreadsheet and I managed to get it into a
state where the single-tab workbook managed to calculate using Shift+F9, I
wouldn't have to force a Ctrl+Alt+F9. However, even in this case a plain,
simple F9 failed.

Nope, a saved workbook remembers its calculation state from the last save
so F9 does a recalc rather than a full calc.

Thanks for this information. If I have a workbook with just one tab, what's
the difference between F9 and Shift+F9?

Regards,
Schiz

 

[Charles Williams]

Shift-F9 does not clean dirtied cells (cells needing a recalc) but F9 does