Sub HL_FollowIfFormula()
MsgBox HL_Path(Range("C3")) '# hyperlink formula
MsgBox HL_Path(Range("B19")) '# hyperlink formula with indirect
Stop
Dim rgHyperlink As Range
Dim sHyperlink As String
Set rgHyperlink = Range("B19")
sHyperlink = HL_Path(rgHyperlink)
ActiveWorkbook.FollowHyperlink Address:=sHyperlink, NewWindow:=True
End Sub
Function HL_Path(rg As Range)
Dim sArgument As String, sTEMP1 As String
'# Range with real hyperlinks
If rg.Hyperlinks.Count > 0 Then HL_Path = rg.Hyperlinks(1).Address: Exit Function
'# Get 1st argument of formula and evaluate
'# if 1st argument contains a function, Excel returns its value
'# if 1st argument is a string, Excel returns string without quotes
HL_Path = ARG(rg, 1) '# Read 1st argument
HL_Path = Evaluate(HL_Path) '# Evaluate argument
End Function
Function ARG$(cel As Range, ordre%)
Dim f As String, Deb As Long, Fin As Long, i As Long, Txt As String, ng!, n%
f = cel.Formula
If IsEmpty(cel) Then Exit Function
f = Mid(f, InStr(f, "(") + 1, Len(f) - InStr(f, "(") - 1) & ","
Deb = 1
Fin = Len(f)
For i = 1 To Fin
If Mid(f, i, 1) = "," Then
Txt = Mid(f, Deb, i - Deb)
ng = (Len(Txt) - Len(Replace(Txt, """", ""))) / 2
If ng = Int(ng) And Len(Replace(Txt, "(", "")) = Len(Replace(Txt, ")", "")) Then
n = n + 1
If n = ordre Then ARG = Txt: Exit Function
Deb = i + 1
End If
End If
Next
End Function