Using Regular Expressions with VBA
 

I have done a fair bit of scripting with javascript and php and am learning
VBA.

Is it possible to use regular expressions in VBA / Excel in such a way that
other users can open a workbook and the regular expressions will work without
them having to download mrefunc.xll or to alter the Tools / References list
in the VBA editor.

Looks to me like the answer is no but thought I would ask.

Andrew

Using Regular Expressions with VBA
 

Andrew,
If you use early binding, you can set a reference to "MS VBScript Regular
Expression x.x". The reference is associated with the workbook so the user
will not to change it, assuming:
- that reference, or a later version, is available on the system
- scripting has not been disabled, by Admin/Policy

Then
Dim mRegEx As RegExp

If you mean the above may be problematic, then use late binding and trap the
error. so no reference is set and

Dim mRegEx As Object
Set mRegEx = CreateObject("VBScript.RegExp")

If mRegEx Is Nothing Then
MsgBox "Could create RegExp object"
End If

NickHK

"Andrew Hall NZ" wrote in message
...
I have done a fair bit of scripting with javascript and php and am
learning
VBA.

Is it possible to use regular expressions in VBA / Excel in such a way
that
other users can open a workbook and the regular expressions will work
without
them having to download mrefunc.xll or to alter the Tools / References
list
in the VBA editor.

Looks to me like the answer is no but thought I would ask.

Andrew

Using Regular Expressions with VBA
 

That sounds very promising, thanks, where can I read something about binding.

Andrew

Using Regular Expressions with VBA
 

Andrew,
Take your pick :
http://www.google.co.uk/search?hl=en...e+Search&meta=

NickHK

"Andrew Hall NZ" wrote in message
...

That sounds very promising, thanks, where can I read something about
binding.

Andrew

Using Regular Expressions with VBA
 

Here is a simple example that validates email addresses.

'-----------------------------------------------------------------
Public Function ValidEmail(Adress As String) As Boolean
'-----------------------------------------------------------------
Dim oRegEx As Object
Set oRegEx = CreateObject("VBScript.RegExp")
With oRegEx
.Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
' .Pattern = "^(\w+\.)*(\w+)@(\w+\.)+([a-zA-Z]{2,4})$"
ValidEmail = .Test(Adress)
End With
Set oRegEx = Nothing
End Function


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Andrew Hall NZ" wrote in message
...

That sounds very promising, thanks, where can I read something about
binding.

Andrew

Using Regular Expressions with VBA
 

Thank you both for your replies, all is working fine. It can be pretty
frustrating working with a new language, being able to ask questions makes
all the difference.

Andrew