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