Excel VBA Looping Problem
Posted by Debbie on September 01, 1999 5:33 PM
I have an Excel VBA loop that is crashing after 45 loops. You can restart the macro and it will run another 45 loops. Is there a looping limitation in VBA loops? This program opens 5 different spreadsheets, saves them to one workbook (with 5 pages) closes the spreadsheets and saves them in a new directory. This program has to loop about 200 times. Any ideas on how to fix?
Posted by Chris on September 02, 1999 5:23 AM
Debbie,
There is no limitation to the number of times a routine can loop. What is the error that you receive when the macro crashes? That will most likely lead to the cause of the failures.
Chris
Posted by Debbie on September 02, 1999 4:38 PM
That is the weird thing. It produces an illegal error and shuts down Excel.
Posted by Ivan Moala on September 03, 1999 5:00 AM
Debbie,
Is the loop copying / adding these sheets to your
new workbook, if so then this article may help;
http://support.microsoft.com/support/kb/articles/Q177/6/34.ASP
Ivan
Posted by KC Caldwell on December 15, 1999 12:18 PM
I'm not entirely sure, but it might be some sort of memory overload. Maybe you can look at ways to reduce the size of the files, or import the information from the unopened files.
KC
Posted by Dave on January 09, 2000 4:23 PM
Debbie
If your code is using the Set keyword you may want to try resetting it back to Nothing.
EG
Set MySheet = ActiveWorkbook.Activesheet
<Your code>
Set MySheet = Nothing
Dave
