VBAのWorksheetFunctionの引数に配列を使いたい

質問

VBAにチャレンジし始めて、１ヶ月ほどの者です。

WorksheetFunctionの引数にVBAの配列を入れて計算させたいと思っています。

例えば、作業用の配列temp_arrayを定義し、temp_array()に格納した数字の平均値をWorksheetFunctionで求めるといったことです。

しかし、
Dim temp_array() As Variant
Dim a As Double
a = WorksheetFunction.Average(temp_array())
としても、
「実行時エラー'1004'
WorksheetFunctionクラスのAverageプロパティーを取得できません」
というエラーで叱られます。

ネットで調べていると、Excelのワークシート関数には、引数として配列を扱えるものが多いとあったので、試してみています。
例えば、参考にしたのは下記のサイトの記述です。
http://www.clayhouse.jp/array/array03_d.htm

もちろん、平均値を求めるぐらいなら、自分で関数をつくった方が早いのだとは思いますが、エクセルには色々な統計関数があるので、本当に配列として、WorksheetFunctionに簡単にわたせるなら、相当プログラムが楽でシンプルにできるなぁと思います。

もし、よくご存じの方がいたらアドバイスいただけませんでしょうか？

回答No.2

end-u · ベストアンサー

temp_array()の中は何がはいっているのでしょう？
その中身の問題じゃないですか？
文字列だったらエラーになります。

Dim temp_array() As Variant
Dim a As Double
temp_array() = Array(1, 2)
a = WorksheetFunction.Average(temp_array())
MsgBox a

お礼

早速のアドバイスありがとうございます。

アドバイスのコードを試したらちゃんとできますね。

今は試しなので、中には4桁の整数を5つ入れてます。
配列内にちゃんと数字が入っていることは確認しているのですが。
サッパリわからなくなってきました。

もう少し原因究明してみます。

補足

すいません。

実は、大きな配列から、特定の範囲の平均値を求めるために、
まず、大きな配列から対象範囲を切り出して、temp_array()に格納するという部分を関数としてつくってありました。

関数の戻り値が配列で、その配列をtemp_array()に代入しているのですが、代入結果の各要素をMsgBoxで表示させると、確かに対象となる部分の数字が５つ表示されるので、データの中身には疑問を持っていませんでした。

でも、全く同じ５つの数字をArrayで指定し直して、WorksheetFunction.Averageに渡すとちゃんと計算されました。

やはり、temp_array()のデータの中身に問題があるようです。
相変わらず何が問題なのかは皆目検討がつきませんが、その点は、投稿した質問内容とは異なる部分の問題のようです。

ありがとうございました。

回答No.8

Wendy02

こんにちは。

余計なことを書くようで、もしお気に障ったら、すみませんです。

どうやら、私の書いたメッセージはどうも伝わらなかったようで、とても残念です。
VBAにかなり自信のある方だとお見受けしましたが、端的に言うと、掲示板では、VBAの質問でVBAのコードを見なければ、お話は進まないということです。できれば、回答者側に回ってみてください。見えないものが見えてきます。

CSVであろうが、どうであろうが、配列の中身に文字列になるというのは、そこのコードのどこかにミスか不足があるのだと考えたのです。繰り返しますが、エラーは、そのプロセスに問題があるのであって、文字列であるから、というのは、結果論なのです。

それと、配列の中身を調べるなら、ブレークポイントを入れて、ローカルウィンドウで、変数を確認すれば済みます。

Variant型配列だけで、そのデータが文字列になるなんていうことは、ありえないのです。文字列を文字列として入れたからなのです。

あまり、参考にはならないと思いますが、最初に、今回のような内容を出されていたら、こんなコードを出していたと思います。なお、最近は、変数は重複して使うようですが、Step モード(F8)で見やすいように、以下は、ほとんど重複して使ってはいません。

シートにデータが出力されるものです。

'---------------------------------------------

Sub CSVImport()
　　Dim FileName As String
　　Dim FNo As Integer
　　Dim buf As String
　　Dim myAr() As Variant
　　Dim myArBuf As Variant
　　Dim myArVal() As Variant
　　Dim i As Long
　　Dim j As Integer
　　Dim k As Long
　　Dim v As Variant
　　Dim ret1 As Double
　　Dim ret2 As Double

　　Const sSEP As String = "," '区切り文字
　　Const STARTL As Long = 5 '切り出し数（始め)
　　Const ENDL As Long = 20 '　''　　　（終わり)

