機能と数式 | VBA | セミナー | オンラインソフト | お問い合わせ | その他
Top > Excel > VBA

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



セルに「=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種類の変換ができます。

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

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






このエントリーをはてなブックマークに追加