Excel VBA 逆引き集 | 高速化設定の定石

Excel VBA
スポンサーリンク

ねらい：高速化設定の「定石」を一括で押さえ、どの処理にも迷わず適用する

Excel VBAの速度は「設定スイッチ」で大きく変わります。まず環境を最適化（描画・イベント・再計算を止める）、処理は配列でまとめて、最後に確実に元へ戻す。この枠組みをテンプレ化すれば、初心者でも安定して高速化できます。

高速化スイッチの基本セット（開始・終了の枠）

' 高速化の開始（必ず最初に呼ぶ）
Sub AppEnter(Optional ByVal status As String = "")
    Application.ScreenUpdating = False          ' 画面描画を止める
    Application.EnableEvents = False            ' シート/ブックイベントを止める
    Application.DisplayAlerts = False           ' 確認ダイアログを抑制（必要な場面のみ）
    Application.Calculation = xlCalculationManual ' 再計算を手動に
    Application.StatusBar = IIf(status <> "", status, False)
End Sub

' 高速化の終了（必ず最後に呼ぶ／エラー時も）
Sub AppLeave()
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
VB
  • 重要（深掘り）：
    • Finallyの徹底: 途中でエラーが起きても必ず元に戻すため、開始・終了の“枠”で包む。
    • 再計算の扱い: 手動へ切替えると一気に速くなるが、終了時に必ず自動へ戻す。数式の多いブックでは効果が大きい。
    • DisplayAlertsは慎重に: 上書きや削除の誤操作を招くため、必要箇所だけON/OFFする運用が安全。

定石1：セル往復をやめ、配列で一括処理する

Sub Fast_ArrayIO()
    On Error GoTo ErrHandler
    AppEnter "大量処理開始"
    
    Dim wsIn As Worksheet: Set wsIn = Worksheets("Input")
    Dim wsOut As Worksheet: Set wsOut = Worksheets("Output")
    
    ' 一括読み（ヘッダー含む表）
    Dim arr As Variant: arr = wsIn.Range("A1").CurrentRegion.Value
    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 r As Long, c As Long
    For c = 1 To cols: out(1, c) = arr(1, c): Next
    out(1, cols + 1) = "合格"
    
    For r = 2 To rows
        For c = 1 To cols: out(r, c) = arr(r, c): Next
        out(r, cols + 1) = IIf(Val(arr(r, 5)) >= 70, "○", "×") ' 例：点数がE列
    Next
    
    ' 一括書き戻し
    wsOut.Range("A1").Resize(rows, cols + 1).Value = out
    
    AppLeave
    MsgBox "完了（" & rows - 1 & "件）"
    Exit Sub
ErrHandler:
    AppLeave
    MsgBox "失敗: " & Err.Description, vbExclamation
End Sub
VB
  • 重要（深掘り）：
    • 1セルずつは超遅い: 参照コストが桁違い。必ず“まとめて読み→配列で処理→まとめて書く”。
    • 配列は1ベース: Excelから読み込んだ2次元配列は1始まり。0ベースと混同しない。

定石2：進捗・DoEventsは「間引き」してUIフリーズを防ぐ

Sub ProgressThrottled(ByVal cur As Long, ByVal total As Long, Optional ByVal label As String = "進捗")
    If total <= 0 Then Exit Sub
    Dim stepN As Long: stepN = Application.WorksheetFunction.Max(1, total \ 100) ' 1%刻み
    If cur Mod stepN = 0 Then
        Application.StatusBar = label & " " & Format(cur / total, "0%") & " (" & cur & "/" & total & ")"
        DoEvents ' 応答性を保つ
    End If
End Sub
VB
  • 重要（深掘り）：
    • DoEventsの呼びすぎは遅さの元凶: 1〜5%刻み程度で十分。毎ループは避ける。
    • 体感の安心を確保: 長処理でもUIが応答するだけで「固まった不安」が消える。

定石3：テーブル＋列名参照で耐久性と速度を両立

Sub Fast_Filter_Table()
    AppEnter "テーブル高速処理"
    Dim ws As Worksheet: Set ws = Worksheets("Data")
    Dim lo As ListObject: Set lo = ws.ListObjects("tblSales")
    
    ' 列名からIndex（列順が変わっても安全）
    Dim idxDate As Long: idxDate = lo.ListColumns("SalesDate").Index
    Dim idxAmt  As Long: idxAmt  = lo.ListColumns("Amount").Index
    
    ' 期間絞り込み→可視セルだけ配列化
    lo.Range.AutoFilter Field:=idxDate, Criteria1:=">=2025/01/01", Operator:=xlAnd, Criteria2:="<=2025/12/31"
    Dim arr As Variant: arr = lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Value
    
    ' 配列で集計
    Dim sumAmt As Double, i As Long
    For i = 1 To UBound(arr, 1): sumAmt = sumAmt + CDbl(arr(i, idxAmt)): Next
    Worksheets("Summary").Range("B2").Value = sumAmt
    
    lo.AutoFilter.ShowAllData
    AppLeave
