VBA之正则表达式（47）-- 快速将公式转换为静态值计算

实例需求：工作表I列包含多种计算公式，为了便于演示，将I列公式显示在J列单元格中，现在需要将公式的单元格引用转换为静态值，如K列所示。

示例代码如下。

Sub RegExpDemoReplace()
    Dim Res()
    Dim objRegEx As Object
    Dim objMH As Object, sRef As String
    Dim j As Integer, i As Long, sFormula As String
    Set objRegEx = CreateObject("vbscript.regexp")
    objRegEx.Pattern = "([A-Z]{1,3}\d{1,7})"
    objRegEx.Global = True
    Dim lastRow As Long, arrData
    lastRow = Cells(Rows.Count, "I").End(xlUp).Row
    arrData = Range("I6:I" & lastRow).Formula
    For i = 1 To UBound(arrData)
        If Left(arrData(i, 1), 1) = "=" Then
            sFormula = arrData(i, 1) & " "
            Set objMH = objRegEx.Execute(sFormula)
            If objMH.Count > 0 Then
                For j = 0 To objMH.Count - 1
                    sRef = objMH(j).submatches(0)
                    sFormula = Replace(sFormula, sRef, Range(sRef))
                Next
                arrData(i, 1) = "'" & sFormula
            End If
        End If
    Next
    [K6].Resize(UBound(arrData), 1).Value = arrData
    Set objRegEx = Nothing
    Set objMH = Nothing
End Sub

【代码解析】
第7行代码创建正则对象。
第8行代码设置正则匹配模式。
第9行代码设置正则全局匹配。
第11行代码获取最后数据行的行号。
第12行代码将I列公式保存在数组中。
第14行代码判断单元格中是否包含公式。
第15行代码执行正则匹配。
第17行代码判断是否匹配成功。
第18~21行代码循环处理每个匹配结果。
第19行代码读取匹配结果的子匹配。
第20行代码将单元格引用替换为静态值。
第22行代码将更新后的公式写入数组。
第26行代码公式写入工作表K列，当然此处也可以用于更新I列的公式。
第27~28行代码清空对象变量，释放系统资源。

---

请注意代码不适用如下几种场景：

1. 公式中使用跨工作表引用，代码中Range(sRef)只能获取活动工作表引用单元格对应的静态值。
2. 公式中存在多单元格引用区域，例如SUM(A1:C3)
3. 单元格引用地址存在部分重复，例如公式 =A1+A100，需要优化代码首先替换A100，然后再替换A1，否则无法得到正确结果