How to extract "destination address" from Hyperlink - VBA

[spwmarluk]

Hi,

I have following function:

Function HL_SheetName(komorka As Range) As String
If komorka.Hyperlinks.Count = 0 Then
Exit Function
End If
Dim poz As Integer
HL_SheetName = komorka.Hyperlinks(1).SubAddress
poz = InStr(1, HL_SheetName, "!")
poz = poz - 1
HL_SheetName = Mid(HL_SheetName, 1, poz)
poz = InStr(1, HL_SheetName, "'")
If poz = 1 Then
poz = Len(HL_SheetName)
poz = poz - 2
HL_SheetName = Mid(HL_SheetName, 2, poz)
End If
End Function

This function returns SubAddress from hyperlink ( actually name of the
Sheet ).
It works, but sometimes I get wrong sheet name, and hyperlink works
well.
For example:

Cell A1 have hyperlink to Sheet2!A1.
I enter the formula in B1: HL_SheetName(A1) , and I get in B1: Sheet2.

Cell A2 have hyperlink to Sheet3!A1.
I enter the formula in B2: HL_SheetName(A2) , and I get in B2: Sheet2.

WTF??
Both hyperlinks works fine.
I don't understand.

With the hyperlinks(1).TextToDisplay is the same situation...

Where exactly is the destination address in the structure of
hyperlink? ( SubAddress, Address , ..... ?? )

 

[Barb Reinhardt]

I'm guessing you're in 2007. I not aware of HL_SheetName functionality in
2003. Or is this a UDF?

Barb Reinhardt

 

[Barb Reinhardt]

Ignore the previous response.

 

[JP]

I duplicated your example, but was not able to duplicate the error.
The function exited immediately after hitting "If
komorka.Hyperlinks.Count = 0 Then " and a check of the 'komorka' shows
that it is equal to 100, not a range reference to A1.

--JP

 

[JP]

What I meant was "a check of the "komorka" variable shows that it is
equal to the value in cell A1, not a range reference to A1"

--JP

 

[spwmarluk]

See example: http://rapidshare.com/files/157022208/example1.zip.html

I copied a piece of my XLS file with VBA functions.

What is wrong with these hyperlinks??

 

[spwmarluk]

See example:http://rapidshare.com/files/157022208/example1.zip.html

I copied a piece of my XLS file with VBA functions.

What is wrong with these hyperlinks??

PS.
I have Excel 2002 SP3 (from MS Office XP PRO).

 

[JP]

OK, I think I figured out what I was doing wrong. I was creating cell
references, not hyperlinks, that's why the code kept exiting.

I created a workbook with three worksheets: Sheet1, Sheet2, Sheet3. I
placed your code in a standard module in that workbook. In Sheet1!A1 I
created a hyperlink to Sheet2!A1 (Insert>Hyperlink). In Sheet1!A2 I
created a hyperlink to Sheet3!A1. When I used your UDF, cells B1 and
B2 correctly displayed "Sheet2" and "Sheet3" respectively.

I duplicated your workbook and got the correct results. Not sure why
it doesn't work for you. I am using Excel 2003, not sure if that makes
a difference. I can send you a copy of the workbook if you'd like.


--JP

 

[spwmarluk]

Ok, I solved the problem.
In some cells I had more than one hyperlink.

Thanks.

 

[JP]

Glad to hear it!

--JP