Is there any tool to trace dependency of Excel cel

问题:

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

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.

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?

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?

回答1:

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?

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

回答2:

I don't know if the following answers your question - but I thought it was helpful.

The Range object has a Precedents and Dependents property. This allows you, from within VBA, to do what the menu Tools->auditing does in the UI. (I have to acknowledge @Bathsheba's answer to this recent question https://stackoverflow.com/a/20635795/1967396 for teaching me this…).

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

Note - you do need the Set ; otherwise you end up with the values of the precedents, rather than references to the objects (in which case you could not see the address…)

You can do the same thing with .Dependents, obviously:

This does not answer the "if there is a line of VBA code that creates a relationship" question… which is especially tricky since you can have cells modified by variable and unpredictable offsets, e.g.

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

Which cell depends on what will depend on the date you run this script…