Macros SLOW in 2002, FAST in '97 ?

[ericafc]

Has anyone else come across this problem??

I ran the same macro in excel 2002, and then excel 97 (both from the C
drive)

the 2002 version took 25 minutes
the '97 version took 3 minutes

has anyone else come across macro speed issues between differen
versions of excel

 

[firefytr]

yeah, sometimes. only time i've seen it is when variables were no
dim(ensioned) and application properties were not handled properly.
for example, excel 2002 and up, if you disable screenupdating, will no
automatically turn it back on. have you thought about turning o
option explicit? as a double check for your variables? you coul
always post your code in the excel programming forum and get a ver
thorough critique.

ht

 

[Dave Peterson]

Difficult to guess without specifics, but...

And a couple of easy things to do outside of excel and in your macro:

Close excel, clean up your windows temp folder.

In your code:
set calculation to manual, run your code, reset it to what it was.
Turn screenupdating off when you start and on when you finish.
turn the display of pagebreaks off when you start.


Lots of my macros have this at the top:

Dim CalcMode As Long
CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
ActiveSheet.DisplayPageBreaks = False

and near the bottom:

Application.Calculation = CalcMode
Application.ScreenUpdating = True

========
I had one macro that deleted rows. The first time through, it was fine. It
slowed to a crawl on subsequent runs.

Each time I deleted a row, excel thought it had to determine where to draw those
page break lines. Turning off this display really speeded things up.

(maybe it's not xl97/xl2002--maybe it was just a coincidence.)

 

[Dave Peterson]

You may want to take a look at these two sites
(Charles Williams and David McRitchie):

http://www.decisionmodels.com
http://www.mvps.org/dmcritchie/excel/slowresp.htm