less than 1 minute read
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
Categories: vba
Updated: November 07, 2018