ねらい：壊れにくいマクロを量産する「設計テンプレ」
現場で長く使えるVBAは、コードの巧みさより「設計」が命です。開始・検証・処理・ログ・進捗・停止・後片付けを統一フレームにすると、再利用性・保守性・信頼性が一気に上がります。初心者でも貼ってすぐ使える「設計テンプレ」を、重要ポイントを深掘りしながら紹介します。
プロジェクト構成の骨子
- 標準モジュール（ModApp）: 最適化スイッチ、後片付け、共通メッセージ
- 標準モジュール（ModGuard）: 入力検証、前提チェック（シート・ヘッダー・範囲）
- 標準モジュール（ModLog）: ログ出力（シート、ステータスバー、ファイル）
- 標準モジュール（ModProgress）: 進捗表示（ステータスバー／フォーム）
- 標準モジュール（ModMain）: 業務処理テンプレ（開始→検証→処理→終了の流れ）
- オプション（UserFormProgress）: プログレスバーと停止ボタン
- 重要ポイント:
- 分離設計: 共通機能（ログ・進捗・検証）をモジュール化し、業務処理は薄く保つ。
- 入口統一: すべての処理は同じ「開始/終了フレーム」で包む（事故防止）。
共通フレーム（開始・終了・例外）テンプレ
' ModApp
Option Explicit
Public Sub AppEnter(Optional ByVal msg As String = "")
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If Len(msg) > 0 Then Application.StatusBar = msg
End Sub
Public Sub AppLeave(Optional ByVal clearStatusBar As Boolean = True)
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
If clearStatusBar Then Application.StatusBar = False
End Sub
Public Sub SafeRun(ByVal procName As String, ByVal runner As Variant)
On Error GoTo ErrHandler
AppEnter "開始: " & procName
Application.Run runner ' 実処理を渡す（例：AddressOf できないため名前で）
AppLeave True
MsgBox procName & " が完了しました。"
Exit Sub
ErrHandler:
Dim m As String: m = "エラー: " & Err.Number & " - " & Err.Description
AppLeave True
MsgBox m, vbCritical
End Sub
- 重要ポイント（深掘り）:
- Finallyの徹底: エラー時でも確実に元の状態へ戻す。
- 入口の揃え方: SafeRunで開始/後片付け/エラーメッセージを統一。
入力検証・前提チェックテンプレ（壊れない初手）
' ModGuard
Option Explicit
Public Function RequireSheet(ByVal name As String) As Worksheet
On Error Resume Next
Set RequireSheet = ThisWorkbook.Worksheets(name)
On Error GoTo 0
If RequireSheet Is Nothing Then Err.Raise 513, , "必須シートがありません: " & name
End Function
Public Function RequireHeader(ByVal ws As Worksheet, ByVal headerRow As Long, ByRef expected() As String) As Boolean
Dim lastCol As Long: lastCol = ws.Cells(headerRow, ws.Columns.Count).End(xlToLeft).Column
Dim setCols As Object: Set setCols = CreateObject("Scripting.Dictionary")
Dim c As Long
For c = 1 To lastCol
setCols(Trim$(CStr(ws.Cells(headerRow, c).Value))) = True
Next
Dim miss As Collection: Set miss = New Collection
Dim i As Long
For i = LBound(expected) To UBound(expected)
If Not setCols.Exists(Trim$(expected(i))) Then miss.Add expected(i)
Next
If miss.Count > 0 Then
Err.Raise 514, , "ヘッダー不足: " & Join(CollectionToArray(miss), ", ")
End If
RequireHeader = True
End Function
Private Function CollectionToArray(ByVal col As Collection) As Variant
If col.Count = 0 Then CollectionToArray = Split("", ","): Exit Function
Dim i As Long, a() As String: ReDim a(1 To col.Count)
For i = 1 To col.Count: a(i) = CStr(col(i)): Next
CollectionToArray = a
End Function
- 重要ポイント（深掘り）:
- 即時中断: 前提が壊れていたら処理に入らない（早期Failは安全）。
- 具体的な指摘: “ヘッダー不足: 氏名, 入社日”のように直せる情報を出す。
ログテンプレ（透明性と追跡）
' ModLog
Option Explicit
Public Sub LogToSheet(ByVal action As String, Optional ByVal detail As String = "")
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Log")
Dim r As Long: r = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
ws.Cells(r, 1).Value = Format(Now, "yyyy-mm-dd HH:NN:SS")
ws.Cells(r, 2).Value = action
ws.Cells(r, 3).Value = detail
End Sub
Public Sub Status(ByVal text As String)
Application.StatusBar = text
End Sub
- 重要ポイント（深掘り）:
- 開始・終了・件数・エラーを記録: 問題が起きたときに時系列で辿れる。
- 人に見える場所: Logシートは最初に作る。フォーマットはシンプルで十分。
進捗テンプレ（ステータスバー簡易版）
' ModProgress
Option Explicit
Public Sub Progress(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
Dim pct As Double: pct = cur / total
Application.StatusBar = label & " " & Format(pct, "0%") & " (" & cur & "/" & total & ")"
DoEvents
End If
End Sub
- 重要ポイント（深掘り）:
- 更新間引き: 総件数に応じて更新頻度を自動調整。
- DoEvents: UI応答を保つ（停止ボタンなどと連携）。
キャンセル（停止）テンプレ
' ModProgress（または専用モジュール）
Public gCancel As Boolean
Public Sub RequestCancel()
gCancel = True
End Sub
Public Function ShouldCancel() As Boolean
DoEvents
ShouldCancel = gCancel
End Function
- 重要ポイント（深掘り）:
- フラグ判定: ループ内で
If ShouldCancel Then Exit Sub。 - 後片付け: 停止時も AppLeave とログ出力を必ず。
- フラグ判定: ループ内で
業務処理テンプレ（統一フロー）
' ModMain
Option Explicit
Public Sub Run_EmployeeImport()
On Error GoTo ErrHandler
AppEnter "社員取込 開始"
LogToSheet "Start", "社員取込"
' 1. 前提チェック
Dim ws As Worksheet: Set ws = RequireSheet("Input")
Dim expected() As String: expected = Split("社員番号,氏名,電話,入社日", ",")
Call RequireHeader(ws, 1, expected)
' 2. 総件数
Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then Err.Raise 515, , "入力が空です"
Dim total As Long: total = lastRow - 1
' 3. ループ処理（進捗＆停止対応）
Dim r As Long
For r = 2 To lastRow
' 停止
If ShouldCancel Then
LogToSheet "Canceled", "行=" & r
Err.Raise 516, , "ユーザーにより中断"
End If
' --- 本処理例：検証＋整形＋書き込み ---
Dim emp As String: emp = CStr(ws.Cells(r, "A").Value)
If emp Like "*[!0-9]*" Or Len(emp) <> 6 Then
LogToSheet "Warn", "形式不正 社員番号 行=" & r
End If
' ----------------------------------------
Progress r - 1, total, "社員取込"
Next
' 4. 完了
LogToSheet "Finish", "社員取込 件数=" & total
AppLeave True
MsgBox "社員取込が完了しました。（件数 " & total & "）"
Exit Sub
ErrHandler:
Dim m As String: m = "失敗: " & Err.Description
LogToSheet "Error", m
AppLeave True
MsgBox m, vbExclamation
End Sub
- 重要ポイント（深掘り）:
- 順序の固定化: 前提→件数→処理→進捗→停止→完了/失敗。すべての業務処理で同じ流れにする。
- エラーを握らない: エラーメッセージ＋ログ必須。沈黙は故障の元。
ユーザーフォーム連携（プログレス＋停止）
- 構成: UserFormProgress（LabelBar, LabelText, ButtonCancel）
- 開始時:
UserFormProgress.Show vbModeless - 更新: LabelBar.Width でバー伸長、ButtonCancelで
RequestCancel - 終了時:
Unload UserFormProgress - 重要ポイント:
- 非モーダル表示: 長処理中でもクリックを受け付ける。
- UIとロジック分離: フォームは表示だけ、停止はフラグで受ける。
例題で練習（貼って試せる）
- 例1（フレーム体験）: SafeRun “社員取込”, “Run_EmployeeImport” を実行し、開始/終了/例外時の動きを確認。
- 例2（検証ミスを再現）: Inputのヘッダーを1つ消し、RequireHeaderが止めるか確認。
- 例3（進捗・停止）: 大量行で実行し、RequestCancel をボタンに割り当てて中断。
- 例4（ログ確認）: Logシートに Start/Finish/Warn/Error が記録されることを確認。
実務の落とし穴と対策（ここが肝）
- 落とし穴1：開始・終了処理の抜け漏れ
- 対策: すべて SafeRun/AppEnter/AppLeave 経由で実行。直呼び禁止。
- 落とし穴2：検証不足で後半で落ちる
- 対策: Require系を先頭に。Fail Fastで前提崩れを弾く。
- 落とし穴3：UIフリーズ
- 対策: 進捗更新＋DoEvents。ScreenUpdatingはOFFで負荷低減。
- 落とし穴4：停止後の後片付け忘れ
- 対策: ErrHandlerでも必ず AppLeave とログ。Finally思想の徹底。
- 落とし穴5：ログが散在して読めない
- 対策: LogToSheetを一本化。時刻・アクション・詳細の3列固定。
スターター手順（最短導入）
- ModApp・ModGuard・ModLog・ModProgress を貼る。
- Logシート を作成（A:日時、B:アクション、C:詳細）。
- 業務処理を ModMain に追加（Run_XXXXX の形）。
- SafeRun で実行し、進捗・停止・エラー時の挙動を確認。
- 必要ならユーザーフォーム進捗を追加してUI強化。