跟踪图表中使用的单元格的依赖项

Question

如何使用VBA来确定图表中是否使用了特定的Excel单元格工作簿的其他位置？

我经常使用内置的Trace Dependents函数来查看更改/删除单元格的容易程度，但它只指示哪些单元格函数引用该单元格。

(Excel先生有一个相关的老问题没有回答(

Answer 1

据我所知，没有任何内置程序，您需要使用一些VBA代码
您需要了解Excel/VBA:中图表的对象模型

工作表包含一个DataSheetObjects集合
DataSheetObject是图表本身的容器，存储为属性Chart
图表包含一组数据系列。可用SeriesCollection访问可见系列，可用FullSeriesCollection访问所有(甚至隐藏(系列。

不幸的是，访问系列中使用的范围有点棘手。您必须使用属性Formula，它看起来像这样：=SERIES("MyName",Sheet1!$A$4:$A$7,Sheet1!$B$4:$B$7,1)。

让我们快速查看这些片段：它基本上包含4个片段，用逗号分隔：
(0(系列名称："MyName"(前缀为=SERIES(，但我们不在乎(
(1(保存x轴值的范围：Sheet1!$A$4:$A$7
(2(保存值的范围(Sheet1!$B$4:$B$7,1
((3(索引：1(加上关闭的)(

我们对这两个范围感兴趣。因此，我们要做的是编写一个函数，提取这些范围，并将其与您感兴趣的单元格进行比较。以下函数检查一个图表：

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

现在，您所要做的就是在所有工作表的所有图表上循环，或者换句话说，在所有工作单的所有图表对象上循环。

一个小补充：您可能在工作簿中将图表作为工作表。您可以使用工作簿的Charts集合来访问它们。为此，使用以下函数中的第二个循环。

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