スピル関数をVBAで挿入する方法

Excelのバージョンが365や2024等の新しいものではスピルという機能を利用した新しい関数使用可能です。

スピルという機能は『数式によって複数の値が生成され、その値が数式を入力したセルの隣のセルに配置されることです』

※当記事ではスピル機能を使用した関数をスピル関数と呼ぶことにします

従来の数式をセルに挿入する際にはFormulaプロパティを使用しますが、スピル関数を挿入する場合にはそれだとうまくいきません。

従来の関数をVBAでセルに挿入するときは、Formulaプロパティを使います。
たとえば、A1セルに=SUM(B1:B10)という数式を入れたいときは、このようなコードになります。

Sub 通常関数挿入()
    Range("A1").Formula = "=SUM(B1:B10)"
End Sub

では、スピル関数で同じようにFormulaプロパティを使うとどうなるか確認してみます。
たとえば、A1セルに=SEQUENCE(10)というスピル関数を入力します。
SEQUENCE関数は、指定した数だけ連番を作ってくれる関数です。

Sub スピル関数でFormulaを使用()
    Range("A1").Formula = "=SEQUENCE(10)"
End Sub

正しくSEQUENCE関数が動作していれば、B列と同じように１～１０の連番が表示されるのですが、１しか表示されていません。
入力された数式を確認すると『=@SEQUENCE(10)』という表記になっていて、マクロ内で指定した数式に『@』が追記されています。

この『@』マークを共通部分演算子といい、今回の例では『値が配列の場合は、左上の値を採用します』の機能が働いていると思われます。

ではVBAでスピル関数を正しく挿入するためにはどうすればいいか？
FormulaではなくFormula2プロパティを使用します。
先程のSEQUENCE関数をFormula2プロパティで挿入してみましょう

Sub スピル関数をFormula2で挿入()
    Range("A1").Formula2 = "=SEQUENCE(10)"
End Sub

このコードを実行すると、A1セルからA10セルに1から10までの連番が正しく表示されます。

スピル関数を上手に使用できるようになれば、今まで関数関連でややこしいコードになっていたものが簡潔に表現できるようになるかもしれません。

Excelが大好きだ！