VBA之正则表达式（2）-- 批量修改公式

最新推荐文章于 2023-04-10 23:07:38 发布

原创最新推荐文章于 2023-04-10 23:07:38 发布 · 1.1w 阅读

49 ·

CC 4.0 BY-SA版权

文章标签：

#正则 #VBA #公式 #替换 #引用

VBA 同时被 3 个专栏收录

325 篇文章

订阅专栏

Excel

165 篇文章

订阅专栏

正则

52 篇文章

订阅专栏

本文介绍如何使用VBA和正则表达式处理Excel单元格引用，通过在引用前添加工作表名称，实现公式或数据的跨工作表引用。详细解析了正则表达式的匹配模式及VBA代码实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

实例需求：数据保存在F列（公式，判断条件，whatever），需要在每个单元格引用之前添加A列指定的工作表名称+！，结果如H列所示，对于F16中的公式，由于I49已经指定工作表，所以此单元格不需要再处理，核心问题是如何定位单元格引用。

示例代码如下。

Sub RegExpDemoReplace()
    Dim Res()
    Dim objRegEx As Object
    Dim objMH As Object
    Dim j As Integer
    Set objRegEx = CreateObject("vbscript.regexp")
    objRegEx.Pattern = "[^!]([A-Z]{1,3}\d{1,7})"
    objRegEx.Global = True
    For i = 1 To Cells(Rows.Count, "F").End(xlUp).Row
        form = " " & Cells(i, "F")
        pre = Cells(i, 1) & "!"
        Set objMH = objRegEx.Execute(form)
        If objMH.Count > 0 Then
            newform = form
            For j = 0 To objMH.Count - 1
                ref = objMH(j).submatches(0)
                newform = Replace(newform, ref, pre & ref)
            Next
            Cells(i, "I") = Trim(newform)
        End If
    Next
    Set objRegEx = Nothing
    Set objMH = Nothing
End Sub

【代码解析】
第6行代码使用后期绑定创建正则对象。
第7行代码指定正则匹配字符串，这是应用正则表达式的核心。本文仅对匹配模式进行简单讲解，如果需要系统的学习正则，可以自行搜索相关学习材料。

匹配模式	含义
[^!]	匹配除了叹号之外的一个字符，用于剔除已经指定工作表的单元格引用
[A-Z]{1,3}	1个至3个大小字母，匹配列标A到XFD，这里不是严格的匹配，例如ZZZ也可以被匹配，但是这不是合法的单元格引用
\d{1,6}	1位至6位数字，匹配行号，同上也不是严格的匹配
([A-Z]{1,3}\d{1,7})	圆括号代表提取匹配的字符组，也就是单元格引用

第8行代码设置为全局搜索模式。
第9行代码第21行代码循环处理工作表中的数据。
第10行代码在数据前添加一个空格，确保第一个单元格引用可以被正则匹配成功。
第11行代码将添加的工作表名字符串保存在变量中。
第12行代码执行正则匹配，返回结果为MatchCollection集合对象。

MatchCollection集合对象有两个只读属性。