VBA之正则表达式（19）-- 相对引用转绝对引用-白红宇

VBA之正则表达式（19）-- 相对引用转绝对引用

阅读量：272 次

发布时间：2019-03-03

本文共 1107 字，大约阅读时间需要 3 分钟。

大家都知道Excel公式中使用的单元格引用有两种形式：相对引用（如A1）和绝对引用（如$A$1）两种形式，在输入公式按可切换两种引用形式。对于已有的公式，如果需要变更引用形式，逐个手工修改有些费劲，而且可能会导致人为错误，使用正则表达式功能，可以完成公式的转换。

工作表中B列已经设置公式，为了便于查看将公式显示于C列表，可以看出公式中既有相对引用，也有绝对引用，如下图所示。

示例代码如下。

Sub ChangeFormulaRef()    Dim objRegEx As Object    Dim objCell As Range    Dim strFormula As String    Set objRegEx = CreateObject("vbscript.Regexp")    objRegEx.Pattern = "([A-Z]{  1,3})(\d{  1,7})"    objRegEx.Global = True    For Each objCell In ActiveSheet.UsedRange.Columns(2).Cells        If objCell.HasFormula = True Then            strFormula = objCell.Formula            strFormula = objRegEx.Replace(strFormula, "$$$1$$$2")            objCell.Formula = strFormula        End If    Next    Set objRegEx = Nothing    Set objCell = NothingEnd Sub

运行示例代码，结果如下图所示，公式中的相对引用转换为绝对引用。

【代码解析】

第5行代码创建正则表达式对象。

第6行代码设置匹配模式。

匹配模式	含义
[A-Z]{1,3}	1个至3个大小字母，匹配列标A到XFD，这里不是严格的匹配，例如ZZZ也可以被匹配，但是这并不是合法的单元格列标
\d{1,6}	1位至6位数字，匹配行号，同上也不是严格的匹配

第8~14行循环遍历处理第2列的单元格。

第9行代码判断单元格是否包含公式。

第10行代码提取单元格公式。

第11行使用正则替换变换公式字符串。

匹配模式	含义
$$	是转义的$
$1	代表第一个捕获组的字符内容，即单元格引用中的列标
$2	代表第二个捕获组的字符内容，即单元格引用中的行号

第14行代码更新公式。

第15和16行代码释放对象变量的系统资源。

转载地址：http://xkjl.baihongyu.com/

你可能感兴趣的文章