12 VBA中使用正则¶

一、为什么要在 VBA 中用正则表达式？¶

Excel/VBA 原生的字符串函数（如 InStr、Replace、Split 等）适用于简单情况，但面对以下场景时会显得力不从心：

复杂格式验证（邮箱、手机号、身份证等）
从非结构化文本中批量提取特定信息
基于模式执行替换（例如批量格式化内容）
处理日志、HTML 等不规则字符串

这时，正则表达式（RegEx） 是唯一高效、灵活的解决方案，关于正则表达式的具体语法，我这里就不再多提，因为我之前写过一篇关于正则表达式的系统梳理，如有需要可以跳转到【正则表达式系统梳理】

二、启用正则表达式支持¶

方法 1 — 前期绑定¶

打开 VBA 编辑器（Alt + F11）
选择上方菜单：工具 → 引用
找到并勾选 Microsoft VBScript Regular Expressions 5.5

注意还有一个版本是1.0的，不要勾选那个，正确启用后，VBA 能识别 RegExp 类型，并有智能提示，如下：

方法 2 — 后期绑定¶

无需手动引用库，兼容性更好，尤其适合分发到多台电脑，这种方式不会触发“缺少引用”的错误：

1 2	`Dim regex As Object Set regex = CreateObject("VBScript.RegExp")`

三、RegExp 对象核心属性与方法¶

在 VBA 中操作正则表达式的核心，就是 RegExp 对象，它具有几个关键属性和方法，总结如下：

属性	说明
`.Pattern`	要匹配的正则字符串
`.Global`	是否全局匹配（查找所有匹配项）
`.IgnoreCase`	是否忽略大小写
`.MultiLine`	多行模式（`^`/`$` 对更多行有效）

而方法主要有验证，提取和替换三种，具体表示为：

Test(str)：判断字符串是否匹配模式，匹配模式由.Pattern给出具体表达式
Execute(str)：返回所有匹配项集合
Replace(str, repl)：替换匹配到的内容

四、核心用法示例¶

4.1 验证模式：判断是否匹配¶

用途： 通常用来检查字符串是否符合某种规则或格式，比如下方检测是否满足日期格式，.Test 返回 True/False

Sub ValidatePattern()
    Dim re As Object
    Set re = CreateObject("VBScript.RegExp") '创建正则表达式对象

    With re
        .Pattern = "^\d{4}-\d{2}-\d{2}$"
        .IgnoreCase = True '忽略大小写
    End With

    Dim s As String
    s = "2025-12-31"

    If re.Test(s) Then
        Debug.Print s & " 格式正确"
    Else
        Debug.Print s & " 格式错误"
    End If
End Sub

4.2 匹配提取：找到所有匹配项¶

用途： 从文本中提取所有符合模式的片段，.Execute 返回一个集合，其中每个 Match 都有 .Value 属性

Sub ExtractMatches()
    Dim re As Object, matches As Object, m
    Dim text As String

    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "\d+" '匹配一个或多个数字
    re.Global = True

    text = "订单编号: 12, 客户编号: 45, 数量: 67"

    Set matches = re.Execute(text)

    For Each m In matches '遍历所有匹配项
        Debug.Print "匹配项: " & m.Value
    Next
End Sub

4.3 批量替换：按模式替换字符串¶

用途： 按规则修改文本内容，比 Replace 更强、更灵活，比如以下代码输出 “dog dog catfish dog”

Sub ReplaceText()
    Dim re As Object
    Set re = CreateObject("VBScript.RegExp")

    re.Pattern = "\bcat\b" '匹配独立的单词"cat"
    re.Global = True

    Dim s As String
    s = "cat dog catfish cat" '包含多个"cat", 只有独立的"cat"会被替换

    Dim result As String
    result = re.Replace(s, "dog") '返回替换后的字符串

    Debug.Print result
End Sub

4.4 多行规则：行首行尾界定¶

在处理 Excel 单元格、批量说明文本时，字符串往往是“多行”的，而这时很多人会发现：明明正则写对了，却怎么也匹配不到？，根本原因通常只有一个：没有正确理解或启用 MultiLine 模式。

需要记得：

.MultiLine = False（默认）：只匹配整个字符串的开头 / 结尾
.MultiLine = True：匹配 每一行 的开头 / 结尾

例如我们看下面这个例子，在这里，文本部分代表的是：

ID:1001 Name:Tom

ID:1002 Name:Jerry

ID:1003 Name:Alice

这个是一个多行的内容，如果我们采用re.MultiLine = False，则这里只会提取到第一个1001，因为是按整个字符串提取的

Sub Demo_MultiLine_False()
    Dim re As Object, mc As Object, m
    Set re = CreateObject("VBScript.RegExp")

    re.Pattern = "^ID:(\d+)"
    re.Global = True
    re.MultiLine = False   ' 默认值

    Dim txt As String
    txt = "ID:1001 Name:Tom" & vbCrLf & _
          "ID:1002 Name:Jerry" & vbCrLf & _
          "ID:1003 Name:Alice"

    Set mc = re.Execute(txt)

    For Each m In mc
        Debug.Print m.SubMatches(0)
    Next
End Sub

而如果我们设置 re.MultiLine = True，就能将三个数字完整地都提取出来。

五、两大典型实战示例¶

在本节，我们看看两个经典的案例，比如在第一个例子中我们对选中的单元格逐个运行正则提取，提取邮箱

5.1 从单元格批量提取邮箱¶

Sub ExtractEmailsFromRange()
    Dim re As Object, mc As Object, m
    Dim r As Range, cell
    Set re = CreateObject("VBScript.RegExp")

    ' 匹配电子邮件地址的正则表达式
    re.Pattern = "\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}\b"
    re.Global = True

    ' 遍历选中的单元格，提取邮箱地址
    For Each cell In Selection
        If re.Test(cell.Value) Then
            Set mc = re.Execute(cell.Value)
            For Each m In mc
                Debug.Print "Found Email: " & m.Value
            Next
        End If
    Next
End Sub

5.2 删除字符串中的重复单词¶

我们在pattern中对组的表达可以是\1的形式，但是在具体方法中捕获，利用捕获组 $1 实现替换

Sub RemoveDuplicateWords()
    Dim re As Object
    Set re = CreateObject("VBScript.RegExp")

    ' 匹配重复单词的正则表达式
    re.Pattern = "\b(\w+)\b\s+\b\1\b"
    re.Global = True

    Dim s As String
    s = "hello hello world world"

    ' 替换重复单词为第一个单词
    Debug.Print re.Replace(s, "$1")
End Sub

VBA 本身并不原生支持正则表达式语法，但通过 VBScript RegExp 对象的引入，可以方便我们对复杂字符串的处理