HasExternalLink Function

[ExcelMonkey]

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?

[Jake Marx]

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?

[ExcelMonkey]

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?
>
>.
>

[Jake Marx]

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?
>>
>> .