Asked by:
VB: Hyperlink UDF 'automatic' separate address from friendly name
Question

UPDATE: pardon not gotten back yet. running into a few things (irreg hours, eom, running a bit slow), catching up.. thanks. note2: running really slow, still getting back to this, will need a new day to review problem. think reply might include: still not sure how to apply: example has VB variables? instead of what i should type? pardon, am still pretty slow at vb/ i don't get.. thanks..

hi, i have been running into some problems updating code to reflect the friendly names in hyperlinks, as they change.
is there a more automated way to include into a udf the separate parts for those two items? (for the hyperlink and the friendly name) thanks.
the example UDF i have for follow a hyperlink is below.
Sub goFHL(strF As String, strDL As String) '255 chars AS: goFHL ActiveCell.Formula, ",friendlyname"
'split(x, y) syntax what, strDL what: x
Dim v As Variant 'follow HYPERLINK AS: (left of friendly name): goFHL ActiveCell.Formula, ",IF("
'enter quotes as string ANS: either double up on quotes:
'goFHL ActiveCell.Formula, ",""SAM""" 'for: ,"SAM" OR USE: & CHR(34) &
'goFHL ActiveCell.Formula, "," & CHR(34) & "SAM" & CHR(34)
'goFHL ActiveCell.Formula, ",IF("
'goFHL ActiveCell.Formula, ",$"
'Dim J6 As String: J6 = RANGE("J6")
'goFHL Cells(activecell.row, J6).OFFSET(, 7).Formula, ",$" 'note: J6 workcell shows column eg: BL:BL
If InStr(1, strF, "HYPERLINK") = 0 Or InStr(1, strF, strDL) = 0 Then
application.EnableEvents = True: Exit Sub 'EVENTS
End If
v = split(strF, strDL) 'split/rem? strDL from right?
On Error Resume Next
ActiveWorkbook.FollowHyperlink application.Evaluate(Replace(v(0), "HYPERLINK(", "")) 'replace quotes with nothing?
'get plain eg: no friendly name
application.EnableEvents = True 'EVENTS
'work cell J6 has:
'=SUBSTITUTE(SUBSTITUTE(CELL("address",$BL6),"$",""),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address",$BL6),"$",""),ROW(),"")
'gofhl() GOFHL()
End Sub
 Edited by Davexx Monday, July 24, 2017 10:42 AM
All replies

