Return the hyperlink
To get the hyperlink of a cell you can use a simple UDF, like this:
Function RETURNHYPERLINK(Rng As Range) As String
If Rng.Hyperlinks.Count> 0 Then
RETURNHYPERLINK = Rng.Hyperlinks(1).Address
If Len(RETURNHYPERLINK) = 0 Then
RETURNHYPERLINK = Rng.Hyperlinks(1).SubAddress
End If
End If
End Function
If Rng.Hyperlinks.Count> 0 Then
RETURNHYPERLINK = Rng.Hyperlinks(1).Address
If Len(RETURNHYPERLINK) = 0 Then
RETURNHYPERLINK = Rng.Hyperlinks(1).SubAddress
End If
End If
End Function
You can then use it in your worksheet like
=RETURNHYPERLINK(A13)
I check for the Address and the SubAddress properties because a hyperlink to a "place in this document" for example is not stored in the Address property (which appears empty), but in the SubAddress.
Zabzoo:
Hi There
The utility seems usefull but maybe lacks information from your side or experience from my side.
I copied the "Code" to the Macroeditor, but in Excel putting =RETURNHYPERLINK(D7) returns with #Name
I did put a friendly_name with a hyperlink in that cel!
Thanks Anyway,
6 September 2005, 4:00 amzab
Juan Pablo González:
Zabzoo,
The code needs to go in a standard module, in the workbook where you want to use the formula. Look at this for more info:
http://www.danielklann.com/excel/where_do_i_place_code_in_excel.htm
http://www.cpearson.com/excel/codemods.htm
6 September 2005, 10:23 am