Excel VBA 逆引き集 | MVC風構造

Excel VBA
スポンサーリンク

ねらい：ExcelでMVC風構造にして「読みやすく・壊れにくく・拡張しやすく」する

Excel VBAは、シート操作や業務ルール、フォームの入力が混ざりやすく、少しの変更で全体が崩れがちです。MVC風構造（Model・View・Controllerの役割分担）にすると、どこを直せばよいかが明確になり、速度と保守性が同時に上がります。ここでは初心者でも貼って動かせる最小コードと、例題を通した実践的な設計ポイントを解説します。

重要ポイントの深掘り

ExcelのMVC風では、Modelは「業務データとルール」、Viewは「シートやフォームの見せ方」、Controllerは「全体の流れと調停」を担います。依存の向きを一方向に固定し、Modelはセルを触らず、Viewはロジックを書かず、Controllerは両者をつなぐだけにすると、修正の影響範囲が小さく保たれます。さらに、入出力は配列I/O（まとめて読み・まとめて書き）に統一すると、速度と安定性が大きく向上します。

全体像：MVCの役割と依存の向き

Modelの責務と設計ポイント

Modelは「業務データの構造」と「妥当性」「業務ルールの判定」を持ちます。セル参照やRange操作は持たず、値と配列を扱う純粋なコードにします。妥当性はプロパティやコンストラクタで早期にチェックし、不正値は例外で止めることで、下流の事故を防ぎます。

Viewの責務と設計ポイント

Viewは「見せる・入力を受ける」だけを担当します。シートの見出し、フォームのテキストボックスやボタンの配置、完了メッセージなどUIの体験を提供します。ロジックや入出力は書かず、Controllerの公開メソッドを呼ぶだけにします。

Controllerの責務と設計ポイント

Controllerは「開始→読み→検証→処理→出力→通知→記録」という流れを束ねる司令塔です。開始・終了の共通枠（描画・イベント・再計算の制御）、例外復帰、進捗、ログなどの運用品質をここで統一します。ModelとViewをつなぎ、入出力の詳細はRepositoryに委譲します。

最小テンプレート一式

Model（業務データとルールの純粋化）

' CEmployee.cls（モデル：社員1件）
Option Explicit

Private pEmpNo As String
Private pName As String
Private pDept As String
Private pScore As Double

Public Property Get EmpNo() As String: EmpNo = pEmpNo: End Property
Public Property Let EmpNo(ByVal v As String)
    If Len(v) <> 6 Or v Like "*[!0-9]*" Then Err.Raise 1001, , "社員番号は6桁の数字"
    pEmpNo = v
End Property

Public Property Get Name() As String: Name = pName: End Property
Public Property Let Name(ByVal v As String): pName = Trim$(v): End Property

Public Property Get Dept() As String: Dept = pDept: End Property
Public Property Let Dept(ByVal v As String): pDept = Trim$(v): End Property

Public Property Get Score() As Double: Score = pScore: End Property
Public Property Let Score(ByVal v As Double)
    If v < 0 Or v > 100 Then Err.Raise 1002, , "スコアは0～100"
    pScore = v
End Property

Public Function IsPass(ByVal threshold As Double) As Boolean
    IsPass = (pScore >= threshold)
End Function
VB
' CEmployeeList.cls（モデル：集合）
Option Explicit
Private items As Collection
Private Sub Class_Initialize(): Set items = New Collection: End Sub

Public Sub Add(ByVal emp As CEmployee): items.Add emp, emp.EmpNo: End Sub
Public Function Count() As Long: Count = items.Count: End Function

Public Function ToArrayWithPass(ByVal threshold As Double) As Variant
    If items.Count = 0 Then Exit Function
    Dim a() As Variant: ReDim a(1 To items.Count + 1, 1 To 5)
    a(1, 1) = "EmpNo": a(1, 2) = "Name": a(1, 3) = "Dept": a(1, 4) = "Score": a(1, 5) = "Pass"
    Dim i As Long
    For i = 1 To items.Count
        Dim e As CEmployee: Set e = items(i)
        a(i + 1, 1) = e.EmpNo
        a(i + 1, 2) = e.Name
        a(i + 1, 3) = e.Dept
        a(i + 1, 4) = e.Score
        a(i + 1, 5) = IIf(e.IsPass(threshold), "○", "×")
    Next
    ToArrayWithPass = a
End Function
VB

Repository（入出力の窓口を分離）

' CEmployeeRepository.cls（シートI/O）
Option Explicit

Public Function ReadFromSheet(ByVal ws As Worksheet) As CEmployeeList
    Dim last As Long: last = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Dim list As New CEmployeeList
    Dim r As Long
    For r = 2 To last
        Dim emp As New CEmployee
        emp.EmpNo = CStr(ws.Cells(r, "A").Value)
        emp.Name  = CStr(ws.Cells(r, "B").Value)
        emp.Dept  = CStr(ws.Cells(r, "C").Value)
        If IsNumeric(ws.Cells(r, "D").Value) Then emp.Score = CDbl(ws.Cells(r, "D").Value)
        list.Add emp
    Next
    Set ReadFromSheet = list
End Function

Public Sub WritePassToSheet(ByVal ws As Worksheet, ByVal list As CEmployeeList, ByVal threshold As Double)
    Dim arr As Variant: arr = list.ToArrayWithPass(threshold)
    If IsEmpty(arr) Then Exit Sub
    ws.Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End Sub
