Chart "Name" Property

[Jim Hagan]

I have an application whereby I am automatically processing a bunch of data
and adding scatter plots (x-y plots) to a worksheet (not a chart sheet).

My spreadsheet currently has 3 charts on it. Using the following code ...

Private Sub DebugPrintChartNames()
Dim chtObj As ChartObject
For Each chtObj In ActiveSheet.ChartObjects
Debug.Print chtObj.Name
Next chtObj
End Sub

I see that my charts are named ...
Chart 1
Chart 2
Chart 3

Now if I delete 2 of the charts, (say charts 2 and 3 for example) and create
2 new charts, I see that my charts are now named as ...

Chart 1
Chart 4
Chart 5

First question is ... why doesn't Excel reuse the names Chart 2 and Chart
3, instead of creating Chart 4 and Chart 5?

Second question is ... is there a limit on how many charts can be created in
a worksheet?

Third issue ... I renamed the charts using the following code ...

Private Sub RenameChartObjects()
i = 1
For Each chtObj In ActiveSheet.ChartObjects
chtObj.Name = "Chart" & i
Debug.Print chtObj.Name
i = i + 1
Next chtObj
End Sub

.... and got the following chart names ...
Chart1
Chart2
Chart3

I then added an additional chart and again printed out the chart names, only
to get this ...

Chart1
Chart2
Chart3
Chart 6

If I delete all 4 charts and then add 1, the chart name is "Chart 7". Is
there a way to reinitialize the internal chart numbering within Excel?

My biggest concern is that I'll hit a limit on Excel's chart numbering
scheme somewhere down the line. I don't know what the line is or if I should
even be concerned. Nonetheless, I intend for the application to be used by a
number of people who can add or delete charts as they please. We're talking
about a relatively large amount of data with alot of dependent variables
within the data (> 200), so it's conceivable that 100 or more charts could be
created (until we can figure out what's important and what's not).

Thanks in advance for any help provided. Really just looking for an
explanation of how the chart numbering scheme works in Excel.

Jim Hagan

 

[Leith Ross]

Hello Jim,

To understand what is happening here you have to remember that this a
collection. All collections share common attributes like Add, Count,
Item, and Remove methods. They also contain a "Key" and "Value". The
key is the system's index into the table of Values.

The Chart collection doesn't allow you to set the "Key" like you can
with standard Collection object. If it did you could reuse the numbers
you have already deleted. The only way to reset the Chart collection is
to remove all objects from it and re-open the workbook.

As far as the limit goes, I don't think that will be an issue. If the
index is only an integer value, that would give 65536 charts. If if it
were a Long Integer you could have up 16.7 million. But the size of the
chart and it's complexity are more of an issue. Graphics use lots of
memory resources. Add to that what programs are loaded , the amount of
memory available, etc. and it's almost impossible to give an exact
number of charts that the system can handle at once.

Hope this helps you understand a lottle more about what is happening.

Sincerely,
Leith Ross

 

[Peter T]

Hi Jim,

I have a slightly different take. Excel increments something akin to an
object counter each time an object is added to the sheet. The default name
is applied as "ObjectType x". There is only one counter, ie not separate
counters for charts and (say) rectangles.

The only way I know to reset the counter is to remove ALL objects from the
sheet, save & close. If you want to keep objects and reset the counter -
first copy them to another sheet.

I have never hit the "counter" limit, even after adding/deleting many 1000's
of objects multiple times, but it is irritating.

Charts occupy surprisingly little in the way of resources so you shouldn't
have any problem with 1-200 charts, particularly if spread over a few sheets
(even in an old system). The related data and possible recalculation/redraw
of charts is another matter.

Regards,
Peter T