VBＡの配列内容の確認

質問

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に代入されます。

色々、考えたのですが、何がおかしいのか全くお手上げです。

この怪奇現象について、何か思い当たる方がいたら、アドバイスいただけませんでしょうか？

回答No.1

hige_082 · ベストアンサー

＞※temp_array()という一時的な配列に、別の大きな配列の一部を切り出して代入
の部分が怪しいですね

次のマクロを実行してみてください

'値がすべて数字：エラーなし
Sub test1()
Dim aa() As Variant
[a1:c3].Value = [{10,20,30;40,50,60;70,80,90}]
aa() = Range("a1:c3").Value
MsgBox WorksheetFunction.Average(aa())
End Sub

'一部文字列：エラーなし
Sub test2()
Dim aa() As Variant
[a1:c3].Value = [{"a","b","c";40,50,60;70,80,90}]
aa() = Range("a1:c3").Value
MsgBox WorksheetFunction.Average(aa())
End Sub

'すべて文字列：実行時エラー'1004':・・・
Sub test3()
Dim aa() As Variant
[a1:c3].Value = [{"a","b","c";"d","e","f";"g","h","i"}]
aa() = Range("a1:c3").Value
MsgBox WorksheetFunction.Average(aa())
End Sub

'エラーがある場合：実行時エラー'1004':・・・
Sub test4()
Dim aa() As Variant
[a1:c3].Value = [{"=0/0",20,30;40,50,60;70,80,90}]
aa() = Range("a1:c3").Value
MsgBox WorksheetFunction.Average(aa())
End Sub

他のパターンもあるかもしれないが
主なものはこんなもんでしょう
参考までに

お礼

ありがとうございます。

おっしゃるとおりでした。

大きな配列から、WooksheetFunctionに入れる一時的な配列を切り出す処理を関数として書いています。
Variant型でないと配列としてやり取りができなかったので、Variant型にしていたのですが、見てみたら、何故か文字列として入っていました。

エクセルに書き出した時点で、文字データが数値データに変換されていたので、違いがわかりませんでした。

とても、一人では気がつかなかったので、大変助かりました。
ありがとうございます。

回答No.2

noname#144013

こんにちは。

#1の方が言われているように、

>　※temp_array()という一時的な配列に、別の大きな配列の一部を切り出して代入

↑この個所（処理内容）が問題かもしれません。

temp_arrray()は、Variant型で宣言されていますので、代入するデータの型、及び代入する
やり方によって、数値型（Integer、Long、Doubleなど）ではなく、例えば文字型（String）の
データとして値が格納されているかもしれません。

ですので、temp_array()の中身が 数値型以外 であった場合は、

　　　a = WorksheetFunction.Average(temp_array())

の個所で、

　　　「実行時エラー'1004':
　　　　WorksheetFuntionクラスのAverageプロパティーを取得できません。」

などのエラーが発生する可能性があると思われます。

実際、temp_array() の中身の型がどうなっているかは、「Visual Basic Editor」上で、
temp_array を「ウォッチ式」に追加しておいて、

　　　マクロを実行
　　　　　↓
　　　エラーが発生し、エラーのダイアログが表示される
　　　　　↓
　　　ダイアログ上の「デバッグ」ボタンをクリック
　　　　　↓
　　　「Visual Basic Editor」が表示され、エラー個所がマークされブレークしている
　　　　　↓
　　　ウォッチウィンドウで、temp_array() の中身を確認

で、データの中身・データの型などが確認できると思います。

もしも、この確認で temp_array() の中身が数値型でなかった場合は、temp_array()
へのデータ代入処理を見直した方が良いかと思われます。

以上、見当違いの内容でしたらすみません。

お礼

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

原因がわかりました。

また、ウォッチ式の使い方を初めて知りました。
何分、VBAにチャレンジし始めて、1ヶ月ほどなので、デバッグツールをうまく使いこなせていません。

このエラーが解決しそうなのも、ありがたいのですが、
デバッグツールの使い方を勉強できたのも大収穫です。

重ねてお礼申し上げます。