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!