(Solved) – VBA Excel – Finding where all names are used in Workbook


I’m looking for some VBA code or reference that is able to locate all places in a workbook (or possibly outside, but that is not a normal use for me). I’m using Excel 2010 (for now).

I am recording the full list of names, their sources, formulas, and where they are used. I have the first several from the names class, but the “where they are used” method is time consuming, even when turning off Excel update actions.

I have seen multiple references to this code (below) in various sites but this is a time consuming method. (I reference certain names in large amounts, like the thousands).
Code below from: https://excelhelphq.com/how-to-find-all-dependent-cells-outside-of-worksheet-and-workbook-in-excel-vba/

Sub messageBoxCellDependents()
    Dim SelRange As Range
    Set SelRange = Selection
    MsgBox findDepend(SelRange) 'show user dependent cells in a pop up message box
End Sub

Function fullAddress(inCell As Range) As String
    fullAddress = inCell.Address(External:=True)
End Function

Function findDepend(ByVal inRange As Range) As String
    Dim sheetIdx As Integer
    sheetIdx = Sheets(inRange.Parent.Name).Index

    If sheetIdx = Worksheets.Count Then 'vba bug workaround
        Sheets(sheetIdx - 1).Activate
    End If

    Dim inAddress As String, returnSelection As Range
    Dim i As Long, pCount As Long, qCount As Long
    Set returnSelection = Selection
    inAddress = fullAddress(inRange)

    Application.ScreenUpdating = False
    With inRange
        .NavigateArrow False, 1
        Do Until fullAddress(ActiveCell) = inAddress
            pCount = pCount   1
            .NavigateArrow False, pCount
            If ActiveSheet.Name <> returnSelection.Parent.Name Then
                    qCount = qCount   1
                    .NavigateArrow False, pCount, qCount
                    findDepend = findDepend & fullAddress(Selection) & Chr(13)

                    On Error Resume Next
                    .NavigateArrow False, pCount, qCount   1
                Loop Until Err.Number <> 0
                .NavigateArrow False, pCount   1
                findDepend = findDepend & fullAddress(Selection) & Chr(13)

                .NavigateArrow False, pCount   1
            End If
    End With

    With returnSelection
    End With

    Sheets(sheetIdx).Activate 'activate original worksheet
End Function

However, when using the “Trace Dependents” feature in the Ribbon, the arrows and references produced take only as long as it takes for the arrows (when in sheet) to render on screen, which appears to me like there is a recorded list of where items are used (at least within a workbook).

Does this list actually exist? Is there a way to get to it? There should be a faster method, in theory.

Leave a Reply

Your email address will not be published. Required fields are marked *