hi, i have been running into some problems updating code to reflect the friendly names in hyperlinks, as they change.
Well, I don't know how you call that sub and what you have in your cells, resp. what you pass as argument to that sub...
So your question is quite strange...
Anyway, a cell can have a hyperlink directly or can have a HYPERLINK formula, like
=IF(A1="G",HYPERLINK("http://www.google.de","Google"),HYPERLINK("http://www.duckduckgo.com","My Favorite"))
So I would call a link from such a cell in a different way.
Andreas.Sub CallHyperlink(ByVal FromCell As Range, Optional ByVal Nth As Integer = 1) Const HypStart = "HYPERLINK(""" Const HypEnd = """," Dim i As Long, j As Long Dim Link As String With FromCell If .Hyperlinks.Count > 0 Then 'Get the Nth link directly Link = .Hyperlinks(Nth).Address Else 'We parse the Nth link from the formula i = 0 Do i = InStr(i + 1, .Formula, HypStart, vbTextCompare) If i = 0 Then Exit Sub j = InStr(i + 1, .Formula, HypEnd, vbTextCompare) If j = 0 Then Exit Sub Nth = Nth  1 Loop Until Nth <= 0 i = i + Len(HypStart) Link = Mid$(.Formula, i, j  i) End If End With ThisWorkbook.FollowHyperlink Link End Sub

hi, thanks for the reply. pardon my missing anything. i am slow at vb, even if spend many hours at it just trying to fix small stuff.
i tried a couple of variations on how to call your example, but could not get it to work. what is the example for calling it? thanks.
i tried:
'CallHyperlink (selection) 'na dead
'selection.CallHyperlink 'na dead
'activecell.CallHyperlink 'na dead
'activecell CallHyperlink 'err: argument not optional
'CallHyperlink.selection 'err: argument not optional
'CallHyperlink (activecell) 'na dead
'CallHyperlink 'err: argument not optional
for an example of my hyperlinks, I have been using single "Hyperlink" references in a single cell. that is: =HYPERLINK( etc once, but i have been using work cells to hold pieces of the strings to the address for eg: hyperlink($a$2&B9&$a$3,if($a1="x","A","B") A B for friendly name.
an actual example is:
=HYPERLINK(IF(OR($Y$7>0,CA1487="x"),$BM$2&A1487&"%22",$BM$5&BG1487&$BM$6),IF(CA1487="x","G",$Y$8))
it would of been nice of me to inlcude that in the original post. many pardons.
the call as for my vb example would be: 'goFHL ActiveCell.Formula, ",IF("
the friendly name designation may change and the link wouldn't open for: ",IF("

=HYPERLINK(IF(OR($Y$7>0,CA1487="x"),$BM$2&A1487&"%22",$BM$5&BG1487&$BM$6),IF(CA1487="x","G",$Y$8))
Okay, that is much more complex, you have to parse the formula by yourself.
I'll make a simple sample (for followers to reproduce), let us assume we have this values, resp. formulas in the cells:
A1: http://www
A2: duckduckgo
A3: google
A4: de
A5: com
A7: =HYPERLINK("http://www.google.de","Simple")
B7: =HYPERLINK(A1&"."&IF(LEFT(C1,1)="G",A3,A2)&"."&A4,IF(A4="de","German",IF(A4="com","English","?")))
C7: =IF(C1="",HYPERLINK("http://www.google.de","Google"),HYPERLINK("http://www.duckduckgo.de","Duck"))
The 1st one is simple, the 2nd complex and for the 3rd we have to parse the friendly name and compare it with the value of the cell.
The code below fails if the friendly name itself contains one of this chars: (),
We can prevent that, but that is much more complex.
Andreas.
Option Explicit #Const Develop = True Sub Test() Dim Link As String, FName As String Dim R As Range Dim i As Long For Each R In Range("A7:C7") For i = 1 To CountHyperlinks(R) Link = GetHyperlink(R, FName, i) 'Friendly name match with the cell? If FName = R.Value Then 'Found #If Develop Then Debug.Print R.Address(0, 0); " "; Link, FName #Else ThisWorkbook.FollowHyperlink Link #End If Exit For End If Next Next End Sub Function GetHyperlink(ByVal FromCell As Range, _
Optional ByRef FriendlyName, Optional ByVal Nth As Integer = 1) As String
'Returns the Nth hyperlink from a cell which can have a real hyperlink or a complex HYPERLINK formula
Const HypStart = "HYPERLINK("
Dim i As Long, j As Long, b As Long
With FromCell
If .Hyperlinks.Count > 0 Then
'Get the Nth GetHyperlink directly
With .Hyperlinks(Nth)
GetHyperlink = .Address
FriendlyName = .TextToDisplay
End With
Else
'We parse the Nth link from the formula
Do
i = InStr(i + 1, .Formula, HypStart, vbTextCompare)
If i = 0 Then Exit Function
Nth = Nth  1
Loop Until Nth <= 0
'Calculate the start position
i = i + Len(HypStart)
'Find the end of the link
For j = i To Len(.Formula)
Select Case Mid$(.Formula, j, 1)
Case "(": b = b + 1
Case ")": b = b  1
Case ",": If b = 0 Then Exit For
End Select
Next
'Get the part that builds the hyperlink, this might be a formula
GetHyperlink = Mid$(.Formula, i, j  i + b)
'Create a hyperlink formula and let Excel evaluate the link
GetHyperlink = Application.Evaluate("=" & HypStart & GetHyperlink & ")")
'What's this? In some cases Evaluate returns a special ? in front, remove it:
If GetHyperlink <> "" Then
If AscW(Left$(GetHyperlink, 1)) = 257 Then GetHyperlink = Mid$(GetHyperlink, 2)
End If
If IsMissing(FriendlyName) Then Exit Function
i = j + 1
'Find the end of the link
For j = i To Len(.Formula)
Select Case Mid$(.Formula, j, 1)
Case "(": b = b + 1
Case ")"
b = b  1
If b < 0 Then Exit For
End Select
Next
'Get the part that builds the friendly name, this might be a formula
FriendlyName = Mid$(.Formula, i, j  i)
If FriendlyName = "" Then Exit Function
'Create a formula and let Excel evaluate the friendly name
FriendlyName = Application.Evaluate("=" & FriendlyName)
'Same as above
If FriendlyName = "" Then Exit Function
If AscW(Left$(FriendlyName, 1)) = 257 Then FriendlyName = Mid$(FriendlyName, 2)
End If
End With
End FunctionFunction CountHyperlinks(ByVal FromCell As Range) As Long 'Returns the number of hyperlinks in a cell which can have a real hyperlink or a complex HYPERLINK formula Const HypStart = "HYPERLINK(" Dim i As Long With FromCell If .Hyperlinks.Count > 0 Then CountHyperlinks = .Hyperlinks.Count Else Do i = InStr(i + 1, .Formula, HypStart, vbTextCompare) If i = 0 Then Exit Function CountHyperlinks = CountHyperlinks + 1 Loop End If End With End Function
 Edited by Andreas Killer Thursday, June 29, 2017 2:23 PM Bug ChrW(257) fixed

hi, thanks for the reply.. i have had some down time.. pardon still need to check this out, at least keeping to 1Q at a time, so not to cause too much a problem :) note: i am not sure i know how to execute your examples. i did maybe 2 things as posted now, but if wrong how do i use them. thanks.
looks like it will take me awhile to digest.. thanks for all the effort. i looked at a line of links mainly used, and some do have parenthesis, especially true for goFHL example (if posted here) follow hyperlink allows test for left of a parenthesis eg: ",IF("
i just thought since the Hyperlink command existed, there would be an easy facility for programming reference to that.. for link vs friendly name. hope i am not turning MS too much upside down on this :))
anyways.. am going to try to test them now before hit enter here. (im calling it the hyperlink testies). i did not know there would be this much involved in this & am not ?.. needing this as any kind of rush..
for the first TEST() mac i was not able to get a link to activate. by putting cursor in area & hitting run.
if the 2nd item is something that can be "called", i only tried as: GetHyperlink (selection)
(i am pretty at some vb things, so not sure how to call it otherwise).
if to kid alittle, to prove i looked at :) each line & rearranged for some more tidy view, so i could try to tell what it is doing.. will put in a 2nd post here.. that shows where the error came up at.
Thanks! its going to take me awhile to figure it out. if there is something i did wrong in running it. let me know, eg: place cursor on cell & hit run? or make a different reference etc.
 Edited by Davexx Tuesday, June 27, 2017 11:36 AM

i am including a copy of what you posted. mostly to show where error i had is located. thanks.
i can / (do) spend some significant time testing items, but have not gotten there on this yet. i do not get what 3rd item is for / yet. i think i can say i basically only use 1 hyperlink in a cell, if with a few separations from work cells by andpersands.
'Option Explicit
#Const Develop = True
Sub testLINKS() 'Sub Test()
Dim Link As String, FName As String, R As RANGE, i As Long
For Each R In RANGE("BN1187") '< B: get same error below 'A: For Each R In RANGE("BK1187:BR1187") 'For Each R In RANGE("A7:C7")
For i = 1 To CountHyperlinks(R) 'link B: =HYPERLINK($BN$6&BF1187,"N")
'ERR: type mismatch (whole line yellow):
Link = GetHyperlink(R, i, FName) 'Friendly name match with the cell? 'ERR: type mismatch (for A above)
If FName = R.Value Then 'Found
#If Develop Then
Debug.Print R.address(0, 0); " "; Link, FName
#Else
ThisWorkbook.FollowHyperlink Link
#End If
Exit For
End If
Next
Next
End Sub
Function GetHyperlink(ByVal FromCell As RANGE, Optional ByRef FriendlyName, Optional ByVal Nth As Integer = 1) As String
Const HypStart = "HYPERLINK(" 'Returns the Nth hyperlink from a cell which can have a real hyperlink or a complex HYPERLINK formula
Dim i As Long, j As Long, b As Long
With FromCell
If .HYPERLINKS.Count > 0 Then
With .HYPERLINKS(Nth) 'Get the Nth GetHyperlink directly
GetHyperlink = .address
FriendlyName = .TextToDisplay
End With
Else
Do 'We parse the Nth link from the formula
i = InStr(i + 1, .Formula, HypStart, vbTextCompare)
If i = 0 Then Exit Function
Nth = Nth  1
Loop Until Nth <= 0
i = i + Len(HypStart) 'Calculate the start position
For j = i To Len(.Formula) 'Find the end of the link
Select Case Mid$(.Formula, j, 1)
Case "(": b = b + 1
Case ")": b = b  1
Case ",": If b = 0 Then Exit For
End Select
Next
GetHyperlink = Mid$(.Formula, i, j  i) 'Get the part that builds the hyperlink, this might be a formula
GetHyperlink = application.Evaluate("=" & HypStart & GetHyperlink & ")") 'Create a hyperlink formula and let Excel evaluate the link
If IsMissing(FriendlyName) Then Exit Function
i = j + 1
For j = i To Len(.Formula) 'Find the end of the link
Select Case Mid$(.Formula, j, 1)
Case "(": b = b + 1
Case ")"
b = b  1
If b < 0 Then Exit For
End Select
Next
FriendlyName = Mid$(.Formula, i, j  i) 'Get the part that builds the friendly name, this might be a formula
FriendlyName = application.Evaluate("=" & FriendlyName) 'Create a formula and let Excel evaluate the friendly name
End If
End With
End Function
Function CountHyperlinks(ByVal FromCell As RANGE) As Long
Const HypStart = "HYPERLINK(" 'Returns the number of hyperlinks in a cell which can have a real hyperlink or a complex HYPERLINK formula
Dim i As Long
With FromCell
If .HYPERLINKS.Count > 0 Then
CountHyperlinks = .HYPERLINKS.Count
Else
Do
i = InStr(i + 1, .Formula, HypStart, vbTextCompare)
If i = 0 Then Exit Function
CountHyperlinks = CountHyperlinks + 1
Loop
End If
End With
End Function
 Edited by Davexx Tuesday, June 27, 2017 10:43 AM

i can / (do) spend some significant time testing items, but have not gotten there on this yet. i do not get what 3rd item is for / yet. i think i can say i basically only use 1 hyperlink in a cell, if with a few separations from work cells by andpersands.
Make a sample file and post the download link here. I'll take a look.
Andreas.

hi, i can do that but will take at least a day before can get to it. what is the method of vb to call your code?
am not sure my example will show much besides what can list here, as i am not sure i am making the "call" correctly.
an actual example of a link i use:
=HYPERLINK($BO$2&A9&$BO$3,IF($BO$1="y","Y",$X$8))
$BO$2: ="https://encrypted.google.com/search?complete=0&num=100&hl=en&safe=off&q=%22"
A9: Microsoft
$BO$3: ="%22+AND+info+site:http:%2F%2Fbiz.yahoo.com%2Fe%2F"
the vb call i currently use is:
goFHL Cells(activecell.row, J6).Formula, ",IF("
workcell J6 has column designation like: M:M
and so, i have no idea how to apply your code(s) examples. can i get the examples for that. does the above suffice for a workbook copy. think fairly simple enough. thanks
 Edited by Davexx Thursday, June 29, 2017 12:56 PM

think fairly simple enough.
Well, I've setup the cells with the values, resp. formulas as you've shown.
If I click on the cell with the hyperlink formula manually (without any code), I get an error:
Means your sample did not work. The created hyperlink is not valid.
BTW, the issue for the Type Mismatch Error above is the wrong order of the arguments in this call:
Link = GetHyperlink(R, i, FName)
it has to be
Link = GetHyperlink(R, FName, i)Sorry for that.
Andreas.

If I click on the cell with the hyperlink formula manually (without any code), I get an error:
Means your sample did not work. The created hyperlink is not valid.
I found the issue for that error, seems we hit a Bug in Excel?
The evaluated link from your sample data is:
?https://encrypted.google.com/search?complete=0&num=100&hl=en&safe=off&q=%22Microsoft%22+AND+info+site:http:%2F%2Fbiz.yahoo.com%2Fe%2F
As you see there is a "?" sign in front.
The question is which signs in the given arguments causing this...
The issue is not related to my code, the ? sign is added if we call Application.Evaluate
Andreas.

As you see there is a "?" sign in front.
The question is which signs in the given arguments causing this...
The issue is not related to my code, the ? sign is added if we call Application.Evaluate
Okay, this is a little mysterious, I don't know there reason for this ? sign, but it is not a usual ? sign, it is an ASCII 257!
It seems that Excel uses this char internally for some special purposes, I don't know which one, but you can't see it within a cell.
Run this in a new file, curious result:
Sub Test() Dim i As Integer For i = 1 To 20 Cells(i, 1) = i Cells(i, 2) = String(i, ChrW(257)) Cells(i, 3).Formula = "=LEN(RC[1])" Next End Sub
Edit the cells in column B afterwards, they seems to be empty, but the formula shows the opposite.
Okay, but that is no issue for us, we can catch that sign and remove it.
I've replaced the Function GetHyperlink in the post above, please copy the code again.
Andreas.
 Edited by Andreas Killer Thursday, June 29, 2017 2:41 PM

hi, thanks for the replies. i will try to follow as i can.. it seems like this is a tuff issue for not having an easy answer. i am not that worried about getting a solution compared to the amount been writing about it.. so no worries.
still pretty novice at vb but for item your example: GetHyperlink(R, i, FName) just offhand, am not sure what to type make it work. i get Fname, what is the R & i
if matters :) i try to rip items apart, if even to get a work around. for some normal vb? to follow a hyperlink that is 'all' coded (if at all helps me to find a way to strip a friendly name off without my identifying it.. :) don't know this is all i have:
ThisWorkbook.FollowHyperlink address:= _
"http://www.yahoo.com/" & Cells(activecell.row, B5) & "/anything" '< NO FRIENDLY NAME AS AN OPTION
wild guessing: if i can apply link as this vb, can i vb / udf apply a friendly name afterwards. 2. for the goFHL eg the strDL is what it is looking for? and how to isolate that.i am not familiar enough with your example to test it by pieces yet.. but as below, MS has already identified the 2 items: string and Fname? yep.. if any of this helps.. sorry, up all night. working on a few areas. (i will look this stuff over some more).
same thing, not sure how to say it: the thing i see / wonder about is that the hyperlink function is that it is already written and seems to be able to readily identify its on sections for the Address vs a Friendly Name. it is curious how hard it seems to be able to grab on to the components, to make it work.
item, a goog home page, uses same question mark. not all my links will be google.
https://encrypted.google.com/search?complete=0&num=100&hl=en&safe=off&q=vba+excel+2002+xp+%22zbxla%22+tips.net+pdf+hivmr.com+excelworkbooktalk.info+bigresource.com
 Edited by Davexx Saturday, July 1, 2017 2:04 PM
