Excel 2003

[Guest]

I recently discovered that Excel 2003 seems to invoke Functions when rows are hidden and unhidden. I have a rather large spreadsheet that I've used for years, and it has several custom functions and macros. The macros basically hide and unhide rows based on selections a user makes, and the functions are just basic stuff. I have proven that in Excel 2002, the functions are NOT called when rows are hidden or unhidden, but the same spreadhsheet opened in 2003 DOES call the functions when rows are hidden/unhidden. The result of this is it is VERY slow...Keep in mind, it is NOT the macro because even if you just highlight a bunch of rows, it is slow, BECAUSE it is calling the function. Does anyone know WHY it thinks the function needs to be called?

Thanks,
Steve

 

[Charles Williams]

Hi Steve,

I agree that Excel 2003 has changed the way it handles hiding/unhiding rows:
hiding and unhiding (even when no rows are actually hidden or unhidden)
seems to flag the rows as dirty, which triggers a recalculation if
calculation is automatic.

Previous versions of Excel did not do this.

I do not know why it does this.

You can bypass most of the problem by switching calculation to manual at the
start of your macro, and back to automatic at athe end.

regards
Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

I recently discovered that Excel 2003 seems to invoke Functions when rows

are hidden and unhidden. I have a rather large spreadsheet that I've used
for years, and it has several custom functions and macros. The macros
basically hide and unhide rows based on selections a user makes, and the
functions are just basic stuff. I have proven that in Excel 2002, the
functions are NOT called when rows are hidden or unhidden, but the same
spreadhsheet opened in 2003 DOES call the functions when rows are
hidden/unhidden. The result of this is it is VERY slow...Keep in mind, it
is NOT the macro because even if you just highlight a bunch of rows, it is
slow, BECAUSE it is calling the function. Does anyone know WHY it thinks
the function needs to be called?

 

[Charles Williams]

I suspect the reason that Excel 2003 has to do this is because the SUBTOTAL
function has an option to ignore or include hidden rows, and therefore Excel
needs to recalculate if you hide or unhide rows.


regards
Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com