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

Question

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に簡単にわたせるなら、相当プログラムが楽でシンプルにできるなぁと思います。

もし、よくご存じの方がいたらアドバイスいただけませんでしょうか？

end-u · Accepted Answer

temp_array()の中は何がはいっているのでしょう？
その中身の問題じゃないですか？
文字列だったらエラーになります。

Dim temp_array() As Variant
Dim a As Double
temp_array() = Array(1, 2)
a = WorksheetFunction.Average(temp_array())
MsgBox a

Wendy02 · Answer

こんにちは。

余計なことを書くようで、もしお気に障ったら、すみませんです。

どうやら、私の書いたメッセージはどうも伝わらなかったようで、とても残念です。
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


'------------------------------------

Wendy02 · Answer

こんばんは。

>原因究明ができましたので、問題が解決いたしました。

うーん。読まされている側は、狐につままれたような感じで、逆に、何が何だか分からないのですが。別に、配列変数が、Variant 型だから、いけないというわけでもないのです。実際の内容が分からないので、一体、原因はなんだったのか、さっぱり分かりません？？？自己解決してしまったから、もう答える義務もゆかりもないのかもしれませんが、何か、すっきりとしていないものが残ります。

自称専門家さん以外の、ここで回答している人たちの半分以上は、「教える」というステータスを誇示しているのではなくて、ここで、同じように学習しているわけで、やっぱり原因を知りたいと思うのです。つまり、教えることと、教わることのインタラクティブということです。

私は、#6で、

>>やはり、temp_array()のデータの中身に問題があるようです。
>いいえ、その前の問題です。その切り出し方法が問題ですね。肝心なところが書かれていません。

と書いたのですが、配列の中身は、結果論です。

単に、文字列の数字を数値に置き換えるなら、あえて、Average 関数など使う必要などないわけです。ワークシートの数字自体は、ほとんどは、Variant 型で、そこには、文字列と数値の区分けなど存在していません。文字列として扱ったときに初めて文字列となるわけです。

それに、別で書かれた、Watch式っていうのは、そこで、True/False で、コードをストップさせて値を見るものであって、通常は、ローカルウィンドウとステップイン(F8)だけで十分だと思います。

これに対して、答えなくてはならない義務とかはないけれども、読んでいる側は、終わっていないということだけは伝えておきますね。

Wendy02 · Answer

こんにちは。

>やはり、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
'----------------------------------------

Wendy02 · Answer

こんにちは。

#2さんのご指摘で分かっているかもしれませんが、配列変数の数値の中に、ワークシートのエラー値が含まれていれば、そのような実行時エラーが発生します。ワークシート関数には、そのようなエラー値を無視する機能がありません。

それから、VBAでは、ワークシート関数を使うのは、関数の仕組みをある程度理解していないと、難しいことが多いと思います。算術型の関数の多くの引数は、文字列と数値だけしか区分けしませんので、それ以外は、エラーを出すことが多いです。

imogasi · Answer

例データ　元データ　A1:C9　D列横計
1236
57719
12141238
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

xls88 · Answer

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

xls88 · Answer

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

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

temp_array()の中は何がはいっているのでしょう？

この回答への補足

こんにちは。

こんばんは。

こんにちは。

こんにちは。

例データ 元データ A1:C9 D列横計

回答番号：No.1の内容は、回答ではなく”怪答”でした。

配列変数の()が余分です。

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

例データ　元データ　A1:C9　D列横計