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

devtools/2024/10/15 20:17:37/

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

在这里插入图片描述

示例代码如下。

Sub RegExpDemoReplace()Dim Res()Dim objRegEx As ObjectDim objMH As Object, sRef As StringDim j As Integer, i As Long, sFormula As StringSet objRegEx = CreateObject("vbscript.regexp")objRegEx.Pattern = "([A-Z]{1,3}\d{1,7})"objRegEx.Global = TrueDim lastRow As Long, arrDatalastRow = Cells(Rows.Count, "I").End(xlUp).RowarrData = Range("I6:I" & lastRow).FormulaFor i = 1 To UBound(arrData)If Left(arrData(i, 1), 1) = "=" ThensFormula = arrData(i, 1) & " "Set objMH = objRegEx.Execute(sFormula)If objMH.Count > 0 ThenFor j = 0 To objMH.Count - 1sRef = objMH(j).submatches(0)sFormula = Replace(sFormula, sRef, Range(sRef))NextarrData(i, 1) = "'" & sFormulaEnd IfEnd IfNext[K6].Resize(UBound(arrData), 1).Value = arrDataSet objRegEx = NothingSet objMH = Nothing
End Sub

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