Finding external cell dependents works in a Sub() but not a UDF.

[baobob]

In Excel 2002 SP 3, I need a UDF that displays a concatenated string
of the texts of the external dependents of a given cell.

So if say Sheet1!A1 has 3 external dependents--Sheet2!A1 (text “cat”),
Sheet2!A2 (“dog”) and Sheet3!A1 (“bird”), then formula “=MyUDF(Sheet1!
A1)” should return “catdogbird”.

But I find no posts using .NavigateArrows in a Function(). All
examples are Sub()’s. True to that, this code:

Do
Set D = .NavigateArrow(False, 1, i)
If Not (D.Worksheet Is SourceRange.Worksheet) Then
Result = Result & D.Text
End If
i = i + 1
Loop Until <Whatever>

works perfectly for me (cycles thru all externals) in a Sub(), but
fails in a Function().

Can it therefore not be done in a UDF?

If not, do any of you Knights of the Web have a workaround?

Thanks very much.

***

[P.S. The first post on:

http://groups.google.com/group/micr...=gst&q="external+dependents"#cdba3a450db971a2

intriguingly is in a Function(). But it self-defeats because its first
line:

If TypeOf Application.Caller Is Range Then GoTo theExit

is always true (at least for me), hence his code never executes. And
anyway, he uses the same .NavigateArrows logic as above, and if you
comment out the above line it fails as expected.]

***

 

[AB]

I didn't use the NavigateArrows method (I just don't know what it
does) but this works for me:

Public Function ConcatenateDependantCellsTexts(TheMasterCell As Range)
As String
Dim v As Range

For Each v In TheMasterCell.Dependents
ConcatenateDependantCellsTexts =
ConcatenateDependantCellsTexts & v.Text
Next v

End Function


But!! - keep in mind that you can't really use it in a cell as a
function because it would cause a circular reference - i.e., just by
entering this function you'd increase the MasterCell's dependatns by 1
(i.e, the cell that you enter the function into will also become a
dependent of the mastercell.

Also, as far as i know the only real difference between sub and
function is that a function returns a value whereas sub doesn't.

 

[baobob]

AB:

Thanks much for reply.

***