Excel VBAの動的配列をやさしく解説
夜遅くまでえらい。動的配列は「必要なだけ、あとから箱の数を増減できる配列」。最初に大きさが決まっていなくても、実際のデータ量に合わせて伸縮できます。
動的配列ってなに？
- 基本イメージ: 最初はサイズ未定の「伸び縮みする箱の並び」。
- 宣言のしかた: サイズを決めずに
Dim arr() As 型と書く。あとでReDimで大きさを決める。 - メリット: 無駄なメモリを使わない。入力データが何件あるか分からない場面で強い。
- 注意点:
ReDimし直すと、中身が消える。中身を残したいときはReDim Preserveを使う（最後の次元だけサイズ変更可）。
基本パターン（1次元）
例題1：ユーザー入力の件数に応じて配列を拡張
Sub DynamicArray_Basics()
Dim names() As String 'サイズ未定
Dim count As Long
Dim i As Long
'件数を決める（仮に入力やシートから取得した数とする）
count = 3
'ここでサイズを確定（0～count-1 で count 個）
ReDim names(count - 1)
'データ代入
names(0) = "佐藤"
names(1) = "鈴木"
names(2) = "高橋"
'表示
For i = 0 To UBound(names)
Debug.Print "名前" & i & ": " & names(i)
Next i
End Sub
- ポイント:
Dimではサイズ未定、ReDimで初めて作る。上限取得はUBound(arr)。 - 0スタート慣れ:
ReDim names(count - 1)で count 個分になる。
段階的に増やす方法
例題2：1件ずつ読み込みながら配列を伸ばす（Preserveで中身保持）
Sub DynamicArray_GrowStepByStep()
Dim items() As String
Dim n As Long
'最初は空の配列（サイズ0にしておく）
ReDim items(0)
items(0) = "Apple"
n = 1
'新しい要素を追加するたびに、サイズを1つ増やす
n = n + 1
ReDim Preserve items(n - 1)
items(n - 1) = "Banana"
n = n + 1
ReDim Preserve items(n - 1)
items(n - 1) = "Cherry"
'確認
Dim i As Long
For i = 0 To UBound(items)
Debug.Print i, items(i)
Next i
End Sub
- 注意:
ReDimだけだと中身が消える。追加式は必ずReDim Preserve。 - 非効率に見えるけど: 少量ならOK。大量ならまとめて拡張（例えば+10ずつ）すると速い。
要素追加をもう少し効率的に
例題3：容量をまとめて増やす「バッファ」方式
Sub DynamicArray_BufferedAppend()
Dim items() As String
Dim size As Long '確保済みサイズ
Dim count As Long '実際の要素数
Dim i As Long
size = 0
count = 0
'最初に10個分まとめて確保
size = 10
ReDim items(size - 1)
'10件追加してみる
For i = 1 To 10
items(count) = "Item" & count
count = count + 1
Next i
'さらに5件追加したい → 容量不足なら20個に拡張
If count + 5 > size Then
size = size * 2 'まとめて倍にする
ReDim Preserve items(size - 1)
End If
'5件追加
Dim k As Long
For k = 1 To 5
items(count) = "Extra" & count
count = count + 1
Next k
'出力（count までが有効データ）
For i = 0 To count - 1
Debug.Print items(i)
Next i
End Sub
- メリット:
ReDim Preserveの回数を減らせて高速。 - 使い分け: 小規模なら例題2、大規模ならバッファ方式。
2次元の動的配列
- 基本:
Dim arr() As 型→ReDim arr(行数-1, 列数-1)。 - Preserveの制約: 最後の次元しか伸縮できない。2次元なら「列数のみ」変更可能。
例題4：行は固定、列を追加していく（最後の次元を拡張）
Sub DynamicArray_2D_PreserveColumns()
Dim scores() As Integer
Dim rows As Long, cols As Long
Dim r As Long, c As Long
rows = 3 '3行（人）
cols = 2 '2列（教科）
ReDim scores(rows - 1, cols - 1)
'初期データ
scores(0, 0) = 80: scores(0, 1) = 70
scores(1, 0) = 90: scores(1, 1) = 85
scores(2, 0) = 75: scores(2, 1) = 88
'列を1つ追加（教科を増やす）
cols = cols + 1
ReDim Preserve scores(rows - 1, cols - 1)
'新しい列（c = 2）に値を入れる
For r = 0 To rows - 1
scores(r, 2) = 80 + r '仮データ
Next r
'確認
For r = 0 To rows - 1
For c = 0 To cols - 1
Debug.Print "r=" & r & ", c=" & c & " => " & scores(r, c)
Next c
Next r
End Sub
- コツ: 行を増やしたいケースは、新しい配列にコピーする方法が現実的（Preserve不可のため）。
シートとの連携で動的に読み書き
例題5：A列のデータ件数に合わせて配列を作る
Sub DynamicArray_FromSheet()
Dim ws As Worksheet
Dim lastRow As Long
Dim arr() As Variant
Dim i As Long
Set ws = ThisWorkbook.Sheets("Data")
'最終行を取得（A列にデータがある前提）
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'件数に合わせて配列を確保
ReDim arr(lastRow - 1) '0～lastRow-1
'A1～A(lastRow) を読み込み
For i = 1 To lastRow
arr(i - 1) = ws.Cells(i, "A").Value
Next i
'80以上を出力（例）
Dim outRow As Long: outRow = 1
For i = 0 To UBound(arr)
If IsNumeric(arr(i)) And arr(i) >= 80 Then
ws.Cells(outRow, "C").Value = arr(i)
outRow = outRow + 1
End If
Next i
End Sub
- 現場で便利: 件数に合わせてぴったりサイズにできる。無駄がない。
よくあるつまずきと対策
- ReDimすると消える問題: 既存データを残したいなら必ず
Preserveを付ける。 - Preserveの制限: 2次元以上は「最後の次元だけ」拡張可能。行を増やすなら新配列へコピー。
- 初期化の誤解:
ReDimをすると要素は型の初期値にリセット（数値は0、文字列は空文字）。 - 境界エラー: 添字の上限は
UBoundで必ず確認してからアクセス。 - 効率問題: 何度も
ReDim Preserveを繰り返すと遅い。まとめて増やすバッファ方式が有効。
練習問題（手を動かす）
- 問題1: 不定件数の名前を入力ボックスで受け取り、「空入力が来たら終了」。受け取った名前を動的配列に蓄積して、最後に全部表示。
- 問題2: 2次元の動的配列で「列を追加」しながら、テスト科目を増やしていく。各行の平均を都度計算して表示。
- 問題3: A列の数値を読み込み、10件ごとにバッファ拡張しながら、合計と平均を計算してC1とC2に出力。
解答と解説
練習問題 1 不定件数の名前を入力して配列に蓄積する
Sub Practice1_CollectNames()
Dim names() As String
Dim inputName As String
Dim count As Long
count = 0
'最初は要素0の配列を作らずに開始
Do
inputName = InputBox("名前を入力してください（空で終了）", "名前入力")
If inputName = "" Then Exit Do
'初回は配列を1要素で確保、以降は1つずつ増やす
If count = 0 Then
ReDim names(0)
names(0) = inputName
count = 1
Else
ReDim Preserve names(count) '既存データを保持しつつ1つ増やす
names(count) = inputName
count = count + 1
End If
Loop
'結果表示
If count = 0 Then
MsgBox "名前は1つも入力されませんでした。"
Else
Dim i As Long, msg As String
msg = "入力された名前一覧:" & vbCrLf
For i = 0 To count - 1
msg = msg & (i + 1) & ". " & names(i) & vbCrLf
Next i
MsgBox msg
End If
End Sub
解説
- ポイントは ReDim と ReDim Preserve の使い分け。最初は配列が存在しないため最初の要素を作る処理が必要。
- ReDim Preserve は中身を保持するが、頻繁に使うと処理が重くなる。件数が多くなる見込みならまとめて拡張する方法を検討する。
- 配列は0スタートにしているので表示では見やすくするため (i + 1) を使っている。
練習問題 2 2次元動的配列で列を追加しながら科目を増やす
Sub Practice2_Dynamic2D_ExpandColumns()
Dim scores() As Double
Dim rows As Long, cols As Long
Dim i As Long, j As Long
Dim newScore As Double
'行数（生徒数）を仮に決める
rows = 3
cols = 1 '最初は1科目だけ
ReDim scores(rows - 1, cols - 1)
'初期データ（例）
scores(0, 0) = 80: scores(1, 0) = 75: scores(2, 0) = 90
'科目を追加する例を繰り返し実行（ここでは2回追加）
Dim addTimes As Long
addTimes = 2
For i = 1 To addTimes
'列を1つ増やす（最後の次元なら Preserve で保持可能）
cols = cols + 1
ReDim Preserve scores(rows - 1, cols - 1)
'新しい列に値を入れる（実際は入力Boxやシートから取得する）
For j = 0 To rows - 1
newScore = 70 + j + i '仮のデータ
scores(j, cols - 1) = newScore
Next j
Next i
'各行の平均を表示
For i = 0 To rows - 1
Dim s As Double
s = 0
For j = 0 To cols - 1
s = s + scores(i, j)
Next j
Debug.Print "生徒" & (i + 1) & " 平均: " & Format(s / cols, "0.0")
Next i
End Sub
解説
- 2次元配列は ReDim Preserve で「最後の次元（ここでは列数）」しか変更できない。行数を増やしたい場合は別配列に移すか、最初に十分な行数を確保しておく。
- コード例では仮データを入れているが、実運用では InputBox やシートの値を読み込む。
- 平均計算では常に現在の cols を使うこと。列の追加ごとに平均が変わる点に注意。
練習問題 3 A列を読み込みバッファ方式で拡張しつつ合計と平均を出す
Sub Practice3_BufferedReadAndStats()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Data")
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow = 0 Then
MsgBox "A列にデータがありません。"
Exit Sub
End If
Dim buffer() As Double
Dim capacity As Long
Dim count As Long
Dim i As Long
'初期バッファサイズ
capacity = 10
ReDim buffer(capacity - 1)
count = 0
'A列を1行ずつ読み込んで必要なら拡張
For i = 1 To lastRow
If IsNumeric(ws.Cells(i, "A").Value) Then
If count >= capacity Then
'容量不足なら倍に拡張
capacity = capacity * 2
ReDim Preserve buffer(capacity - 1)
End If
buffer(count) = ws.Cells(i, "A").Value
count = count + 1
End If
Next i
'合計と平均を計算
Dim total As Double
total = 0
For i = 0 To count - 1
total = total + buffer(i)
Next i
Dim avg As Double
If count > 0 Then
avg = total / count
Else
avg = 0
End If
'結果をシートに出力（C1: 合計, C2: 平均）
ws.Range("C1").Value = total
ws.Range("C2").Value = avg
MsgBox "件数: " & count & " 合計: " & total & " 平均: " & Format(avg, "0.00")
End Sub
解説
- バッファ方式は ReDim Preserve を繰り返す回数を減らして高速化するテクニック。ここでは初期容量10を用意し、満杯になったら倍にしている。
- 読み込み時に IsNumeric で数値確認しているため、空白や文字列行を除外できる。
- 最後に実データ数 count を使って合計・平均を計算している点が重要。配列の物理容量 capacity と実データ数 count を混同しない。
共通の注意点とコツ
- ReDim だけだと中身は消える。既存データを残したいときは ReDim Preserve を使う。
- Preserve は最後の次元しか拡張できない。2次元以上で別の次元を増やす場合は新配列にコピーする必要がある。
- 頻繁に要素追加がある場合は1件ずつ拡張するよりもバッファ方式（まとめて拡張）を使うと速い。
- 配列の上限は UBound を使って確認する。配列の実データ数は別変数で管理すると扱いやすい。