Excel VBAで動的配列の使い方をお探しですね。
広告
Excel VBAの動的配列を使いこなそう！ReDimとPreserveの使い方を丁寧に解説
Excel VBAでデータを配列に入れて処理していると、「最初は何件あるか分からない」「条件に合うセルだけを後から追加したい」という場面に出会うことがよくあります。
こんなときに便利なのが、**要素数を後から変えられる動的配列**です。
動的配列では `ReDim` を使って配列のサイズを決めたり、`ReDim Preserve` で今入っているデータを残したまま要素数を増やしたりできます。
ただ、使い方を間違えると「さっきまで入っていた値が消えた！」「インデックスが有効範囲にありませんってエラーが出た」「2次元配列でうまくいかない」といったトラブルに遭遇しがちです。
この記事では、Excel VBAの動的配列について、宣言の仕方から `ReDim` と `Preserve` の違い、実際に使うときの注意点まで、順を追って分かりやすく説明していきます。
1. Excel VBAの動的配列って何？
Excel VBAの配列には、大きく分けて**静的配列**と**動的配列**の2種類があります。
**静的配列**は、宣言するときに要素数を決めてしまう配列です。
たとえば `Dim arr(2) As String` と書くと、`arr(0)`、`arr(1)`、`arr(2)` の3つの要素を持つ配列ができあがります（インデックスが0から始まる場合）。
一方、**動的配列**は宣言するときに要素数を決めず、後から `ReDim` でサイズを指定する配列です。
処理の途中でデータの件数が変わる場合や、条件に一致したデータだけを集めたい場合に向いています。
動的配列を宣言するときは、配列名の後ろに**空のかっこ**を付けます。
かっこの中に数字を書かないのがポイントです。
“`vb
Dim names() As String
“`
この時点では、配列の「入れ物」を用意しただけで、実際に値を入れられる領域はまだ確保されていません。
なので、宣言した直後に `names(0) = “田中”` のように代入しようとすると、「インデックスが有効範囲にありません」というエラーが出てしまいます。
動的配列は、**宣言した後に `ReDim` で要素数を確保してから使う**という流れで覚えておくと分かりやすいですよ。
静的配列と動的配列の使い分けは、**要素数が最初から分かっているかどうか**で判断します。
たとえば、必ず12か月分のデータを扱うなら静的配列で十分です。
でも、ワークシート上で空白じゃないセルだけを集めたい、検索条件に合う行だけを配列に入れたい、フォルダー内のファイル数に応じて配列を増やしたい、といった処理では件数が事前に分からないので、動的配列が役立ちます。
Excel VBAではセル範囲や検索結果を扱う場面が多いので、動的配列を理解しておくとマクロの幅がぐっと広がります。
2. ReDimで要素数を後から変更する基本
`ReDim` は、動的配列の要素数を確保したり、設定し直したりするための命令です。
基本的な書き方は `ReDim 配列名(最大インデックス番号)` です。
たとえば `ReDim names(2)` と書くと、通常は `names(0)` から `names(2)` までの3つの要素を持つ配列になります。
VBAの配列は多くの場合インデックスが0から始まるので、「指定した数字＝要素数」じゃなくて、「指定した数字＋1個の要素」になることに注意してください。
“`vb
Sub SampleReDim()
Dim names() As String
ReDim names(2)
names(0) = “田中”
names(1) = “鈴木”
names(2) = “佐藤”
MsgBox names(0) & “,” & names(1) & “,” & names(2)
End Sub
“`
この例では、`ReDim names(2)` で3件分の領域を確保しています。
その後、それぞれのインデックス番号を指定して値を入れています。
Excel VBAでは、配列の最大インデックス番号を確認したいときに `UBound` 関数、最小インデックス番号を確認したいときに `LBound` 関数を使います。
配列の下限が0とは限らないコードを書く場合は、`0` を決め打ちせず、`LBound` と `UBound` を使うと安全です。
“`vb
Sub SampleBounds()
Dim nums() As Long
Dim i As Long
ReDim nums(1 To 3)
For i = LBound(nums) To UBound(nums)
nums(i) = i * 10
Next i
MsgBox “下限：” & LBound(nums) & ” / 上限：” & UBound(nums)
End Sub
“`
`ReDim` は同じ配列に対して何度でも実行できます。
ただし、ここで重要なのが、**普通の `ReDim` をもう一度実行すると、配列に入っていた値が初期化される**ということです。
文字列型なら空文字、数値型なら0、Variant型ならEmptyのように、データ型に応じた初期値に戻ってしまいます。
「要素数を増やしただけのつもりだったのに、前に入れた値が消えちゃった！」というトラブルは、`ReDim` のこの性質を知らないときによく起こります。
“`vb
Sub SampleReDimClear()
Dim names() As String
ReDim names(1)
names(0) = “田中”
names(1) = “鈴木”
ReDim names(2)
MsgBox “0番目の値：” & names(0) ‘ 空になってる！
End Sub
“`
このコードでは、2回目の `ReDim names(2)` で要素数は増えますが、`names(0)` と `names(1)` に入っていた値は消えてしまいます。
既存の値を残したまま要素数を変更したい場合は、次に説明する `Preserve` を付ける必要があります。
**`ReDim` は「サイズを作り直す」、`ReDim Preserve` は「中身を残してサイズを変える」**と区別して覚えておくと、実務で迷いにくくなりますよ。
3. ReDim Preserveで既存データを残して要素を追加する
`ReDim Preserve` は、配列に入っている既存データを残したまま、要素数を変更したいときに使います。
たとえば、条件に一致したセルの値だけを配列に追加していく場合、すでに入れた値を消してしまったら意味がないですよね。
そんなときは、カウント用の変数を1つ用意して、該当データが見つかるたびに配列を広げて値を入れる書き方がよく使われます。
“`vb
Sub SamplePreserve()
Dim names() As String
Dim count As Long
count = count + 1
ReDim Preserve names(1 To count)
names(count) = “田中”
count = count + 1
ReDim Preserve names(1 To count)
names(count) = “鈴木”
MsgBox names(1) & “,” & names(2)
End Sub
“`
この例では、`count` が増えるたびに `ReDim Preserve names(1 To count)` で配列の上限を広げています。
`Preserve` を付けているので、先に入れた `”田中”` は消えずに残り、次の `”鈴木”` を追加できるわけです。
Excelの実務では、ワークシートのA列を上から確認して、空白じゃないセルだけを配列に入れるような処理に応用できます。
配列の件数が事前に分からないときでも、見つかった分だけ後から要素数を増やせる点が動的配列の大きなメリットです。
“`vb
Sub CollectNonBlankCells()
Dim values() As String
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim count As Long
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
For i = 1 To lastRow
If ws.Cells(i, “A”).Value <> “” Then
count = count + 1
ReDim Preserve values(1 To count)
values(count) = ws.Cells(i, “A”).Value
End If
Next i
If count > 0 Then
MsgBox “取得件数：” & count & vbCrLf & “先頭データ：” & values(1)
Else
MsgBox “対象データはありません。
”
End If
End Sub
“`
ただし、`ReDim Preserve` にも注意点があります。
便利だからといって、大量データに対して1件追加するたびに毎回実行すると、処理速度が遅くなることがあります。
`Preserve` は既存データを保ちながら配列を作り直すので、データ件数が多いほど負荷が大きくなりやすいんです。
数十件から数百件くらいなら問題にならないことも多いですが、数万件規模のデータを扱う場合は、最初に大きめの領域を確保しておいて、最後に必要なサイズへ縮める方法も検討するといいでしょう。
`ReDim Preserve` を使うときに覚えておきたいポイントは、次のとおりです。
– 既存データを残したい場合は `ReDim` じゃなくて `ReDim Preserve` を使う
– 要素数を小さくすると、削られた範囲のデータは失われる
– 大量データで頻繁に実行すると、処理速度に影響する場合がある
– 配列の下限を変更するとエラーになるので、`1 To count` などの形を維持する
このように、`Preserve` は「値を残せる便利な指定」ですが、万能ではありません。
特に、既存データを保持する必要がない場面では普通の `ReDim` の方がシンプルです。
配列を完全にリセットしたい場合は `Erase 配列名` を使う方法もあります。
`Erase` を実行すると動的配列の領域が解放されるので、もう一度使う場合は改めて `ReDim` で要素数を確保する必要があります。
4. 2次元配列・実務で失敗しやすい注意点
Excel VBAで動的配列を使うとき、特に混乱しやすいのが**2次元配列**です。
2次元配列は `arr(行, 列)` みたいな感覚で扱えるので、Excelの表データと相性がよさそうに見えますよね。
でも、`ReDim Preserve` には**「変更できるのは最後の次元だけ」**という重要な制約があります。
つまり、`arr(1 To 3, 1 To count)` のような配列であれば、2番目の次元である `count` 側は増やせますが、1番目の次元を変更しながらデータを保持することはできないんです。
“`vb
Sub SampleTwoDimension()
Dim data() As Variant
Dim count As Long
count = count + 1
ReDim Preserve data(1 To 3, 1 To count)
data(1, count) = “田中”
data(2, count) = “営業部”
data(3, count) = 350000
End Sub
“`
この例では、1件のデータに対して「氏名」「部署」「給与」の3項目を持たせて、件数が増えるたびに第2次元を広げています。
表として考えると、行と列の向きがExcelシートの感覚と逆に感じるかもしれません。
でも、`ReDim Preserve` で増やせるのは最後の次元だけなので、動的に件数を増やしたい場合は、**件数を最後の次元に置く設計**が必要です。
最後にシートへ出力するときは、必要に応じて行列を入れ替える処理を行うと扱いやすくなります。
また、`Preserve` を使う場合は**下限を変更できない**点にも注意が必要です。
最初に `ReDim Preserve arr(1 To count)` とした配列を、後から `ReDim Preserve arr(0 To count)` のように変更するとエラーになります。
配列の開始番号を0にするのか1にするのかは、最初に決めて統一することが大切です。
Excelの行番号と合わせて考えたい場合は `1 To count`、VBA標準の感覚に合わせたい場合は `0 To count – 1` のように、プロジェクト内でルールを決めておくとミスを減らせます。
動的配列を安定して使うには、`LBound` と `UBound` を組み合わせて範囲外アクセスを避けることも重要です。
特に、対象データが0件だった場合、まだ `ReDim` されていない配列に対して `UBound` を使うとエラーになることがあります。
なので、カウント変数で件数を管理して、件数が1以上のときだけ配列を参照するようにすると安全です。
検索結果や抽出結果を扱うマクロでは、「データがある場合」だけじゃなくて「データがなかった場合」の分岐も必ず用意しましょう。
実務での基本方針としては、まず動的配列を `Dim 配列名() As 型` で宣言して、必要になった時点で `ReDim` します。
既存データを残す必要がある追加処理では `ReDim Preserve` を使って、不要になった配列は `Erase` でリセットします。
2次元配列では最後の次元だけを変更できることを前提に、配列の構造を先に考えてからコードを書くことが大切です。
この流れを押さえておけば、Excel VBAで要素数が変わるデータを扱う場面でも、値の消失や範囲外エラーを避けながら柔軟なマクロを作れるようになりますよ。
広告
