I've written a VB routine to create a custom chart in which rectangles
are placed at many different X-Y locations. With each new batch of
data, I first erase the old rectangles via:

For n = ActiveChart.Shapes.Count To 1 Step -1
ActiveChart.Shapes.Range(Array(n)).Delete
Next n

However, Excel clearly retains some memory of my previous work because,
if I select a given rectangle (e.g. while recording a macro), I get
code that looks like:

ActiveChart.Shapes("Rectangle 3163").Select

Apparently, I have already plotted and erased over 3000 rectangles!

I'm worried about overflowing this rectangle object counter, and
inadvertently destroying my workbook's ability to function. Does
anyone know if there is a way to reset this counter, or if its maximum
size is so large that I needn't worry about it? (I probably won't
exceed 100,000 rectangles over the life of the workbook.)

Many thanks, and Happy Holidays!

Tom Kreutz

Hi Tom,

Indeed that object counter increments whenever any kind of object is
inserted on the sheet. The only way I know to reset is to remove ALL objects
from the sheet and save (possibly also need to close & re-open).

I am not aware of any problems with a very high object counter, I've never
experienced any even with the counter at many 100k's. Ultimately I guess
there must be a limit before it reaches infinity!

Regards,
Peter T

"kreutz" wrote in message
oups.com...
I've written a VB routine to create a custom chart in which rectangles
are placed at many different X-Y locations. With each new batch of
data, I first erase the old rectangles via:

For n = ActiveChart.Shapes.Count To 1 Step -1
ActiveChart.Shapes.Range(Array(n)).Delete
Next n

However, Excel clearly retains some memory of my previous work because,
if I select a given rectangle (e.g. while recording a macro), I get
code that looks like:

ActiveChart.Shapes("Rectangle 3163").Select

Apparently, I have already plotted and erased over 3000 rectangles!

I'm worried about overflowing this rectangle object counter, and
inadvertently destroying my workbook's ability to function. Does
anyone know if there is a way to reset this counter, or if its maximum
size is so large that I needn't worry about it? (I probably won't
exceed 100,000 rectangles over the life of the workbook.)

Many thanks, and Happy Holidays!

Tom Kreutz

Fwiw, Tom, in all my years hanging here I've never heard of anyone hitting
any kind of limitation with regard to this counter. I think if this is your
biggest concern, you're in great shape!<g.

--
Jim
"kreutz" wrote in message
oups.com...
I've written a VB routine to create a custom chart in which rectangles
are placed at many different X-Y locations. With each new batch of
data, I first erase the old rectangles via:

For n = ActiveChart.Shapes.Count To 1 Step -1
ActiveChart.Shapes.Range(Array(n)).Delete
Next n

However, Excel clearly retains some memory of my previous work because,
if I select a given rectangle (e.g. while recording a macro), I get
code that looks like:

ActiveChart.Shapes("Rectangle 3163").Select

Apparently, I have already plotted and erased over 3000 rectangles!

I'm worried about overflowing this rectangle object counter, and
inadvertently destroying my workbook's ability to function. Does
anyone know if there is a way to reset this counter, or if its maximum
size is so large that I needn't worry about it? (I probably won't
exceed 100,000 rectangles over the life of the workbook.)

Many thanks, and Happy Holidays!

Tom Kreutz

"Peter T" <peter_t@discussions wrote in message
...
Hi Tom,

Indeed that object counter increments whenever any kind of object is
inserted on the sheet. The only way I know to reset is to remove ALL
objects
from the sheet and save (possibly also need to close & re-open).

I am not aware of any problems with a very high object counter, I've never
experienced any even with the counter at many 100k's. Ultimately I guess
there must be a limit before it reaches infinity!

Regards,
Peter T

"kreutz" wrote in message
oups.com...
I've written a VB routine to create a custom chart in which rectangles
are placed at many different X-Y locations. With each new batch of
data, I first erase the old rectangles via:

For n = ActiveChart.Shapes.Count To 1 Step -1
ActiveChart.Shapes.Range(Array(n)).Delete
Next n

However, Excel clearly retains some memory of my previous work because,
if I select a given rectangle (e.g. while recording a macro), I get
code that looks like:

ActiveChart.Shapes("Rectangle 3163").Select

Apparently, I have already plotted and erased over 3000 rectangles!

I'm worried about overflowing this rectangle object counter, and
inadvertently destroying my workbook's ability to function. Does
anyone know if there is a way to reset this counter, or if its maximum
size is so large that I needn't worry about it? (I probably won't
exceed 100,000 rectangles over the life of the workbook.)

Many thanks, and Happy Holidays!

Tom Kreutz

I didn't read your question properly re adding shapes to a chartobject. The
chart has it's own counter but I don't think that can be reset, apart of
course from deleting the chart (but no need to delete other objects).

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Hi Tom,

Indeed that object counter increments whenever any kind of object is
inserted on the sheet. The only way I know to reset is to remove ALL
objects
from the sheet and save (possibly also need to close & re-open).

I am not aware of any problems with a very high object counter, I've never
experienced any even with the counter at many 100k's. Ultimately I guess
there must be a limit before it reaches infinity!

Regards,
Peter T

"kreutz" wrote in message
oups.com...
I've written a VB routine to create a custom chart in which rectangles
are placed at many different X-Y locations. With each new batch of
data, I first erase the old rectangles via:

For n = ActiveChart.Shapes.Count To 1 Step -1
ActiveChart.Shapes.Range(Array(n)).Delete
Next n

However, Excel clearly retains some memory of my previous work because,
if I select a given rectangle (e.g. while recording a macro), I get
code that looks like:

ActiveChart.Shapes("Rectangle 3163").Select

Apparently, I have already plotted and erased over 3000 rectangles!

I'm worried about overflowing this rectangle object counter, and
inadvertently destroying my workbook's ability to function. Does
anyone know if there is a way to reset this counter, or if its maximum
size is so large that I needn't worry about it? (I probably won't
exceed 100,000 rectangles over the life of the workbook.)

Many thanks, and Happy Holidays!

Tom Kreutz

Just for the hell of it, I ran the following code

Dim WS As Worksheet
Dim SH As Shape
Dim N As Long
Set WS = ActiveSheet
Do Until Err.Number < 0
N = N + 1
Set SH = WS.Shapes.AddLine(0, 0, 0, 0)
SH.Delete
Loop

for a while before breaking out of it.

It got up to about 180,000 before I got bored and broke out of the code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"kreutz" wrote in message
oups.com...
I've written a VB routine to create a custom chart in which rectangles
are placed at many different X-Y locations. With each new batch of
data, I first erase the old rectangles via:

For n = ActiveChart.Shapes.Count To 1 Step -1
ActiveChart.Shapes.Range(Array(n)).Delete
Next n

However, Excel clearly retains some memory of my previous work because,
if I select a given rectangle (e.g. while recording a macro), I get
code that looks like:

ActiveChart.Shapes("Rectangle 3163").Select

Apparently, I have already plotted and erased over 3000 rectangles!

I'm worried about overflowing this rectangle object counter, and
inadvertently destroying my workbook's ability to function. Does
anyone know if there is a way to reset this counter, or if its maximum
size is so large that I needn't worry about it? (I probably won't
exceed 100,000 rectangles over the life of the workbook.)

Many thanks, and Happy Holidays!

Tom Kreutz

Dear Folks,

Thanks for all your great comments!! Very helpful!

Tom