Conditional Calculation

[ffffloyd]

One of the sheets in my workbook contains a cross-reference grid that takes a considerable amount of grunt to recalculate.

The problem is that whenever I change anything else it wants to recalculate this page every time, which takes about 20 seconds each time.

I cannot just switch the Options to Manual Calculation because that affects the whole workbook. I do want it to calculate other sheets automatically.

There are even parts of the sheet in question that I want it to recalculate (eg, grid labels).

I presume I can tell it somehow in the Worksheet_Calculate event to ignore a particular range under certain conditions when recalculating, but what is the actual mechanism, ie how would I code it, to not calculate for a given region?

[MatrixMan]

The manual/auto calculate option is an application level setting as you've pointed out, so it's either on or off. What you can do is "interupt" the calculation if the cell that's caused the recalc is within the range in question. I'd suggest you look at the Worksheet_Calculate event and put code in there to halt it if target.address is in your range (look at the Intersect function to determine if the target is in your range). Hope that helps. MM.

[ffffloyd]

*puts on construction worker's helmet*

singing (after a fashion):
Matrix, Matrix Man!
I wanna be a Matrix Man!

*removes construction worker's helmet and looks around with embarrassment hoping nobody was watching*


Sorry, MM, I probably didn't put it clear enough because that didn't answer the question I was asking. To put it another way, I know I need to put code into Worksheet_Calculate but what specific function or coding approach would I use to prevent the calculation if a target cell falls into the range (a simple, single rectangular range) that is not to be calculated?

In addition, I imagine Worksheet_Calculate is itself called with a range to be recalculated. Given that range parameter, how do I divide the cells that can be calculated from those which should not?

[shg]

In VBA, you can recalculate the workbook, a sheet, a range, or a single cell using the Calculate method., e.g.

Please Login or Register  to view this content.

See Help for the Calculate method.

[mikerickson]

Have you looked at setting Excel>Options>Calculation to Automatic Except Tables?