MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point 2nd Edition

Fifteen months after Dynamic Arrays debuted for Office Insiders, the functions are being released to General Availability. This second edition of the book has been updated with new examples: see how Dynamic Arrays make XLOOKUP better. The chapter on the logic behind arrays has been expanded.