Office TANAKA - Excel VBA Tips[相対参照と絶対参照を変換する]

相対参照のアドレスを絶対参照に変換するには、次のように考えられます。

ConvertFormula(Formula:=変換元の数式を指定します(必須), _
               FromReferenceStyle:=変換元の参照形式(A1/R1C1)を指定します(必須), _
               ToReferenceStyle:=参照形式(A1/R1C1)を変更しないので省略, _
               ToAbsolute:=XlReferenceTypeクラスの定数を指定します, _
               RelativeTo:=今回は省略)

実際にやってみましょう。

Sub Sample2()
    Dim msg As String
    msg = Application.ConvertFormula(Formula:=Range("B2").Formula, _
                                     FromReferenceStyle:=xlA1, _
                                     ToAbsolute:=xlAbsolute)
    MsgBox msg
End Sub

変換後の参照形式を「A1形式にするかR1C1形式にするか」を指定する引数ToReferenceStyleを省略しています。省略したのですから、変換元の参照形式である「A1形式」のままで「A1:A3」が「$A$1:$A$3」に変換されるはずです。しかし、結果はR1C1形式に変換されてしまいました。

変換後の参照形式を「A1形式にするかR1C1形式にするか」を指定する引数ToReferenceStyleを省略すると、予期せぬ変換が行われることがあるようです。上のように、引数ToReferenceStyleを省略して、引数ToAbsoluteを指定すると、R1C1形式に変換されましたが、次のように引数ToAbsoluteも省略するとA1形式のままです。

Sub Sample3()
    Dim msg As String
    msg = Application.ConvertFormula(Formula:=Range("B2").Formula, _
                                     FromReferenceStyle:=xlA1)
    MsgBox msg
End Sub

しかし、元の数式がR1C1形式で入力されていた場合、上と同じように省略すると

Sub Sample4()
    Dim msg As String
    msg = Application.ConvertFormula(Formula:=Range("B2").Formula, _
                                     FromReferenceStyle:=xlR1C1)
    MsgBox msg
End Sub

今度はA1形式に変換されます。プロシージャSample2のように、R1C1形式になることもありますので、必ずしもA1形式が返るとも言えません。

結論として、引数ToReferenceStyleは省略しないというのが安全策ですね。どんなケースで何の引数を省略すると、どのように変換されてしまうのか・・・その法則を苦労して見つけるよりも、ヘルプには「省略できる」と書いてあるけれど「省略しない」と考えることが運用上はベターでしょう。省略しなければ、予期しない変換も行われないのですから。

次のコードは、選択したセルの数式を、すべて絶対参照に変換します。

Sub Sample5()
    Dim c As Range
    For Each c In Selection
        c.Formula = Application.ConvertFormula(c.Formula, xlA1, xlA1, xlAbsolute)
    Next c
End Sub

次のコードは、選択したセルの数式が、セル範囲D2:E4内のセルを参照しているとき、数式内のアドレスを絶対参照に変換します。

Sub Sample6()
    Dim c As Range, p As Range
    With Application
        For Each c In Selection
            For Each p In c.DirectPrecedents
                If Not .Intersect(p, Range("D2:E4")) Is Nothing Then
                    c.Formula = .ConvertFormula(c.Formula, xlA1, xlA1, xlAbsolute)
                End If
            Next p
        Next c
    End With
End Sub

関連項目
あるセルがセル範囲に含まれるかどうか