The RegExp Object
VBA has no native regex operator, but you can use the VBScript RegExp engine via CreateObject("VBScript.RegExp") (late binding) or a reference to 'Microsoft VBScript Regular Expressions 5.5'. You configure three properties before use: Pattern (the regex string), Global (True to match all occurrences, not just the first), and IgnoreCase (True for case-insensitive matching). The engine supports the common subset — character classes, quantifiers, anchors, groups, and alternation — but not the newer PCRE features like lookbehind.
Cricket analogy: The RegExp object is a bowling machine you dial in before a session: Pattern is the line and length, Global is bowling a whole over versus one ball, and IgnoreCase is ignoring which end you bowl from.
Test, Execute, and Replace
The RegExp object has three methods. Test(text) returns True/False for whether the pattern matches at all — perfect for validation like checking an email shape. Execute(text) returns a MatchCollection of every match (when Global is True), where each Match exposes Value, FirstIndex, Length, and a SubMatches collection holding captured groups. Replace(text, replacement) swaps matches, and in the replacement string $1, $2 refer to captured groups, letting you reformat data — for example turning 'Last, First' into 'First Last'.
Cricket analogy: Test is the third umpire's simple out/not-out call; Execute is the full ball-by-ball commentary listing every event; Replace is editing the scorecard so '$1' and '$2' swap the batsman and bowler columns.
Function ExtractEmails(text As String) As Collection
Dim re As Object, matches As Object, m As Object
Set re = CreateObject("VBScript.RegExp")
re.Pattern = "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}"
re.Global = True
re.IgnoreCase = True
Dim result As New Collection
Set matches = re.Execute(text)
For Each m In matches
result.Add m.Value
Next m
Set ExtractEmails = result
End Function
Sub SwapName()
Dim re As Object
Set re = CreateObject("VBScript.RegExp")
re.Pattern = "^(\w+),\s*(\w+)$" ' Last, First
Debug.Print re.Replace("Sharma, Rohit", "$2 $1") ' -> Rohit Sharma
End SubGroups, Greediness, and Gotchas
Parentheses create capturing groups accessible via Match.SubMatches(0), SubMatches(1), and so on — note this collection is 0-based, unlike most VBA collections. Quantifiers like * and + are greedy by default, matching as much as possible; append ? (as in .*?) to make them lazy when you want the shortest match, which matters when pulling text between HTML tags. Because the VBScript engine is older, it lacks named groups and lookbehind, so complex extraction sometimes needs a two-step pattern or post-processing.
Cricket analogy: SubMatches being 0-based is like an over's balls counted from 0 in the data feed; greedy quantifiers are a batsman going for every run until forced to stop, while lazy '.*?' is nudging a single and settling for the shortest safe outcome.
SubMatches is 0-based: the first captured group is Match.SubMatches(0). This trips up developers used to VBA's 1-based Collection and Range indexing. Match.Value gives the whole match, while SubMatches gives only the group captures.
Setting Global = False (the default) makes Execute return at most one match and Replace change only the first occurrence — a frequent source of 'why did only one get replaced?' bugs. Also remember to double-escape backslashes conceptually: in the Pattern string a literal dot is \. and a digit class is \d; forgetting the backslash makes '.' match any character.
- VBA uses the VBScript.RegExp object for regular expressions, via CreateObject or a 5.5 library reference.
- Configure Pattern, Global (all matches), and IgnoreCase before running.
- Test returns a Boolean, Execute returns a MatchCollection, and Replace substitutes with $1/$2 back-references.
- Captured groups live in Match.SubMatches, which is 0-based unlike most VBA collections.
- Quantifiers are greedy by default; add ? for lazy matching to get the shortest match.
- Global defaults to False, so remember to set it True to match/replace all occurrences.
- The engine lacks named groups and lookbehind, so complex parsing may need two passes.
Practice what you learned
1. Which property must be True for Execute to return all matches rather than just the first?
2. How do you access the first captured group of a match?
3. What does appending ? to a quantifier, as in .*?, do?
4. Which method is best for validating that a string looks like an email?
5. Which feature does the VBScript RegExp engine NOT support?
Was this page helpful?
You May Also Like
Reading and Writing Cell Data
How to read from and write to worksheet cells in VBA using the Range and Cells objects, and how to move data efficiently between the sheet and VBA arrays.
Dictionaries and Collections in VBA
Comparing VBA's built-in Collection with the Scripting.Dictionary for storing keyed data, and choosing the right one for lookups, de-duplication, and counting.
VBA and ADO Database Access
Using ActiveX Data Objects (ADO) from VBA to connect to databases, run parameterized queries, and read results into a worksheet or recordset.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics