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
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.
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!
Published March 06, 2014
Last Updated May 02, 2019