Using Regular Expressions with VBA

[Andrew Hall NZ]

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

 

[NickHK]

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]

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

Andrew

 

[NickHK]

Andrew,
Take your pick :
http://www.google.co.uk/search?hl=en&q=excel+early+late+binding&btnG=Google+Search&meta=

NickHK

 

[Bob Phillips]

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]

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