What is VBA equivalent to Tools/Auditing/Trace Dependents?

[baobob]

I just need to know whether a cell has dependents, yea or nay; I don't
need to identify them.

Thanks.

***

 

[Peter T]

Function CellHasPrecs(cell As Range) As Boolean
Dim rPrecs As Range

On Error Resume Next
Set rPrecs = Nothing
Set rPrecs = cell.Precedents

CellHasPrecs = Not rPrecs Is Nothing
End Function

Sub test()
Dim bHasPrecs As Boolean
Dim r As Range
Set r = Range("d5")

bHasPrecs = CellHasPrecs(r)

MsgBox bHasPrecs
End Sub

Regards,
Peter T

 

[Peter T]

I always forget both cell.Precedents and also cell.DirectPrecedents only
return precedents on the same sheet as 'cell'. This might be a bit better
but note the caveat in the comments, could further parse the formula to
decrease the possibility of returning a false positive.

Function CellHasPrecs(cell As Range) As Boolean
Dim rDirPrecs As Range

If cell.HasFormula Then

On Error Resume Next
Set rDirPrecs = cell.DirectPrecedents
On Error GoTo 0
If Not rDirPrecs Is Nothing Then
CellHasPrecs = True
ElseIf InStr(cell.Formula, "!") > 0 Then
' formula contains an ! strongly indicates a ref to
' to another sheet but not 100% conclusive
CellHasPrecs = True
End If
End If

End Function

Regards,
Peter T

 

[Charles Williams]

You can either use the .Dependents property, but as Peter said, this only
works with references within the sheet,
or you can use .ShowDependents followed by .NavigateArrow, which will
include off-sheet dependents.

warning: .showDependents and .NavigateArrow are a bit slow, if performance
is an issue look at only doing it once for similar blocks of formulae (use
R1C1 mode to find similar formulae).

regards
Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm