VBAのWorksheetFunctionで配列を使いたい
- VBAでWorksheetFunctionの引数に配列を使用する方法について相談です。
- Excelのワークシート関数には配列を扱えるものが多く、VBAでも使いたいと思っています。
- しかし、配列を引数に指定してもエラーが発生し、正しく計算されません。アドバイスをお願いします。
- ベストアンサー
VBAのWorksheetFunctionの引数に配列を使いたい
VBAにチャレンジし始めて、1ヶ月ほどの者です。 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に簡単にわたせるなら、相当プログラムが楽でシンプルにできるなぁと思います。 もし、よくご存じの方がいたらアドバイスいただけませんでしょうか?
- mindspring
- お礼率97% (71/73)
- オフィス系ソフト
- 回答数8
- ありがとう数7
- みんなの回答 (8)
- 専門家の回答
質問者が選んだベストアンサー
temp_array()の中は何がはいっているのでしょう? その中身の問題じゃないですか? 文字列だったらエラーになります。 Dim temp_array() As Variant Dim a As Double temp_array() = Array(1, 2) a = WorksheetFunction.Average(temp_array()) MsgBox a
その他の回答 (7)
- Wendy02
- ベストアンサー率57% (3570/6232)
こんにちは。 余計なことを書くようで、もしお気に障ったら、すみませんです。 どうやら、私の書いたメッセージはどうも伝わらなかったようで、とても残念です。 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
- ベストアンサー率57% (3570/6232)
こんばんは。 >原因究明ができましたので、問題が解決いたしました。 うーん。読まされている側は、狐につままれたような感じで、逆に、何が何だか分からないのですが。別に、配列変数が、Variant 型だから、いけないというわけでもないのです。実際の内容が分からないので、一体、原因はなんだったのか、さっぱり分かりません???自己解決してしまったから、もう答える義務もゆかりもないのかもしれませんが、何か、すっきりとしていないものが残ります。 自称専門家さん以外の、ここで回答している人たちの半分以上は、「教える」というステータスを誇示しているのではなくて、ここで、同じように学習しているわけで、やっぱり原因を知りたいと思うのです。つまり、教えることと、教わることのインタラクティブということです。 私は、#6で、 >>やはり、temp_array()のデータの中身に問題があるようです。 >いいえ、その前の問題です。その切り出し方法が問題ですね。肝心なところが書かれていません。 と書いたのですが、配列の中身は、結果論です。 単に、文字列の数字を数値に置き換えるなら、あえて、Average 関数など使う必要などないわけです。ワークシートの数字自体は、ほとんどは、Variant 型で、そこには、文字列と数値の区分けなど存在していません。文字列として扱ったときに初めて文字列となるわけです。 それに、別で書かれた、Watch式っていうのは、そこで、True/False で、コードをストップさせて値を見るものであって、通常は、ローカルウィンドウとステップイン(F8)だけで十分だと思います。 これに対して、答えなくてはならない義務とかはないけれども、読んでいる側は、終わっていないということだけは伝えておきますね。
お礼
アドバイス頂いたのに、当方の言葉足らずで、すいません。 私がつくっていたのは、数万点のデータが入ったcsvファイルを一旦、原配列()に読み込んだ上で、特定の範囲の部分だけの平均値や標準偏差を求めようというものです。 そこで、特定の範囲のデータ列だけ、作業用配列()として切り出す関数をつくりました。 関数の引数は始点,終点,原配列()の3つで、戻り値は作業用配列()です。 配列の受け渡しの際のデータ型のことは細かく考えたくなかったので、 原配列も作業用配列もバリアント型にすれば、すくなくとも関数との配列の受け渡しでトラブルはないだろうと思ったのが失敗です。 一昨日までデバッグツールの使い方も知りませんでしたので、関数からの戻り値である配列の各要素をMsgBoxで表示させたり、エクセルのセルに書き込んだりて中身を確認していたのですが、表示されるのは、想定通りの数字だったので、頭を抱えたわけです。 しかし、ウォッチ式で関数から戻ってくる配列の中身を確認したら、全て数字が文字列として入っていたことがわかりました。 csvファイルから読み込んだ数字が原配列に全て文字列として入ってしまっていたのが、トラブルの原因でした。 >ワークシートの数字自体は、ほとんどは、Variant 型で、そこには、文字列と数値の区分けなど存在していません。 私は、データ型に関する理解が不十分なのですが、WorksheetFunction.Average()は渡した配列の中身が文字型だとダメみたいです。 結果的に、原配列も作業用配列もDoubleとして宣言したら、エラーもなくうまくいった次第です。
- Wendy02
- ベストアンサー率57% (3570/6232)
こんにちは。 >やはり、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を組み合わせることで、 とても、スッキリと短いコードで思い通りの結果が得られるようになりました。 デバッグツールの使い方も覚えることができましたし、 皆様のご助言に感謝、感謝です。
- Wendy02
- ベストアンサー率57% (3570/6232)
こんにちは。 #2さんのご指摘で分かっているかもしれませんが、配列変数の数値の中に、ワークシートのエラー値が含まれていれば、そのような実行時エラーが発生します。ワークシート関数には、そのようなエラー値を無視する機能がありません。 それから、VBAでは、ワークシート関数を使うのは、関数の仕組みをある程度理解していないと、難しいことが多いと思います。算術型の関数の多くの引数は、文字列と数値だけしか区分けしませんので、それ以外は、エラーを出すことが多いです。
お礼
ありがとうございます。 原因は、配列をVariantと宣言していたためか、 配列に数字が文字列として代入されていたためということがわかりました。 デバッグツールの使い方をよくわかっておらず、 配列の中身をMsgBoxで表示させるという原始的な方法で確認していたので、 文字列なのか、数字なのか、区別できていませんでした。
- imogasi
- ベストアンサー率27% (4737/17068)
例データ 元データ 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
お礼
ありがとうございます。 どうやら、配列データに理解不能な現象が起きているのが原因のようです。 プログラミングというのは、一旦、行き詰まると出口が見えなくなる迷路のようです。
- xls88
- ベストアンサー率56% (669/1189)
回答番号:No.1の内容は、回答ではなく”怪答”でした。 スルーお願いします。 大変、失礼しました。
お礼
いえいえ、ご親切にありがとうございます。
- xls88
- ベストアンサー率56% (669/1189)
配列変数の()が余分です。 a = WorksheetFunction.Average(temp_array) 下記で配列内の4番目のデータが取り出せます。 配列の開始番号は、0 からになります。 a = WorksheetFunction.Average(temp_array(3))
お礼
早速のアドバイスありがとうございます。 ()を取ってみたのですが、やはり同じエラーが出ます。 でも、WorksheetFunction.Averageの引数に配列を入れるのは有りなんですね。
関連するQ&A
- VBAの配列内容の確認
VBAの配列で頭を抱えています。 元々、WorksheetFunctionの引数に配列を入れて計算したかったのですが、原因不明のエラーで行き詰まっています。 Dim temp_array() As Variant Dim a As Double ↓ ※temp_array()という一時的な配列に、別の大きな配列の一部を切り出して代入 ↓ a = WorksheetFunction.Average(temp_array()) という流れで、temp_array()に格納された数字の平均値を求めるという流れなのですが、 「実行時エラー'1004': WorksheetFuntionクラスのAverageプロパティーを取得できません。」 となります。 配列の内容データがおかしいのかと思って、以下のようにエクセルシートに書き出して確認しても、データにおかしいところはありません。 For k = LBound(temp_array) To UBound(temp_array) Cells(k, 1) = temp_array(k) Next ところが、書き出された数字(例えば、10,20,30,40,50とします)を temp_array()=array(10,20,30,40,50) として、全く同じ数字をtemp_array()に入れ直して、 a = WorksheetFunction.Average(temp_array()) とすると、今度は、エラーも出ずに期待した平均値が計算されて、aに代入されます。 色々、考えたのですが、何がおかしいのか全くお手上げです。 この怪奇現象について、何か思い当たる方がいたら、アドバイスいただけませんでしょうか?
- ベストアンサー
- オフィス系ソフト
- VBAでの配列について
VBA で、 Dim pow(23) As String Dim temp As Double temp = WorksheetFunction.Average(pow) としたいのですが、状況に応じて、powには、文字列が入ったり、0や空白だったりもします。 0や空白の場合、平均値に影響させたくありません。 現状、文字列等が入ると、 平均できません 見たいなエラーが出ます。 どういう風にするのが良いでしょうか
- 締切済み
- オフィス系ソフト
- Excel VBA配列をFunctionに渡す
こんばんは、引数について教えてください。 Excel VBAの関数を作っていましたが、 1.Function ColumnArrayの部分でコンパイルエラーが発生し、 「配列がありません」と表示されます。 引数を配列のみで渡した場合、問題なく渡せるようですが、 他の引数と、CriteriaArrsの配列と一緒に渡せないのでしょうか。 すべて配列として1つにまとめて渡さなければならないのでしょうか。 2.CriteriaArrs = Array("田中", "鈴木")の部分は、文字列の増減が発生しますので 配列はParamArray CriteriaArrs()とした方がよいのでしょうか 説明が不足している点があるかもしれませんが宜しくお願いいたします。 Function ColumnArray(SheetName As Worksheet, _ StartCell As Range, _ FieldColumn As Long, _ CountColumn As Long, _ CriteriaArrs As Variant _ ) As Long ・・・ End Function ------------------------------------- sub test() Dim CriteriaArrs() As Variant Dim SheetA As WorkSheet DIm RangeA range CriteriaArrs = Array("田中", "鈴木") set SheetA =Worksheet(1) set RangeA=Range("B3") FilterCount = ColumnArray(SheetA, RangeA, 3, 2, CriteriaArrs) end sub
- 締切済み
- Excel(エクセル)
- VBAの配列
VBAの配列の処理でこまっています。 dim test as Variant test = Array( _ Array("Aさん", 65, 70, 45), _ Array("Bさん", 80, 10, 90)) とした成績データがありまして、あとから(定義の段階でなく)、"Cさん", 70, 70, 75をtest変数に追加したい場合、どのようにすればよいのでしょうか? また、これ(test)を戻り値にした場合、正しく左辺値にはいるのでしょうか?オブジェクトの解放などの問題も知りたいです。 function seiseki () as variant ... 上の処理 seiseki = test end function ... dim cp as Variant cp = seiseki() 'cp変数にコピーされるのでしょうか?
- ベストアンサー
- Visual Basic
- linest関数に配列を渡す
こんばんは。 回帰分析をやってくれるワークシート関数にLinestとうのがありますが、 それの引数に、配列を渡したいのですが、うまくいきません。 データ自体はRangeではなく、Variant型の配列となっているのですが、Linestを 使うときは、一旦、シートに貼り付けて、配列→Range型に変換するなどするしか 対応できませんでしょうか。 もしくは、Linestと同等な自作関数を作っても良いのですが、もともと機能として あるなら、Linestを使用したいと思ってます。 うまくいかない例^^; Sub test() Dim a(1 To 3) as Variant Dim b(1 To 3, 1 To 2) as Variant a(1) = 1 a(2) = 3 a(3) = 2 b(1, 1) = 4 b(2, 1) = 5 b(3, 1) = 6 b(1, 2) = 12 b(2, 2) = 15 b(3, 2) = 19 MsgBox WorksheetFunction.LinEst(a, b, True, True) End Sub -- エクセル2003
- ベストアンサー
- Visual Basic
- VBAで配列のある部分だけをを配列に入れたい
VBAを勉強をしております。 例えばA1からG20までをある配列に入れているとします。 その配列のある部分(例えばB1からC20)を、別の配列にいれるにはどうすれば良いのでしょうか。 以下の方法で試してみましたが、上手くいきません。 Dim A() As Variant Dim B() As Variant A = Range("A1:G20") B = Range(Cells(A(1,2),Cells(A(20,3)))
- 締切済み
- Excel(エクセル)
- VBAで配列内のマッチ
VBAでfor文などループを使わないで配列の文字列とマッチするかの判定を行いたいのですが、良い方法はありますか? Dim vrnArray As Variant vrnArray = Array("A", "B", "C", "D", "E", "F", "G") で ret = match???? (vrnArray, "C") retは真 のようなことがしたいです。
- ベストアンサー
- Visual Basic
- 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 前は確か出来ていた だけに 出来ない のが、口惜しい です 資料もマシン崩壊に伴い消失 なんともはや…
- ベストアンサー
- Excel(エクセル)
- 【エクセルVBA】Functionの引数として、配列は使えるのでしょうか?
VBA初心者です。 Function で関数を定義するとき、その引数に配列を使う方法はあるのでしょうか? たとえば Function Test (C as double,n as integer) というFunctionの C に3×3の行列を入れたいのですが…。
- ベストアンサー
- オフィス系ソフト
- 配列内データにsmallを使う
以下の箇所で、「WorksheetFunction Smallでプロパティが取得できません。」 (実行時エラー1004)となります。 Range("A1") = Application.WorksheetFunction.Small(hause, 7) hauseは Dim hause(15) as variant で定義し、数字が15個入っています。 原因を教えてください。 配列のsmallの例が見つからず、分からないでいます。
- ベストアンサー
- Excel(エクセル)
お礼
早速のアドバイスありがとうございます。 アドバイスのコードを試したらちゃんとできますね。 今は試しなので、中には4桁の整数を5つ入れてます。 配列内にちゃんと数字が入っていることは確認しているのですが。 サッパリわからなくなってきました。 もう少し原因究明してみます。
補足
すいません。 実は、大きな配列から、特定の範囲の平均値を求めるために、 まず、大きな配列から対象範囲を切り出して、temp_array()に格納するという部分を関数としてつくってありました。 関数の戻り値が配列で、その配列をtemp_array()に代入しているのですが、代入結果の各要素をMsgBoxで表示させると、確かに対象となる部分の数字が5つ表示されるので、データの中身には疑問を持っていませんでした。 でも、全く同じ5つの数字をArrayで指定し直して、WorksheetFunction.Averageに渡すとちゃんと計算されました。 やはり、temp_array()のデータの中身に問題があるようです。 相変わらず何が問題なのかは皆目検討がつきませんが、その点は、投稿した質問内容とは異なる部分の問題のようです。 ありがとうございました。