Function GetChartByCaption(ws As Worksheet, sCaption As String) As Chart
Dim myChart As ChartObject
Dim myChart As Chart
Dim sTitle As String
Set myChart = Nothing
For Each myChart In ws.ChartObjects
If myChart.Chart.HasTitle Then
sTitle = myChart.Chart.ChartTitle.Caption
If StrComp(sTitle, sCaption, vbTextCompare) = 0 Then
Set myChart = myChart.Chart
Exit For
End If
End If
Next
Set GetChartByCaption = myChart
Set myChart = Nothing
Set myChart = Nothing
End Function
Sub TestGetChartByCaption()
Dim myChart As Chart
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set myChart = GetChartByCaption(ws, "I am the Chart Title")
If Not myChart Is Nothing Then
Debug.Print "Found chart"
Else
Debug.Print "Sorry - chart not found"
End If
Set ws = Nothing
Set myChart = Nothing
End Sub