数式文字列-A1方式・R1C1方式-変換

Excelでは、セルの参照形式として、A1方式とR1C1方式があります。
A1方式で記述されている数式をR1C1方式の記述に変更するプロシージャと、その逆をするプロシージャを作ってみました。
実は、「条件付き書式の「条件」が数式の場合のカウント」のために作ったものです。

' # 数式文字列変換(A1→R1C1)                               #
'   argFmlA1    :i   数式文字列(A1)
'   argBaseAdls :i   基準セルアドレス
'   argFmlR1C1  :o   数式文字列(R1C1)
Public Function A1ToR1C1(ByVal argFmlA1 As String _
                       , ByVal argBaseAdls As String _
                       , ByRef argFmlR1C1 As String) As Boolean
    Dim aryD As Variant
    Dim wkF As String
    Dim i As Variant
    Dim V As Variant
    Dim j As Long
    Dim wkRowB As Long
    Dim wkColB As Long
    Dim wkExPos As Long
    Dim wkAdlsA As String
    Dim wkSheetA As String
    Dim rngA As Range
    Dim wkRowA As Long
    Dim wkColA As Long

    wkRowB = Range(argBaseAdls).Row
    wkColB = Range(argBaseAdls).Column
    aryD = Array("=", "<", ">", "(", ")", "+", "-", "*", "/", "^", "&", " ", ":", ",")
    wkF = argFmlA1
    For Each i In aryD
        wkF = Replace(wkF, i, vbTab & i & vbTab)
    Next i

    V = Split(wkF, vbTab)
    For j = LBound(V) To UBound(V)
        ' シート参照の場合、シート部分とセル部分に分割する
        wkExPos = InStrRev(V(j), "!")
        If wkExPos = 0 Then
            wkSheetA = ""
            wkAdlsA = V(j)
        Else
            wkSheetA = Left$(V(j), wkExPos)
            wkAdlsA = Mid$(V(j), wkExPos + 1)
        End If
        ' セルかどうか判別する
        Set rngA = Nothing
        On Error Resume Next
        Set rngA = Range(wkAdlsA)
        On Error GoTo 0
        ' セルの場合、R1C1形式に変換する
        If Not rngA Is Nothing Then
            wkRowA = rngA.Row
            wkColA = rngA.Column
            V(j) = "R[" & (wkRowA - wkRowB) _
                 & "]C[" & (wkColA - wkColB) & "]"
            V(j) = wkSheetA & Replace(V(j), "[0]", "")
        End If
    Next j

    argFmlR1C1 = Replace(Join(V, vbTab), vbTab, "")
    A1ToR1C1 = True
End Function

' # 数式文字列変換(R1C1→A1)                               #
'   argFmlR1C1  :i   数式文字列(R1C1)
'   argBaseAdls :i   基準セルアドレス
'   argFmlA1    :o   数式文字列(A1)
Public Function R1C1ToA1(ByVal argFmlR1C1 As String _
                       , ByVal argBaseAdls As String _
                       , ByRef argFmlA1 As String) As Boolean
    Dim aryD As Variant
    Dim wkF As String
    Dim i As Variant
    Dim V As Variant
    Dim j As Long
    Dim wkRowB As Long
    Dim wkColB As Long
    Dim wkExPos As Long
    Dim wkAdlsA As String
    Dim wkSheetA As String
    Dim wkTxt As String
    Dim wkAdlsRowFlg As Boolean
    Dim wkAdlsColFlg As Boolean
    Dim wkRowA As Long
    Dim wkColA As Long

    wkRowB = Range(argBaseAdls).Row
    wkColB = Range(argBaseAdls).Column
    aryD = Array("=", "<", ">", "(", ")", "+", "-", "*", "/", "^", "&", " ", ":", ",")
    wkF = argFmlR1C1
    For Each i In aryD
        wkF = Replace(wkF, i, vbTab & i & vbTab)
    Next i
    V = Split(wkF, vbTab)
    For j = LBound(V) + 2 To UBound(V) - 0
        If Right(V(j - 2), 1) = "[" And V(j - 1) = "-" And InStr(1, V(j), "]") > 0 Then
            V(j) = V(j - 2) & V(j - 1) & V(j)
            V(j - 2) = ""
            V(j - 1) = ""
        End If
    Next j

    For j = LBound(V) To UBound(V)
        ' シート参照の場合、シート部分とセル部分に分割する
        wkExPos = InStrRev(V(j), "!")
        If wkExPos = 0 Then
            wkSheetA = ""
            wkAdlsA = V(j)
        Else
            wkSheetA = Left$(V(j), wkExPos)
            wkAdlsA = Mid$(V(j), wkExPos + 1)
        End If
        ' セルかどうか判別し、行・列の番号を求める
        wkAdlsRowFlg = False
        wkAdlsColFlg = False
        If wkAdlsA Like "R*C*" Then
            wkTxt = Mid$(wkAdlsA, 2, InStr(1, wkAdlsA, "C") - 2)
            If wkTxt = "" Then
                wkAdlsRowFlg = True
                wkRowA = wkRowB
            ElseIf IsNumeric(wkTxt) Then
                wkAdlsRowFlg = True
                wkRowA = CLng(wkTxt)
            ElseIf Left(wkTxt, 1) = "[" And Right(wkTxt, 1) = "]" Then
                wkTxt = Mid(wkTxt, 2, Len(wkTxt) - 2)
                If IsNumeric(wkTxt) Then
                    wkAdlsRowFlg = True
                    wkRowA = CLng(wkTxt) + wkRowB
                End If
            End If
            wkTxt = Mid$(wkAdlsA, InStr(1, wkAdlsA, "C") + 1)
            If wkTxt = "" Then
                wkAdlsColFlg = True
                wkColA = wkColB
            ElseIf IsNumeric(wkTxt) Then
                wkAdlsColFlg = True
                wkColA = CLng(wkTxt)
            ElseIf Left(wkTxt, 1) = "[" And Right(wkTxt, 1) = "]" Then
                wkTxt = Mid(wkTxt, 2, Len(wkTxt) - 2)
                If IsNumeric(wkTxt) Then
                    wkAdlsColFlg = True
                    wkColA = CLng(wkTxt) + wkColB
                End If
            End If
        End If
        ' セルの場合、A1形式に変換する
        If wkAdlsRowFlg And wkAdlsColFlg Then
            V(j) = wkSheetA & Cells(wkRowA, wkColA).Address(False, False)
        End If
    Next j

    argFmlA1 = Replace(Join(V, vbTab), vbTab, "")
    R1C1ToA1 = True
End Function

両方ともあまり完成度の高くないコードなので、数式次第では期待した結果を返さない場合があります。
例えば、数式内に文字列があり、それがセルアドレスとaryDで使っている区切りで構成される場合などがそうです。