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

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

VBA
1Function SumAvg(範囲 As Range)
2  Dim result(1, 0) As Double
3  cnt = 0
4  For Each myCell In 範囲
5    cnt = cnt + 1
6    result(0, 0) = result(0, 0) + myCell.Value
7  Next
8  result(1, 0) = result(0, 0) / cnt
9  SumAvg = result
10End Function

Excel
1SumAve(範囲)

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

VBA
1Function SumAvg(範囲 As Range, ByRef sum As Integer, ByRef ave As Single)
2  cnt = 0
3  For Each myCell In 範囲
4    cnt = cnt + 1
5    sum = sum + myCell.Value
6  Next
7  ave = sum / cnt
8End Function

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

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

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

行動規範の内容に同意します

回答1件

ベストアンサー

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

vba
1Function SumAvg(範囲 As Range, Optional 縦 As Boolean = True)
2  Dim cnt, mycell
3  
4  Dim s As Integer
5  Dim a As Single
6  
7  cnt = 0
8  For Each mycell In 範囲
9    cnt = cnt + 1
10    s = s + mycell.Value
11  Next
12  a = s / cnt
13  Dim result
14  result = Array(s, a)
15  
16  If 縦 Then
17    result = WorksheetFunction.Transpose(Array(s, a))
18  Else
19    result = Array(s, a)
20  End If
21  
22  SumAvg = result
23
24End Function