🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Regex in Excel — the RegExp Object and Why Your Match Comes Back Empty

|

VBA Regex in Excel — the RegExp Object and Why Your Match Comes Back Empty

TL;DR — There is no Regex keyword in VBA. You create the VBScript RegExp object, either late bound with CreateObject("VBScript.RegExp") or early bound via a reference. It does nothing until you set its properties: .Pattern, and the two that default to the unhelpful value — .Global = False (only the first match) and .IgnoreCase = False (case-sensitive). Then pick the right verb: .Test returns a Boolean, .Replace returns a string (backreferences are $1, not \1), and .Execute returns a collection of matches whose captured groups live in .SubMatches — not in .Value.

Sub RegexBasics()
    Dim re As Object
    Set re = CreateObject("VBScript.RegExp")   ' late binding - no reference needed
    re.Pattern = "PO-(\d+)"                     ' a group captures the digits
    re.Global = True                            ' find ALL matches, not just the first
    re.IgnoreCase = True                        ' case-insensitive

    Dim m As Object
    For Each m In re.Execute("po-17 and PO-9004")
        Debug.Print m.Value             ' PO-17    then PO-9004  (the whole match)
        Debug.Print m.SubMatches(0)     ' 17       then 9004     (the captured group)
    Next m
End Sub

What you'll learn

  • The mental model — regex is an object you create, not a keyword
  • Late binding vs adding the reference, and the compile error that follows
  • Why Global = False means you only ever get the first match
  • What Test, Replace and Execute each return
  • Why extracting a value means reaching past Match.Value to SubMatches
  • What the VBScript pattern flavor supports, and what it does not

The mental model: regex is an object you create, not a keyword

Coming from almost any other language, you expect a regex operator or function. VBA has neither. What it has is a COM component — the VBScript Regular Expressions library — that you instantiate, wire up with properties, and then call. So the mental model is not "call a function" but "configure an object, then ask it a question". Every frustration below is really "I forgot to set a property before I asked."

That also tells you when it is worth reaching for. RegExp is the heavyweight at the top of the ladder, above the Like operator and plain InStr. You pay a setup cost — create it, set the pattern, flip the switches — and in return you get repetition counts, alternation, and the one thing the others cannot do: pull captured pieces out of the match.

Setting it up: late binding vs a reference

Two ways to get the object, and the choice has real consequences:

' Late binding - portable, needs no reference, resolves at run time
Dim re As Object
Set re = CreateObject("VBScript.RegExp")

' Early binding - add the reference first, then this compiles
' Tools -> References -> "Microsoft VBScript Regular Expressions 5.5"
Dim re As New RegExp

Early binding gives you IntelliSense and slightly faster calls, but it only compiles if the reference is ticked — and if you send the workbook to someone whose reference is missing, they get a compile error User-defined type not defined on the As New RegExp line before a single statement runs. Late binding (As Object + CreateObject) has none of that fragility: it resolves at run time and travels without a reference. For anything you distribute, prefer late binding — the portability is worth losing IntelliSense.

Trap 1: Global is off, so you only get the first match

.Global defaults to False, and that single default causes the two most common "regex is broken" reports:

re.Pattern = "\d+"
re.Global = False                       ' the default
Debug.Print re.Replace("a1 b2 c3", "#") ' "a# b2 c3"  <- only the FIRST run replaced
Debug.Print re.Execute("a1 b2 c3").Count ' 1          <- only the FIRST match found

re.Global = True
Debug.Print re.Replace("a1 b2 c3", "#") ' "a# b# c#"  <- now all of them
Debug.Print re.Execute("a1 b2 c3").Count ' 3

If a replace only touched the first occurrence, or a search found one match when you can see three, this is almost always the cause. Set .Global = True whenever you mean "everywhere". Its neighbour .IgnoreCase defaults to False in the same way, so a pattern that clearly should match different-case text and does not is usually one missing re.IgnoreCase = True.

Trap 2: Test, Replace, and Execute return three different things

The object exposes three verbs, and confusing their return types is the second big stumble:

  • re.Test(s) returns a Boolean — does the pattern match anywhere? Use it as a validator, the regex-powered cousin of Like.
  • re.Replace(s, replacement) returns a String — the input with every match (if Global) replaced. Backreferences in the replacement use a dollar sign: $1 is the first captured group. Writing \1 inserts a literal backslash-one, a frequent bug for people arriving from other languages.
  • re.Execute(s) returns a MatchCollection — a collection of Match objects you loop over to inspect or extract.
re.Pattern = "(\d{4})-(\d{2})"
Debug.Print re.Test("2026-09")                 ' True
Debug.Print re.Replace("2026-09", "$2/$1")     ' "09/2026"  - $1, $2 are the groups

Pick the verb by the answer you need: a yes/no is Test, a transformed string is Replace, and anything where you want the matched text or its pieces is Execute.

Trap 3: to extract, reach past Value to SubMatches

