ReDim Preserveをループの中で繰り返すExcelマクロ

VBA（Visual Basic for Applications）で、動的配列をまだ上手く使うことができない方から、
「ループ処理の中で、格納済みのデータを残したまま、配列のReDimを繰り返すにはどうすればいいのでしょう？」
といったご質問をいただくことがあります。

配列の要素がいくつ必要なのかわからない場合、とりあえず配列変数の宣言だけをしておいて、あとから実際のサイズをReDim文で変更するという処理を行います。

配列要素がいくつ必要なのかを確認する処理を行ってから、配列のサイズを決めて、その後データを配列に格納するという流れが、わかりやすいはずですが、配列のサイズを大きくしながらデータの格納も行いたいと思った方から、
「ループ処理の中で、格納済みのデータを残したまま、配列のReDimを繰り返すにはどうすればいいのでしょう？」
といったご質問をいただくことがあります。

このサイト・インストラクターのネタ帳のアクセスログで見かける
「vba redimうまく使えない」
「excelvba redim preserve インデックスが有効ではありません」
といった検索をなさった方も、同じような疑問をもっていらしたのかもしれません。

ループ処理の中で、既に格納されているデータを残したまま、1次元配列のReDimを何度も行うマクロを、2つご紹介しておきます。

添字0の配列要素を使わずループ処理の中で動的配列をReDimしてデータを格納するサンプルマクロ

動的配列に慣れていない方の場合は、添字・インデックスの最小値「0」の配列要素、先頭の配列要素を使わない処理が、理解しやすいように思います。

For Each～Nextループの中で、
　For Each rng In Range("A1:C2")
A1:C2セルの値を順番にチェックして「10」以上だったときに、
　　If rng.Value >= 10 Then
その値を動的配列に格納しているプロシージャです。

A1:C2セルに、10以上の数値と10未満の数値を適当に織り交ぜて入力しておいてから、上記のマクロを実行してみてください。

最後のStop文の箇所でステップ実行モードになりますから、その状態でローカルウィンドウを表示して配列変数・arrの中をご確認ください。

下図のように「10」以上のデータだけが、配列変数・arrに格納されていることが見えます。

ここで、添字・インデックスの最小値の配列要素・arr(0)に格納されている「0」は、VBAの数値型変数の初期値としての「0」で、ループ処理の中で格納された値ではないことに注意してください。

配列要素を1つ増やしておいてから、
　　　ReDim Preserve arr(UBound(arr) + 1)
増やされたばかりの最後の配列要素にセルの値を格納しているために、
　　　arr(UBound(arr)) = rng.Value
先頭の配列要素にはデータが格納されないことになります。

この後の処理で配列に格納されたデータを利用する際には、この先頭の配列要素にデータが入っていないことに注意しておく必要がありますが、理解しやすい処理ではないかと思います。

ループ処理の中で動的配列をReDimしてデータを格納して末尾の配列要素を削るサンプルマクロ

添字・インデックス「0」の配列要素が使われない状態を、なんだか気持ち悪いと感じるのなら、以下のようなプロシージャです。

先のマクロと違うのは、配列要素にデータの格納を行ってから、次にデータを格納するための配列要素を増やしている点です。
　　　arr(UBound(arr)) = rng.Value
　　　ReDim Preserve arr(UBound(arr) + 1)

つまり、データの格納を行った直後に、次にデータが格納されるかどうかはわからないけれど、配列の要素を増やしているわけです。

この配列要素を事前に増やす処理があるため、ループを抜けたあとには、データの格納されていない配列要素が、必ず1つ存在することになります。その余分に作ってしまった配列要素を減らす処理が、
　ReDim Preserve arr(UBound(arr) - 1)
ループを抜けたあとに入っています。