セルに「=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参照形式に変換したくない場合は、この引数を省略
できるはず です。ヘルプにはそのように書かれています。省略すると、変換元の数式と同じ参照形式を返すそうです。
