Excel 2007 Charts & Memory Leak??

[Denis]

I have an Excel 2003 macro that creates line charts using defined
names to specify the x and y values for one or more data series on a
chart. This macro also uses the Dictionary object. Those are the
only two features that I can think of that are perhaps somewhat unique
to this macro.

When I run this macro with Excel 2003 to create about a 100 charts or
so in the same workbook, everything runs fine. I can watch the memory
usage via the Task Manager and the usage peaks at about 100M.
However, when I run this same macro under Excel 2007, the memory usage
grows continually to over 1G and 2007 eventually crashes as it runs
out of memory.

Given that I can watch the memory usage grow as more and more charts
are created, that leads me to suspect that Excel 2007 has a memory
leak somewhere. Does anyone know of a memory leak that might be
associated with this kind of a macro?

Denis

 

[Paul Robinson]

Hi
As always, post the code.
regards
Paul

 

[Denis]

Hi
As always, post the code.
regards
Paul

Normally, I would have posted a relevant section of code but in this
case the code is very extensive and it would be very time consuming
for someone to peruse the code and understand what is going on (and if
it's a memory leak then reading the code is not likely to uncover a
memory leak). That's why I offered the two features that I would
think might most likely be associated with a memory leak. By doing so
I was hoping someone might be able to identify some known 2007 problem
that could lead to a memory leak issue.

Denis

 

[Donald Guillett]

Normally, I would have posted a relevant section of code but in this
case the code is very extensive and it would be very time consuming
for someone to peruse the code and understand what is going on (and if
it's a memory leak then reading the code is not likely to uncover a
memory leak).  That's why I offered the two features that I would
think might most likely be associated with a memory leak.  By doing so
I was hoping someone might be able to identify some known 2007 problem
that could lead to a memory leak issue.

Denis- Hide quoted text -

- Show quoted text -

I question the need to create so many charts when one or a few could
do the same thing with variables.

 

[Denis]

I question the need to create so many charts when one or a few could
do the same thing with variables.

I don't understand why you would question this need. These charts
already have a number of lines on a single chart so more can't be
realistically squeezed on the same chart. Each of these charts chart
something different. So it would not be possible to have fewer charts
without losing information.

Denis

 

[Martin Brown]

I have an Excel 2003 macro that creates line charts using defined
names to specify the x and y values for one or more data series on a
chart. This macro also uses the Dictionary object. Those are the
only two features that I can think of that are perhaps somewhat unique
to this macro.

When I run this macro with Excel 2003 to create about a 100 charts or
so in the same workbook, everything runs fine. I can watch the memory

And also I presume about 10x quicker in XL2003 - at least that is what I
observe in my applications which also plot a lot of graphs from VBA.

usage via the Task Manager and the usage peaks at about 100M.
However, when I run this same macro under Excel 2007, the memory usage
grows continually to over 1G and 2007 eventually crashes as it runs
out of memory.

I don't see memory leaks that grow out of control, but the large number
of graphs I plot each have only modest numbers of data points.

Given that I can watch the memory usage grow as more and more charts
are created, that leads me to suspect that Excel 2007 has a memory
leak somewhere. Does anyone know of a memory leak that might be
associated with this kind of a macro?

It wouldn't surprise me. XL2007 graphics objects and charts in
particular are riddled with bugs and glacially slow.

My first guess would be something odd about interactions between named
regions, dictionary objects and graphics objects leaving orphanned
memory copies of the data lying around. Can you alter the amount of data
plotted or use fixed unnamed ranges per graph to see how it scales?

Regards,
Martin Brown

 

[Denis]

And also I presume about 10x quicker in XL2003 - at least that is what I
observe in my applications which also plot a lot of graphs from VBA.


I don't see memory leaks that grow out of control, but the large number
of graphs I plot each have only modest numbers of data points.




It wouldn't surprise me. XL2007 graphics objects and charts in
particular are riddled with bugs and glacially slow.

My first guess would be something odd about interactions between named
regions, dictionary objects and graphics objects leaving orphanned
memory copies of the data lying around. Can you alter the amount of data
plotted or use fixed unnamed ranges per graph to see how it scales?

Regards,
Martin Brown

Thanks for the suggestions. The charts I'm complaining about chart
data day-by-day and have a lot of data points. Interestingly, I have
they same charts that are plotted with weekly data and have
significantly fewer data points. The 2007 weekly charts run
noticeably slower than 2003 but they do complete. I'll have to
recheck them for memory usage comparison since I'm not sure if I did
that.

As far as named regions are concerned I don't use named regions. I
used defined names to specify the x,y values directly. Here's an
example using the defined names xvals1 for x-values and yvals1_s1 and
yvals1_s2 for data series s1 and s2 y-values:

xvals1={"05/02/2007","05/04/2007","05/05/2007","05/06/2007","05/07/2007","05/11/2007")
yvals1_s1={99.12,99.14,99.21,99.22,99.15,99.12)
yvals1_s2={99.39,99.32,99.27,99.32,99.33,99.25)

Denis

 

[Donald Guillett]

I don't understand why you would question this need.  These charts
already have a number of lines on a single chart so more can't be
realistically squeezed on the same chart.  Each of these charts chart
something different.  So it would not be possible to have fewer charts
without losing information.

Denis- Hide quoted text -

- Show quoted text -

Wanna bet?