Excel VBA 逆引き集 | 自動バックグラウンド処理

Excel VBA
スポンサーリンク

ねらい：Excelで「自動バックグラウンド処理」を安全に回し、UIを固めずに完走させる

自動バックグラウンド処理とは、ユーザー操作に依存せず、一定間隔や条件で軽く監視しながら、重い処理は分割して少しずつ進める設計です。Excelでは完全な非同期はできませんが、OnTime予約で疑似的に段階実行にし、配列I/Oで高速化し、進捗表示とキャンセルを備えれば、長時間・大量データでも安定運用が可能です。初心者向けに、貼って動くテンプレートを「予約・分割・可視化・停止・復帰」の観点で解説します。

重要ポイントの深掘り

長い処理を「ひと塊で回し切る」のではなく、「チャンク（小さな塊）」に分けて実行し、各チャンクの終わりでUIを解放して次回を予約するのがコツです。開始・終了の共通枠で描画と再計算を制御し、途中の進捗を間引いて更新し、キャンセルフラグや排他ロックを設けることで、固まらず・二重起動せず・必ず戻るバランスが取れます。

疑似非同期の基盤：OnTimeで段階実行を予約する

OnTimeの使い方と設計の勘所

Application.OnTimeは指定時刻に指定プロシージャを呼びます。チャンク処理の各終了時に「次のチャンクを何秒後に予約」すると、UI応答を保ちながら連続的に進められます。呼び出しが連続するので、キャンセル用のフラグと、次回時刻の記録を必ず用意します。

' ModScheduler.bas
Option Explicit

Private gNext As Date
Private gStop As Boolean

Public Sub StartBackground(ByVal seconds As Double)
    gStop = False
    ScheduleNext seconds
End Sub

Public Sub StopBackground()
    gStop = True
End Sub

Private Sub ScheduleNext(ByVal seconds As Double)
    gNext = Now + seconds / 86400#   ' 秒 → 日
    Application.OnTime gNext, "RunNextChunk", , True
End Sub
VB

チャンク処理テンプレート：読み・処理・書き戻し・予約を繰り返す

大量行を数千行単位で分割し、UIを固めずに処理する

チャンクでは、対象範囲を配列に読み、必要な計算を行い、結果を一括で書き戻します。各チャンク終了時に進捗を表示し、次回を予約します。

' ModChunkWorker.bas
Option Explicit

Private gStartRow As Long
Private gLastRow As Long
Private gChunkSize As Long

Public Sub InitChunkWorker(ByVal ws As Worksheet, ByVal firstDataRow As Long, ByVal lastDataRow As Long, ByVal chunkSize As Long)
    gStartRow = firstDataRow
    gLastRow = lastDataRow
    gChunkSize = chunkSize
End Sub

Public Sub RunNextChunk()
    If gStartRow = 0 Or gChunkSize = 0 Then Exit Sub      ' 未初期化ガード
    If gStop Then
        Application.StatusBar = False
        Exit Sub
    End If

    If gStartRow > gLastRow Then
        Application.StatusBar = "完了"
        DoEvents
        Application.StatusBar = False
        Exit Sub
    End If

    Dim ws As Worksheet: Set ws = Worksheets("Input")
    Dim endRow As Long: endRow = WorksheetFunction.Min(gStartRow + gChunkSize - 1, gLastRow)

    Dim arr As Variant
    arr = ws.Range(ws.Cells(gStartRow, "A"), ws.Cells(endRow, "F")).Value

    Dim i As Long, idxAmt As Long: idxAmt = 4   ' 例：D列が Amount
    Dim total As Double
    For i = 1 To UBound(arr, 1)
        total = total + CDbl(arr(i, idxAmt))
    Next

    Worksheets("Summary").Range("B2").Value = Worksheets("Summary").Range("B2").Value + total

    Application.StatusBar = "進捗 " & Format(endRow / gLastRow, "0%") & " (" & endRow & "/" & gLastRow & ")"
    DoEvents

    gStartRow = endRow + 1
    ScheduleNext 1   ' 次チャンクを1秒後に予約
End Sub
VB

実行の入口と後片付け：開始・終了枠で「落ちても戻る」

入口から初期化・予約までを一気通貫で行う

開始・終了の共通枠（描画・イベント・再計算の制御）を入口に入れつつ、チャンクワーカーを初期化し、最初のチャンクを予約します。停止時は必ずステータスバーを戻します。

' ModEntry.bas
Option Explicit

Public Sub Run_BackgroundAggregate()
    On Error GoTo EH
    AppEnter "バックグラウンド集計"

    Dim ws As Worksheet: Set ws = Worksheets("Input")
    Dim last As Long: last = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    InitChunkWorker ws, 2, last, 5000
    StartBackground 0   ' 即時に次チャンクへ

    AppLeave
    MsgBox "バックグラウンド開始（停止は StopBackground を実行）"
    Exit Sub
