I've a few Excel VBA macros which repetitively open a few hundred data
files (no more than three or four are open at any one time), do some
heavy number-crunching, save the results, and then do it all over
again a few hundred times with slight variations in parameters. It
takes over a week to run one of these, and using the Timer function I
discovered that the time taken to do one iteration slowly increases
from a few minutes to hours (even though the data and calculations are
the same) and despite all effort to close files as soon as finished,
etc. I'm using Excel 2000 (9.0) and read in the Microsoft knowledge
base that there are memory leak bugs supposedly fixed with the service
pack releases, which I applied, to no effect. Then I tried installing
Excel XP and found that the increasing slowdown still was there (and
Excel XP in total runs noticeably slower than 2000, so I uninstalled
XP) so if it is a memory leak it's common to multiple versions. No
error messages generated and it eventually successfully finishes, but
it just slowly slows to a crawl as it runs. The OS is XP. Restarting
Excel (without rebooting) gets it going fast again. Any and all
suggestions as to where to look for this problem, how to narrow down
the source or diagnose it, fix it, etc., would be most welcome.
Thanks!

Richard,

If you are doing any Cut/Copy & Pastes then be sure to do the following
command after the Paste, and before closing the file:-

Application.CutCopyMode=False

I have had memory problems when I didn't do this.

regards,

JohnI

"Richard" wrote in message
m...
I've a few Excel VBA macros which repetitively open a few hundred data
files (no more than three or four are open at any one time), do some
heavy number-crunching, save the results, and then do it all over
again a few hundred times with slight variations in parameters. It
takes over a week to run one of these, and using the Timer function I
discovered that the time taken to do one iteration slowly increases
from a few minutes to hours (even though the data and calculations are
the same) and despite all effort to close files as soon as finished,
etc. I'm using Excel 2000 (9.0) and read in the Microsoft knowledge
base that there are memory leak bugs supposedly fixed with the service
pack releases, which I applied, to no effect. Then I tried installing
Excel XP and found that the increasing slowdown still was there (and
Excel XP in total runs noticeably slower than 2000, so I uninstalled
XP) so if it is a memory leak it's common to multiple versions. No
error messages generated and it eventually successfully finishes, but
it just slowly slows to a crawl as it runs. The OS is XP. Restarting
Excel (without rebooting) gets it going fast again. Any and all
suggestions as to where to look for this problem, how to narrow down
the source or diagnose it, fix it, etc., would be most welcome.
Thanks!

"Richard" wrote in message
m...
I've a few Excel VBA macros which repetitively open a few hundred data
files (no more than three or four are open at any one time), do some
heavy number-crunching, save the results, and then do it all over
again a few hundred times with slight variations in parameters. It
takes over a week to run one of these, and using the Timer function I
discovered that the time taken to do one iteration slowly increases
from a few minutes to hours (even though the data and calculations are
the same) and despite all effort to close files as soon as finished,
etc. I'm using Excel 2000 (9.0) and read in the Microsoft knowledge
base that there are memory leak bugs supposedly fixed with the service
pack releases, which I applied, to no effect. Then I tried installing
Excel XP and found that the increasing slowdown still was there (and
Excel XP in total runs noticeably slower than 2000, so I uninstalled
XP) so if it is a memory leak it's common to multiple versions. No
error messages generated and it eventually successfully finishes, but
it just slowly slows to a crawl as it runs. The OS is XP. Restarting
Excel (without rebooting) gets it going fast again. Any and all
suggestions as to where to look for this problem, how to narrow down
the source or diagnose it, fix it, etc., would be most welcome.
Thanks!



One thing to check- if you are using MS Outlook go into "Options",
"Preferences", and click on "Journal Options" in the "Contacts" portion of
the window. Make sure that Microsoft Excel is not checked in the "Also
record files from" window. When we installed our most recent version of MS
Office, the journal was automatically recording everything in Excel and it
really slowed things down.....