Excel VBA "Out of Memory" debugging techniques

Question

I was debugging a problem mentioned in a few other* questions on SO and noticed a strange behavior during the debugging process.

The behavior: Experienced 'out of memory' error while pasting complex formulas. Only about half of the 20,000 rows I'm iterating get formulas pasted before the error. Commented out virtually all code, error goes away. Uncomment code incrementally in the hopes of discovering the specific section of code that's causing it. End up uncommenting all code and stop experiencing the bug!

This means the exact same code worked fine in the same Excel instance, and fixing it only required running various lighter versions of the code before going back to the original version. What could possibly cause this?

Solution

Assuming the data you were running on was exactly identical every time, it sounds more like your problem was with the environment - the problem might be that the operating system ran out of memory. In Excel 2007, usable memory for formulas and pivot caches was increased to 2 gigabytes (GB), so that is probably not the issue. However it is of course also limited by how much memory your operating system had available at that time.

The problem may have occurred because when you first tested it, your available operating system memory was lower (from other processes running... could even have been pushed over the limit by background programs such as Antivirus software running a scan) than when you ran the full macro later. I would try running your macro with the Task Manager open to see if you are getting anywhere close to low on physical memory. Also, (assuming you are on Excel 2007 or later) look at how much memory Excel is using and see if you are getting anywhere close to the 2GB limit. I doubt that this would be the issue, but it's at least worth double checking. Also, like Zairja said, make sure you're setting the calculation to manual at the beginning.

You said that you were using complex formulas... check out this article on Improving Performance in Excel

There is a lot of useful information in the article that will probably help you streamline your macro.

Is this helpful to you?