EH:
    AppLeave
    Application.StatusBar = False
    MsgBox "失敗: " & Err.Description, vbExclamation
End Sub

Public 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

Public Sub AppLeave()
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
VB

進捗表示とキャンセル：体感の安心と安全な停止を備える

ステータスバーの更新を間引き、DoEventsでUI応答を保つ

進捗は常に更新せず、チャンク終了時や一定割合で更新します。DoEventsは呼び過ぎると遅くなるため、チャンク終端のみに配置します。終了時は必ず StatusBar を False に戻します。

停止フラグで安全に中断する

StopBackground を呼ぶと gStop が立ち、次のチャンク開始時に処理を抜けます。ユーザー操作やエラー復帰の場面でも、確実に止められる経路を用意します。

' すでに前章に定義済み（StopBackground と gStop を使用）
' 停止の例：リボンやボタンに StopBackground を割り当て
Sub Run_Stop()
    StopBackground
    Application.StatusBar = False
    MsgBox "停止しました"
End Sub
VB

ファイル取り込みのバックグラウンド化：更新を検知してインポート

最終更新日時の差分で取り込みタイミングを判断する

タイマーで一定間隔にファイルの更新をチェックし、差分があればインポート処理を予約します。取り込み自体もチャンク化して、巨大CSVでも固まりません。

' ModCsvWatchWorker.bas
Option Explicit

Private gLastStamp As Date

Public Sub StartCsvWatcher(ByVal csvPath As String)
    gLastStamp = GetStamp(csvPath)
    ScheduleNext 5
End Sub

Public Sub TickCsvWatcher()
    Dim path As String: path = ThisWorkbook.Path & "\input.csv"
    Dim cur As Date: cur = GetStamp(path)
    If cur > gLastStamp Then
        gLastStamp = cur
        InitCsvImport path
        ScheduleNext 0
    Else
        ScheduleNext 5
    End If
End Sub

Private Function GetStamp(ByVal path As String) As Date
    Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FileExists(path) Then GetStamp = fso.GetFile(path).DateLastModified
End Function

Public Sub InitCsvImport(ByVal path As String)
    ' ここでCSVのチャンク読み込みを初期化する（同様の構造でRunNextChunkへ）
    MsgBox "CSV更新を検知：取り込み開始"
End Sub
VB

二重起動防止と失敗時の復帰：実務で落とさないための枠

実行ロックで「同時起動」を抑止する

名前定義や隠しセルをロックとして使い、入口でロックを取得できなければスキップします。終了時の解除は、成功・失敗に関わらず確実に行います。

' ModLock.bas
Option Explicit

Public Function TryLock() As Boolean
    On Error Resume Next
    ThisWorkbook.Names("nm_Lock").RefersToRange.Value = Environ$("UserName") & "@" & Format(Now, "yyyy-mm-dd HH:NN:SS")
    TryLock = (Err.Number = 0)
    Err.Clear
    On Error GoTo 0
End Function

Public Sub Unlock()
    On Error Resume Next
    ThisWorkbook.Names("nm_Lock").RefersToRange.Value = ""
    On Error GoTo 0
End Sub
VB
' 入口で使用例
Sub Run_BackgroundAggregate_Safe()
    If Not TryLock() Then
        MsgBox "他の処理が実行中です。しばらくしてから再試行してください。", vbExclamation
        Exit Sub
    End If
    On Error GoTo EH
    Run_BackgroundAggregate
    Unlock
    Exit Sub
EH:
    Unlock
End Sub
VB

例題の通し方と現場での確認ポイント

チャンク集計を回してUIが固まらないかを確認する

Inputシートに数万行以上のデータを用意し、Run_BackgroundAggregate を実行します。Summaryの合計がチャンクごとに増え、ステータスバーが進捗を示し、Excel操作が可能なまま完了するかを確認します。

停止と再開が安全に動作するかを確認する

途中で Run_Stop を実行して停止できるか、StatusBarが消えるか、再度 Run_BackgroundAggregate で再開できるかを確認します。停止後に再開した際に二重起動にならないことも合わせて見ます。

ファイル更新監視で自動取り込みが正しくトリガーされるかを確認する

input.csv を更新し、StartCsvWatcher を開始しておくと、更新検知のメッセージや取り込み初期化が走ることを確認します。本取り込みをチャンクワーカーへつなげると、巨大CSVでも固まりません。

まとめ：予約・分割・可視化・停止・復帰の五点で「固まらない自動化」を実現する

OnTimeで次回を予約し、処理はチャンクへ分割、進捗は間引いて可視化し、キャンセルで安全に止め、開始・終了枠とロックで必ず復帰させる。この五点が揃うと、Excelでも実用的なバックグラウンド処理が成立します。

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