RegExpr for VB & VBA is a Visual Basic and Visual Basic for Applications code library for regular expression matching. Regular expressions are used for finding string patterns and string replacement. RegExpr brings the power of regular expressions to VB and VBA, which lack native support for them.
In order to use regular expressions, include RegExpr.Bas in your Visual Basic project. In VBA, include the RegExpr module in your document.
The main function is called RegExpr. It has the following syntax:
Public Function RegExpr(Text, RegularExpression, Optional Flags) As Boolean
RegExpr will return True if a match is found and False otherwise. The simplest use for regular expressions is to check if a string matches a given pattern.
SomeText$ = "My name is Jesse James" If RegExpr(SomeText$, "Jesse") Then ...
The condition is True if SomeText$ includes the string Jesse. By default, regular expressions are case sensitive, so "My name is jesse james" wouldn't match the above condition. You can set Flags = reCaseInSensitive to ignore case.
So far, all this could be easily done with VB's normal "=" or Like statements. Let's now see something more sophisticated.
Here are some (but not all) useful special characters you can use in regular expressions:
| . | Any single character (exception: see Flags) |
| * | Zero or more of the last character |
| + | One or more of the last character |
| ? | Zero or one of the last character |
Characters * + and ? are called quantifiers, because they are used to tell how many of something should be matched. You can use parentheses to group things. Here are some examples:
| be.r | matches e.g. bear or beer |
| bee? | matches both be and bee |
| (bee)? | matches both bee and an empty string |
| gr+ | matches gr, grr, grrr, grrrr etc. |
| argh* | matches arg, argh, arghh, arghhh etc. |
| (argh)+ | matches argh, arghargh, argharghargh etc. |
For example, you could use:
SomeText$ = "The dog said grrrr" If RegExpr(SomeText$, "gr+") Then RunAwayFast
Square brackets are used to match any one of the characters inside them. It's somewhat similar to the [] syntax in VB's Like statement, but there are some differences.
| [abc] | matches a single a, b, or c. |
| [abc]+ | matches any combination of a, b and c. |
A hyphen indicates "between" in ASCII order.
| [a-c] | matches a single a, b, or c. |
| [c-a] | is a syntax error, because c comes after a in ASCII. |
| [a-zA-Z] | matches any lower- or upper-case character (but not including ASCII 128 - 255) |
A carat at the beginning means "not":
| [^z] | matches any character except z |
| [a^z] | matches a, z, or ^, because ^ doesn't start the expression inside the brackets |
If you want to include ], ^ or - inside [], use the escape character: \], \^ or \-.
There are a couple of special escape sequences. Here are the most usual ones (see Syntax rules for more):
| \w | Any alphanumeric (word) character. By default, the same as [a-zA-Z0-9_€ÿ], that is, underscore, all numbers and letters including ASCII 128 - 255. See the syntax rules for details. |
| \d | Any digit. The same as [0-9]. |
| \s | Any whitespace character: space, tab, or newline. |
Examples:
| \w+ | Any word |
| \d+ | Any positive integer |
| x\s+y | "x y", with one or more whitespace between x and y |
Another example:
Person$ = "Mrs Jones" If RegExpr(Person$, "Mr[s]? \w+") Then ' The text looks like Mr or Mrs Somebody MsgBox "Welcome, " & Person$ & "!" End If
Sometimes you're interested in the text that actually matched your regular expression. You can then use the RegExprResult function to find that out. At its simplest form, you can use it like this:
If RegExpr("My lucky number is 123", "\d+") Then
MsgBox "Your lucky number is " & RegExprResult()
End If
Now you're through with this short tutorial, and ready to read some more.
Regular expression syntax rules
VB functions in RegExpr
Subexpressions