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/micro...ba3a450db971a2

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.]

***