Set Calculation Mode to Automatic without calculating

[jeeerooome]

Hello,


I'm experiencing a huge performance issue when testing a fairly large
workbook on XL2003 (vs XL2000).

Calculation Mode is set to Automatic. I found out that XL2003 triggers
a recalculation when hiding/showing some rows, while XL2000 does not.

- Is there a way to make XL2003 behave like XL2000 on this very point?
(no calculation upon hide/show rows)




If not I'm trying to workaround this (VBA).

I can set the calculation mode to Manual right before hiding/showing
the rows, and then reset it back to automatic afterwards. My problem
now is that XL recalculates when Calculation mode is reset to
automatic.

Is there a way to reset the Calculation Mode to automatic (I don't want
to keep Manual calculation mode) without having XL to recalculate
everything?


Thanks in advance for your help,
Jerome

 

[Jim Rech]

The reason for this change in Excel 2003 is that the SUBTOTAL function was
enhanced to include the option to sum just visible rows. So since hiding
and unhiding rows can now change calculated results, Excel does a calc.

The only option I can think of is to set calculation to manual and to do a
calc via macro every time the user makes an entry (I assume that is your
goal in keeping calc automatic).

To do that you'd need to add this sub to the ThisWorkbook module of the
workbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Calculate
End Sub


--
Jim
| Hello,
|
|
| I'm experiencing a huge performance issue when testing a fairly large
| workbook on XL2003 (vs XL2000).
|
| Calculation Mode is set to Automatic. I found out that XL2003 triggers
| a recalculation when hiding/showing some rows, while XL2000 does not.
|
| - Is there a way to make XL2003 behave like XL2000 on this very point?
| (no calculation upon hide/show rows)
|
|
|
|
| If not I'm trying to workaround this (VBA).
|
| I can set the calculation mode to Manual right before hiding/showing
| the rows, and then reset it back to automatic afterwards. My problem
| now is that XL recalculates when Calculation mode is reset to
| automatic.
|
| Is there a way to reset the Calculation Mode to automatic (I don't want
| to keep Manual calculation mode) without having XL to recalculate
| everything?
|
|
| Thanks in advance for your help,
| Jerome
|

 

[jeeerooome]

Hi Jim (and the others too)


I figured I would have to *manually* fake the autocalculation whenever
required.
I was just hoping someone would come up with an idea on how to set
calculation mode back to automatic without firing a calculation.
At some point I thought I found a way with the CalculationState
property but unfortunately it's a read-only property and I can't set it
to xlDone myself like I trick the Saved property of the workbook before
close.

Thanks anyway.
Just in case there's still a solution around, anybody else?


Jerome