ChartObject Name property allowed characters 1

[xjena]

Hi,
I'm trying to write some macro in VBA for Excel that manipulates with charts. I'm using ChartObject and I need to access them by name, so I rename them as shown in code sample below.

Sub test()
    ' add two new chart objects and name them
    Dim co As ChartObject
    Set co = ActiveSheet.ChartObjects.Add(10, 10, 100, 100)
    co.Name = "chart-1"
    Set co = ActiveSheet.ChartObjects.Add(20, 20, 100, 100)
    co.Name = "chart 2"
    
    ' loop through ChartOjects and show their names
    For Each co In ActiveSheet.ChartObjects
        Debug.Print co.Name
    Next
End Sub

Expected result of the macro above is

chart-1
chart 2

but I get only

chart 2

Does anyone know, what is the problem?
Does anyone know if there are any (more than "-") prohibited characters in ChartObject.Name property?

-xjena

 

[xjena]

I didn't notice that I'm using Microsoft Excel 2002 SP3.
Sorry.

 

[PHV]

What is the value of ActiveSheet.ChartObjects.Count ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xjena]

The value of ActiveSheet.ChartObjects.Count is 2.
On the active sheet in Excel I can see Two chart objects. If I select each by clicking on it while holdinf Ctrl key, they have right names, see

 

[PHV]

And this ?
For i = 1 To ActiveSheet.ChartObjects.Count
Debug.Print ActiveSheet.ChartObjects(i).Name
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xjena]

That works fine!

I need to do this (apart from other things):

Dim co as ChartObject, chartObjectName as String
Set co = ActiveSheet.ChartObjects(chartObjectName)
...

with given chartObjectName.
Apparently this code doesn't work for chartObjectName containing "-" character for the same reason as "for each" loop.

But I can handle it now with the code you sent.

Thanks

 

[xjena]

Solution:

Alternative function to method WorkSheet.ChartObjects(chartObjectName).
The method WorkSheet.ChartObjects(chartObjectName) doesn't work doesn't return ChartObject which Name property contains character "-" (at least). Function below works correctly.

' returns ChartObject with name chartObjectName if exists on worksheet sheet, otherwise returns Nohning.
Private Function getChartObjectByName(sheet As Worksheet, chartObjectName As String) As ChartObject
    Dim i As Integer
    For i = 1 To sheet.ChartObjects.Count
        If (sheet.ChartObjects(i).Name = chartObjectName) Then
            Set getChartObjectByName = sheet.ChartObjects(i)
            Exit Function
        End If
    Next i
    Set getChartObjectByName = Nothing
End Function

And use it as

Dim co As ChartObject, sheet As WorkSheet, chartObjectname As String
set co = getChartObjectByName(sheet, chartObjectName)

instead of

Dim co As ChartObject, sheet As WorkSheet, chartObjectname As String
set co = sheet.ChartObects(chartObjectName)

.

Instead of

Dim co As ChartObject, sheet As WorkSheet
For Each co In sheet.ChartObjects
    ' code using "co"
    ' ...
Next

use

Dim co As ChartObject, sheet As WorkSheet
For i = 1 to sheet.ChartObjects.Count
    Set co = sheet.ChartObjects(i)
    ' code using "co"
    ' ...
Next