none
VB: Hyperlink UDF 'automatic' separate address from friendly name RRS feed

  • 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
    Saturday, June 17, 2017 10:55 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
    

    Sunday, June 18, 2017 6:03 AM
  • 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("

    Monday, June 19, 2017 2:09 AM
  • =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 Function

    Function 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
    Monday, June 19, 2017 10:13 AM
  • 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 & re-arranged 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
    Tuesday, June 27, 2017 10:38 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
    Tuesday, June 27, 2017 10:39 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.

    Tuesday, June 27, 2017 12:46 PM
  • 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
    Thursday, June 29, 2017 12:55 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.

    Thursday, June 29, 2017 1:29 PM
  • 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.

    Thursday, June 29, 2017 1:50 PM
  •  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.




    Thursday, June 29, 2017 2:35 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
    Saturday, July 1, 2017 1:21 PM
  • i am not familiar enough with your example to test it by pieces yet..

    Show me your file, otherwise I can not help you.

    Andreas.

    Saturday, July 1, 2017 1:27 PM