HOME > 即効テクニック > Excel VBA > セル操作関連のテクニック > 相対参照と絶対参照を変換する

即効テクニック

セル操作関連のテクニック

相対参照と絶対参照を変換する

(Excel 97/2000/2002/2003/2007)
セルに「=SUM(A1:A3)」のような数式が入力されていたとします。
この「A1:A3」を「$A$1:$A$3」に変換するにはどうしたらいいでしょう。
セルに入力されている数式はFormulaプロパティで取得できます。
文字列として取得して、先頭から1文字ずつチェックして・・・
う〜む、想像しただけでも面倒くさそうです。

VBAには、数式で使用しているセル参照を変換する機能が用意されています。
ApplicationオブジェクトのConvertFormulaメソッドです。
ConvertFormulaメソッドは、次の2種類の変換ができます。

(1)A1参照形式とR1C1参照形式の変換
(2)相対参照形式と絶対参照形式の変換

Excelの標準設定はA1参照形式ですし、R1C1形式を使う人はあまり多くありませんから、一般的には(2)の変換をする機会が多いと思います。

ConvertFormulaメソッドの書式は次の通りです。

ConvertFormula(Formula, FromReferenceStyle, ToReferenceStyle, _
        ToAbsolute, RelativeTo)
先頭の引数Formulaには、変換元の数式を指定します。 数式は先頭が「=」で始まる有効な数式でなければいけません。 2番目の引数FromReferenceStyleには、変換元の数式が「A1参照形式なのか、R1C1参照形式なのか」を、次の定数で指定します。 「A1参照形式とR1C1参照形式の変換」を行う機会は少ないと思います。 それよりも、A1参照形式のままで「相対参照形式と絶対参照形式の変換」を行うケースが多いでしょう。そのときに指定するのが、4番目の引数ToAbsoluteです。 相対参照のアドレスを絶対参照に変換するには、次のようにします。
ConvertFormula(Formula:=変換元の数式を指定します(必須), _
      FromReferenceStyle:=変換元の参照形式(A1/R1C1)を指定します(必須), _
      ToReferenceStyle:=参照形式(A1/R1C1)を指定します(必須), _
      ToAbsolute:=XlReferenceTypeクラスの定数を指定します, _
      RelativeTo:=今回は省略)
引数ToReferenceStyleは、ヘルプでは省略可能となっていますが、省略するとA1形式とR1C1形式が誤変換されることがありますので省略しないで指定した方がいいでしょう。 次のコードは、選択したセルの数式を、すべて絶対参照に変換します。
Sub Sample1()
  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 Sample2()
  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