Bring Excel to its Knees!

If you ever want to make Excel on a Mac die quickly, go ahead and do what I did. It's pretty fun to watch the RAM consumed by Excel climb up from its usual 80MB -250MB range climb above 750MB, and keep climbing. Apparently 1.7 GB is the magic number in Excel 2011 on my MacBook Pro:

Even between Excel and Firefox taking up 25% of my 8GG, my computer never felt sluggish. Excel didn't run sluggishly either, it just kept chugging away happily until it died.

So, What Happened?

I was testing a VBA program in Excel, and the first time Excel completely bombed out and crashed I had no idea what happened. Excel just disappeared, the 10 windows open vanished and were replaced with a "You screwed it up. Wanna admit it?" type message. Naturally, I tried to reproduce the error which I succeeded at! This time I was paying attention to a few things, namely watching CPU and memory usage in Activity Monitor in OS X. I quickly became interested in the occasional jumps in RAM usage and right about the time I took the screenshot above, it crashed again.

Restarting the process yet again, I watched the program run to see when the RAM would jump up and I noticed an obvious pattern that lines up with the handling of a specific temporary file. I went to the class that handles the file in question.

Dim tmpWrkBk As Workbook 
Set tmpWrkBk = Workbooks.Add

' Lots of code here
tmpWrkBk.Close SaveChanges:=False

There's a block of code that bring a lot of data into this file from various sources, manipulate as needed, and then pushes the data out to its final resting place before closing the file. At that point I had neglected to add this line:

Set tmpWrkBk = Nothing

Oops...

Compounded by Loops

The block of code above just happens to be inside a loop that cycles through up to 20 times so when I had tested with a limit of 3 cycles, Excel was far from running out of RAM and I never even noticed the jumps in consumption. But as it cycled through more times it would continue allocating more RAM without letting it go.

Lesson Learned

All in all, it only took a few minutes to narrow down the source of the error with some careful observation, but it's always good to double check to make sure you are cleaning up your variables when you're done with them!