Excel VBA 逆引き集 | 大量データの扱い方

Excel VBA
スポンサーリンク

ねらい：大量データでも「速く・安全・見える化」して処理する設計と実装

Excelで数万〜数十万行を扱うと、画面が固まる、遅い、落ちる、壊れる…が起きがち。大量データの基本は「読みは一括」「処理はメモリ」「書き戻しはまとめて」「UIは間引いて更新」「最適化スイッチで環境を守る」。初心者でも貼って動かせるテンプレと、なぜそうするかの理由まで深掘りして解説します。

パフォーマンスの基本方針と最適化スイッチ

  • 基本方針:
    • 一括読み込み: Range→配列へ一発で読み、ループは配列上で回す。
    • 一括書き戻し: 結果配列をまとめてRangeへ書く（1回の代入）。
    • 画面・イベント停止: ScreenUpdating/EnableEvents/CalculationをOFFに。
    • UI更新は間引き: 進捗表示は1〜5%ごとに。DoEventsも間引く。
    • 参照削減: Cells/Rangeへの都度アクセスをなくす。オブジェクト参照をキャッシュ。
  • 環境最適化テンプレ（開始/終了の枠）:
Sub AppEnter(Optional ByVal status As String = "")
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    If Len(status) > 0 Then Application.StatusBar = status
End Sub

Sub AppLeave()
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
VB
  • 重要ポイント（深掘り）：
    • 最適化は必ず枠で管理: 途中でエラーが出ても「必ず戻す」ために開始/終了のセットで使う。
    • 計算ON/OFFの使い分け: 要再計算のブックは終了時に自動へ戻さないと誤動作の原因になる。

配列で処理するテンプレ（読み→処理→書き戻し）

Sub BulkProcess_ArrayIO()
    On Error GoTo ErrHandler
    AppEnter "大量データ処理開始"

    Dim wsIn As Worksheet: Set wsIn = Worksheets("Input")
    Dim wsOut As Worksheet: Set wsOut = Worksheets("Output")
    
    ' 1. 範囲を一括読み（ヘッダー含む CurrentRegion）
    Dim arr As Variant
    arr = wsIn.Range("A1").CurrentRegion.Value  ' 2次元Variant配列
    
    ' 2. 結果配列を用意（列追加の例：+1列）
    Dim rows As Long: rows = UBound(arr, 1)
    Dim cols As Long: cols = UBound(arr, 2)
    Dim out() As Variant: ReDim out(1 To rows, 1 To cols + 1)
    
    ' ヘッダーコピー＋新列名
    Dim c As Long
    For c = 1 To cols: out(1, c) = arr(1, c): Next
    out(1, cols + 1) = "合格"  ' 新列
    
    ' 3. メモリ上で処理（例：E列=5列の点数で判定）
    Dim r As Long
    For r = 2 To rows
        ' 元データコピー
        For c = 1 To cols: out(r, c) = arr(r, c): Next
        ' 判定（>=70なら○）
        out(r, cols + 1) = IIf(Val(arr(r, 5)) >= 70, "○", "×")
        ' 進捗は間引き（1%ごと）
        If r Mod WorksheetFunction.Max(1, rows \ 100) = 0 Then
            Application.StatusBar = "進捗 " & Format(r / rows, "0%")
            DoEvents
        End If
    Next
    
    ' 4. 一括書き戻し
    wsOut.Range("A1").Resize(rows, cols + 1).Value = out
    
    AppLeave
    MsgBox "処理完了（" & rows - 1 & " 件）"
    Exit Sub
ErrHandler:
    AppLeave
    MsgBox "失敗: " & Err.Description, vbExclamation
End Sub
VB
  • 重要ポイント（深掘り）：
    • Range→配列は1回で読み切る: 1セルずつ読むのは桁違いに遅い。
    • 配列のインデックスは1始まり: Excel→配列は基本1ベース。0ベースと混同しない。
    • 書き戻しもResize一発: 1セルずつ書かない。数万行でも瞬間で反映される。

フィルタ＋可変行の高速処理（SpecialCellsの活用）

大量データから条件に合う行だけを対象にするなら、まず可視セルの範囲を絞ると速い。

