スキルアップ・問題解決なら、モーグにおまかせ！

セルに「=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