I spend a good deal of time building and auditing spreadsheets in my position as an analyst in a Finance department. One of the features that has always frustrated me about Excel is the program's ability to audit the spreadsheet. Specifically, the need to double-click on the auditing arrows to move to precedent or dependent cells is, in my opinion, very difficult and inefficient.

Aaron Blood's Explode Add-In converts all of the precedent or dependent cells into cell addresses that appear in a listbox. You can then click on the listbox addresses to move to each cell address in the spreadsheet. The Explode Add-In is a great idea. Unfortunately, the code is protected so I couldn't customize it to my needs.

In this and future posts, I'm going to attempt to create my own formula auditing tool. Like the Explode Add-In, I want to be able to click on formula references to move between precedent and dependent cells. My other qualifications are that the add-in needs to be simple, efficient, and easy to use.

The first challenge to building the Formula Trace Tool add-in is to come up with the code that converts the precedent and dependent cell references to cell addresses. That code is below.

Sub TracePrecedents()

    On Error Resume Next

    Dim Rng As Range
    Dim Adrs As String
    Dim UniqueValues As New Collection
    Dim iArrow As Integer

    Set Origin = ActiveCell

    Origin.ShowPrecedents

    For iArrow = 1 To 1000
        
        Err = 0
        
        Set oPrec = Origin.NavigateArrow(True, iArrow)
        Adrs = oPrec.Parent.Name & " - " & oPrec.Address
        UniqueValues.Add Adrs, CStr(Adrs)
        
        If Err <> 0 Then Exit For

    Next

    Set Rng = ActiveSheet.Range("E3")

    For Each Item In UniqueValues
        Rng.Value = Item
        Set Rng = Rng.Offset(1, 0)
    Next Item

End Sub

To view the output of the procedure, set the active cell to a cell with precedents and run the macro. In the screenshot below, the active cell is C22 and the precedent cells are indicated by the blue auditing references. Each precedent cell is listed in the range E3:E9.

Alternatively, get rid of the range references and work in the immediate window. The resulting code is below.

Sub TracePrecedents()

    On Error Resume Next

    Dim Rng As Range
    Dim Adrs As String
    Dim UniqueValues As New Collection
    Dim iArrow As Integer

    Set Origin = ActiveCell

    Origin.ShowPrecedents

    For iArrow = 1 To 1000
        
        Err = 0
        
        Set oPrec = Origin.NavigateArrow(True, iArrow)
        Adrs = oPrec.Parent.Name & " - " & oPrec.Address
        UniqueValues.Add Adrs, CStr(Adrs)
        
        If Err <> 0 Then Exit For

    Next

    For Each Item In UniqueValues
        Debug.Print Item
    Next Item

End Sub

As a sidenote, Microsoft provides a list of error codes as well as selected error handling procedures.

The next step is to try to replace the "Debug.Print Item" statement with some type of listbox load. I'll attempt to address that in the next post of this series.