Claiming Dependents
 

That is strange. If I enter =dep(A1) in cell H3, it returns $A$1, just
as you said. But if I run this macro

Sub dep3()
MsgBox dep(Range("A1"))
End Sub

then it returns $B$1,$H$3. Curiouser and curiouser. How can the same
function return two different results? Somebody smart is going to have
to figure this one out. James

Gary''s Student wrote:
In A1 I have 1
In B1 I have the formula =A1

When I run:

Sub dep2()
MsgBox (Range("A1").DirectDependents.Address)
End Sub

It correctly returns $B$1

The UDF, however

Function dep(r As Range) As String
dep = r.Dependents.Address
End Function

When used as
=dep(A1)
returns $A$1 Why?
--
Gary's Student

Claiming Dependents
 

I did a quick google search and came across this previous post with
exactly the same problem:

http://groups.google.co.uk/group/mic...1305fa10e75dff

Chip Pearson says it can't be done so its likely it can't be done.

James

Zone wrote:
That is strange. If I enter =dep(A1) in cell H3, it returns $A$1, just
as you said. But if I run this macro

Sub dep3()
MsgBox dep(Range("A1"))
End Sub

then it returns $B$1,$H$3. Curiouser and curiouser. How can the same
function return two different results? Somebody smart is going to have
to figure this one out. James

Gary''s Student wrote:
In A1 I have 1
In B1 I have the formula =A1

When I run:

Sub dep2()
MsgBox (Range("A1").DirectDependents.Address)
End Sub

It correctly returns $B$1

The UDF, however

Function dep(r As Range) As String
dep = r.Dependents.Address
End Function

When used as
=dep(A1)
returns $A$1 Why?
--
Gary's Student