【ExcelVBA】スピル数式をVBAから入力する - Formula2

スピル数式をVBAから入力する方法を解説します。

RangeオブジェクトのFormula2プロパティを使用します。

セル数式をVBAから入力する場合は、通常Formulaプロパティを使用します。

Range("A1:A5").Formula = "=B1+C1"

　
Formulaプロパティの詳しい使い方についてはこちらを参照ください。

さてこのFormulaプロパティですが、
スピル式を入力した場合は以下のような実行結果になります。

Range("B1").Formula = "=UNIQUE(A:A)"

Formulaプロパティの実行例

本来はB5までスピルするはずの数式がB1セルだけに留まっており、
よく見ると数式の先頭に「@」マークが挿入されています。

この「@」は(暗黙的)共通部分演算子と呼び、
スピル式やテーブルの構造化参照式を単行だけに限定する演算子です。

要はスピルやテーブルの「複数行へ反映」機能をOFFにする演算子ですね。

なぜこれが入るかというと、スピル以前のExcelと互換性を保つためで、
昔のマクロを実行した際に挙動が変わらないようこうなっています。

昔のVBAは入力セル以外には影響を及ぼさなかったわけですからね。

さて普通に入力してもスピルしてくれないFormulaプロパティですが、
スピルさせる方法は簡単で、単にFormula2プロパティを使用すればよいです。

Range("B1").Formula2 = "=UNIQUE(A:A)"

Formula2プロパティの実行例

しっかりスピルしてくれていますね。

このスピルの有無以外の仕様は全く同じですので、
単純にFormulaをFormula2に書き換えるだけでOKです。

VBAからスピル式を入力する場合はこのプロパティを使用してください。

Formulaプロパティがシート数式に「@」を挿入する仕様は、
旧版Excelのマクロと互換性を持たせるためと説明しました。

同じく互換性を持たせるために機能が追加されたメソッドとして、
FindメソッドとReplaceメソッドの二つがあります。

まずFindメソッドですが、検索対象「LookIn」を「数式」とした際、
従来の「xlFormulas」ではスピル部分が検知されません。

旧版エクセルでは第1セルしか検知していなかったわけですからね。

スピルした部分も検索結果に含みたい場合は、
LookInに「xlFormulas2」を指定する必要があります。

続いてReplaceメソッドですが、
こちらは「置換した結果スピル式になる」ことがあります。

このとき、今までのコードをそのまま実行した場合と互換性を持たせるために、
Formula2の時と同様、共通部分演算子「@」が挿入されます。

Replaceをした結果スピルしないようになっているわけですね。

これをちゃんとスピルさせたい場合は、Replaceメソッドの新引数である、
FormulaVersionに「xlReplaceFormula2」を渡す必要があります。

スピル式をFindで探したり、スピル式をReplaceで修正したいとき、
VBAから実行するとうまくいかない場合はこの2つの引数をまず確認してください。

和風スパゲティのレシピ