Back

About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store
Podcast
Search
Contact
Home

 

 

Past Tip of the Day

 

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.

By Bill Jelen on 05-Dec-2001

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

 

Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.