Sub FastProcess_Filtered()
    On Error GoTo ErrHandler
    AppEnter "フィルタ処理"

    Dim ws As Worksheet: Set ws = Worksheets("Data")
    Dim lo As ListObject: Set lo = ws.ListObjects("tblSales") ' テーブル前提が高速＆安全
    
    ' AutoFilterで期間絞り込み（例：日付列が SalesDate）
    lo.Range.AutoFilter Field:=lo.ListColumns("SalesDate").Index, _
                        Criteria1:=">=2025/01/01", Operator:=xlAnd, Criteria2:="<=2025/12/31"
    
    ' 可視セルだけ対象（ヘッダー除く）
    Dim rngVis As Range
    Set rngVis = lo.DataBodyRange.SpecialCells(xlCellTypeVisible)
    
    ' 可視セルを配列化
    Dim arr As Variant: arr = rngVis.Value
    
    ' 配列上で集計（例：Amount合計）
    Dim idxAmt As Long: idxAmt = lo.ListColumns("Amount").Index
    Dim sumAmt As Double, i As Long
    For i = 1 To UBound(arr, 1)
        sumAmt = sumAmt + Val(arr(i, idxAmt))
    Next
    
    ' 結果
    Worksheets("Summary").Range("B2").Value = sumAmt
    
    ' フィルタ解除
    lo.AutoFilter.ShowAllData
    
    AppLeave
    MsgBox "集計完了: " & Format(sumAmt, "#,##0")
    Exit Sub
ErrHandler:
    AppLeave
    MsgBox "失敗: " & Err.Description, vbExclamation
End Sub
VB
  • 重要ポイント（深掘り）：
    • テーブル前提で列参照を安定化: 列名→Indexが取れ、列順が変わっても壊れにくい。
    • フィルタで行を減らす→配列にする: 対象行が減るほど配列処理も速くなる。

高速ループの書き方（よくある遅さの原因を潰す）

  • 遅さの原因:
    • Cells/Rangeへの都度アクセス: 1セルごとの読み書きは非常に遅い。
    • DoEventsの入れすぎ: 毎ループで呼ぶと劇的に遅い。
    • 文字列連結の繰り返し: 長い文字列連結は累積コストが大。
    • 型の暗黙変換: Variantの意図しない型変換で遅くなる。
  • 高速化テク：
    • 参照キャッシュ: 列や範囲、ListColumn.Indexは変数へ保持。
    • 数値はDouble、カウンタはLong: 正しい型で計算コストを減らす。
    • StringBuilder的に配列→Join: 大量文字列なら配列に溜めて最後にJoin。
    • DoEventsは「一定間隔」: 総件数に応じて1%ごとなど。
' 文字列結合の高速化（配列→Join）
Function JoinLines(ByVal arr As Variant) As String
    Dim i As Long, tmp() As String
    ReDim tmp(1 To UBound(arr, 1))
    For i = 1 To UBound(arr, 1)
        tmp(i) = CStr(arr(i, 1))
    Next
    JoinLines = Join(tmp, vbCrLf)
End Function
VB
  • 重要ポイント（深掘り）：
    • 型を意識するだけで速くなる: ループ内のVariant→Double/Longの明示化が効く。
    • 画面やイベントの負荷をゼロに: ScreenUpdating/EnableEvents OFFが大前提。

メモリと安定性の設計（安全に落ちないために）

  • 設計のコツ:
    • チャンク処理: 100k行を一気にやらず、数千行単位に分けて順次処理。
    • クリアポイント: 大きな配列は使い終わったら Erase や Set obj = Nothing。
    • エラー時の復帰: AppLeaveを必ず呼ぶ。Finally的枠を徹底。
    • バックアップ: 破壊的操作（削除/上書き）は SaveCopyAs を先に実行。
Sub SafeBackup(ByVal suffix As String)
    Dim path As String
    path = ThisWorkbook.Path & "\" & ThisWorkbook.Name & "_" & suffix & ".bak"
    ThisWorkbook.SaveCopyAs path
