How do you look for two distinct patterns (RegExp)
 

I have a regular expression with defined pattern. How do I incorporate
another pattern. That is, I want to evalute both the pattern in the code
below AND OR a second pattern:
\=[.+\].+\!

What is the proper syntax for passing more than one string to the pattern
property. I want to say look for X AND OR Y.


Public Function CellHasWhatIAmLookingFor(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 = "\=.+\[.+\].+\!.+" 'also want \=[.+\].+\!
CellHasExternalLinks = .test(rng.Formula)
End With
Else
CellHasExternalLinks = False
End If
End Function

How do you look for two distinct patterns (RegExp)
 

I'm not too sure about your pattern..

e.g. a ref to book.xls!MyName is not caught..
but no inclination to do a better pattern :(

use | for OR

normally to define an external ref:
it would suffic to search a ! provided is not not within
DOUBLE QUOTES. (regex for VBscript is not simple as no lookbehind)

for efficiency I DO recommend that RE object and pattern are static

Function IsExternal(Reference As Range) As Boolean
Static oRE As Object
If oRE Is Nothing Then
Set oRE = CreateObject("vbscript.regexp")
oRE.Pattern = "\[.+\].*\!"
End If
If Reference.HasFormula Then
IsExternal = oRE.Test(Reference.Formula)
End If
End Function





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


ExcelMonkey wrote :

I have a regular expression with defined pattern. How do I
incorporate another pattern. That is, I want to evalute both the
pattern in the code below AND OR a second pattern:
\=[.+\].+\!

What is the proper syntax for passing more than one string to the
pattern property. I want to say look for X AND OR Y.


Public Function CellHasWhatIAmLookingFor(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 = "\=.+\[.+\].+\!.+" 'also want \=[.+\].+\!
CellHasExternalLinks = .test(rng.Formula)
End With
Else
CellHasExternalLinks = False
End If
End Function