VB

View（フォームは薄く、Controllerの窓口だけ）

' FrmThreshold.frm（ビュー：閾値入力）
Option Explicit
Public Property Get ThresholdValue() As Double
    ThresholdValue = Val(Me.txtThreshold.Text)
End Property

Private Sub btnOK_Click(): Me.Hide: End Sub
Private Sub btnCancel_Click(): Me.Hide: End Sub
VB

Controller（業務フローの司令塔）

' CEmployeeService.cls（コントローラ：業務フロー）
Option Explicit

Public Sub RunPassProcess(ByVal wsIn As Worksheet, ByVal wsOut As Worksheet, ByVal threshold As Double)
    On Error GoTo EH
    AppEnter "合格判定"
    
    Dim repo As New CEmployeeRepository
    Dim list As CEmployeeList
    Set list = repo.ReadFromSheet(wsIn)
    
    repo.WritePassToSheet wsOut, list, threshold
    
    AppLeave
    MsgBox "完了（" & list.Count & "件）"
    Exit Sub
EH:
    AppLeave
    MsgBox "失敗: " & Err.Description, vbExclamation
End Sub
VB
' ModApp.bas（共通枠：開始・終了）
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
VB

例題：日次合格判定フローをMVCで構築

Modelの単体確認

Modelがセルを触らずに正しく判定できるかを確認します。Immediateウィンドウに結果が出れば、ロジックは独立してテスト可能です。

Sub Test_Model_IsPass()
    Dim e As New CEmployee
    e.EmpNo = "000123": e.Name = "山田": e.Dept = "Sales": e.Score = 80
    Debug.Print e.IsPass(70) ' True
End Sub
VB

ViewからControllerを呼び出す流れ

UIは薄く、Controllerの公開メソッドだけ呼びます。フォームで閾値を受け取り、既定のシートに対してControllerを実行します。

' ModEntry.bas（入口統一：Run_XXXX）
Option Explicit
Public Sub Run_EmployeePass_MVC()
    Dim frm As New FrmThreshold
    frm.txtThreshold.Text = "70"
    frm.Show vbModal
    If Len(frm.txtThreshold.Text) = 0 Then Unload frm: Exit Sub
    
    Dim svc As New CEmployeeService
    svc.RunPassProcess Worksheets("Input"), Worksheets("Output"), frm.ThresholdValue
    
    Unload frm
End Sub
VB

実行方法と期待結果の確認ポイント

InputシートのA列にEmpNo（6桁数字）、B列にName、C列にDept、D列にScore（0～100）を用意します。Run_EmployeePass_MVC を実行すると、OutputシートA1から「EmpNo, Name, Dept, Score, Pass」ヘッダーで表が出力され、Scoreが閾値以上なら「○」、未満なら「×」が表示されます。列や閾値を変更しても、ViewとControllerの接続を保ちつつModelは同じロジックで動作します。

深掘り：壊れないための設計ルール

依存方向の固定が保守性を生む

UI（View）→Controller→Model／Repositoryの順だけに依存させます。ModelからRangeを参照しない、Controllerにセル処理を持ち込まない、Repositoryに業務ルールを書かないという三禁則を守ると、列変更やルール変更の修正範囲が自然に小さくなります。

配列I/Oで速度とテスト容易性を両立する

読みは一括（CurrentRegionやDataBodyRange）、処理は配列上のループ、書き戻しも一括（Resizeで代入）に統一します。1セルずつの往復は桁違いに遅く、イベントや再計算を引きがちです。配列にするとModelのテストも容易になり、性能も安定します。

イベントと進捗の扱いはControllerで統一する

DoEventsの呼びすぎは遅さの原因です。進捗の更新は1～5%刻みに間引き、開始・終了の枠で描画・イベント・再計算を止めるのを徹底します。UI応答と速度のバランスはControllerで集中管理すると、どのフローでも品質が揃います。

拡張の道筋と落とし穴回避

入力源の変更に耐えるリポジトリ差し替え

シートから読み取るCEmployeeRepositoryの代わりに、テーブル（ListObject）やCSVのRepositoryを作れば、Controllerの呼び出しだけ差し替えて同じModelとViewを保てます。契約（配列の入出力）を固定するのがコツです。

画面拡張はViewを増やすだけで済む構造にする

フォームを増やす場合も、Controllerの公開メソッドを呼ぶだけにすれば、Viewの差し替えで対応可能です。入力チェックはViewで最小限、業務ルールはModelで扱う分担を守ることで、画面追加の影響が限定されます。

ロジック変更の影響を最小化する

合格判定の基準が変わる、部門によって基準が違うなどの変更は、ModelのIsPassや出力配列生成の部分だけ差し替えれば済むようにしておきます。ControllerやViewには触れずに改訂できる設計が、業務フローの安定運用につながります。

まとめ：ExcelでもMVC風に分ければ「直す場所が分かる」

Modelは値とルール、Viewは見せるだけ、Controllerは流れと品質を束ねる。入出力はRepositoryに閉じ込め、配列I/Oで高速・安定・テスト可能にする。これだけで、Excelの現場でも「読みやすく・壊れにくく・拡張しやすい」VBAに変わります。

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