0

I am trying to write VBA code to validate links on spreadsheet. I have it working well until I encountered Hyperlinks created from formula. For example =Hyperlink(A1,"Link1") or =Hyperlink(A1&A2,"Link2")

The standard Hyperlinks(1).Address VBA does not register these as having link, and I cannot find any other solutions online.

Any ideas?

2
  • Unclear............do you want to convert the formula into an Inserted Hyperlink?? Apr 12, 2018 at 10:40
  • I want to check a hyperlink that was created from the Hyperlink function actually has a valid address.
    – aliw1
    Apr 12, 2018 at 10:56

3 Answers 3

0

Here is a simple approach that will handle some of your formulas. Say A1 contains the formula:

=HYPERLINK("http://www.google.com","search")

If we select it and run:

Sub HyperChecker()
    Dim s1 As String, s2 As String, arr

    s1 = ActiveCell.Formula
    s2 = Mid(s1, 12)
    arr = Split(s2, ",")
    MsgBox arr(0)
End Sub

we get:

enter image description here

The code discards the beginning and end of the formula and returns the URL.

The problem arises if the first argument of the function is an expression rather than a string constant.

You would then need to EVALUATE() that expression.

2
  • That is about as far as I got with my code. When links have been created from nested formula it becomes a lot more complex. Thanks for the help.
    – aliw1
    Apr 13, 2018 at 11:55
  • @aliw1 I have a couple of ideas on attacking complex hyperlinks. If you want, I will update this Answer later today. Apr 13, 2018 at 11:58
0

For the more general case, you can simply use something like:

url_formula = Split(Mid(cell.formula, 12), ",")(0)
Debug.Print Evaluate(url_formula)