End Sub
VB
  • 重要ポイント（深掘り）：
    • 「落ちても戻る」仕組みが安心: バックアップと環境復帰で現場信頼が上がる。
    • 超大規模ならチャンク分割＋疑似非同期: OnTimeでUIを固めない設計が有効。

範囲選択の高速ユーティリティ（最終行・最終列・CurrentRegion）

Function LastRow(ByVal ws As Worksheet, ByVal col As Variant) As Long
    LastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
End Function

Function LastCol(ByVal ws As Worksheet, ByVal headerRow As Long) As Long
    LastCol = ws.Cells(headerRow, ws.Columns.Count).End(xlToLeft).Column
End Function

Function ReadRegion(ByVal topLeft As Range) As Variant
    ReadRegion = topLeft.CurrentRegion.Value
End Function
VB
  • 重要ポイント（深掘り）：
    • 最終行/列の取得は共通化: プロジェクト中の実装差を無くし、バグを減らす。
    • CurrentRegionで塊を読む: 空行がない前提なら最短で表全体を読める。

画面固まり対策：進捗とキャンセルの組み込み

Public gCancel As Boolean

Sub RequestCancel(): gCancel = True: End Sub

Sub ProgressStatus(ByVal cur As Long, ByVal total As Long, Optional ByVal label As String = "進捗")
    If total <= 0 Then Exit Sub
    If cur Mod Application.WorksheetFunction.Max(1, total \ 100) = 0 Then
        Application.StatusBar = label & " " & Format(cur / total, "0%") & " (" & cur & "/" & total & ")"
        DoEvents
    End If
End Sub

Sub BulkProcess_WithCancel()
    AppEnter "大量処理（キャンセル可）"
    Dim ws As Worksheet: Set ws = Worksheets("Input")
    Dim arr As Variant: arr = ws.Range("A1").CurrentRegion.Value
    
    Dim rows As Long: rows = UBound(arr, 1)
    Dim r As Long
    For r = 2 To rows
        ' 本処理…
        If gCancel Then
            AppLeave
            MsgBox "中断しました。": Exit Sub
        End If
        ProgressStatus r, rows
    Next
    AppLeave
    MsgBox "完了"
End Sub
VB
  • 重要ポイント（深掘り）：
    • 「止められる」安心: 大量処理はキャンセルボタンとセットで設計。
    • 進捗は1%刻み: 体感が良く、無駄な描画も減らせる。

例題で練習（貼って試せる）

  • 例1（配列I/O）： BulkProcess_ArrayIO → 2次元配列で処理して一括書き戻しの速さを体験。
  • 例2（フィルタ高速化）： FastProcess_Filtered → テーブル＋AutoFilter＋SpecialCellsで対象を絞る。
  • 例3（進捗＋キャンセル）： BulkProcess_WithCancel → 1%刻み進捗と安全な中断。
  • 例4（チャンク設計）： 大行数を数千行ずつ処理するように分割し、UI応答を維持。

実務の落とし穴と対策（ここが肝）

  • 落とし穴1：1セルずつ読み書きしてしまう
    • 対策: Range→配列→一括書き戻しへ全面置換。
  • 落とし穴2：DoEvents多用で激遅
    • 対策: 総件数に応じて1〜5%刻みだけ。毎回は呼ばない。
  • 落とし穴3：画面更新ONのまま
    • 対策: AppEnter/AppLeaveの枠を必ず通す。環境復帰を徹底。
  • 落とし穴4：列順変更で壊れる
    • 対策: テーブル＋列名Index取得で安定化。Config/名前定義も活用。
  • 落とし穴5：全件一気に処理して落ちる
    • 対策: チャンク分割＋疑似非同期（OnTime）でUI応答を確保。こまめに解放。

スターター手順（最短導入）

  1. AppEnter/AppLeave を導入し、すべての大量処理を枠で包む。
  2. Range→配列→一括書き戻し の基本テンプレへ置換。
  3. 進捗・キャンセル を1%刻みで追加。
  4. テーブル＋列名参照 に切り替えて列変更耐性を上げる。
  5. 必要に応じてチャンク分割 とバックアップ設計を入れる。

スポンサーリンク
タイトルとURLをコピーしました