巨大配列の高速化
Excel VBAで数万〜数十万行のデータを扱うとき、巨大配列をどう効率的に処理するか が重要です。初心者向けに、コード例とテンプレートをかみ砕いて説明します。
基本の考え方
- セルを1つずつ読むのは遅い →
Cells(i,j).Valueを繰り返すと極端に遅くなる。 - Rangeを一括で配列に読み込む →
v = Range("A1:C100000").Valueで一瞬。 - 配列内で処理 → 計算や検索は配列上で行う。
- 結果も一括で書き戻す →
Range.Resize(...).Value = 配列で高速。 - 不要な画面更新を止める →
Application.ScreenUpdating = Falseでさらに高速化。
テンプレ1：巨大配列を一括読み込み
Sub HugeArray_Read()
Dim ws As Worksheet: Set ws = Worksheets("Data")
Dim rg As Range: Set rg = ws.Range("A2:C100000") ' 10万行
Dim v As Variant
v = rg.Value ' 一括で二次元配列に読み込み
Debug.Print "行数=" & UBound(v, 1), "列数=" & UBound(v, 2)
End Sub
- ポイント: 10万行でも一瞬で読み込める。
テンプレ2：配列内で集計（数量合計）
Sub HugeArray_Sum()
Dim ws As Worksheet: Set ws = Worksheets("Data")
Dim rg As Range: Set rg = ws.Range("A2:C100000")
Dim v As Variant: v = rg.Value
Dim sumQty As Double, r As Long
For r = 1 To UBound(v, 1)
sumQty = sumQty + v(r, 3) ' C列=数量
Next r
MsgBox "数量合計 = " & sumQty
End Sub
- ポイント: セルを直接読むより数十倍速い。
テンプレ3：結果を一括書き戻し
Sub HugeArray_WriteBack()
Dim ws As Worksheet: Set ws = Worksheets("Data")
Dim rg As Range: Set rg = ws.Range("A2:C100000")
Dim v As Variant: v = rg.Value
Dim r As Long
For r = 1 To UBound(v, 1)
v(r, 3) = v(r, 3) * 2 ' 数量を2倍
Next r
' 一括書き戻し
rg.Value = v
End Sub
- ポイント: 10万行でも一瞬で書き戻せる。
テンプレ4：画面更新・計算を止めて高速化
Sub HugeArray_FastProcess()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Dim ws As Worksheet: Set ws = Worksheets("Data")
Dim rg As Range: Set rg = ws.Range("A2:C100000")
Dim v As Variant: v = rg.Value
Dim r As Long
For r = 1 To UBound(v, 1)
v(r, 3) = v(r, 3) + 10
Next r
rg.Value = v
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub
- ポイント:
ScreenUpdating = False→ 画面描画を止める。Calculation = Manual→ 再計算を止める。EnableEvents = False→ イベント処理を止める。
→ 巨大データ処理がさらに速くなる。
テンプレ5：巨大配列をDictionaryで検索高速化
Sub HugeArray_SearchDict()
Dim ws As Worksheet: Set ws = Worksheets("Data")
Dim rg As Range: Set rg = ws.Range("A2:B100000") ' A=コード, B=商品名
Dim v As Variant: v = rg.Value
Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
Dim r As Long
For r = 1 To UBound(v, 1)
dict(v(r, 1)) = v(r, 2)
Next r
' 検索
Dim key As String: key = "C50000"
If dict.Exists(key) Then
MsgBox "コード " & key & " の商品名は " & dict(key)
Else
MsgBox "該当なし"
End If
End Sub
- ポイント: 巨大配列でも検索が一瞬。
例題で練習
'例1：10万行を一括読み込み
Sub Example_Read()
HugeArray_Read
End Sub
'例2：数量合計を計算
Sub Example_Sum()
HugeArray_Sum
End Sub
'例3：数量を2倍にして書き戻す
Sub Example_WriteBack()
HugeArray_WriteBack
End Sub
'例4：画面更新を止めて高速処理
Sub Example_Fast()
HugeArray_FastProcess
End Sub
'例5：Dictionaryで高速検索
Sub Example_Search()
HugeArray_SearchDict
End Sub
初心者向けポイント
- セル直読みはNG: 巨大データでは極端に遅い。
- 配列一括処理が必須: 読み込み・加工・書き戻しを配列で。
- 画面更新を止める:
ScreenUpdating=Falseでさらに高速化。 - 検索はDictionary: 巨大配列でも即検索可能。
- 元データは壊さない: 結果は別シートに出力すると安心。