how to track named ranges in chart

[JackkG]

Hi all,


Is there any way to track named range in chart. I mean is it possible to detect if a given named range is used within a chart with the help of VBA?


Thanks!


cross-posted here:
how to track named range in a chart

 

[mancubus]

see my reply with attached file at: named range tracking via VB

Sub vbax_52434_ChartSeriesSourceData()

    Dim wbNamedRanges()
    Dim cht As ChartObject
    Dim SeriesFormula As String, SeriesAddress As String, SeriesRange As String
    Dim i As Long
    
    With ThisWorkbook
        Select Case .Names.Count
            Case Is = 0
                MsgBox "There are no named ranges in this workbook!"
                Exit Sub
            Case Else
                ReDim wbNamedRanges(1 To .Names.Count)
                For i = 1 To .Names.Count
                    wbNamedRanges(i) = .Names(i).Name
                Next i
        End Select
    End With
    
    Set cht = Worksheets(1).ChartObjects(1) 'sample chart: named range
    'Set cht = Worksheets(1).ChartObjects(2) 'sample chart: not named range
    
    For i = 1 To cht.Chart.SeriesCollection.Count
        SeriesFormula = cht.Chart.SeriesCollection(i).Formula
        SeriesAddress = Split(SeriesFormula, ",")(2) '3rd bit in series formula
        SeriesRange = Mid(SeriesAddress, InStr(SeriesAddress, "!") + 1) 'remove wb/ws referenece
        If UBound(Filter(wbNamedRanges, SeriesRange)) > -1 Then
            MsgBox "Chart: " & cht.Name & " / Series: " & cht.Chart.SeriesCollection(i).Name & " / Source Data: " & Filter(wbNamedRanges, SeriesRange)(0) & " / Named Range"
        Else
            MsgBox "Chart: " & cht.Name & " / Series: " & cht.Chart.SeriesCollection(i).Name & " / Source Data: " & SeriesRange & " / Not Named Range!"
        End If
    Next

End Sub

 

[JackkG]

Hi mancubus,

This works great. Just one tweak to this, what if i want to find if the given named range is used within a chart. I mean in multiple sheets there are named ranges, and say if i want to find one of the named ranges to check if its used in chart, how to go about this in VBA?

Thanks!

 

[JackkG]

Or the given code does the same?