　　FileName = Application.GetOpenFilename("CSV ファイル(*.csv),*.csv")
　　If FileName = "False" Then
　　　　Exit Sub
　　End If
　　'
　　FNo = FreeFile()
　　Open FileName For Input As #FNo
　　Do Until EOF(FNo)
　　　　Line Input #FNo, buf
　　　　myArBuf = Split(buf, sSEP)
　　　　ReDim Preserve myAr(i + UBound(myArBuf))
　　　　For Each v In myArBuf
　　　　　　myAr(i) = v
　　　　　　i = i + 1
　　　　Next v
　　Loop
　　Close #FNo
　　ReDim myArVal(ENDL - STARTL)
　　For Each v In myAr()
　　　　If STARTL <= j And ENDL >= j Then
　　　　　　myArVal(k) = Val(v) '文字列がなければ、CLng などの方がよい
　　　　　　k = k + 1
　　　　End If
　　　　j = j + 1
　　Next

　　'シートへの出力
　　Cells(1, 1).Resize(UBound(myArVal) + 1).Value = Application.Transpose(myArVal())
　　Cells(UBound(myArVal) + 2, 1).Formula = "=AVERAGE(R1C1:R[-1]C)"
　　Cells(UBound(myArVal) + 3, 1).Formula = "=STDEVP(R1C1:R[-2]C)"

　　'ユーザー定義関数との比較
　　　　ret1 = myAverage(myArVal())
　　　　ret2 = mySTDEV(myArVal())

　　Cells(UBound(myArVal) + 2, 2).Formula = ret1
　　Cells(UBound(myArVal) + 3, 2).Formula = ret2

End Sub

''
Private Function myAverage(myAr() As Variant)
'平均値
Dim mSum As Double
Dim i As Long
Dim v As Variant
　 For Each v In myAr()
　　 mSum = mSum + v
　　 i = i + 1
　 Next v
　 myAverage = mSum / i
End Function
'
Private Function mySTDEV(myAr() As Variant)
'標準偏差
Dim ave As Double
Dim mSum As Double
Dim i As Long
Dim dev As Double
Dim v As Variant

　　For Each v In myAr()
　　 mSum = mSum + v
　　 i = i + 1
　 Next v
　　ave = mSum / i　'平均値
'
　　For Each v In myAr()
　　　 dev = dev + (ave - v) ^ 2
　　Next v
　　mySTDEV = (dev / i) ^ (1 / 2)
End Function

'------------------------------------

回答No.7

Wendy02

こんばんは。

>原因究明ができましたので、問題が解決いたしました。

うーん。読まされている側は、狐につままれたような感じで、逆に、何が何だか分からないのですが。別に、配列変数が、Variant 型だから、いけないというわけでもないのです。実際の内容が分からないので、一体、原因はなんだったのか、さっぱり分かりません？？？自己解決してしまったから、もう答える義務もゆかりもないのかもしれませんが、何か、すっきりとしていないものが残ります。

自称専門家さん以外の、ここで回答している人たちの半分以上は、「教える」というステータスを誇示しているのではなくて、ここで、同じように学習しているわけで、やっぱり原因を知りたいと思うのです。つまり、教えることと、教わることのインタラクティブということです。

私は、#6で、

>>やはり、temp_array()のデータの中身に問題があるようです。
>いいえ、その前の問題です。その切り出し方法が問題ですね。肝心なところが書かれていません。

と書いたのですが、配列の中身は、結果論です。

単に、文字列の数字を数値に置き換えるなら、あえて、Average 関数など使う必要などないわけです。ワークシートの数字自体は、ほとんどは、Variant 型で、そこには、文字列と数値の区分けなど存在していません。文字列として扱ったときに初めて文字列となるわけです。

それに、別で書かれた、Watch式っていうのは、そこで、True/False で、コードをストップさせて値を見るものであって、通常は、ローカルウィンドウとステップイン(F8)だけで十分だと思います。

これに対して、答えなくてはならない義務とかはないけれども、読んでいる側は、終わっていないということだけは伝えておきますね。

お礼

アドバイス頂いたのに、当方の言葉足らずで、すいません。

私がつくっていたのは、数万点のデータが入ったcsvファイルを一旦、原配列（）に読み込んだ上で、特定の範囲の部分だけの平均値や標準偏差を求めようというものです。

そこで、特定の範囲のデータ列だけ、作業用配列（）として切り出す関数をつくりました。

関数の引数は始点,終点,原配列()の３つで、戻り値は作業用配列()です。

配列の受け渡しの際のデータ型のことは細かく考えたくなかったので、
原配列も作業用配列もバリアント型にすれば、すくなくとも関数との配列の受け渡しでトラブルはないだろうと思ったのが失敗です。

一昨日までデバッグツールの使い方も知りませんでしたので、関数からの戻り値である配列の各要素をMsgBoxで表示させたり、エクセルのセルに書き込んだりて中身を確認していたのですが、表示されるのは、想定通りの数字だったので、頭を抱えたわけです。

