Excel 2019およびOffice365では、スピル(動的配列数式)が使えるようになりました。
スピルとは「1つの式が複数値(配列)を返すときに、複数のセルに値を表示する」という機能になります。
試しに、引数に指定した範囲内から重複しない配列を返すUnique関数などを使ってみれば動きがわかりやすいです。
↓C1列にUnique関数を入れてみると…

↓以下のようにC1~C3列に値が設定されます。

このスピルを使うことで、今まではピポットテーブルなどを使わないと面倒だった集計表などが関数で作れるようになるかと思います。
また、今まで複雑なVBAを記載しなければいけなかったことについても、配列を返却する自作関数に置き換えることもできるようになるかと思います。
自作関数でスピルを使用する方法を記載してみたいと思います。
自作関数から配列を返却するだけでスピルを使用できる
結論から言うと、自作関数でスピルを使用するためには、返却値を配列にするだけでOKです。
以下に例を示します。引数で与えた値をもとに、VBAのSetterに使える文を返却する自作関数になります。
Public Function makeSetter(r As Range)
Dim ret(2) As String
ret(0) = "Public Property Set " & r.Value & "(ByVal _" & r.Value & " As String)"
ret(1) = " " & r.Value & " = _" & r.Value
ret(2) = "End Property"
makeSetter = ret
End Function
実行結果

上記では「ret(2)」というインデックス数が3の配列を返却しているので、3つのセルに値が設定されます。
(上記例ではC3~E5セルに値が設定されている)
スピルで複数セルに値が返却されるようになったことで、このようなコードの自動生成みたいなことが非常にやりやすくなったと思います。
スピルで設定されるセルの位置を操作する
返却値を縦(行)に展開する
前述した例ですと、スピルの返却値がC3~E5セルに設定されてしまいました。
しかし実際に使う上では、縦に値が展開されるようにしたほうが使い勝手が良いと思います。(C3~C5に値が展開されるようにしたい。)
その場合は、2次元配列を使用して以下のように設定します。
Public Function makeSetter(r As Range)
Dim ret(2, 0) As String
ret(0, 0) = "Public Property Set " & r.Value & "(ByVal _" & r.Value & " As String)"
ret(1, 0) = " " & r.Value & " = _" & r.Value
ret(2, 0) = "End Property"
makeSetter = ret
End Function
実行結果

二次元配列を返却値にした際の、1つ目のインデックスが行番号を表し、2つ目のインデックスが列番号を表しています。
今回の例では、2つ目のインデックスは常に0なのですべて同じ列に返されるという形になります。
返却値を表のように展開する
縦方向にも横方向にも展開するスピルを作成することも可能です。
これを使用することで表を返却する自作関数を作成することができます。
以下が作成例になります。九九の組み合わせ表を返却する自作関数です。
Public Function calcKuKu()
Dim ret(8, 8) As String
Dim i As Integer
Dim j As Integer
For i = 1 To 9
For j = 1 To 9
ret(i - 1, j - 1) = i * j
Next j
Next i
calcKuKu = ret
End Function
実行結果

引数に複数セルを指定できるようにする場合
ここまで紹介してきた例ですと、予め返却されるセルの数が決まっているため、配列の要素数も変数宣言時に設定していました。
しかし、実際には引数の内容によって配列の要素数を変更したい場合があります。
ReDim Preserveを使用して、都度配列拡張していくことは難しいことが多いので、予めどのくらい配列を使用するかを定義するしかないです。
(ReDim Preserveは2次元配列だと1つ目のインデックスは拡張できないため、都度拡張するのは面倒。)
以下が例になります。上の方で作成したSetterを作成する自作関数について、範囲指定で複数値を引数にできるようにしたものです。
Public Function makeSetter(r As Range)
Dim ret() As String
Dim i As Integer
ReDim Preserve ret(r.Cells.Count * 3 - 1, 0)
i = 1
For Each c In r
ret(3 * i - 3, 0) = "Public Property Set " & c.Value & "(ByVal _" & c.Value & " As String)"
ret(3 * i - 2, 0) = " " & c.Value & " = _" & c.Value
ret(3 * i - 1, 0) = "End Property"
i = i + 1
Next c
makeSetter = ret
End Function

Range.Cells.Countで自作関数が受け取る範囲のセルの個数を数えて、それによって配列のインデックス数を動的に定義しています。
まとめ
- Excel 2019およびOffice365では、スピルを使用することができます
- 自作関数の返却値を複数値にすることで、複数セルに値が設定されます
- 返却値を2次元配列にすることで、縦(行)方向に値を展開したり、表形式で値を展開したりできます
単独の列に返すだけならWorkSheetfunction.Transposeのほうが簡単です。