VBA之正規表達式（19）-- 相對引用轉絕對引用

大家都知道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 = Nothing
End 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行代碼釋放對象變量的系統資源。