【Excel/VBA】配列をループするとき配列の最後にダミーを入れると最後の処理がスマートになることがある

エクセルのシートをIDごとに切り分けて別ファイルで保存、とかしたりするのに切り分けキーの列を配列に入れて（入れなくてもいいんだけど、行数が多いときはいちいちセルにアクセスすると時間がかかるので、いったん配列に入れてから処理する）、開始行と終了行をディクショナリに登録したりする、ということをやっています。

たとえば、こんな感じの表を切り分けたいとします。

A列のIDで切り分けたい。

最初はid「1001」開始行は「１」終了行は「１」

次はid「1002」開始行は「2」終了行は「5」

・・・

最後はid「1005」開始行は「11」終了行は「12」

切り分けキーidと開始行、終了行を登録していくコードを書きました。

（ディクショナリを利用するために参照設定でMicrosoft Scripting Runtimeを事前に設定しています）

Sub test()

　’Variant型配列にセル範囲の値を代入
　Dim arr As Variant
　arr = Range(“A1:A12”)

　’ディクショナリを宣言。
　’キー値にid、値に配列で[開始インデックス、終了インデックス]代入
　Dim dic As Dictionary
　Set dic = New Dictionary

　’開始インデックス、終了インデックスを一時格納する変数
　Dim stridx As Long, endidx As Long
　’最初の開始インデックスを代入しておく
　stridx = 1

　’配列をループ。
　Dim i As Long
　For i = LBound(arr) To UBound(arr) – 1
　　’次のIDと異なる場合、dicに格納する
　　If arr(i, 1) <> arr(i + 1, 1) Then
　　　’終了インデックスをendidxに代入
　　　endidx = i

　　　’キー値ID、stridxとendidxを配列にしてdicに代入する
　　　dic.Add arr(i, 1), Array(stridx, endidx)

　　　’次のインデックスをstridxに入れておく
　　　stridx = i + 1

　　End If
　Next i

　Stop

End Sub

Stopのところで止まったところでローカルウインドウを見てみると、

あら残念。最後のid「1005」がdicに登録されていません。

ループを抜けた後に、最後のidの値をdicに登録するコードを書き足しました。

*******************

‘配列をループ。
Dim i As Long
For i = LBound(arr) To UBound(arr) – 1
　　’次のIDと異なる場合、dicに格納する
　If arr(i, 1) <> arr(i + 1, 1) Then
　　’終了インデックスをendidxに代入
　　endidx = i

　　’キー値ID、stridxとendidxを配列にして代入する
　　dic.Add arr(i, 1), Array(stridx, endidx)

　　’次のインデックスをstridxに入れておく
　　stridx = i + 1

　End If
Next i

‘最後の値をdicに代入する
dic.Add arr(UBound(arr), 1), Array(stridx, UBound(arr))

********************************

赤字部分が足したコードです。

これでＯＫ。最後のid「1005」が入りました。

でも、なんかかっこ悪い。最後に残ったやつを入れるのがかっこ悪い気がする。

そうだ配列arrの最後にダミーidをいれれば、書き足さなくても配列ループだけで必要な情報がdicに入れられるのでは！

***************************

‘1行多く配列に代入しておく
Dim arr As Variant
arr = Range(“A1:A13“)

‘ダミーを配列の最後に代入
arr(13, 1) = 9999

***************************

ちなにみダミーの値を入れなくても1行多く配列に代入しておくだけでも大丈夫でした。

改良後のコード

********************************

‘Variant型配列にセル範囲の値を代入
‘1行多く配列に代入しておく
Dim arr As Variant
arr = Range(“A1:A13”)

‘ダミーを配列の最後に代入
arr(13, 1) = 9999

‘ディクショナリを宣言。
‘キー値にid、値に配列で[開始インデックス、終了インデックス]代入
Dim dic As Dictionary
Set dic = New Dictionary

‘開始インデックス、終了インデックスを一時格納する変数
Dim stridx As Long, endidx As Long
‘最初の開始インデックスを代入しておく
stridx = 1

‘配列をループ。
Dim i As Long
For i = LBound(arr) To UBound(arr) – 1
　’次のIDと異なる場合、dicに格納する
　If arr(i, 1) <> arr(i + 1, 1) Then
　　’終了インデックスをendidxに代入
　　endidx = i

　　’キー値ID、stridxとendidxを配列にして代入する
　　dic.Add arr(i, 1), Array(stridx, endidx)

　　’次のインデックスをstridxに入れておく
　　stridx = i + 1

　End If
Next i

Stop

********************************

これでオッケー。

開始行と最終行はインデックスを指定して取り出します。