End Sub
VB
  • 重要（深掘り）：
    • 列名ベースが壊れにくい: 列順変更に強く、参照の再計算不要。
    • フィルタで対象行を減らす→配列処理: データ量を絞るだけで体感速度は大きく向上。

定石4：Select/Activate禁止、参照はWith/変数で直接指定

' 悪い例：選択を多用
Range("A1").Select
Selection.Value = "x"

' 良い例：直接参照
With Worksheets("Sheet1")
    .Range("A1").Value = "x"
End With
VB
  • 重要（深掘り）：
    • 選択は描画とイベントのトリガー: 余計なコストが積み上がる。
    • “オブジェクトを変数に”が基本: 何度も使う参照は変数にキャッシュ。

定石5：文字列結合は配列→Join、数値は型を明示

' 文字列は配列へ溜めて最後にJoin
Function JoinLines(ByVal arr As Variant) As String
    Dim i As Long, buf() As String
    ReDim buf(1 To UBound(arr, 1))
    For i = 1 To UBound(arr, 1): buf(i) = CStr(arr(i, 1)): Next
    JoinLines = Join(buf, vbCrLf)
End Function

' 数値はDouble/Longを明示（Variantだらけは遅い）
Sub SumFast(ByVal arr As Variant)
    Dim s As Double, i As Long
    For i = 1 To UBound(arr, 1)
        s = s + CDbl(arr(i, 1))
    Next
    Debug.Print s
End Sub
VB
  • 重要（深掘り）：
    • 連結のたびにメモリ確保が発生: 文字列は配列にしてJoinが鉄板。
    • 型の暗黙変換を減らす: Double/Longを明示するだけで計算は安定して速くなる。

定石6：印刷・外部連携の隠れコストを抑える

  • 印刷設定の通信停止（大量印刷時）: Application.PrintCommunication = False ' 設定変更をバッチ化 ' ... PageSetupの大量変更 ... Application.PrintCommunication = True
  • ページ区切りの自動計算停止（巨大シート扱い）: ActiveSheet.DisplayPageBreaks = False
  • 重要（深掘り）：
    • PageSetupは重い: まとめて適用して通信再開。
    • ページ区切り表示は計算負荷: 巨大データ時はFalseが基本。

まとめテンプレ：安全高速フレーム（貼って使うだけ）

Sub Run_FastTemplate()
    On Error GoTo ErrHandler
    AppEnter "高速処理テンプレ"

    ' 1. 入力（配列で一括）
    Dim ws As Worksheet: Set ws = Worksheets("Input")
    Dim arr As Variant: arr = ws.Range("A1").CurrentRegion.Value

    ' 2. 処理（配列上／型明示／進捗は間引き）
    Dim rows As Long: rows = UBound(arr, 1)
    Dim i As Long, total As Double
    For i = 2 To rows
        total = total + CDbl(arr(i, 5)) ' 例：数値集計
        ProgressThrottled i, rows, "集計"
    Next

    ' 3. 出力（まとめて書く）
    Worksheets("Summary").Range("B2").Value = total

    AppLeave
    MsgBox "完了: " & Format(total, "#,##0")
    Exit Sub
ErrHandler:
    AppLeave
    MsgBox "失敗: " & Err.Description, vbExclamation
End Sub
VB
  • 重要（深掘り）：
    • “枠を通す”だけで安全・高速: 設定スイッチ＋配列I/O＋間引き進捗の3点セットが定石。
    • 処理部分を差し替えるだけで汎用化: フレームは共通資産にできる。

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

  • 落とし穴1：開始・終了の復帰漏れ
    • 対策: 例外でもAppLeaveが必ず走る枠（On Error）にする。
  • 落とし穴2：DoEventsを毎回呼ぶ
    • 対策: 1〜5%刻みの間引きルールに固定。
  • 落とし穴3：DisplayAlertsで事故
    • 対策: 重要操作前後で一時的にONへ戻す、確認UIを用意。
  • 落とし穴4：セル往復の名残
    • 対策: プロジェクト方針として“配列I/O徹底”を宣言。レビュー対象にする。
  • 落とし穴5：列順変更で壊れる
    • 対策: テーブル＋列名Indexで参照。Configや名前定義も併用。

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

  1. AppEnter/AppLeaveを共通モジュールへ入れる。
  2. 既存の長処理を“配列I/O”へ置換。
  3. 進捗・DoEventsは1%刻みの関数で標準化。
  4. テーブル＋列名参照へ切替え、列変更耐性を確保。
  5. 印刷・外部連携の隠れコストを必要に応じてOFFに。

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