[EXCEL VBA] 配列メモリの動的使用について

Question

セキュリティ的な理由で、職場にてLL言語の代わりにEXCEL VBAを使用する機会がふえています。
使用していてちょっと気になることができたので質問いたします。

必要に応じてサイズを増やすため、要素を動的に確保するプログラムにしたのですが、最初の要素を追加する場面でエラーになります。
予め最初の要素を作っておくとエラーにならないのですが、要素が無題になるので気に入りませんｗ
この場合、もっと適切がやりかたがあるのでしょうか？

Sub Sample()
　　Dim ary() As Variant, e As Variant
　　Dim size As Long
　　
　　
　　'ReDim ary(0) ' ←ここを有効にするとOK
　　
　　size = UBound(ary) ' ←ここでエラー"インデックスが有効範囲にありません"
　　ReDim Preserve ary(size + 1)
　　ary(size + 1) = "foo"
　　
　　size = UBound(ary)
　　ReDim Preserve ary(size + 1)
　　ary(size + 1) = "bar"
　　
　　For Each e In ary
　　　　MsgBox TypeName(e) & ":" & e
　　Next
End Sub

WindFaller · Accepted Answer

配列の組み込み方というものは、様々なスタイルがありますから、一概にどれがよいとは言えません。

ただし、
　For Each e In ary
　　　MsgBox TypeName(e) & ":" & e
　Next

VBAをよく使う人たちで時々見かけますが、配列で、Indexの初期値に値があろうとなかろうと、こうしたFor Each スタイルを取るというのはレアケースだと思います。

配列は、
　For Lbound(ary) To Ubound(ary)
　
　Next

というスタイルが一般的です。

>予め最初の要素を作っておくとエラーにならないのですが、要素が無題になるので気に入りませんｗ

好む、好まないにも関わらず、配列変数のindex =0 の値を捨ててしまうコードは時々みかけます。というか、私などは、WebコントロールでDOMのオブジェクトから、データを取り出しますが、必ずしも、index=0 が生きたデータを持っているとは限らないからです。

そして、一回ずつ、UBoundで大きさを測る必要はないと思います。
そのindex は、インクリメントですから。

まとめますと、
・Index Counter の初期値　-1
　この手法は、VB6の時に、林晴比古さんのコードによく出てきました。

・配列変数の宣言の時に、多めに、Index 設けておく。ary(1000)
　収集が終わった時点で、不要なものを、Redim Preserve で空の部分を捨てる

・最初は、ary　の配列宣言をしない。最初だけ、Redim で、後は、Redim Preserveにする。

・配列の代わりに、Collection にしてしまう。
　また、dictionary オブジェクトやArrayList(.Net Framework)を使ってしまう。

など、思いつきます。

なお、私が、最近、多く書くスタイルというと以下のようなものです。

Sub Sample1()
　　Dim ary() As Variant, i As Long, j As Long
　　Dim LastRow As Long
　　
　　LastRow = Cells(Rows.Count, 1).End(xlUp).Row
　　ReDim ary(j)　　'ここでは、LastRowは反映させないことにする。
　　
　　For i = 1 To LastRow
　　　　ary(j) = Cells(i, 1).Value
　　　　j = j + 1
　　　　If j >= LastRow Then Exit For
　　　　ReDim Preserve ary(j)
　　Next i
　For i = LBound(ary) To UBound(ary)
　　 MsgBox ary(i)
　Next i
End Sub

WindFaller · Answer

#1の回答者です。

私は、Excel VBAでは、配列で処理することが多いのですが、もう何年もやっているはずなのに、まだ、新しいやり方をみて、それを使ったりします。

#1のお礼欄のご質問、さすがに鋭いですね。#1で省略した部分です。微妙な所もあって、書き渋ってしまいました。

人には人のスタイルがある、という所をわきまえていただければ十分なのですが、人によっては、白を黒と言いくるめられるぐらいの技量のある人から、私の書いた内容を、うやむやにするか、あえて、間違っているかのように、例をつけて説明する人がいます。

お急ぎなら、最後のまとめがありますので、それで読んだほうがはやいです。

それで、まず、ここから。
>セルと配列のインデックスを分けて管理しやすく？しているのだと思いますが、
>他になにか理由はありますか。

>> こうしたFor Each スタイルを取るというのはレアケースだと思います。
かつて、九天社というところで、『VBA デバッグ　実践のツボ』には、

以下のコードとともに、「For Each ～ Next では、検査はできても代入はできない」として、紹介していました。

Sub Sample12Kten()
　　Dim ary_num() As Variant　　'配列要素を格納
　　Dim i As Variant　　'個々の配列要素を格納
　　ary_num = Array(123, 234, 345, 456)　　'初期値を格納
　　'特定の配列要素の場合、配列の内容を変更
　　For i = LBound(ary_num) To UBound(ary_num)
　　　　If ary_num(i) = 234 Then
　　　　　　 ary_num = Array(567, 678, 789, 890)
　　　　End If
　　Next i
　　'配列要素を表示
　　MsgBox "ary_num(0) = " & ary_num(0) & vbCrLf & _
　　　　　 "ary_num(1) = " & ary_num(1) & vbCrLf & _
　　　　　 "ary_num(2) = " & ary_num(2) & vbCrLf & _
　　　　　 "ary_num(3) = " & ary_num(3)
End Sub

