By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,247 Members | 1,517 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,247 IT Pros & Developers. It's quick & easy.

simple vba regex

P: 30
can anybody give me sample vba regex code to me for study .


is there any video learning tutorial for regex
Aug 14 '12 #1
Share this Question
Share on Google+
1 Reply


twinnyfo
Expert Mod 2.5K+
P: 3,284
prasad,

Try this:

Open a VBA project, insert new module, then pick Tools -> References and add a reference to Microsoft VBScript Regular Expressions 5.5. Then create a function in that module:

Expand|Select|Wrap|Line Numbers
  1. Function UseRegExp(ByRef strInputn As String, ByVal MyPattern As String) As String
  2. On Error GoTo EH
  3.     Dim MyRegExp As New RegExp
  4.     Dim colMatches As MatchCollection
  5.     With MyRegExp
  6.         .Pattern = MyPattern
  7.         .IgnoreCase = True
  8.         .Global = False
  9.         .MultiLine = False
  10.         Set colMatches = .Execute(strinput)
  11.     End With
  12.     If colMatches.Count > 0 Then
  13.         UseRegExp = colMatches(0).Value
  14.     Else
  15.         UseRegExp = ""
  16.     End If
  17.     Exit Function
  18. EH:
  19.     MsgBox Err.Number & " " & Err.Description
  20. End Function
  21.  

Now you may use the function above in your SQL queries by invoking the following (as an example):

Expand|Select|Wrap|Line Numbers
  1. SELECT UseRegExp(MyVariable, "^[0]{1}[0-9]{8,9}$")
  2. FROM tblTableName;
  3.  
If the function returns an empty string, nothing is matched.
Aug 14 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.