ExcelVBA Evaluateでシート関数に…

質問

以下のもので
…(1)において
「型が違う」
と、お叱りを受けます。

何処が間違えていますでしょうか？
お教えください。

Function Average(リスト As Variant) As Variant
　　Average = Evaluate("AVERAGE(" & リスト & ")")　　…(1)
End Function

Function testのMain(θ1 As Variant, θ2 As Variant) As Variant
Dim Temp1 As Variant
　　Temp1 = Average(Array(θ1, θ2))
End Function

前は確か出来ていた
だけに
出来ない
のが、口惜しい
です

資料もマシン崩壊に伴い消失
なんともはや…

回答No.3

chie65536（@chie65535） · ベストアンサー

＞て、事は
＞Average = Evaluate("AVERAGE(" & CStr(リスト) & ")")
＞ですかね？

Variant型の動的配列には、CStr()は使えません。上記のようにしても「型が一致しません」が出ます。

わざわざforループで回して、いちいち面倒臭く文字列変数に文字列を作っているのは「一気にCStrで変換出来ないから」です。

また、CStrは「単一の変数を文字列として関数に渡す場合」に使用し、普段は使いません。

・単一の変数を文字列として関数に渡す場合
Dim val As Variant
Dim ans As Variant
val = cells(1,1) '中身はどんな型か判らない
'そのまま「MyFunction(val)」では「文字列になってくれない」のでCStrが必要。
ans = MyFunction(CStr(val)) 'MyFunctionは引数に文字列型を要求する

・CStrが不要な場合
Dim val As Variant
Dim ans As Variant
val = cells(1,1) '中身はどんな型か判らない
'文字列定数に連結し段階で「式が文字列になる」ので、CStrは不要
ans = MyFunction("(" & val & ")") 'MyFunctionは引数に文字列型を要求する

「&演算子」で連結する場合、CStrが無くても正しく文字列に変換されるし、CStrは「呼び出すと処理が重い」ので、式に&演算子を含む場合は、CStrは呼ばなくても良いです。

お礼

有り難うございます。
そうだったのですね

回答No.2

chie65536（@chie65535）

ど～しても「自作関数でやりたい」という場合は、以下のようにします。

Function Average(リスト As Variant) As Variant
Dim Str As String
Dim i As Integer
For i = LBound(リスト) To UBound(リスト) - 1
Str = Str & リスト(i) & ","
Next
Str = Str & リスト(UBound(リスト))
Average = Evaluate("AVERAGE(" & Str & ")")
End Function

お礼

あっ！！
判りました

確かに型が違いますね
テキスト型にしないと駄目
なのですね
(数値でも渡せた
と、思ってた
の、ですが…)

て、事は
Average = Evaluate("AVERAGE(" & CStr(リスト) & ")")

ですかね？

有り難うございます。

回答No.1

chie65536（@chie65535）

以下
Function Average(リスト As Variant) As Variant
　　Average = Evaluate("AVERAGE(" & リスト & ")")　　…(1)
End Function
の３行を削除して

Temp1 = Average(Array(θ1, θ2))
の代りに
Temp1 = Application.WorksheetFunction.Average(θ1, θ2)
として下さい。

つまり「自作などせずとも、マクロで使える組み込み関数を使えばよい」です。

お礼

有り難うございます。

でも、そうも行きません

例えば
function　ソート(…　）
　ソート = Evaluato("small(" CStr(リスト) " & "Row(indirect(" & ""A1:"" & UBound(リスト) & ")))")
End　function

はたまた
function　一致件数(…　)
　一致件数 = Evaluato("Sumproduct((CStr(" & リスト & ")," & 条件式 & ") + 1)")
End　function
仮にこんな事が出来れば

特に後者は
多数条件でも
書き方次第で
動きますし

そもそも
速度差は雲泥の筈

おまけに
記載も凄く簡素

知ってれば
昇順並べ替え
なのが
一目瞭然

まあでも、
160万件位しか対応できないから
(※注：行が其れ位までしかないから、恐らく…　)
クイックソート宜しく
元のリストを
再帰的二分法で
150万件程以内に絞って
渡す必要が出ます
けどね

それでも、まぁ
シート関数並みの速度が
期待できます
よね

シートへ書き込む手間と
タイムロスも不要
其れで
恐らくはチューンされた
ソート等のライブラリ
此にアクセスできる

諦め難い…
ですよね
生意気言って済みません。