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 Else Sheets(Worksheets.Count).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 .ShowPrecedents .ShowDependents .NavigateArrow False, 1 Do Until fullAddress(ActiveCell) = inAddress pCount = pCount 1 .NavigateArrow False, pCount If ActiveSheet.Name <> returnSelection.Parent.Name Then Do 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 Else findDepend = findDepend & fullAddress(Selection) & Chr(13) .NavigateArrow False, pCount 1 End If Loop .Parent.ClearArrows End With With returnSelection .Parent.Activate .Select 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.