"Calculate" does NOT appear in Excel's status bar
"Calculate" does NOT appear in Excel's status bar
(OP)
I have a quite complicated, but not particularly large, multi-sheet workbook. It has a lot of named ranges, and makes extensive use of dynamic arrays. Most of its formulae (many of which are array formulae) use cells on different sheets. Nearly all its operations are performed by executing macros.
I need to run the workbook with its calculation mode set to manual. Then some of my macros will do different things according to whether or not a recalculation is pending (indicated in the Excel environment by the word "Calculate" appearing in the status bar, and in the VBA environment by Application.CalculationState having the value xlPending rather than xlDone).
My problem with this particular workbook is that when I make a change to a cell with dependents, "Calculate" does NOT appear in the status bar (and CalculationState is not set to xlPending). Some characteristic of my workbook seems to have upset Excel in such a way as to cancel its normal behaviour.
I have reproduced this (mis)behaviour on two computers out of two tries. One computer was running Excel 2002, the other Excel 2003.
A Google search throws up many requests for assistance where Excel puts up the word "Calculate" when the spreadsheeter believes the word should not be there. What I have is exactly the opposite.
Has anyone come across this behaviour before, and (if so) what was the explanation / cure?
I need to run the workbook with its calculation mode set to manual. Then some of my macros will do different things according to whether or not a recalculation is pending (indicated in the Excel environment by the word "Calculate" appearing in the status bar, and in the VBA environment by Application.CalculationState having the value xlPending rather than xlDone).
My problem with this particular workbook is that when I make a change to a cell with dependents, "Calculate" does NOT appear in the status bar (and CalculationState is not set to xlPending). Some characteristic of my workbook seems to have upset Excel in such a way as to cancel its normal behaviour.
I have reproduced this (mis)behaviour on two computers out of two tries. One computer was running Excel 2002, the other Excel 2003.
A Google search throws up many requests for assistance where Excel puts up the word "Calculate" when the spreadsheeter believes the word should not be there. What I have is exactly the opposite.
Has anyone come across this behaviour before, and (if so) what was the explanation / cure?





RE: "Calculate" does NOT appear in Excel's status bar
RE: "Calculate" does NOT appear in Excel's status bar
After working on this most of the night (and worrying about it for what remained of the night), I tried an experiment. As I stated above, my troublesome workbook makes a lot of use of dynamic named ranges. I converted all of these to explicit, static named ranges. The "calculate" message then seems to work as expected.
Of course this leaves my workbook totally crippled, but it might offer some sort of clue as to what is going on. I will experiment a bit more when I next get a moment.
Could you please tell me whether you have dynamic named ranges in the workbooks in which you have experienced a similar problem. If so, and if it is relatively easy to do, could you also see whether converting your dynamic named ranges to static ones persuades Excel to do the right thing with the "calculate" message in the status bar.
Thanks (in advance) for your further help.
RE: "Calculate" does NOT appear in Excel's status bar
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: "Calculate" does NOT appear in Excel's status bar
I'm not quite sure I want to go that way yet.
Yes, I can detect a change to a worksheet. But it will require pretty intricate logic for my VBA code to distinguish between changes with consequences (ie to cells with dependents) and changes without consequences.
Part of the reason for wanting to be able to detect calculate in the first place is so I can build in some logic to force a calculation at times when it is essential, while leaving it to the user's discretion when it is merely desirable. If I can use VBA's CalculationState as a part of the process, life will be a lot easier.
So I am hoping I can find a way to make calculate work correctly. Finding out that others have experienced the problem was encouraging. Isolating the cause might help, and that is my current hope.
I may well end up having to code some sort of work-around, which I think is what you are suggesting, but not yet. As a naïve idealist, I'd rather have Excel behave the way it ought.
RE: "Calculate" does NOT appear in Excel's status bar
When one takes a moment to think about what the OFFSET function does, this is not all that surprising. The formula
=OFFSET(A1,B2,B3,1,1)
can return the value of any cell on the worksheet, depending upon the values in cells B2 and B3 (and not depending upon the value in cell A1).
Further thought suggests that the resulting recalculation would need to be a full recalculation rather than a "normal" one, and that this will hold whether the recalculation is initiated automatically or manually. Hopefully Excel is clever enough to always use full recalculation on a worksheet that contains an OFFSET function.
RE: "Calculate" does NOT appear in Excel's status bar
It seems that OFFSET is volatile, i.e. it always gets executed when the worksheet is recalculated/changed. INDEX is not, at least after Excel 97 - so you might try using that. I found some interesting background material on http://www.decisionmodels.com/calcsecretsi.htm
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: "Calculate" does NOT appear in Excel's status bar
Does anyone have any good ideas on a way to define a dynamic named range without using the OFFSET function? (And preferably without using any other volatile functions.)
To use a specific example, can the end result of
=OFFSET(Sheet!$C$1,0,0,COUNTA(Sheet!$C:$C),1)
be achieved in some other way?
RE: "Calculate" does NOT appear in Excel's status bar
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: "Calculate" does NOT appear in Excel's status bar
Consider a column of data that begins at cell B5 and can extend down the worksheet as far as you like provided that none of the cells between B5 and the bottom of the data are blank. Allow for the possibility that cells B1 through B4 might or might not be blank. The "standard" way of defining this dynamic array is
=OFFSET($B$5,0,0,COUNTA($B:$B)-COUNTA($B$1:$B$5)+1,1)
Using the INDEX function instead of the OFFSET function, the same dynamic array can be defined as
=$B$5:INDEX($B:$B,ROW($B$5)+COUNTA($B:$B)-COUNTA($B$1:$B$5))
This works on my simple test workbook, so I am hoping it will work on my main megabook when I get around to making the necessary edits.
Thanks to everyone who helped, either through suggestions or encouragement.