是否有任何工具可以跟踪Excel单元格和VBA代码的依赖关系

Question

Excel 具有跟踪单元格precedents和dependents的功能（通过菜单 Tools > Auditing ）。例如，如果我们在 C4 中= C3 + 1，那么 Excel 可以跟踪C4是C3的依赖项（C3是 C4 的先例）。

由于VBA代码

也会对Excel单元格产生影响，因此我想知道是否有任何工具可以跟踪VBA代码和Excel单元格之间的依赖关系。

例如，如果有一行VBA代码Range("C4").Value = Range("C3").Value + 1，是否有工具可以跟踪C4可能是C3的依赖者？

另外，如果有一行 VBA 代码Range("C4").Value = 5，是否有工具可以跟踪这段代码可能修改C4？

Answer 1

VBA 所做的更改不容易跟踪（因为单元格可能由函数以任意方式修改或作为字符串传递给函数）。

你可以做一些事情，但它有点棘手，我想它不会真正满足你：你可以检测一个细胞是否有任何依赖性。

它是如何工作的？编写如下宏：

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
End Sub

您可以将更改的单元格记录到即时窗口。现在更改单元格值，在即时窗口中，您将看到依赖于您更改的单元格的所有单元格。弊？

• 不幸的是，反向过程是不可能的：您无法选择一个单元格并查看其所有依赖项。
• 该过程不能（真正）自动化，因为从代码中通常您不知道单元格的有效值（您可能会按单元格类型猜测，但由于规则，您无法确定，例如，"0"可能是有效值，"1"否）。
• 它无法真正检测每个依赖项（如果两个给定值不会在单元格中产生更改，那么您将不会看到该依赖项）。
• 对于大型工作表，它可能非常乏味，因此仅当应用于一小组单元格时才指示它（如果我更改/移动此单元格怎么办？会影响什么吗？

Answer 2

我不知道以下内容是否回答了您的问题 - 但我认为这很有帮助。

Range对象具有 Precedents 和 Dependents 属性。这允许您从 VBA 中执行菜单Tools->auditing在 UI 中执行的操作。（我必须感谢@Bathsheba对最近这个问题的回答 https://stackoverflow.com/a/20635795/1967396 教我这个......

Dim prec, p
Set prec = [C4].Precedents  ' [C4] is shorthand for Range("C4")
For Each p in prec
  Debug.Print "Precedent found: " & p.Address
next p

注意 - 你确实需要Set;否则你最终会得到先例的值，而不是对对象的引用（在这种情况下，你看不到地址......

显然，您可以对.Dependents做同样的事情：

这并不能回答"如果有一行 VBA 代码创建关系"的问题......这尤其棘手，因为您可以通过可变和不可预测的偏移量修改单元格，例如

For j = 1 To n
  [B5].Offset(j+3, 2+k) = [A2].Offset(Int(DateValue(Now)) Mod 3, 1)
Next j

哪个单元格取决于什么将取决于运行此脚本的日期...

Answer 3

也许这对你有用。我编写了一个宏，以便您可以选择一个范围并显示该范围内每个单元格的优先级或依赖关系。

Sub tracerange()
On Error Resume Next
    Dim R As Range
    Dim DorP As Variant
    R = ActiveWindow.RangeSelection.Address
    DorP = LCase(Application.InputBox("Please input Precedents or Dependece (P or D):", , , , , , , Type:=2))
        For Each R In Selection
            If DorP = "p" Then
                R.ShowPrecedents
            Else
                R.ShowDependents
            End If
            DoEvents
        Next
End Sub