×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

"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?

 

RE: "Calculate" does NOT appear in Excel's status bar

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.
 

RE: "Calculate" does NOT appear in Excel's status bar

(OP)
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.
 

RE: "Calculate" does NOT appear in Excel's status bar

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: Eng-Tips.com Forum Policies

RE: "Calculate" does NOT appear in Excel's status bar

(OP)
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.
 

RE: "Calculate" does NOT appear in Excel's status bar

(OP)
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.
 

RE: "Calculate" does NOT appear in Excel's status bar

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 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

(OP)
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?

RE: "Calculate" does NOT appear in Excel's status bar

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: 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

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources