Not Enough Memory Message

December 05, 2001 - by Bill Jelen

Steven from Australia writes:

I have created a VBA macro which has to create about 50 Charts from one worksheet. The problem is that everytime I run the program when I get to the 33rd chart an error message displays "Not enough memory", then the Excel program locks up and I have to terminate the program. I have 256Meg of RAM in my PC and I'm using Excel 97 in a Windows NT operating system.

Are you creating each chart as its own chart sheet? Excel can handle 16 million cells on a worksheet, but the quiet secret is that is can not handle a lot of worksheets. The help file says that the number of worksheets is limited by "available memory".

I regularly experience the problem that you have. It is horribly frustrating, because you never know when it is about to crash. If Visual Basic would give you a trappable error, you could stop the macro, save the file, start in a new file. But they don't - you just get a crash.

I have seen the crash happen as late as 130 worksheets and as early as 40. You have to gauge where it is going to crash in your system, then put a counter in the macro. If you think you are going to crash after 32 charts, then stop the process at 30 charts, save them in a new workbook, close that workbook, and start creating them again in a new workbook.

This isn't pretty, but it is the only workaround that I have found.

Another thought - make sure that you close each module and userform in the Visual Basic Editor using the "X" in the upper right corner. I have found that by simply closing all of the components in Visual Basic before running the macro, you can free up a bit more memory and possibly squeeze a few more charts into the "available memory".

Above, I talked about doing things to conserve memory. Steven wrote back today with an excellent discovery:

I found that if I set the Charts AutoScaleFont to False, I could create about 120 chart, which has solved my problem.

Why this is so I have no idea, but thats Excel. Excellent tip - tuck this one away as an obscure method for conserving memory.