Checking cell for Dependents

[ExcelMonkey]

Thank-you. However I am not sure how to use this? It does not seem a
though this returns a boolean. When I try to call it below I get a
error stmt saying "Argument not optional" inplying that I have not pu
in an argument. Yet you only have one argument. What am I doin
wrong????

Sub Thing()
Dim Cell As Range
Dim HasIntDep As Boolean
Dim ExtIntDep As Boolean
Dim CountDep As Integer
Dim X As Double

Set Cell = Range("StartCell")
ExtIntDep = ExternalDependents(Cell)

End Sub

Function ExternalDependents(SrcRange As Range) As Collection

Dim DstRange As Range, Externals As New Collection, n&

If TypeOf Application.Caller Is Range Then GoTo theExit
If SrcRange.Cells.Count > 1 Then GoTo theExit

On Error Resume Next
Application.ScreenUpdating = False

With SrcRange
If Not .DirectDependents Is Nothing Then
.ShowDependents True
.ShowDependents False
'Escape if there are too many...
Set DstRange = .NavigateArrow(False, 1, 1025)
If Err = 0 Then
Externals.Add CVErr(xlErrValue)
GoTo theExit
End If

n = 1

Do
Set DstRange = .NavigateArrow(False, 1, n)
If Err <> 0 Then Exit Do
Externals.Add DstRange
n = n + 1
Loop While n <= 1024
End If
End With
Stop

theExit:
Application.ScreenUpdating = True
Set ExternalDependents = Externals
End Functio

 

[keepITcool]

NOTE: when replying from ExcelForum/ExcelTip be aware that other users
relying on NNTP will not be informed of your replies!

The webmaster of ExcelTip/ExcelForum has been repeatedly informed that he
is violating NNTP RTF specifications by not including the reference field
in the header of messages posted as Replies by users of ExcelTip/
ExcelForum.



hmm..

first note the syntax of the function in your quoted post IS NOT CORRECT.
(several dots inside the with/end with construct are missing.
(please check and compare to my original post)

Second note that my function returns a collection

Third and most important.. the function is insufficently tested and may
return erroneous results.

I'll sleep now, and give you a more complete answer with edits tomorrow.
(and i'll try to find the otherm messages in the thread)

Please answer if you just require only a BOOLEAN test IF a cel has
external references. Also note this arrow tracing is painfully slow if the
count goes up. to trace ONE cell which has 60000 dependents may take up to
a minute.