Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set Calculation Mode to Automatic without calculating
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set Calculation Mode to Automatic without calculating
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 wrote in message oups.com... | 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 | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set Calculation Mode to Automatic without calculating
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manual Calculation in Excel 2007 with Calculate mode set to Automatic | Excel Worksheet Functions | |||
Formulas not calculating - Calculation is on Automatic & I pressed | Excel Discussion (Misc queries) | |||
Calculation Mode | Excel Programming | |||
Calculation Mode | Excel Programming | |||
range.calculation with UDF not working when calculation is set to automatic | Excel Programming |