VBAで数式や関数を入力するには

VBAで数式や関数を入力する場合には、セルを示すRangeオブジェクトのFormulaプロパティを使います。

例えば、A3セルにSUM関数を設定したい場合は以下のように書きます。

書き方で重要な点が２つあります。

１つ目は、Excelで数式を入力する場合と同様に、先頭に”=”を入れる必要があります。
「”=SUM(A1:A2)”」

２つ目は、設定する数式をダブルクォーテーションで囲って文字列にしなければなりません。
「“=SUM(A1:A2)“」

このルールはどのような数式であっても同じです。

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

数式を設定するプロパティには、Formulaプロパティの他に、FormulaR1C1プロパティもあります。この２つの違いは、セルをどのように参照するか、の違いです。

以下のコードはどちらもA3セルに”=SUM(A1:A2)”を設定しています。

Formulaプロパティでの書き方

Formulaプロパティは、Excelで数式を入力する場合と同じ書き方です。そのため、一度Excelの数式バーで書いてそれをそのままコードにコピペしてもOKです。セルの座標を指定する場合は「A1」のように指定して書きます。セル範囲の場合であれば「A1:B3」のように書きます。

FormulaR1C1プロパティでの書き方

FormulaR1C1プロパティは、セルの座標を「”R1C1″」と書きます。Row（行）、Column（列）の順に基準となるセルからどれだけ離れた場所のセルかを指定します。

見ての通りFormulaR1C1プロパティはわかりにくく、Formulaプロパティの方が使いやすいです。


コードの内容とExcel上の数式の表示の仕方

実行した結果の数式は、Excelのオプションの「数式」→「数式の処理」→「R1C1参照形式を使用する」にチェックが入っていれば、R1C1形式、チェックが無ければA1形式で表示されます。通常はチェックが付いていないため、A1形式で表示されます。

そのため、FormulaプロパティとFormulaR1C1プロパティのどちらでコードを書いても、Excel上に表示される数式は「R1C1参照形式を使用する」チェックに従うため、コードとは異なる表示になることがあります。

スピルでの数式入力（Formula2、Formula2R1C1）

RangeオブジェクトにはFormula2プロパティとFormula2R1C1プロパティという「2」が付くプロパティがあります。これらは「スピル」として入力する場合に利用します。「2」とかはやめてくれ、と思いますが命名がおかしいのは仕様です。

以下のコードは、B1セルでA1からA3セルを参照するスピルを設定するコードです。B1セルで実行するため、スピル機能によりB2セルとB3セルにも反映されます。B1セルがA1セル、B2セルがA2セル、B3セルがA3セルを参照するようになります。

Formula2プロパティでの書き方

Formula2R1C1プロパティでの書き方

「スピル」とは、セルに書いた数式を他のセルでも適用されるように書くことができる機能のことです。適用範囲は「スピルが設定されている状態のセル」として扱われ、セルとしての扱いも数式の書き方も旧来とは異なります。

スピルは使い方を覚えれば便利な機能ではありますが、Excel2019（Office365）から実装された新しい機能であるため認知度も低く、知らない人が見ると何をやっているのか分からないという欠点があります。また、表形式のデータでないと利用する機会がなく、従来できていたことができなくなるなど使い勝手の悪い点もあります。

スピルは必須機能かと言われるとそうではありません。スピルを使った方がよいと判断した場合にのみ使うようにして、そうでない場合は旧来の書き方の方がよいと思われます。