Hi ExcelMonkey,
Here's a UDF you could try:
Public Function HasExtLink(rng As Range) As Variant
Dim reg As Object
On Error GoTo ErrHandler
If rng.HasFormula Then
Set reg = CreateObject("VBScript.RegExp")
With reg
.Pattern = "\=\[.+\].+"
HasExtLink = .test(rng.Formula)
End With
Else
HasExtLink = False
End If
ExitRoutine:
Set reg = Nothing
Exit Function
ErrHandler:
HasExtLink = CVErr(Err.Number)
Resume ExitRoutine
End Function
If you put this into a standard module, you can use it in Excel just like
you would any other worksheet function. You could probably make this more
robust by looking for additional things after the last "]", but it should
work in most cases.
--
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]
ExcelMonkey wrote:
I am looking to write a function to see if a cell has an
external link. I know that if it does, it will have:
"="
"["
"]"
I could realistically use .Hasformula property to test
that the cell is a formula and then use the Find Method
with some sort of combined search
.Find([, lookin:=xlValues)
.Find(], lookin:=xlValues)
But is there an easier way to test (Boolean) if a cell has
an external link?