是否有任何工具来追踪Excel单元格和VBA代码的依赖关系？ [英] Is there any tool to trace dependency of Excel cells and VBA code?

问题描述

Excel具有跟踪单元格的先例和依赖关系的功能（通过菜单工具>审核）。例如，如果我们在 C4 中有 = C3 + 1 ，那么Excel可以跟踪 C4 是依赖于 C3 （ C3 是 C4 ）。

Excel has a functionality to trace the precedents and dependents of a cell (via menu Tools > Auditing). For instance, if we have = C3 + 1 in C4, then Excel can trace that C4 is a dependent of C3 (C3is a precedent of C4).

由于VBA代码也对Excel单元格有影响，我想知道是否有任何工具来跟踪依赖关系在VBA代码和Excel单元格之间。

As VBA code also has impact to Excel cells, I would like to know if there is any tool to trace the dependency between VBA code and Excel cells.

例如，如果有一行VBA代码 Range（C4）。Value = Range C3）。值+ 1 ，是否有一个工具来追踪 C4 可能是依赖于 C3 ？

For example, if there is a line of VBA code Range("C4").Value = Range("C3").Value + 1, is there a tool to trace that C4 might be a dependent of C3?

另外，如果有一行VBA代码 Range（C4）。Value = 5 ，是否有一个工具来追踪 C4 可能会被这段代码修改？

Also, if there is a line of VBA code Range("C4").Value = 5, is there a tool to trace that C4 might be modified by this piece of code?

推荐答案

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

Changes made by VBA cannot be tracked easily (because cells may be modified in arbitrary way by a function or passed to a function as string).

有一些你可以做的，但它有点棘手，我想这不会真的满足你：你可以检测一个单元格是否有依赖。

There is something you can do but it's a little bit tricky and I guess it won't really satisfy you: you can detect if a cell has any dependency.

它是如何工作的？写一个这样的宏：

How it works? Write a macro like this:

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

您可以立即登录变更的单元格。现在更改单元格值，在立即窗口中，您将看到所有依赖于您更改的单元格的单元格。缺点？

You may log to the immediate window the changed cells. Now change a cell value, in the immediate window you'll see all cells that depend on the cell you changed. Disadvantages?

• 不幸的是，逆向过程是不可能的：您不能选择单元格并查看其所有依赖项。


• 该过程不能（真正）自动化，因为从代码通常您不知道单元格的有效值（您可能会通过单元格类型猜测，但是由于规则无法确定，0可能是一个有效的值，例如1否）。


• 它不能真正地检测每个依赖（如果两个给定的值不会在单元格中产生变化，那么你赢得了


• 对于大型工作表，它可能非常乏味，所以只有在应用于一小部分单元格时才会显示（如果我更改/移动这个单元格会怎么样？会影响任何东西？）

• Unfortunately the inverse process is not possible: you can't select a cell and see all its dependencies.
• The process cannot be (really) automated because from code usually you don't know valid values for cells (you may guess by cell type but you can't be sure because of rules, "0" may be a valid value and "1" no, for example).
• It can't really detect every dependency (if two given values won't produce a change in a cell then you won't see that dependency).
• For large worksheets it can be pretty tedious so it's indicated only if applied to a small set of cells (what if I change/move this cell? Will it affect anything?)

这篇关于是否有任何工具来追踪Excel单元格和VBA代码的依赖关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！