また、例えば、２次元の時どうするか、ということも含まれています。For Each で取ると、縦横が逆転してしまったります。Index(添字）の管理のほうがずっと優れていると思うのです。

Sub maKingArray()
'こちらはどうでもよいです。２次元変数をつくるためのコード
Dim i, j
Dim a, ar
　ReDim ar(10, 2)　　'元になる方を2次側に置くのが一般(変更可能）
　a = Array("A", "B", "C")
　For j = 0 To 2
　　For i = 0 To 10
　　　If i = 0 Then
　　　　ar(i, j) = a(j)
　　　Else
　　　　ar(i, j) = a(j) & i
　　　End If
　　Next i
　Next j
　Call ArrayTest(ar)
End Sub

Sub Sample3(arg As Variant)
'大事なのはこちら。セルの構造と同じになります。
Dim x, y, i, j
y = UBound(arg)　　　'xは横
x = UBound(arg, 2)　　'yは縦　(1次元か2次元か分からない時は、On Error トラップが必要ですょ

For j = 0 To x
For i = 0 To y
　Cells(i + 1, j + 1).Value = arg(i, j)

Next i
Next j
End Sub

'//

配列というのは、要素にオブジェクトを取り込むことはめったにありません。だいたい文字列か数値か、そのどちらかです。配列の構造も、End of File でも、End of Data でもなく、計数で終わることが可能なのです。

さて本題です。
>> ReDim ary(j)　　'ここでは、LastRowは反映させないことにする。
動的に作るためにはそこでは、配列を作らないようにしました。
Redim ary(LastRow -1 ) で可能だからです。

#1のコードを、

LastRow = Cells(Rows.Count, 1).End(xlUp).Row
　　ReDim ary(LastRow -1)　　'ReDim ary(0 To LastRow -1)

とすることができます。

>変数jの初期設定を行っておりませんが、宣言時にゼロ値（Longなら0）に自動で>初期化されることが保証されるのでしょうか？

私は、その話は、一度書きかけたのですが、#1では、やめたのでした。世の中、いろんな人がいますから、そういう中のひとりが「自分はこうだ」と主張して、こちらの話をうやむやにする人がいます。

「宣言時にゼロ値に自動で初期化される」

デフォルト状態なら、Index(添字）は、必ずゼロからです。
それがすべてなら、なんの問題ももないのですが、そうではないというのは、

配列のIndexの初期値を 1 に設定する、「Option Base 1 」があるという話に発展してきます。

Option Base 1 で、配列のIndexの初期値は、1になります。

個別のコーディングルールを持ち出すのはおこがましいのですが、少なくとも、私の中のルールでは、Option Base 1 や、Option Compare Text も使わないというルールを設けているからです。

http://officetanaka.net/excel/vba/variable/07.htm
田中氏は、相変わらず、すっきりとした説明ではありませんが、、
「配列を返すVBAの命令には、最小の要素が1から始まる配列を返すものもあります」これは、単にオブジェクトを配列として受ける側になれば、配列のように見えるものでも、1から始まるものは、それは配列とは考えない方がよいのではないか、と私は考えています。そして、それはCollectionだと考えます。

Excelで言えば、Sheets, Range は、1からですが、これらは配列ではなく、Collection です。

もうひとつは、私の中では、いつまでも、VBA/VB6の環境が続くのか、という根本的な懸念が含まれているからなのです。他の環境で、Option Base 1 が許されるのか、と考えます。つまり、それは移植性が高いか低いのではないかということです。Option Base 1とかで作られたものを0ベースに直すのは、意外に面倒なものなのです。

どこかのサイトでは、Indexは、0なら、0、1なら1と明示的に宣言してあげなくてはいけないと言っている人がいます。それは、ごもっともだと思います。
でも、それは、つまり、動的配列で一般的にそんなことができるのか、という疑問もあります。

動的配列に、以下のように書く人がいるのかな？
(なんとも不格好なコードだと思います）

Sub Sample2()
　Dim ary() As Variant, i As Long, j As Long
　Dim LastRow As Long
　ReDim ary(1 To 1)
　
　LastRow = Cells(Rows.Count, 1).End(xlUp).Row

j = 1
　For i = 1 To LastRow
　　ary(j) = Cells(i, 1).Value
　　j = j + 1
　　If j >= LastRow Then Exit For
　　ReDim Preserve ary(1 To j)
　Next i
For i = LBound(ary) To UBound(ary)　　'1からですが。
　 MsgBox TypeName(ary(i)) & ": " & ary(i)
Next i

End Sub

私の話をまとめますと、
・配列の管理は、Index（添字）のほうがやりやすいです。
・動的配列では、最初は、基本的には必ずゼロ・スタートです。
・Option Base は、つかないほうがよい、
ということです。

なお、他には、パラメータ配列やコントロール配列という言葉がありますが、これらは別ものですから、今回には加えません。

[EXCEL VBA] 配列メモリの動的使用について

配列の組み込み方というものは、様々なスタイルがありますから、一概にどれがよいとは言えません。

#1の回答者です。

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

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

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

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