Is there any tool to trace dependency of Excel cells and VBA code?

Question

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?

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