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)
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