しかし、ウォッチ式で関数から戻ってくる配列の中身を確認したら、全て数字が文字列として入っていたことがわかりました。
csvファイルから読み込んだ数字が原配列に全て文字列として入ってしまっていたのが、トラブルの原因でした。

＞ワークシートの数字自体は、ほとんどは、Variant 型で、そこには、文字列と数値の区分けなど存在していません。

私は、データ型に関する理解が不十分なのですが、WorksheetFunction.Average()は渡した配列の中身が文字型だとダメみたいです。

結果的に、原配列も作業用配列もDoubleとして宣言したら、エラーもなくうまくいった次第です。

回答No.6

Wendy02

こんにちは。

>やはり、temp_array()のデータの中身に問題があるようです。

いいえ、その前の問題です。その切り出し方法が問題ですね。肝心なところが書かれていません。

'----------------------------------------

Dim Ar As Variant
Const MYTXT As String = "1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16"
Ar = Split(MYTXT, ",")
MsgBox WorksheetFunction.Average(Ar)

'----------------------------------------
これでエラーが出ます。

なお、VBEditor の　F8 を押せば、ステップモードになりますから、そこで、ローカルウィンドウを見て、中を確認すればよいです。しかし、もう、以下のようなコードにしたら、ワークシート関数はあまり意味を持ちません。

'----------------------------------------
Sub Test2()
Dim Ar As Variant
Dim Ar2() As Double
Dim i As Long

Const MYTXT As String = "1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16"
Ar = Split(MYTXT, ",")
ReDim Ar2(UBound(Ar))
For Each v In Ar
　Ar2(i) = v
　i = i + 1
Next
MsgBox WorksheetFunction.Average(Ar2)

End Sub
'----------------------------------------

お礼

#5さんともども、ありがとうございます。

原因究明ができましたので、問題が解決いたしました。
自作関数とWorksheetFunctionを組み合わせることで、
とても、スッキリと短いコードで思い通りの結果が得られるようになりました。

デバッグツールの使い方も覚えることができましたし、
皆様のご助言に感謝、感謝です。

回答No.5

Wendy02

こんにちは。

#2さんのご指摘で分かっているかもしれませんが、配列変数の数値の中に、ワークシートのエラー値が含まれていれば、そのような実行時エラーが発生します。ワークシート関数には、そのようなエラー値を無視する機能がありません。

それから、VBAでは、ワークシート関数を使うのは、関数の仕組みをある程度理解していないと、難しいことが多いと思います。算術型の関数の多くの引数は、文字列と数値だけしか区分けしませんので、それ以外は、エラーを出すことが多いです。

お礼

ありがとうございます。

原因は、配列をVariantと宣言していたためか、
配列に数字が文字列として代入されていたためということがわかりました。

デバッグツールの使い方をよくわかっておらず、
配列の中身をMsgBoxで表示させるという原始的な方法で確認していたので、
文字列なのか、数字なのか、区別できていませんでした。

回答No.4

imogasi

例データ　元データ　A1:C9　D列横計
1 2 3 6
5 7 7 19
12 14 12 38
63　ーー＞和
ーーー
純粋に配列とはいえないかもしてないが、ヴァリアント変数で
コード
Sub test01()
Dim tmp As Variant
tmp = Range("a1:C3")
MsgBox "平均= " & sumary(tmp)
End Sub
Function sumary(tmp)
For Each x In tmp
MsgBox x
t = t + x
n = n + 1
Next
MsgBox t
sumary = t / n
End Function
＝＝＝＝
Sub test02()
Dim tmp As Variant
tmp = Range("a1:C3")
MsgBox "平均= " & avgary2(tmp)
End Sub
Function avgary2(tmp)
avgary2 = WorksheetFunction.Average(tmp)
End Function

お礼

ありがとうございます。
どうやら、配列データに理解不能な現象が起きているのが原因のようです。
プログラミングというのは、一旦、行き詰まると出口が見えなくなる迷路のようです。

回答No.3

xls88

回答番号：No.1の内容は、回答ではなく”怪答”でした。
スルーお願いします。
大変、失礼しました。

お礼

いえいえ、ご親切にありがとうございます。

回答No.1

xls88

配列変数の()が余分です。
a = WorksheetFunction.Average(temp_array)

下記で配列内の4番目のデータが取り出せます。
配列の開始番号は、0 からになります。
a = WorksheetFunction.Average(temp_array(3))

お礼

早速のアドバイスありがとうございます。
()を取ってみたのですが、やはり同じエラーが出ます。
でも、WorksheetFunction.Averageの引数に配列を入れるのは有りなんですね。