Loop through all charts in a workbook with VBA

Question

I am trying to loop through all charts in a workbook. Why is option 1 working, but option 2 not?

'OPTION 1

For Each sht In ActiveWorkbook.Worksheets
    For Each cht In sht.ChartObjects
        MsgBox (cht.Name)
    Next cht
Next sht

'OPTION2

Dim oChart As Chart
    For Each oChart In Application.Charts
        MsgBox (oChart.Name)
    Next oChart
End Sub

Solution 1

There are two flavors of charts:

1. "big" charts - an entire chart sheet
2. "little" charts - chart objects embedded in a worksheet

This code:

Sub dural()
    Dim oChart As Chart
    For Each oChart In Application.Charts
        MsgBox oChart.Parent.Name & vbCrLf & oChart.Name
    Next oChart
End Sub

will display information about the "big" variety.

and if you want information on the "little" charts:

Sub dural2()
    Dim sh As Worksheet, i As Long
    For Each sh In Worksheets
        If sh.ChartObjects.Count > 0 Then
            For i = 1 To sh.ChartObjects.Count
                MsgBox sh.ChartObjects(i).Chart.Name
            Next i
        End If
    Next sh
End Sub

Note that we need an explicit If statement to handle sheets with no charts and .Chart is used to access the Chart within each of the ChartObjects list.

Solution 2

As the documentation states, Application.Charts returns a Sheets collection containg all chart sheets (not charts!). For Worksheet.ChartObjects, however, the documentation says that it returns a ChartObjects collection containing all charts on that sheet.