Where Mid(cell.formula, 12) gets rid of the =HYPERLINK( part of the formula, and Split(..., ",")(0) splits the rest of the formula at the comma part (the (0) selects the first part of the split - and is thus zero-based unlike most of the indexing in Excel).

Then, use the Evaluate function to evaluate the resulting expression. This should be fairly general, at least as general as the Evaluate function allows.

0

Other answers don't handle variations in the formula very well. For example, they fail if the formula contains both the LINK_LOCATION parameter and the FRIENDLY_NAME parameter. Others also fail if the formula has extra spaces or line breaks in certain areas.

This answer isn't perfect but it works better than other answers I have found as of the date I am posting this. I have identified cases where this code will work and where it will fail.

This VBA function is a bit long but it will extract the URL/address of a hyperlink either from a HYPERLINK() formula or a non-formula hyperlink imbedded in a cell.

It checks for a non-formula hyperlink first since that is the easiest and most reliably extracted hyperlink. If one doesn't exist it checks for a hyperlink in a formula.

Extraction from a formula ONLY works if there is nothing outside the HYPERLINK() function except an equal sign.

Acceptable HYPERLINK() Formulas

It WILL work on this formula:

=HYPERLINK("https://" & A1, "My Company Website")

It WILL work on this formula too (notice extra spaces and line breaks):

=    
HYPERLINK(     "https://" & A1, 
         "My Company Website" & B2)

It will NOT work on this formula:

=IF(  LEN(A1)=0, "", HYPERLINK("https://" & A1, "My Company Website")  )

Function

Function HyperLinkText(ByVal Target As Excel.Range) As String
    
    ' If TARGET is multiple cells, only check the first cell.
    Dim firstCellInTarget As Excel.Range
    Set firstCellInTarget = Target.Cells.Item(1)
    
    
    Dim returnString As String
    
    
    ' First check if the cell contains a non-formula hyperlink.
    If Target.Hyperlinks.Count > 0 Then
        ' Cell contains a non-formula hyperlink.
        returnString = Target.Hyperlinks.Item(1).Address    ' extract hyperlink text from the Hyperlinks property of the range
    
    Else
        ' Cell does -NOT- contain a non-formula hyperlink.
        '   Check for a formula hyperlink.
        Dim targetFormula As String
        targetFormula = firstCellInTarget.Formula
        
        
        
        Dim firstOpenParenthesisIndex As Long
        firstOpenParenthesisIndex = VBA.InStr(1, _
                                              targetFormula, _
                                              "(", _
                                              VbCompareMethod.vbBinaryCompare)
        
        Dim cleanFormulaHyperlinkPrefix As String
        cleanFormulaHyperlinkPrefix = Left$(targetFormula, firstOpenParenthesisIndex)
        cleanFormulaHyperlinkPrefix = Replace$(Replace$(Replace$(cleanFormulaHyperlinkPrefix, Space$(1), vbNullString), vbCr, vbNewLine), vbLf, vbNullString)
        
        Dim cleanFormulaPart2 As String
        cleanFormulaPart2 = Mid$(targetFormula, firstOpenParenthesisIndex + 1)
        
        Dim cleanFormulaCombined As String
        cleanFormulaCombined = cleanFormulaHyperlinkPrefix & cleanFormulaPart2
        
        
        ' Get all text inside the HYPERLINK() function.
        '   This is either a single LINK_LOCATION parameter or both the
        '   LINK_LOCATION and FRIENDLY_NAME parameters separated by a comma.
        '
        '   Ex. 1 Parameter:        "https://" & $A$1
        '   Ex. 2 Parameters:       "https://" & $A$1, "Click Here To Open the Company URL"
        '
        Const HYPERLINK_FORMULA_PREFIX As String = "=HYPERLINK("
                
        Dim tmpString As String
        tmpString = Mid$(cleanFormulaCombined, VBA.Len(HYPERLINK_FORMULA_PREFIX) + 1)
        
        Dim textInsideHyperlinkFunction As String
        textInsideHyperlinkFunction = Left$(tmpString, VBA.Len(tmpString) - 1)
        
        
        ' Get the first parameter (LINK_LOCATION) from the text inside the HYPERLINK()
        '   function by using =EVALUATE().  If text inside the HYPERLINK() function
        '   contains two parameters, they will be separated by a comma and EVALUATE()
        '   will return an error.  Start with the entire text inside the HYPERLINK()
        '   function.  If EVALUATE() returns an error, remove one character from the end
        '   of the string being evaluated and try again.  Eventually only one parameter
        '   will be evaluated and EVALUATE() will return a text string.
        '
        '   For example, if the string to be evaluated is:
        '
        '       "https://" & $A$1, "Click Here To Open the Company URL"
        '
        '   and cell A1 contains:
        '
        '       mycompany.com
        '
        '   EVALUATE will return:
        '
        '       https://mycompany.com
        '
        Dim hyperlinkLinkLocation As String
        Dim i As Long
        For i = VBA.Len(textInsideHyperlinkFunction) To 1 Step -1   ' with each failure, shrink length of string-to-evaluate by one

            If Not VBA.IsError(Excel.Application.Evaluate("=" & Left$(textInsideHyperlinkFunction, i))) Then
                hyperlinkLinkLocation = Excel.Application.Evaluate("=" & Left$(textInsideHyperlinkFunction, i))
                Exit For        ' ****
            End If

        Next i
        
        returnString = hyperlinkLinkLocation

    End If
    
    
    ' Return the hyperlink string.
    HyperLinkText = returnString
End Function

How to Use the Function

Sub Test()
    ' Display hyperlink of the first cell
    '    in the currently selected range.
    Msgbox HyperLinkText(Selection) ' displays the hyperlink of the first cell
End Sub
1
  • Please do not post the same answer to multiple questions. If the same information really answers both questions, then one question (usually the newer one) should be closed as a duplicate of the other. You can indicate this by voting to close it as a duplicate or, if you don't have enough reputation for that, raise a flag to indicate that it's a duplicate. Otherwise tailor your answer to this question and don't just paste the same answer in multiple places.
    – DavidPostill
    Jul 29, 2022 at 10:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .