VBA function to get the address of a range

Hello all,

I am writing a user-defined function "GetAddress".

This function takes only one argument as input, a range in a worksheet, then it returns a String - the address of that range.

For example, I would like

GetAddress(B1:B10) to return "B1:B10"

GetAddress($B$1:$B$10) to return "$B$1:$B$10"

GetAddress(B1:$B$10) to return B1:$B$10

GetAddress(sheet2!B1:$B$10) to return "sheet2!B1:$B$10"

It seems that range.address returns always absolute reference... Could anyone tell me which function i should use in VBA to do so?

Thanks and regards

Tie

Re: VBA function to get the address of a range

Dear Tie.

The issue as expressed above is not clear. The result of the function that you are seeking is exactly its argument !

Anyway, the function range.address may return relative references when used with the optional arguments (parameters) RowAbsolute:=False or/and ColumnAbsolute:=False, ...

Regards

Imed.

Thanks for your comment,

Thanks for your comment, Imed.

The argument and the result of the function look same, but they are different types: one is Range, the other is String.

It seems that if we pass a value argument as Range, we can not know if it is absolute or relative...

There is another smart solution:

st = application.activecell.formula

Then we can adjust st to get the address...

Cheers

Tie

Re: Thanks for your comment,

Dear Tie;

Could you explain how to extract the  .address property from the .formula property?

Regards.

Imed 

String

It's usable as a string.

Public Function GetAdress(ByRef zelinfo As Range) As String
st = Application.ActiveCell.Formula
begin = Len("GetAdress") + 3
lengte = (Len(st) - begin)
GetAdress = Mid(st, begin, lengte)
End Function

JPH's picture

forgot to log in

Sry i forgot to log in

The st in this case is usable as a string and can be "cropped" accordingly.

Public Function GetAdress(ByRef zelinfo As Range) As String
    st = Application.ActiveCell.Formula
    begin = Len("GetAdress") + 3
    lengte = (Len(st) - begin)
    GetAdress = Mid(st, begin, lengte)
End Function