Regular Expressions

I've recently discoverd that Regular Expressions can be used in VBA. You just need to add a reference to "Microsoft VBScript Regular Expressions 5.5" in the Visual Basic Editor.

Regular Expressions is a data validation language that is used within many other programming languages and is very powerful at validating data. The great thing about it is that once you learn Regular Expressions you can transfer that knowledge to many other languages that implement Regular Expressions.

You can find more information here:

http://en.wikipedia.org/wiki/Regular_expression
www.regular-expressions.info/
http://regexlib.com/

Private Sub txtEmail_Enter()
' Using Regular expressions Must add a reference to
'Microsoft VBScript Regular Expressions 5.5

Dim re, s

Set re = New RegExp
re.Global = True
s = txtEmailAddress.Text
re.Pattern = "^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$"

If Not re.Test(s) Then
MsgBox "Email address is NOT valid."
End If

End Sub