ねらい：ロジック分離で「壊れにくく、直しやすい」VBAへ
Excel VBAは「セル操作」と「ビジネス処理」が混ざりやすく、修正が難しくなりがちです。ロジック分離とは、画面（UI）・データアクセス（シート/ファイル）・業務ロジック（計算/判定）・設定（Config）・共通基盤（開始/終了/ログ/進捗）を分ける設計のこと。初心者でも貼って使えるテンプレを用意し、重要部分を深掘りして解説します。
- 目的: 変更の影響を局所化し、テスト可能で再利用しやすいコードを作る
- 基本方針: 「業務ロジックはセルを知らない」「UIはロジックを呼ぶだけ」に徹する
- 重要ポイント（深掘り）：
- 分層化が命: 読む・計算する・書くを別モジュールに分ける
- 純粋関数化: 入力→出力だけの関数にするとテスト・再利用が超簡単
- 例外と後片付け: どこで失敗しても「環境復帰」できる共通フレームを用意
ロジック分離の原則（短く強く）
- UIとロジックを分ける:
- UIは見せるだけ: ボタン押下、メッセージ、進捗表示
- ロジックは計算だけ: セル・Range・Workbookを知らない関数
- データアクセスを分ける:
- Reader/Writer: シートから配列に読み、配列からシートに書く
- 設定を分ける:
- Configシート: 変更はシート編集、コードは読むだけ
- 共通基盤を用意:
- 開始/終了/ログ/進捗/停止: すべての処理で同じフレームを再利用
- 深掘り（重要）:
- 依存の向き: UI→サービス（業務）→ロジック（純粋）／逆は無し
- 副作用の封じ込め: セル書き換えは「Writer」だけに集約し、他は触らない
プロジェクト構成テンプレ
- ModApp（共通基盤）: 開始/終了/例外、最適化スイッチ
- ModConfig（設定）: Config読み取りヘルパー
- ModIO（データアクセス）: シート→配列、配列→シート
- ModLogic（業務ロジック）: 計算・判定の純粋関数
- ModService（サービス）: IOとLogicを組み合わせる業務ユースケース
- ModUI（入口）: ボタン/メニュー/メッセージ/進捗
- Optional（UserFormProgress）: プログレスバー、停止ボタン
- 深掘り（重要）:
- 変更の場所が明確: シート列変わった→ModIOだけ直す、判定ルール変わった→ModLogicだけ直す
- テストしやすい: ModLogicは単体テスト可能（配列や値を渡すだけ）
共通基盤（開始・終了・例外）テンプレ
' 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
- 深掘り（重要）:
- Finally思想: 「どこで落ちても」戻せる枠があると安心
- 速度と安定: 画面更新OFF、イベントOFFでブレを減らす
設定の分離（Config読み取り）
' 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 lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Dim r As Long
For r = 2 To lastRow
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 701, , "Configキーが見つかりません: " & key
End Function
- 深掘り（重要）:
- 定数禁止: 文字列・フォルダ・閾値はConfigから読む前提に
- 誤設定検知: 見つからなければ即エラー。黙ったまま進めない
データアクセスの分離（Reader/Writer）
' ModIO
Option Explicit
Public Function ReadInput(ByVal sheetName As String) As Variant
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets(sheetName)
Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Dim lastCol As Long: lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
If lastRow < 2 Then Err.Raise 710, , "入力が空です: " & sheetName
ReadInput = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Value ' 2次元配列
End Function
Public Sub WriteOutput(ByVal sheetName As String, ByVal destTopLeft As String, ByVal data As Variant)
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets(sheetName)
Dim rows As Long: rows = UBound(data, 1)
Dim cols As Long: cols = UBound(data, 2)
ws.Range(destTopLeft).Resize(rows, cols).Value = data
End Sub
- 深掘り（重要）:
- セル非依存のI/O API: ロジックは配列を受け取り、配列を返すだけ
- 範囲の決定はIO側: 列追加・順序変更はIOで吸収
業務ロジックの分離（純粋関数）
例：入力データから「合格判定」列を追加するロジック。セルを触らない。
' ModLogic
Option Explicit
' data: 1行目ヘッダー、2行目以降データ
' threshold: 合格点
Public Function AddPassFlag(ByVal data As Variant, ByVal threshold As Double) As Variant
Dim rows As Long: rows = UBound(data, 1)
Dim cols As Long: cols = UBound(data, 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) = data(1, c): Next
out(1, cols + 1) = "合格"
' 判定（例：点数がE列=5列目）
For r = 2 To rows
For c = 1 To cols: out(r, c) = data(r, c): Next
out(r, cols + 1) = IIf(Val(data(r, 5)) >= threshold, "○", "×")
Next
AddPassFlag = out
End Function
- 深掘り（重要）:
- 副作用ゼロ: 読み・書きはしない。入力→出力のみ
- テスト容易: 単体テストで配列を渡して結果を確認できる
サービス層（IO×Logicの組み合わせ）
' ModService
Option Explicit
Public Sub Run_AddPassFlag()
On Error GoTo ErrHandler
AppEnter "合格判定"
Dim inputSheet As String: inputSheet = GetConfigString("INPUT_SHEET") ' 例: "Input"
Dim outputSheet As String: outputSheet = GetConfigString("OUTPUT_SHEET") ' 例: "Output"
Dim threshold As Double: threshold = Val(GetConfigString("THRESHOLD_SCORE"))
Dim data As Variant: data = ReadInput(inputSheet)
Dim out As Variant: out = AddPassFlag(data, threshold)
Call WriteOutput(outputSheet, "A1", out)
AppLeave True
MsgBox "合格判定を出力しました。"
Exit Sub
ErrHandler:
AppLeave True
MsgBox "失敗: " & Err.Description, vbExclamation
End Sub
- 深掘り（重要）:
- 依存の流れが綺麗: Config→IO→Logic→IO。UIはここを呼ぶだけ
- 差し替え容易: 列位置・判定ルールが変わっても、直す場所が特定できる
UI層（ボタン・メニュー・完了メッセージ）
' ModUI
Option Explicit
Public Sub Btn_Run()
Call Run_AddPassFlag
End Sub
- 深掘り（重要）:
- UIは薄く: 入口だけ。メッセージや進捗表示をここで足す
- 再利用: 同じロジックを複数UI（ボタン/メニュー/ショートカット）から呼べる
単体テストのやり方（ロジックが分離されているから簡単）
Sub Test_AddPassFlag()
Dim data(1 To 3, 1 To 5) As Variant
' ヘッダー
data(1, 1) = "社員番号": data(1, 2) = "氏名": data(1, 3) = "部署": data(1, 4) = "日付": data(1, 5) = "点数"
' データ
data(2, 1) = "000001": data(2, 5) = 80
data(3, 1) = "000002": data(3, 5) = 60
Dim out As Variant: out = AddPassFlag(data, 70)
Debug.Print out(1, 6) ' 合格
Debug.Print out(2, 6) ' ○
Debug.Print out(3, 6) ' ×
End Sub
- 深掘り（重要）:
- Excel不要で検証: シート操作なし。即座に結果を確認
- 回帰テスト: 仕様変更時にテストで壊れを検出できる
例題で練習（貼って試せる）
- 例1（分離体験）: Inputに「点数」を用意→Btn_RunでOutputに「合格」列が追加される。
- 例2（Config変更）: THRESHOLD_SCOREを60に変更→コード無改修で結果が変わる。
- 例3（列変更）: 点数列をE→Fへ移動した場合、ModLogicの参照列だけ直す。
- 例4（単体テスト）: Test_AddPassFlagで配列テストを実行、結果が期待通りか確認。
実務の落とし穴と対策（ここが肝）
- 落とし穴1：ロジックがセル参照だらけ
- 対策: IOで配列化→ロジックは配列のみ扱う。セル参照はWriterに限定。
- 落とし穴2：例外時に環境が戻らない
- 対策: AppEnter/AppLeaveのFinallyを徹底。すべての入口で使う。
- 落とし穴3：責務が曖昧になり再び混ざる
- 対策: モジュールごとに「して良いこと」を決め、違反をレビューで防ぐ。
- 落とし穴4：Configを読まずに定数埋め込み
- 対策: 文字列・パス・閾値は必ずGetConfigXXX経由に統一。
- 落とし穴5：テストがない
- 対策: ModLogicに「純粋関数」を増やし、テストSubをセットで書く。
スターター手順（最短導入）
- モジュールを分ける: ModApp/ModConfig/ModIO/ModLogic/ModService/ModUIを作成。
- IOとロジックを分離: まず「配列に読み→配列で処理→配列を書き戻す」形に変換。
- Config化: シート名・フォルダ・閾値はConfigから読むヘルパーを導入。
- テストSubを用意: ロジックの純粋関数に対して配列テストを作る。
- 入口統一: すべての処理でAppEnter/AppLeave＋例外ハンドリングを徹底。