VBAマクロから複数の値を返してExcelのセルに設定したい

Question

マクロで複数の値を取得し、Excelでそれらをそれぞれ別のセルに設定しようとしています。ネットで調べたら配列で返せばいいと書かれていてとりあえず以下のコードで実現できました。

Function SumAvg(範囲 As Range)
  Dim result(1, 0) As Double
  cnt = 0
  For Each myCell In 範囲
    cnt = cnt + 1
    result(0, 0) = result(0, 0) + myCell.Value
  Next
  result(1, 0) = result(0, 0) / cnt
  SumAvg = result
End Function

SumAve(範囲)

ですが、これだと設定先のセルが限定され、また異なるデータ型の値を同時に返せないと考え、参照渡しの引数を必要な数だけ用意してみました。ところが、それだとExcelで指定したセルに期待する値が設定されません。

Function SumAvg(範囲 As Range, ByRef sum As Integer, ByRef ave As Single)
  cnt = 0
  For Each myCell In 範囲
    cnt = cnt + 1
    sum = sum + myCell.Value
  Next
  ave = sum / cnt
End Function

SumAve(範囲, 合計セル, 平均セル)

今回やりたいことは配列渡しでも対応できる仕様ですが、今後の拡張性を考慮して複数の参照渡しでも実現できるようにしておきたいです。

Office 365を使用しているためExcelのバージョンは不明です。(アカウント情報に2108と書かれているけど、これのことですか？)

Answer 1

異なるデータ型の返却の方は、以下のようにしてはどうでしょうか。
ただ、返却先のセルを指定するのは難しいと思います。
できるとしたら、縦横どちらにスピルするかの指定くらいではないかと思います。

Function SumAvg(範囲 As Range, Optional 縦 As Boolean = True)
  Dim cnt, mycell
  
  Dim s As Integer
  Dim a As Single
  
  cnt = 0
  For Each mycell In 範囲
    cnt = cnt + 1
    s = s + mycell.Value
  Next
  a = s / cnt
  Dim result
  result = Array(s, a)
  
  If 縦 Then
    result = WorksheetFunction.Transpose(Array(s, a))
  Else
    result = Array(s, a)
  End If
  
  SumAvg = result

End Function

Comments

[meguru0411]

回答ありがとうございました。
質問のメインは複数のByRefで任意のセルに値を設定することでしたが、どうやらそれに対する解決策は無さそうなので、貴方の回答をベストアンサーにして解決済みとさせていただきます。