Resetting an object counter

K

kreutz

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
 
P

Peter T

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
 
J

Jim Rech

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>.
 
P

Peter T

Peter T said:
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
Click to expand...
 
P

Peter T

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
 
C

Chip Pearson

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)
 
Top