Hey Jake, sorry its taken so long to reply to this. Have
tried this code below but cannot seem to get it to work.
I am using it as a function in VBA which I call in VBA.
That is I am not trying to use it as User Defined
Function in Excel. On the line of code that says:
HasExtLink = .test(rng.Formula)
It does not give me a TRUE value when the variable rng
has a formula with an external link. Why is this? I
can't say I understand the Pattern or TEST Property. It
appears as though you look for a pattern and then test to
see if it exists.
When I goto the immediate window and check on the rng
variable I get
?rng.formula
='C:\Documents and Settings\Me\My
Documents\TestModel\[ModelA.xls]Inputs'!$L$39
Test Property is still FALSE
Thanks
-----Original Message-----
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]
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?
.
