Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations cowski on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

"Calculate" does NOT appear in Excel's status bar 2

Status
Not open for further replies.

Denial

Structural
Jun 3, 2003
924
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?

 
Replies continue below

Recommended for you

I too have noticed the lack of "Calculate" after changing cells. I haven't tried to determine exactly what was going on or when this occurs. I just try to make sure that I manually recalculate before trusting any results.
 
Jghrist,

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.
 
If your code is running, then it knows that it's calculating, doesn't it? Why can't you have the macros set a flag?

TTFN

FAQ731-376
 
IRstuff,

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.
 
Some more experimentation leads me to the conclusion that the "culprit" is the OFFSET formula. I have reproduced the problem in a very small, single-worksheet workbook, both with the OFFSET function embedded within a dynamic named range and with it explicitly entered directly into a cell.

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.
 
Very sharp, it makes a lot of sense. Interesting behavior.

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
Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
 
Now that (I think) I understand my problem, I need to investigate how I might be able to get around it.

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?
 
INDEX comes to mind, but it depends on what you are going to do with the range that you get. You could also write a user VBA function to return the range that you need, and make it non-volatile (= don't include Application.Volatile). This would only require wrapping the Range.Offset method in a user defined function. It will make calculation slower, though.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
 
Thanks to joerd's suggestion, I have used the INDEX function to come up with an alternative formulation for dynamic ranges that seems to leave Excel's "calculation" prompt message behaving as one would like it to behave.

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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor