UDF to return full address include sheet name

[tony567]

Function MyUDF(r As Range)
    MsgBox r.Address
End Function

=MyUDF('Sheet3'!E3) the result is: msgbox $E$3

need to return full address include sheetname
what is the vba code to do that,


thank you

 

[mikerickson]

Use the ExternalReference argument of the .Address property.

MsgBox ActiveCell.Address(True,False,xlA1,True)

 

[tony567]

aha! thank you Mike, i think i understand what u mean...
now, how if we want to exclude the workbook name?
or is it always good to include the workbook name? i'll used it to referencing range in my UDF

Use the ExternalReference argument of the .Address property.

MsgBox ActiveCell.Address(True,False,xlA1,True)

 

[Sektor]

Function MyUDF(rng As Range) As String
    Application.Volatile
    If UBound(Split(rng.Parent.Name, " ")) > 0 Then
        MyUDF = "'" & rng.Parent.Name & "'!" & rng.Address(False, False, xlA1)
    Else
        MyUDF = rng.Parent.Name & "!" & rng.Address(False, False, xlA1)
    End If
End Function

 

[RoryA]

If you already have a range object, why do you want to use its address to refer to it in a udf?

 

[tony567]

thank you rorya, anyway would please give me an example,
i just confuse when i try to put range in my UDF which is located in another worksheet.

If you already have a range object, why do you want to use its address to refer to it in a udf?

 

[tony567]

just simple example UDF which give unexpected result when the range (r) located in another worksheet,
but the result is OK when the range (r) located on the same worksheet.

Function MyUDF(r As Range)
    MsgBox Evaluate("Sum(" & r.Address & ")")
End Function

If you already have a range object, why do you want to use its address to refer to it in a udf?

 

[RoryA]

Function MyUDF(r As Range)
    MsgBox r.worksheet.Evaluate("Sum(" & r.Address & ")")
End Function

For example.

 

[tony567]

100%!

thank you so much rorya...

Function MyUDF(r As Range)
    MsgBox r.worksheet.Evaluate("Sum(" & r.Address & ")")
End Function

For example.