Extract Link Address From (insert)hyperlink Or Hyperlink Formula

This method that will return the hyperlink text whether it has been created by a formula, or by the Insert/Hyperlink method.

If the former, we merely have to parse the formula; if the latter, we need to iterate through the hyperlinks collection on the worksheet.

The formula will return nothing if there is no hyperlink in cell_ref; change to suit.

' ## Get the formula url from hyperlink/formula or the insert/hyperlink method
'
Function LinkLocation(rng As Range)

    ' vars
    Dim sFormula As String, sAddress As String
    Dim L As Long
    Dim sHyperlink As Hyperlink, rngHyperlink As Hyperlinks
    
    ' cell formula
    sFormula = rng.Formula
    
    ' gets starting position of the file path, also acts as a test if
    ' there is a formula
    L = InStr(1, sFormula, "HYPERLINK(""", vbBinaryCompare)

    ' tests for hyperlink formula and returns the address, if a link
    ' then returns the link location.
    If L > 0 Then
        sAddress = Mid(sFormula, L + 11)
        sAddress = Left(sAddress, InStr(sAddress, """") - 1)
    Else
        Set rngHyperlink = rng.Worksheet.Hyperlinks
        For Each sHyperlink In rngHyperlink
            If sHyperlink.Range = rng Then
                sAddress = sHyperlink.Address
            End If
        Next sHyperlink
    End If
    
    ' boom, got the hyperlink address
    LinkLocation = sAddress

End Function