セルに数式を入力する

このセクションではマクロでセルに数式を入力する方法を解説します。
現実にはマクロでセルに数式・関数・セル参照式を入力することは少ないと思います。
マクロのプログラムの中で検索や計算を行って結果のみをセルなどへ返すことができるからです。
しかしながら、マクロでセルへ数式を入力する機会もまったくないわけではありませんし、また、Formulaプロパティ、FormulaR1C1プロパティを解説する良い題材でもあるので、ここで取り上げたいと思います。

数式を入力する

◆マクロで記録してみると・・・・

「数式を入力する」マクロの書き方を説明する前に、「数式を入力する」動作をマクロに記録した場合を見てみましょう。
下記はアクティブなセルに「=B5」と言うセル参照式を入力する動作を記録したものです。
ActiveCell.FormulaR1C1 = "=R[4]C[1]"
「アクティブセルのFormulaR1C1プロパティに"=R[4]C[1]"を代入する」と言う意味になります。 ここで不思議に思う人が多いのは、"=B5"と入力したのに"=R[4]C[1]"になってしまっている事でしょう。なぜこのような記録のされ方をするのでしょうか。
数式を入力する際に良く使われるFormulaR1C1プロパティとFormulaプロパティを比較してみましょう。

◆FormulaR1C1プロパティ

セルの中に数式を入力する動作を記録するとFormulaR1C1プロパティが適用されます。
つまり、数式がR1C1形式で表現されます。
これは、以前は主流だったR1C1形式のセル参照の名残だともいえますが、相対参照の数式を記録する際にはR1C1形式での記述が有効なせいでもあります。
普段EXCELでA1形式を見なれている人には少し見にくいかもしれません。
R1C1のR、Cはそれぞれ　R　=　Row（行）　、C　=　Column（列）の略です。つまり、「セルA1をR1C1と表現する」形式です。
通常EXCELで使用するA1形式とは列と行を表現する順番が逆になります。
また、同じセルを表現するにもA1形式とはだいぶ違った書き方になります。
下記表はマクロコードとそれを実行したときの実際にセルに入力される数式の対比です。

セルA1選択時にセルB5への参照式を入力
マクロコードセルに入力される数式
Selection.FormulaR1C1 = "=R[4]C[1]"=B5
Selection.FormulaR1C1 = "=R5C2"=$B$5
Selection.FormulaR1C1 = "=R[4]C2"=$B5
Selection.FormulaR1C1 = "=R5C[1]"=B$5
相対参照式を入力する場合は[　]で囲まれた数値で入力するセルからの列、行の相対位置が表現されます。「R[4]C[1]」の場合は「（A1から）4行下1列右」のセルと言うことです。 マクロ実行時に選択したセルが違ってくると、相対参照式が入力されるので「（選択セルから）4行下1列右」のセルを参照する数式が入力されます。
絶対参照式を入力する「R5C2」の場合は「（ワークシートの）5行目の2列目」であり、これは入力したセルによって変化することはありません。

◆Formulaプロパティ

セルに数式を入力するには通常セルのFormulaプロパティに文字列としての数式を代入します。
Formulaプロパティは値の取得、代入が可能です。
また、Formulaプロパティは「数式バー」に表示される状態を表現しています。
参照式「=B5」を選択セルへ入力するには次のように記述します。
Selection.Formula = "=B5"
また次のように記述しても同じ結果になります。
Selection = "=B5"
Selection.Value = "=B5"
この場合、Formulaプロパティを省略した場合は暗黙的にFormulaプロパティに代入されるわけです。
また、Valueプロパティに代入することによっても同じ結果が得られます。

◆FormulaプロパティとFormulaR1C1プロパティの違い

一見、FormulaプロパティとFormulaR1C1プロパティは同じ動作を違った記述の仕方で行っているように見えますが、では、実際にはどのような場合に使い分けをするのでしょう？
下の2つのマクロをセルA1を選択した状態で実行してみてください。
セルに数式を入力するマクロ 
	Sub 数式入力A1（）
	Selection.Formula = "=B5"
	End Sub
	
	Sub 数式入力R1C1()
	Selection.FormulaR1C1 = "=R[4]C[1]"
	End Sub
これら二つのマクロをセルA1を選択状態で実行した場合、どちらも「=B5」が入力されます。
では、セルA5を選択した状態で2つのマクロをそれぞれ実行して、入力されている参照式を確認してみてください。 実行結果は次のようになります。
セルA5選択時の実行結果
マクロ名セルA5に入力される数式
数式入力A1=B5
実行結果はセルA1選択時と同じになります。 つまり、ワークシートのどのセルを選択していても必ず「=B5」というセル参照式が入力されます。
数式入力R1C1=B9
実行結果は「（選択セルから）4行下1列右」のセルを参照する数式が入力されます。 つまり、ワークシートのどのセルを選択していても必ず「相対位置の同じ」セルへの参照式が入力されます。
以上のようにセルの位置にかかわらず数式の参照するセルを固定する場合はFormulaプロパティ、参照セルの相対位置を同じにしたい場合はFormulaR1C1プロパティでの記述が便利です。

◆複数セルに同じ相対参照の数式を入力

複数セルへ数式入力するマクロ 
	Sub 複数セルへ数式入力()
	Range("A1:C1").Select
	Selection.FormulaR1C1 = "=R[4]C[1]"
	End Sub
	Sub
このサンプルでは「A1:C1」のそれぞれのセルに「=B5」「=C5」「=D5」という参照式を入力します。このように、参照の相対位置関係の同じ参照式を複数入力する場合ではFormulaR1C1プロパティはたいへん便利です。
これをA1形式で実現すると変数を使って、入力する文字列同士を演算子でくっつけ･･･と結構おおごとになってしまいます。
また、FormulaR1C1プロパティを使って、他のセルに入力されている数式の参照関係を維持したまま別のセルに数式を入力することも出来ます。
他のセルの相対参照式を入力 
	Sub 別のセルの数式入力R1C1()
	Range("A10").Select
	Selection.FormulaR1C1 = Range("A1").FormulaR1C1
	End Sub
この場合、セルA1に「=B5」が入力されていた場合、セルA10には「=B14」が入力されます。
つまり、セルをコピーして貼り付けた場合と数式の結果が同じになります。
これに対して、Formulaプロパティを使用した場合は結果が大きく異なります。
他のセルの参照式をそのまま入力 
	Sub 別のセルの数式入力A1()
	Range("A10").Select
	Selection.Formula = Range("A1").Formula
	End Sub
この場合、セルA1に「=B5」が入力されていた場合、セルA10にも「=B5」が入力されます。
つまり、数式バーに表示された文字列をコピーして貼り付けた場合と数式の結果が同じになります。

このようにFormulaプロパティとFormulaR1C1プロパティでは実行結果に大きな差がありますのでそれぞれの特徴を理解した上で使い分けしてください。

マクロなお部屋に戻る 　
SEO [PR] 爆速!無料ブログ 無料ホームページ開設 無料ライブ放送