How to Find All Dependent Cells Outside of Worksheet and Workbook in Excel VBA?

Solution

The VBA macro code finds all the dependent cells of the active cell (selected cell) and displays their full address in a message box. This macro shows all dependent cells including ones that are outside of current worksheet and workbook. The key to the solution is to use the method “Range.Navigatearrow” to go through dependent cells. (The function is also capable of going through precedents, but that topic is for another day.)

Entire code is divided into 3 parts:

1. Main Sub Procedure (the macro you need to run): Sub procedure ‘messageBoxCellDependents’ takes selected cell as input and calls the ‘findDepend’ function and displays what is returned by findDepend function.
2. Function – fullAddress (called in the main sub procedure): Function ‘fullAddress’ returns the full address of the given cell along with workbook & sheet name. Eg: [Book1]Sheet1!$A$1 is the full address of Range(“A1”).
3. Function – findDepend (called in the main sub procedure and where all the action takes place): Function ‘findDepend’ returns addresses all the dependents cells including cells outside the active workbook as a string. Key components of findDepend:
   • Range.Navigatearrow property can be used to navigate to dependents/precedents of the range based on the value of first argument value. If the first argument is ‘False’ (see the below code) then it navigates towards dependent cells.
   • pCount variable is used to refer to cells within current worksheet.
   • qCount variable is used to refer to cells outside the current worksheet/workbook.

Known Bugs

Excel 2010: In certain versions of Excel, the Navigatearrow method will have trouble navigating through all dependent cells if there are cells outside of the current workbook or worksheet. The only solution is to make VBA code turn on another worksheet before proceeding to cycle through the dependent cells. At this point, we are not sure why this bug appears. Some installations of Excel 2010 do not have this issue at all.

VBA Macro Code

This is a proof of concept plugin to show that the function findDepend works. For actual VBA code, you may need to edit the code to get the function to return a list of cells, instead of showing cell addresses in a pop up box.

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