I have this macro that takes a long time to run. When it’s done running, Excel become unresponsive and the Task Manager reports that Excel is using close to 100% of the CPU.
I just changed the macro recently so I know I screwed something up when I changed it. It turns out that I failed to close two ADODB Recordset objects. Oh, and these objects were in loops, so there was probably a lot of them left open. These two statements fixed the problem:
rsInvoice.Close
Set rsInvoice = Nothing
Set rsInvoice = Nothing
Lesson learned.
I am curious as to what “these objects were in loops” means. And, how that translated to “a lot of them left open” If the rsInvoice was used in an inner loop of sorts, you must have been reusing the object. So, you should have only one of them. OTOH, if you wrote a procedure to tackle the inner loop, the rsInvoice should have been deallocated whenever you exited the procedure. So, does your experience point to a serious bug in the VB runtime/OS garbage collector?
Is this recordset object by any chance in a class module with multiple instances instantiated from another class?
“rsInvoice should have been deallocated”
I didn’t test it, but I think if I would have closed the recordset, VBA would have cleaned up the variable instance. But the fact that I didn’t close it, kept it open and VBA wouldn’t destroy something that was active. Another odd thing, to me, is that Excel wasn’t using an inordinate amount of memory, just CPU.
Good point. Last year, I streamlined someone else’s VBA code dramatically. I found that not only were two objects not being deallocated, they were created INSIDE a large loop. That meant that every time the code was run, more Excel memory was taken away.
I had similary problem with ADO and Exxel. When I use Query for many times, Excel takes more and more memory and finally crashed.
I found this and it solved it
http://support.microsoft.com/kb/319998