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?

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?

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]


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?

.

Hi,

Sorry - messed up the Pattern. Try this instead:

.Pattern = "\=.+\[.+\].+\!.+"

Another option would be to use the Like operator. Probably a little more
straightforward:

HasExtLink = rng.Formula like "=*[[]*[]]*!*"

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


ExcelMonkey wrote:
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]


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?

.