Resetting an object counter

  • Thread starter Thread starter kreutz
  • Start date Start date
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
 
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
 
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>.
 
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...
 
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
 
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)
 
Back
Top