Excel Using a Lot of CPU

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:

Set rsInvoice = Nothing

Lesson learned.

Posted in Uncategorized

5 thoughts on “Excel Using a Lot of CPU

  1. 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?

  2. “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.

  3. 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.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.