Macros in Excel 2000 running very slowly

[cottage6]

Macros I've created in Excel 2000 SP3 have always executed very rapidly, but
within the last week that's changed and any macro in any file now executes
slowly. I also have Excel 2002 SP3 installed on the same drive (C), and
running the same macros in the same files in 2002 is much faster. I have been
working with a very large file that uses cubes created in MS SQL that are
then used to create pivot tables. This file has been edited and saved in
both Excel 2000 and 2002 as I need to make sure it's usable in both versions.
I'm not sure if this is causing the problem or not, but any ideas short of
re-installing Excel 2000 would be great. I have Windows 2000. TIA!

 

[Fredrik Wahlgren]

Macros I've created in Excel 2000 SP3 have always executed very rapidly, but
within the last week that's changed and any macro in any file now executes
slowly. I also have Excel 2002 SP3 installed on the same drive (C), and
running the same macros in the same files in 2002 is much faster. I have been
working with a very large file that uses cubes created in MS SQL that are
then used to create pivot tables. This file has been edited and saved in
both Excel 2000 and 2002 as I need to make sure it's usable in both versions.
I'm not sure if this is causing the problem or not, but any ideas short of
re-installing Excel 2000 would be great. I have Windows 2000. TIA!

Waht does your macros do? Do they call SQL Server? Does your macros run
faster on some other machine? Since you are working with very large files,
how much free diskspace do you have? Have you defrag'ed your hard disk?

/Fredrik

 

[cottage6]

None of the macros call SQL server, and actually the one that sent me over
the edge today is a small macro in a different file that looks at a range of
cells, and then inserts a date in each cell in another column if the
corresponding cell is not blank. I started to run defrag this morning and got
frustrated after approx. 30 minutes, but probably need to run it overnight
tonight. I have 10.7 gb free disk space. My boss can run the above mentioned
small macro in a nano second, while I just chug along taking my sweet time.

 

[Fredrik Wahlgren]

None of the macros call SQL server, and actually the one that sent me over
the edge today is a small macro in a different file that looks at a range of
cells, and then inserts a date in each cell in another column if the
corresponding cell is not blank. I started to run defrag this morning and got
frustrated after approx. 30 minutes, but probably need to run it overnight
tonight. I have 10.7 gb free disk space. My boss can run the above mentioned
small macro in a nano second, while I just chug along taking my sweet time.

In that case, it seems as if the problem has to do with poor file I/O. I
think you should try to delete whatever you can and then defrag the hard
drive overnight. 10.7 Gb may sound like much but if you have less than 10%
free space available, your disk is pretty much stuffed. If your disk is
really fragmeneted, you should run defrag several times.

/ Fredrik