Trace Dependents for cells used in charts-CodePudding

How can we use VBA, to determine if a particular Excel cell is used in a chart elsewhere in the Workbook?

I regularly use the built-in Trace Dependents function to see how readily I can change/remove cells, but it only indicates which cell functions refer to the cell.

(There's a related old, unanswered question at Mr. Excel)

CodePudding user response：

There is, as far as I know, nothing build-in, you will need to use some VBA code.
You will need to understand the object model of a chart in Excel/VBA:

A worksheet has a collection of DataSheetObjects.
A DataSheetObject is the container for the chart itself, stored as propery Chart.
The chart contains a collection of data series. The visible series can be accessed with SeriesCollection, all (even the hidden) series with FullSeriesCollection.

Unfortunately, it is a little bit tricky to access the range that is used in a series. You have to use the property Formula, that looks something like this: =SERIES("MyName",Sheet1!$A$4:$A$7,Sheet1!$B$4:$B$7,1).

Lets quickly look at the pieces: It contains basically 4 pieces, separated with a comma:
(0) the series name: "MyName" (With the Prefix =SERIES(, but we don't care).
(1) the range holding the x-axis values: Sheet1!$A$4:$A$7
(2) the range holding the values: Sheet1!$B$4:$B$7,1
(3) the index: 1 (plus the closing ) )

We are interested in the two ranges. So what we have to do is to write a functiont that extract this ranges and compare it with the cell you are interested in. The following function checks this for one chart:

Function isCellUsedInChart(cell As Range, ch As Chart) As Boolean
    Dim s As Series
    For Each s In ch.FullSeriesCollection
        ' Debug.Print s.Formula
        Dim pieces() As String
        pieces = Split(s.Formula, ",")
        
        Dim i As Long
        For i = 1 To 2
            Dim p As Long, sheetname As String, seriesAdr As String, seriesRange As Range
            p = InStr(pieces(i), "!")
            ' Extract sheetname
            sheetname = Left(pieces(i), p - 1)
            If sheetname <> cell.Parent.Name Then Exit Function     ' different sheet.
            
            ' Extract data range
            seriesAdr = Mid(pieces(i), p   1)
            Dim ws As Worksheet
            Set ws = cell.Parent
            Set seriesRange = ws.Range(seriesAdr)
            
            If Not Intersect(seriesRange, cell) Is Nothing Then
                isCellUsedInChart = True
                Exit Function
            End If
        Next i
    Next s
End Function

Now all you have to do is to loop over all charts of all sheets, or with other words, of all ChartObjects of all Worksheets.

A small addition: You might have charts as a sheet in a workbook. You can access them using the Charts-collection of the workbook. For this, the 2nd loop in the following function is used.

Function isCellUsedInAnyChart(cell As Range) As Boolean

    Dim wb As Workbook, ws As Worksheet, co As ChartObject
    Set wb = cell.Parent.Parent
    ' Loop over all worksheets
    For Each ws In wb.Worksheets
        For Each co In ws.ChartObjects
            If isCellUsedInChart(cell, co.Chart) Then
                isCellUsedInAnyChart = True
                Exit Function
            End If
        Next
    Next
    Dim ch As Chart
    ' Loop over all chart sheets
    For Each ch In wb.Charts
         If isCellUsedInChart(cell, ch) Then
            isCellUsedInAnyChart = True
            Exit Function
        End If
    Next
End Function