Thread: hyperlink function and macro

Hello Henry,

Welcome to the Forum!

As you have discovered, not all Hyperlinks are created equal. They both produce the same results, but internally they differ in how those results are accomplished. The Hyperlink protocol only describes the Hyperlink's behaviour, not how it has to accomplish it. So, Excel does it one way and VBA does it another way, and browsers do it still in another fashion, but the end results are the same. They just don't play well with others.

You could iterate the formulae within the selection and test for HYPERLINK in that manner
(splitting the formula to extract the reference, building the hyperlink & following [then removing])

How viable that will be will largely depend on the consistency of the formulae themselves (eg embedded functions etc)

A very basic example assuming non-embedded function calls, valid (& basic) links etc...
(ie =HYPERLINK() rather than =IF(...,IF(...,HYPERLINK(...),""),"")

Sub Example()
    Dim rngF As Range, rngC As Range, strLink As String, hl As Hyperlink
    On Error Resume Next
    Set rngF = Selection.SpecialCells(xlCellTypeFormulas).Cells
    On Error GoTo 0
    If Not rngF Is Nothing Then
        For Each rngC In rngF.Cells
            With rngC
                If InStr(1, .Formula, "HYPERLINK(", vbTextCompare) > 0 Then
                    strLink = Split(.Formula, Chr(34))(1)
                    With rngC
                        .Hyperlinks.Add .Cells(1), strLink
                        .Hyperlinks(1).Follow True
                        .Hyperlinks(1).Delete
                        .Formula = .Formula
                    End With
                End If
            End With
        Next rngC
    End If
    Set rngF = Nothing
End Sub

I've no doubt there's a better method...

If you need to Follow the hyperlink then genuine links is best approach.

FWIW, I noticed that the code I provided would not have worked for your samples (concatenation) and the strLink line would need to be revised

strLink = Evaluate(Split(Split(Left(.Formula, Len(.Formula) - 1), "=HYPERLINK(")(1), ",")(0))

also as you have demonstrated there's no real need to add the links to the cells as I was doing