相対参照と絶対参照を変換する
セルに「=SUM(A1:A3)」のような数式が入力されていたとします。この「A1:A3」を「$A$1:$A$3」に変換するにはどうしたらいいでしょう。セルに入力されている数式はFormulaプロパティで取得できます。文字列として取得して、先頭から1文字ずつチェックして・・・う～む、想像しただけでも無理がありそうです。「=SUM(A1:A3)」のような単純なアドレスなら力わざでも何とかなりますが、では「=VLOOKUP(AA1,DATA1,2,FALSE)」でしたらどうでしょう。ちなみに、"DATA1"はセル範囲につけた名前です。
VBAには、数式で使用しているセル参照を変換する機能が用意されています。ApplicationオブジェクトのConvertFormulaメソッドです。ConvertFormulaメソッドは、次の2種類の変換ができます。
- A1参照形式とR1C1参照形式の変換
- 相対参照形式と絶対参照形式の変換
Excelの標準設定はA1参照形式ですし、書籍・雑誌・スクールなどでR1C1形式を主体に教えているのは見たことがありません。したがって、ほとんどのExcelユーザーはA1参照形式を使用しているでしょうから「A1参照形式とR1C1参照形式の変換」のニーズは少ないと思います。ちなみに、次のようにします。
Sub Sample1()
Dim msg As String
msg = Application.ConvertFormula(Formula:=Range("B2").Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlR1C1)
MsgBox msg
End Sub
ConvertFormulaメソッドの書式は次の通りです。
ConvertFormula(Formula, FromReferenceStyle, ToReferenceStyle, ToAbsolute, RelativeTo)
| 引数 | 意味 |
|---|---|
| Formula | 変換元の数式を指定します |
| FromReferenceStyle | 変換元の参照形式を指定します |
| ToReferenceStyle | 変換後の参照形式(A1/R1C1)を指定します |
| ToAbsolute | 変換後の参照形式(相対参照/絶対参照)を指定します |
| RelativeTo | 相対参照の基点セルを指定します |
先頭の引数Formulaには、変換元の数式を指定します。数式は先頭が「=」で始まる有効な数式でなければいけません。2番目の引数FromReferenceStyleには、変換元の数式が「A1参照形式なのかR1C1参照形式なのか」を、次の定数で指定します。
| 定数 | 値 | 意味 |
|---|---|---|
| xlA1 | 1 | A1参照形式 |
| xlR1C1 | -4150 | R1C1参照形式 |
そして、もし、A1参照形式→R1C1参照形式や、R1C1参照形式→A1参照形式の変換をしたいのなら、3番目の引数ToReferenceStyleに、変換したい参照形式を表す定数を指定します。A1参照形式のままで、R1C1参照形式に変換したくない場合は、この引数を省略できるはずです。ヘルプにはそのように書かれています。省略すると、変換元の数式と同じ参照形式を返すそうです。