How do I reference charts with the same name, but on different worksheets?

Question

I have two worksheets containing charts, and use a macro to run over all the sheets with charts in them and update the values charted.

However, I run into a problem when attempting to reference the charts in the sheets after the first one - although the reference for the worksheet changes, the reference to the chart does not.

The loop looks like this:

For Each ws In ThisWorkbook.Worksheets
  Debug.Print ws.Name
  Debug.Print ws.ChartObjects("Kortsone").Chart.Name
  With ws.ChartObjects("Kortsone").Chart
    ...
  End With
Next ws

And the output I get to the immediate window is the following:

Grafar ovn 3
Grafar ovn 3 Kortsone
Grafar ovn 4
Grafar ovn 3 Kortsone

As you can see the reference to the worksheet changes, but the chart reference does not.

Is there any way to fix this, or will I have to rename all my charts with unique names?

I'm using Excel 2013

- edit - I have now done some testing based on suggestions in the comments, and it seems that what is printed to the immediate window depends on what is currently the active sheet.

Trying to use for each chartobject ran into the same issues as I had earlier:

Sub test2()
  Dim ws As Worksheet
  Dim ch As ChartObject

  For Each ws In ThisWorkbook.Worksheets
    For Each ch In ws.ChartObjects
      If ws.CodeName = "Graf4" Then
      Debug.Print ws.Name
      Debug.Print ch.Name
      Debug.Print ch.Chart.Name
      End If
    Next ch
  Next ws
End Sub

Gave:

Grafar ovn 4
Kortsone
Grafar ovn 3 Kortsone
Grafar ovn 4
Langsone
Grafar ovn 3 Langsone
...

Answer 1

There is a difference between Chart and Worksheet.ChartObject.Chart.

Make it clear that

• When you create a chart in a worksheet, Excel creates a ChartObject to contain the Chart. So the Chart is a child of ChartObject which in turn is a child of Worksheet.
• When you create a chart as a sheet, it is a Chart (or you may call it "chart sheet") equivalent to a Worksheet.

Therefore a Worksheet.ChartObject.Chart is different from a Chart sheet in the following way:

• A Chart from Worksheet.ChartObject.Chart contains all the properties of a chart.
• A Chart sheet contains all the properties of a chart AND some properties of a sheet.

So the .Name property is supposed to be for Chart sheet but not for Worksheet.ChartObject.Chart.

I'd say the additional display of the activesheet name when calling ChartObject.Chart.Name is not a bug but a debugged bug. ChartObject.Chart wouldn't and shouldn't have a Name in the first place. You can call ChartObject.Chart.Name because there is an overlapping in the intellisense for object model of Chart. Had Microsoft hasn't allow for this, there would be a bug.

So in short, remember that a chart has no name, it is the ChartObject or the Sheet that carries the name. To contrast this, a chart has ChartTitle.

Answer 2

GetChart will return the chart object on a specific worksheet.

getChart stores the chart objects in a Static Collection. The Static Collection will stay in memory until there is a code break or the workbook is closed.

The first time that you call getChart all the Chart Objects all the each worksheet is activated and each chart on each worksheet is added to the collection. After that the chart is merely lookup up in the static collection.

In the case that a chart (e.g. a chart was added after the function was called) isn't in the collection the function will reload itself.

Get Chart

Function getChart(ChartName As String, WorkSheetName As String, Optional Reload As Boolean) As Chart
    Dim ws As Worksheet, ActiveWS As Worksheet
    Dim co As ChartObject
    Static ChartCollection As Collection

    If ChartCollection Is Nothing Or Reload Then
        Application.ScreenUpdating = False

        Set ChartCollection = New Collection
        Set ActiveWS = ActiveSheet
        For Each ws In ThisWorkbook.Worksheets
            ws.Activate
            For Each co In ws.ChartObjects
                ChartCollection.Add co.Chart, ws.Name & "!" & co.Name
            Next
        Next ws

        ActiveWS.Activate
        Application.ScreenUpdating = True
    End If

    On Error Resume Next
    Set getChart = ChartCollection(WorkSheetName & "!" & ChartName)

    If Err.Number <> 0 And Not Reload Then Set getChart = getChart(ChartName, WorkSheetName, True)

    On Error GoTo 0

End Function

Test

Sub Test()
    Dim ws As Worksheet
    Dim ch As Chart
    Dim msg As String
    Dim Start: Start = Timer

    For Each ws In ThisWorkbook.Worksheets
        Set ch = getChart("Kortsone", ws.Name)
        If Not ch Is Nothing Then

            msg = msg & ws.Name & "!" & ch.Name & " - Validated:" & (ws.Name = ch.Parent.Parent.Name) & vbCrLf

        End If
    Next ws
    msg = msg & "Time in Seconds: " & Timer - Start
    MsgBox msg
End Sub

Answer 3

As you've discovered, the Workheet.ChartObjects method will find the correct ChartObject, but accessing the Chartobject.Chart property will return the Chart of the ActiveSheet. It doesn't matter if you refer to the ChartObject by name or by Index number.

The behavior is the same if you use the Worksheet.Shapes method to find the ChartObject.

This behavior is different to earlier versions of Excel. I confirmed that the code worked in Excel XP/2002, and doesn't work in 2016. I'm unsure when the behavior changed. It might have been 2013, or it might have been a patch to 2013 and 2016? The behavior in Office for mac 2016 is the same (ie. doesn't work)

Until Microsoft comes up with a fix, you'll have to activate the sheet, or activate the ChartObject, before you access the Chart property.

Sub test()
  Dim ws As Worksheet
  Dim co As ChartObject
  For Each ws In ThisWorkbook.Worksheets
    Debug.Print ws.Name
    Set co = ws.ChartObjects("Kortsone")

    ws.Activate
    'or
    co.Activate

    Debug.Print co.Chart.Name
    With ws.ChartObjects("Kortsone").Chart
    End With
  Next ws
End Sub

I suggest that you temporarily disable ScreenUpdating, and reactivate the originally activesheet once you're done.