Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart "Name" Property
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart "Name" Property
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 -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=509138 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart "Name" Property
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 "Jim Hagan" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why do i "sometimes" need to "edit chart object" before "source da | Charts and Charting in Excel | |||
How can I reset the default pie chart label "Other" to "Equity"? | Charts and Charting in Excel | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Multiple "Range" with "Cells" property? | Excel Programming | |||
Multiple "Range" with "Cells" property? | Excel Programming |