Why does a selection trigger the calculation state? 1

[McJe]

Hello,

I have a spreadsheet with many calculations (ASME UHX calculations). Because of the number of calculations, it takes 10 to 20 seconds to see the complete results.

Because of it's slowness, I turned automatic calculation off, and now I press a button to force calculation.

The reason because I do this, is that Excel seems to trigger the calculation state to 'Pending', even if only a depending cell is selected.

I would think the state should be changed to 'Pending' only after the cell is really changed. Or maybe if the cell is edited (even if nothing is really changed).

Is it possible to alter this behaviour?
In my opinion, the state shouldn't be changed to 'pending' only when a cell is selected.

 

[Denial]

This sounds a bit strange to me. You can get some funny effects with recalc=manual when the spreadsheet is big enough to exceed the limits of Excel's dependency tree. There are circumstances where the RecalcPending indicator comes on almost permanently, and it might be that you have struck ths problem: a search via Google Groups will throw up a few discussions on this.

There are also circumstances where the indicator will never come on. One such is if you use the OFFSET function, perhaps to establish dynamic arrays.

A very informative article on Excel's recalc behaviour can be found at

http://www.decisionmodels.com/calcsecrets.htm

This will not directly answer your question, but it might trigger a useful thought or two.

These comments apply to Excel 2002 and Excel 2003. I have no experience of Excel 2007.

 

[McJe]

I've searched the internet quite some time to find answers to my 'strange' findings.

The calculation state shows 'calculated', so everything is fine. Now I can click on every blank cell, cells with static text, ..., the calculation state keeps saying 'calculated'.
But, is I dare to click on a cell that contains a value that is used somewhere in a calculation, the calculation state switches to 'pending'. Nothing has been changed, only selected.
This is not what behaviour should be according to your link.

I just tested it with a small excel sheet, and the behaviour is more or less like described in your post, Denial.

I now think there might be a volatile function hided somewhere in the sheet. I will search for them now.
Thanks for the interesting link!

Jeroen.

 

[MiketheEngineer]

You might try Tek-tips.com - a sister to this forum with some really smart people there....