This is the one that sends people in circles. Each Match from .Execute has a .Value — the whole matched text — and a .SubMatches collection holding the captured groups, the parts you wrapped in parentheses:

re.Pattern = "Invoice #(\d+) dated (\d{4}-\d{2}-\d{2})"
re.Global = True
Dim m As Object
For Each m In re.Execute("Invoice #4471 dated 2026-09-04")
    Debug.Print m.Value          ' Invoice #4471 dated 2026-09-04   (the whole match)
    Debug.Print m.SubMatches(0)  ' 4471                              (first group)
    Debug.Print m.SubMatches(1)  ' 2026-09-04                        (second group)
    Debug.Print m.FirstIndex     ' 0    (0-based start position in the input)
Next m

If you want the number, you want m.SubMatches(0), not m.Value — reading .Value and wondering why it still contains the surrounding text is the number-one extraction bug. SubMatches is zero-based, and a group that did not participate in the match comes back as an empty string, so guard on re.Test(s) (or the collection .Count) before you index into it.

The VBScript flavor: what the pattern language does and does not support

VBA's regex is the VBScript (RegExp 5.5) flavor, not PCRE, and the gaps trip up anyone with regex experience elsewhere. It supports the everyday toolkit: \d \w \s and their negations, quantifiers * + ? {n} {n,m}, alternation a|b, groups ( ), character classes [ ], and anchors ^ $ (with .MultiLine deciding whether those mean line or whole-string). It does not support lookbehind, named capture groups, or atomic/possessive groups — patterns that lean on those simply will not compile the way you expect.

One VBA-specific relief: because the backslash is not an escape character inside VBA string literals, you write patterns straight — "\d{4}", not "\\d{4}". No double-escaping, unlike C#, Java or JSON. The only doubling you need is for a literal quote, which is VBA's own "".

The opinion: regex is worth the ceremony only for real structure

RegExp is the most powerful text tool in VBA and the one most often reached for too early. Creating the object, setting the pattern, and remembering Global and IgnoreCase is real ceremony, and for a fixed shape or a plain yes/no it is ceremony you do not need — Like is one readable line and InStr is faster for "contains this literal".

The line to draw is structure you must pull apart. When the pattern has variable repetition, real alternation, or — the decisive one — a group you need to extract, regex is the only tool in the box that does it, and it earns its keep: an order number out of a subject line, the parts of a messy timestamp, every email in a blob of text. Reach for it there, prefer late binding so it travels, set Global and IgnoreCase on purpose, and read SubMatches for the pieces. Anywhere short of that, climb back down the ladder.

When the parsing is the point — describe the job instead

Even when regex is the right tool, the pattern is rarely the goal — "pull every invoice number and its date out of this column and split them into two fields" is. And a VBScript pattern with capture groups, plus the loop over Execute, plus the SubMatches bookkeeping, plus the edge cases where a row does not match at all, is a lot of fragile machinery around one intent. ExcelMaster lets you describe the result in plain English — "extract the invoice number and date from column B into columns C and D, flag rows with no match" — and it generates Python that does the extraction with a real regex engine, backs up your file first, and reports the rows it could not parse. You describe what to pull out; it handles the pattern, the groups, and the misses.

Frequently asked questions

How do I use regex in VBA?

Create the VBScript RegExp object, set its properties, then call a method. The portable way is late binding: Set re = CreateObject("VBScript.RegExp"), then re.Pattern = "\d+", re.Global = True, re.IgnoreCase = True, and finally re.Test(s), re.Replace(s, repl) or re.Execute(s) depending on whether you want a Boolean, a new string, or the matches.

Why does my VBA regex only replace the first match?

Because .Global defaults to False. With Global off, both .Replace and .Execute stop after the first match. Set re.Global = True before you call them to act on every match in the string.

How do I extract a captured group in VBA regex?

Loop over re.Execute(s) and read Match.SubMatches. SubMatches(0) is the first parenthesised group, SubMatches(1) the second, and so on (zero-based). Match.Value is the whole matched text, not the group — reading .Value when you wanted a group is the most common extraction mistake.

Do I need to add a reference to use regex in VBA?

Only for early binding. Dim re As New RegExp requires the reference "Microsoft VBScript Regular Expressions 5.5" under Tools -> References, and fails to compile with User-defined type not defined if it is missing. Late binding — Dim re As Object with CreateObject("VBScript.RegExp") — needs no reference and is the better choice for workbooks you share.

What regex flavor does VBA use, and does it support lookbehind?

VBA uses the VBScript (RegExp 5.5) flavor. It supports \d \w \s, quantifiers, alternation, groups, character classes and ^ $ anchors, and replacement backreferences with $1. It does not support lookbehind or named capture groups. Also, because the backslash is not special inside VBA string literals, you write "\d{4}" directly with no double-escaping.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-04.

Related guides: VBA Like · VBA IsNumeric · VBA InStr · VBA Replace · VBA Split