Extract URL from hyperlink

[Erik]

I'm trying to extract the URL from a hyperlink in a cell. I've been
trying to use the following function:

Function HyperlinkAddress(Cell)
On Error Resume Next
HyperlinkAddress = Cell.Hyperlinks(1).Address
If HyperlinkAddress = 0 Then HyperlinkAddress = "**"
End Function

All it ever returns is **. Cell.Hyperlinks.Count also returns 1.

This works perfectly with Excel on Windows XP, but I can't get it to
work on the Mac with Excel from Office X. I've also tried a few other
methods, but can't seem to extract the URL. Is there something I'm
doing wrong or does this just not work on the Mac?

 

[JE McGimpsey]

I'm trying to extract the URL from a hyperlink in a cell. I've been
trying to use the following function:

Function HyperlinkAddress(Cell)
On Error Resume Next
HyperlinkAddress = Cell.Hyperlinks(1).Address
If HyperlinkAddress = 0 Then HyperlinkAddress = "**"
End Function

All it ever returns is **. Cell.Hyperlinks.Count also returns 1.

This works perfectly with Excel on Windows XP, but I can't get it to
work on the Mac with Excel from Office X. I've also tried a few other
methods, but can't seem to extract the URL. Is there something I'm
doing wrong or does this just not work on the Mac?

Couple of things:

1) As a matter of style, I'd recommend being consistent with your
variable types. Cell.Hyperlinks(1).Address returns a string. Comparing a
string to 0 doesn't make much sense. It *works* because HyperlinkAddress
is a Variant function.

I'd rewrite it as

Public Function HyperlinkAddress(Cell) As String
On Error Resume Next
HyperlinkAddress = Cell.Hyperlinks(1).Address
If HyperlinkAddress = "" Then HyperlinkAddress = "**"
End Function

Note that this is not the source of your problem.

2) The source of your problem is an XLv.X bug that doesn't return the
hyperlink's address. The only workaround I know of is to return the
..TextToDisplay property instead, though obviously that's not the same
thing.

The bug has been fixed in XL2004.