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:
- 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.
- 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”).
- 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.
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