ねらい：VBAを「実務システム」に仕立てるための設計・運用のコツ
業務で長く使えるVBAは、コードの巧みさだけでなく「設計と運用の仕組み」が要です。壊れにくい構成、設定の外出し、ログ・進捗・停止、エラー復帰、テストのしやすさ、配布・アップデートの楽さ——これらを小さく揃えていくと、現場で安心して回る「実務システム」になります。初心者でも貼ってすぐ使えるテンプレと、重要ポイントを深掘りして解説します。
全体設計のコツ（分離・共通枠・設定の外出し）
- 分離設計: UI（フォーム/ボタン）・サービス（業務フロー）・ロジック（計算/判定）・IO（シート/ファイル）・Config（設定）・基盤（開始/終了/ログ/進捗）を分けると修正範囲が明確になります。
- 共通枠の導入: すべての処理を「開始（最適化）→実行→後片付け（復帰）→通知」の枠で包み、エラー時も必ず復帰させます。
- 設定の外出し: シートや名前定義（nm_）に保存し、コード側は読み取るだけにすると本番変更に強くなります。
' ModApp: 実務フレーム（開始・終了）
Option Explicit
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
- 重要（深掘り）：
- Finally思想: 例外時でも確実に復帰させる仕組みが「業務品質」の根幹。
- 分離が保守性を最大化: 列順が変わったらIO、ルールが変わったらLogic——直す場所が一目で分かる。
Config運用のコツ（キー管理・型検証・管理者編集）
- キー–値で一元管理: A=Key、B=Value、C=Type（string/number/boolean/path）を基本にします。
- 型検証を実行前に: 誤設定は先に弾き、直すべき場所を具体的に指摘します。
- VeryHidden＋保護: 誤編集を避け、管理者だけ表示・編集できる導線を用意します。
' ModConfig: 設定読み取り（型検証付き）
Option Explicit
Private Function ConfigSheet() As Worksheet
Set ConfigSheet = ThisWorkbook.Worksheets("Config")
End Function
Public Function GetConfigString(ByVal key As String) As String
Dim ws As Worksheet: Set ws = ConfigSheet()
Dim last As Long: last = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Dim r As Long
For r = 2 To last
If StrComp(CStr(ws.Cells(r, "A").Value), key, vbTextCompare) = 0 Then
GetConfigString = Trim$(CStr(ws.Cells(r, "B").Value))
Exit Function
End If
Next
Err.Raise 900, , "Configキーが見つかりません: " & key
End Function
Public Function GetConfigNumber(ByVal key As String) As Double
Dim s As String: s = GetConfigString(key)
If Not IsNumeric(s) Then Err.Raise 901, , "数値ではありません: " & key & "=" & s
GetConfigNumber = CDbl(s)
End Function
- 重要（深掘り）：
- 定数禁止: しきい値やフォルダをコードに埋めない。設定表が唯一の真実に。
- 検証で事故予防: 実行前に止めると「調査→復旧」の手間が激減します。
ログ・進捗・停止のコツ（見える化で安心）
- ログの三段: Start/Finish/Error/Warnを時刻つきでシートに記録。問題時の追跡が容易になります。
- 進捗は1〜5%刻み: UIの安心を保ちながら、DoEvents呼び過ぎによる遅さを防ぎます。
- 停止ボタン（キャンセルフラグ）: 長処理は「止められる」設計が親切。安全停止で後片付けを確実に。
' ModLog/ModProgress
Option Explicit
Public gCancel As Boolean
Public Sub LogToSheet(ByVal action As String, Optional ByVal detail As String = "")
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Worksheets("Log")
If ws Is Nothing Then Set ws = ThisWorkbook.Worksheets.Add: ws.Name = "Log"
On Error GoTo 0
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 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)
If cur Mod stepN = 0 Then
Application.StatusBar = label & " " & Format(cur / total, "0%") & " (" & cur & "/" & total & ")"
DoEvents
End If
End Sub
Public Sub RequestCancel(): gCancel = True: End Sub
- 重要（深掘り）：
- 見える化は信頼の土台: 「動いている・どこまで来た・止められる」の三点で不安を消す。
- 停止はフラグで安全に: 強制終了は環境破壊。Exit Subで必ず復帰経路を通す。
データ扱いのコツ（配列I/O・テーブル列名・チャンク）
- 配列I/Oを徹底: Range→配列で読み、配列で処理し、まとめてRangeへ書き戻します。
- テーブル＋列名参照: ListObjectの列名からIndexを取り、列順変更に強くします。
- チャンク処理＋疑似非同期: 超大規模は数千行単位で分割し、OnTimeで次チャンクを予約してUIフリーズを避けます。
' ModService: 大量処理テンプレ
Sub Run_BulkProcess()
On Error GoTo ErrHandler
AppEnter "大量処理"
LogToSheet "Start", "Bulk"
Dim lo As ListObject: Set lo = Worksheets("Data").ListObjects("tblInput")
Dim arr As Variant: arr = lo.DataBodyRange.Value
Dim rows As Long: rows = UBound(arr, 1)
Dim sumAmt As Double, i As Long, idxAmt As Long
idxAmt = lo.ListColumns("Amount").Index
For i = 1 To rows
sumAmt = sumAmt + CDbl(arr(i, idxAmt))
ProgressThrottled i, rows, "集計"
If gCancel Then LogToSheet "Canceled": AppLeave: MsgBox "中断しました。": Exit Sub
Next
Worksheets("Summary").Range("B2").Value = sumAmt
LogToSheet "Finish", "Sum=" & sumAmt
AppLeave
MsgBox "完了: " & Format(sumAmt, "#,##0")
Exit Sub
ErrHandler:
LogToSheet "Error", Err.Description
AppLeave
MsgBox "失敗: " & Err.Description, vbExclamation
End Sub
- 重要（深掘り）：
- セル往復は全面撤廃: 遅さと不具合の温床。配列へ切り替えると一気に安定・高速化。
- 列名で耐久性アップ: 列が入れ替わっても壊れない仕組みが“実務システム”。
エラー処理と復旧のコツ（握らない・復帰する・知らせる）
- On Errorで入口統一: すべての入口Subで例外を捕捉し、AppLeave・ログ・通知まで一括で行います。
- メッセージは具体的に: 誰が直すか分かるよう、キーや行番号など手がかりを出します。
- バックアップ前提の破壊操作: 削除・上書きの前に SaveCopyAs で退路を確保します。
Sub SafeBackup(ByVal tag As String)
Dim path As String
path = ThisWorkbook.Path & "\" & ThisWorkbook.Name & "_" & tag & ".bak"
ThisWorkbook.SaveCopyAs path
End Sub
- 重要（深掘り）：
- 復帰できる安心感: 失敗時でも環境が元に戻るから現場で怖くない。
- 情報のある失敗: 原因に当たりをつけやすいメッセージは復旧時間を短縮します。
テスト・配布・バージョン管理のコツ（.bas運用・契約固定）
- .basで部品化: 共通モジュール（ModApp/ModConfig/ModIO/ModLog/ModProgress）をエクスポートして配布・差分管理します。
- 契約（インターフェース）固定: Public関数の名前・引数・戻り型を変えない。内部改善は自由に。
- テストSubの同梱: それぞれのモジュールに簡単なテストSubを用意し、配布前に実行します。
' ModIO: テスト例
Sub Test_ReadWrite()
Dim ws As Worksheet: Set ws = Worksheets("Input")
Dim arr As Variant: arr = ws.Range("A1").CurrentRegion.Value
Worksheets("Output").Range("A1").Resize(UBound(arr,1), UBound(arr,2)).Value = arr
MsgBox "I/Oテスト完了"
End Sub
- 重要（深掘り）：
- 契約を守れば壊れない: 利用側は安心、部品側は自由に改良できる。
- 配布が楽だと改善が回る: 現場へ小さく頻繁にアップデートできます。
実務導入チェックリスト（最短で仕上げる）
- 共通枠: AppEnter/AppLeave を導入しているか。
- 設定外出し: Config/名前定義 nm_ から読んでいるか。
- 配列I/O: セル往復をやめたか。
- 進捗・停止: ProgressThrottled とキャンセルフラグを入れたか。
- ログ: Start/Finish/Error を記録しているか。
- テーブル列名: ListObjectで列名Index参照にしたか。
- エラー復帰: 例外でも環境が戻るか。
- 配布性: .bas部品化とテストSubがあるか。
- 重要（深掘り）：
- “やったつもり”を防ぐ仕組み: チェックリストで漏れなく導入。
- 小さく完成→現場フィードバック: まず回す。改善ループを作るのがシステム化の核心。
スターター手順（貼って進めるだけ）
- ModApp/ModConfig/ModLog/ModProgress を作成（ここのコードを貼る）。
- 業務入口Sub を共通枠で包み、On Error→ログ→復帰まで揃える。
- データ処理を配列I/O に置換し、テーブル列名参照へ切り替える。
- 進捗と停止 を1〜5%刻みで標準化。ログで開始・完了・エラーを記録。
- .basとして部品を管理 し、テストSubで配布前チェック。