Using Regular Expressions with VBA


G

Guest

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
 
N

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
 
Ad

Advertisements

G

Guest

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

Andrew
 
B

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)
 
Ad

Advertisements

G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top