Thursday, July 15, 2010

Reg-Ex in VBA

How do we validate alpha numeric values using Reg-ex in VBA?

The following script simplified my job to validate the alphanumeric characters.

Its quite different from other technologies, we better go in a reverse direction.
'Scripting
Private Sub test()
    Dim RegEx As Object
    Dim val As Variant
    Dim sPattern As String
    sPattern = "[^\w]"  'I am trying to find whether the text has any non alphanumeric characters init
                                 ' i.e other than A-Z and 0-9 
 val =  "asdf123"  'valid string
               
 Set RegEx = CreateObject("vbscript.regexp")
 With RegEx
  .Global = True
  .IgnoreCase = True
  .Pattern = sPattern
 End With
 If (RegEx.test(val)) Then
    MsgBox "Not an alpha numeric string"
 Else
   MsgBox "Its valid string"
 End If
 
End Sub

More About Reg Ex can be found at the following links:

http://msdn.microsoft.com/en-us/library/1400241x%28VS.85%29.aspx
http://www.georgehernandez.com/h/xComputers/VB/zMisc/RegExp.asp#EG

No comments:

Post a Comment