Out of Memory with many charts in Excel 1

[segmentationfault]

I have between approximately 1000-1500 lines of data which will be broken into as many as 75 charts. I have a macro which gets through about 50 or so charts and I get the very uninformative error message "Not enough memory." The error message is owned by Excel.

I have moved the code that creates the charts into its own module which must be executed on its own to eliminate overhead (this gained another 10 charts before running out of resources.) I am currently reading the Excel sheet while creating the charts - I considered loading all the range boundaries, titles, and format parameters into arrays and then using these to create the charts, but I'm not convinced this would save any resources (I lose the overhead of searching the sheet while creating charts, but I'm using resources to keep the parameters.)

When the macro is complete, if I try to manually add another chart, I get the same message about insufficient memory. System Monitor says that I have 170M memory allocated - 44M of swap file, 9M free physical memory. Windows is managing my swap file for me.

I'm really at a loss, and not even sure if throwing more RAM at the box will fix it. Any ideas?

 

[segmentationfault]

Ok here's my best solution.

I'm going to turn off the application warnings to get rid of the "Not enough memory" errors. These pop up half a dozen times until the code gets back to a chart.add call. When it cannot add the chart, it generates a VB error, which I've trapped. The error trap will save the file, close the file, reopen the file, and resume where it left off.

I've done a couple test runs of this and it seems to work just fine, but my goodness does it make me nervous. This really turns my code into a house of cards. If someone has a more stable solution I'd love to hear about it.

Thanks.

 

[CookieMonster]

This is a built in problem with Xl, it does seem to get less annoying in Xl 2000, but still.
The reason is that for every chart Xl opens a thread to MS Chart. It doesn't seem to remember that it already is using
MS Chart. It's simply your stack going nuts. Besides this when you've found a way around it and keep adding charts it could mean your workbook goes corrupted. Thanks Bill!

 

[segmentationfault]

Corruption of the file is not a major threat. I've already written warnings and notices for the users - the benefits of the charts far outweigh the inconvenience of possibly corrupting the file. It's only an inconvenience because I destroy and recreate it everytime the macro runs anyway.

Is there honestly no secure fix to this? If I created the charts as an array would it avoid making so many threads? If the charts were sheets rather than embedded objects would it improve? I'm really open to just about anything so long as the charts are kept reasonably visible with the data and I don't have to split up the data into 75 little files.