Here is a macro I wrote years ago, which will find all dependents of a
selected cell and list them on a new sheet which is added to the workbook.
Sub FindDependents()
'Declare local variables
Dim xx As Long, xxErr As Boolean, SelCell As Range
Dim StartWS As Worksheet, c As Range, HitCount As Long, NuSht As Worksheet
On Error GoTo FDerr1
'Turn off screen updating and change cursor to hourglass
Application.ScreenUpdating = False
Application.Cursor = xlWait
'Store the starting activesheet and activecell.
Set StartWS = ActiveSheet
Set SelCell = ActiveCell
'Add a new worksheet to the current workbook at the end.
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
Set NuSht = ActiveSheet
'Use HitCount& to set the output row. Will add 1 each time, so set to 3
initially
'to begin output on row 4.
HitCount& = 3
'Set xxErr to FALSE, and return to the starting sheet & cell.
xxErr = False
StartWS.Activate
SelCell.Activate
'DirectDependents only gives dependents on same sheet as SelCell.
For Each c In SelCell.DirectDependents
If xxErr = False Then
HitCount& = HitCount& + 1
NuSht.Cells(HitCount&, 1).Value = "'" & c.Parent.Name
NuSht.Cells(HitCount&, 2).Value = "'" & c.Address
NuSht.Cells(HitCount&, 3).Value = "'" & c.Formula
End If
Next c
'Activate SelCell again, then use ShowDependents method to draw arrows.
SelCell.Activate
ActiveCell.ShowDependents
xx& = 1
'Set xxErr to FALSE again. If there were no DirectDependents, error handling
set xxErr to TRUE.
xxErr = False
'Built-in arbitrary limit of 100,000 (references in this one workbook) to
this one
'cell! Did it this way because Excel doesn't provide a programmatic way to
determine
'the total number of references.
Do While (xx& < 100000)
'Always start by returning to StartWS and SelCell.
StartWS.Activate
SelCell.Activate
'Go to each external dependent reference in turn
ActiveCell.NavigateArrow False, 1, xx&
'If ActiveCell.NavigateArrow fails (xx& higher than number of links), xxErr
will get set
'to True and will break out of Do While loop.
If xxErr = True Then Exit Do
'If activecell is the starting cell, there are no dependents, so stop.
If (ActiveSheet.Name = StartWS.Name) And (ActiveCell.Address =
SelCell.Address) Then Exit Do
HitCount& = HitCount& + 1
NuSht.Cells(HitCount&, 1).Value = "'" & ActiveSheet.Name
NuSht.Cells(HitCount&, 2).Value = "'" & ActiveCell.Address
NuSht.Cells(HitCount&, 3).Value = "'" & ActiveCell.Formula
xx& = xx& + 1
If xx& = 100000 Then
MsgBox "Hit limit of 100,000 dependent references",
vbInformation, "FindDependents macro"
End If
Loop
'Done. Clean up. Add headings for the output rows and resize all columns on
NuSht.
NuSht.Cells(3, 1).Value = "Sheet"
NuSht.Cells(3, 2).Value = "Cell"
NuSht.Cells(3, 3).Value = "Formula"
NuSht.Cells.Select
NuSht.Cells.EntireColumn.AutoFit
Calculate
'Add a heading on row 1
NuSht.Cells(1, 1).Value = "Dependent cells for: Sheet [" & StartWS.Name
& "], Cell [" & SelCell.AddressLocal & "]"
'Go back to the starting sheet and remove the arrows, then go to the new
sheet.
StartWS.Activate
ActiveSheet.ClearArrows
NuSht.Activate
'Turn on screen updating and restore the cursor to default
Application.ScreenUpdating = True
Application.Cursor = xlDefault
'Free object variables.
Set NuSht = Nothing
Set StartWS = Nothing
Set SelCell = Nothing
Set c = Nothing
FDerr1:
xxErr = True
Resume Next
End Sub
When you copy & paste the code above into a VBA module, you will get errors
because some of the lines wrapped in the forum because of their length. Afger
you "unwrap" those lines the macro should run.
Hope it helps,
Hutch
davegb said:
I'm working with a team reverse engineering a rather complex workbook.
The author is helping, but is available on a very limited basis. I've
mostly been using the Audit features to trace precedents and
dependents and manually drawing diagrams showing these relationships,
which is cumbersome and slow. Does anyone have any good suggestions
beyond what I'm already doing on how to expedite this process? Any
tracing macros that will ID all downstream dependents cells of a given
cell? Any clever techniques that would make this easier? I feel like
I'm sawing down a redwood with a Swiss Army knife!
Thanks!- Hide quoted text -
- Show quoted text -
