Excel VBA 逆引き集 | 最強テンプレ構造

Excel VBA
スポンサーリンク
  1. ねらい：どの業務でも「壊れず速く保守しやすい」最強テンプレ構造を手に入れる
    1. 重要ポイントの深掘り
  2. 全体構造のゴール像
    1. 役割分割で見えるコードにする
    2. 依存の向きを固定して壊れにくくする
  3. 共通基盤（開始・終了・例外復帰）
    1. 開始・終了の枠を必ず通す
    2. 重要ポイントの深掘り
  4. 設定外出し（Config）
    1. キーで読み、型を検証する
    2. 重要ポイントの深掘り
  5. 入出力（IO）と業務ロジック（Logic）
    1. Range→配列→一括書き戻しで速度と安定性を得る
    2. ロジックは純粋関数にしてテスト可能にする
    3. 重要ポイントの深掘り
  6. 進捗・ログ・エラー復帰
    1. 進捗は1〜5%刻みでUI応答を保つ
    2. ログはStart・Finish・Errorの三点を必ず記録する
    3. 重要ポイントの深掘り
  7. ファイル命名と出力の標準化
    1. 安全なファイル名とタイムスタンプで世代管理する
    2. 重要ポイントの深掘り
  8. 入口統一（Service）とフルフローの例
    1. Run_XXXXで開始し、枠・Config・IO・Logic・出力・ログまでを一気通貫にする
    2. 重要ポイントの深掘り
  9. CSV出力の標準部品（UTF-8・エスケープ）
    1. 文字化けと引用符問題を部品で解決する
    2. 重要ポイントの深掘り
  10. テストと計測の最小セット
    1. ロジック単体テストで「動作不変」を確認する
    2. 区間計測で効果を数値で見る
    3. 重要ポイントの深掘り
  11. 導入手順の道筋
    1. 先に枠を入れてからロジックを載せる
    2. 小さく導入して確実に前進する
  12. まとめ：枠が最強、ロジックは載せ換え自由

ねらい：どの業務でも「壊れず速く保守しやすい」最強テンプレ構造を手に入れる

最強テンプレ構造とは、VBAをどの業務にも安全に載せ替えられる「設計の型」です。開始・終了の共通枠、設定の外出し、配列I/O、ロジック純粋化、進捗・ログ・エラー復帰、そして入口統一。この型に沿えば、初心者でも貼って動かしながら拡張できます。ここでは、実務で即使えるテンプレをH2〜H4の見出しで体系的に示し、重要箇所は理由まで深掘り解説します。

重要ポイントの深掘り

最強テンプレでは「枠が先、ロジックは後」が鉄則です。最初に環境復帰・設定読み取り・進捗・ログの枠を用意し、そこに業務ロジックを載せます。こうすることで、失敗しても復帰でき、運用差異にも強く、拡張時の修正範囲が明確になります。配列I/Oとロジック純粋化は速度とテスト容易性の核です。

全体構造のゴール像

役割分割で見えるコードにする

モジュールは役割別に分けます。基盤（開始・終了）、設定（Config）、入出力（IO）、業務ロジック（Logic）、業務フロー（Service）、進捗（Progress）、ログ（Log）、ファイル関連（File）。入口はServiceのRun_XXXXに集約し、UIボタンはそこだけを呼びます。

依存の向きを固定して壊れにくくする

UI（ボタン）→Service（業務フロー）→Logic（純粋関数）／IO（配列I/O）／Config（設定読み取り）／Progress／Log。逆流（Logicがセルを触る、Serviceが直接UserForm制御）は禁止します。依存が綺麗だと、列変更やルール変更に強くなります。

共通基盤（開始・終了・例外復帰）

開始・終了の枠を必ず通す

' 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
VB

重要ポイントの深掘り

この枠は「Finally」です。どこでエラーが起きても必ず復帰します。描画・イベント・再計算を止めるだけでも体感速度が上がりますが、最も重要なのは「失敗時に環境が戻る安心感」。業務で怖さが消えます。

設定外出し（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 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
VB

重要ポイントの深掘り

定数直書きを捨て、Configを唯一の真実にします。運用差異はConfigだけで吸収でき、配布し直す必要がなくなります。型検証を入口で行い、誤設定は即停止（Fail Fast）すると調査コストが激減します。

入出力（IO）と業務ロジック（Logic）

Range→配列→一括書き戻しで速度と安定性を得る

' ModIO
Option Explicit

Public Function ReadRegion(ByVal topLeft As Range) As Variant
    ReadRegion = topLeft.CurrentRegion.Value
End Function

Public Sub WriteArray(ByVal ws As Worksheet, ByVal topLeft As String, ByVal arr As Variant)
    ws.Range(topLeft).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End Sub
VB

ロジックは純粋関数にしてテスト可能にする

' ModLogic
Option Explicit

Public Function NormalizeAndFlag(ByVal data As Variant, ByVal th As Double, ByVal scoreCol As Long) 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 c As Long: For c = 1 To cols: out(1, c) = data(1, c): Next
    out(1, cols + 1) = "合格"

    Dim r As Long
    For r = 2 To rows
        For c = 1 To cols
            out(r, c) = Trim$(CStr(data(r, c)))
        Next
        out(r, cols + 1) = IIf(Val(out(r, scoreCol)) >= th, "○", "×")
    Next
    NormalizeAndFlag = out
End Function
VB

重要ポイントの深掘り

配列I/Oは「遅さの根源（セル往復）」を断ちます。ロジックを純粋関数にすると、テストが容易になり、列順変更にも強くなります。業務ルールの変更はLogicだけ、列変更はIOだけ直す構造が最強です。

進捗・ログ・エラー復帰

進捗は1〜5%刻みでUI応答を保つ

' ModProgress
Option Explicit

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
VB

ログはStart・Finish・Errorの三点を必ず記録する

' ModLog
Option Explicit

Public Sub LogInfo(ByVal action As String, ByVal detail As String)
    WriteLog "INFO", action, detail
End Sub

Public Sub LogError(ByVal action As String, ByVal detail As String)
    WriteLog "ERROR", action, detail
End Sub

Private Sub WriteLog(ByVal level As String, ByVal action As String, 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 = level
    ws.Cells(r, 3).Value = action
    ws.Cells(r, 4).Value = detail
End Sub
VB

重要ポイントの深掘り

進捗は「体感の安心」、ログは「事実の記録」。この二つがあるだけで運用の不安が消え、障害調査が速くなります。エラー時は、AppLeave→LogError→通知の順番で必ず実行する「型」を入口に仕込んでおくのが肝です。

ファイル命名と出力の標準化

安全なファイル名とタイムスタンプで世代管理する

' ModFile
Option Explicit

Public Function SafeFileName(ByVal raw As String) As String
    Dim s As String: s = Trim$(raw)
    Dim bad: bad = Array("\","/",":","*","?","""","<",">","|")
    Dim i As Long: For i = LBound(bad) To UBound(bad): s = Replace(s, bad(i), "_"): Next
    SafeFileName = IIf(Len(s) = 0, "untitled", s)
End Function

Public Function BuildExportPath(ByVal folder As String, ByVal base As String, ByVal ext As String) As String
    BuildExportPath = folder & SafeFileName(base) & "_" & Format(Now, "yyyy-mm-dd_HHNNSS") & "." & ext
End Function

Public Sub EnsureFolder(ByVal folderPath As String)
    Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(folderPath) Then fso.CreateFolder folderPath
End Sub
VB

重要ポイントの深掘り

命名と出力場所が標準化されると、次工程への引き渡しがスムーズになります。禁則文字の除去と時刻付き命名は、上書き事故と探索コストを確実に減らします。フォルダ保証までセットで「失敗しない出力」を実現します。

入口統一（Service）とフルフローの例

Run_XXXXで開始し、枠・Config・IO・Logic・出力・ログまでを一気通貫にする

' ModService
Option Explicit

Public Sub Run_DailyScoreFlag()
    On Error GoTo EH
    AppEnter "日次合格判定"
    LogInfo "Start", "DailyScoreFlag"

    Dim inSheet As String: inSheet = GetConfigString("INPUT_SHEET")
    Dim outSheet As String: outSheet = GetConfigString("OUTPUT_SHEET")
    Dim th As Double: th = GetConfigNumber("THRESHOLD")
    Dim scoreCol As Long: scoreCol = CLng(GetConfigNumber("SCORE_COL"))

    Dim data As Variant: data = ReadRegion(Worksheets(inSheet).Range("A1"))
    Dim out As Variant: out = NormalizeAndFlag(data, th, scoreCol)

    WriteArray Worksheets(outSheet), "A1", out
    LogInfo "Finish", outSheet

    AppLeave
    MsgBox "完了: 出力先 " & outSheet
    Exit Sub
EH:
    LogError "DailyScoreFlag", Err.Number & " - " & Err.Description
    AppLeave
    MsgBox "失敗: " & Err.Description, vbExclamation
End Sub
VB

重要ポイントの深掘り

入口統一で「操作が一箇所に集約」され、手順書・教育・障害対応が圧倒的に楽になります。Configのキーで運用差異に対応しつつ、配列I/Oと純粋ロジックで速度と安定性を担保。ログで結果が記録されるため、後から辿れる仕組みになります。

CSV出力の標準部品（UTF-8・エスケープ）

文字化けと引用符問題を部品で解決する

' ModCsv
Option Explicit

Public Sub WriteCsv(ByVal path As String, ByVal arr As Variant)
    Dim st As Object: Set st = CreateObject("ADODB.Stream")
    st.Type = 2: st.Charset = "UTF-8": st.Open

    Dim r As Long, c As Long
    For r = 1 To UBound(arr, 1)
        Dim line As String: line = ""
        For c = 1 To UBound(arr, 2)
            Dim s As String: s = Replace(CStr(arr(r, c)), """", """""")
            line = line & IIf(c > 1, ",", "") & """" & s & """"
        Next
        st.WriteText line & vbCrLf
    Next

    st.SaveToFile path, 2
    st.Close
    Set st = Nothing
End Sub
VB

重要ポイントの深掘り

CSVは現場で最も使われる交換形式です。UTF-8固定とダブルクォートのエスケープを部品化しておけば、毎回悩まず品質を一定にできます。I/Oが安定すると、フロー全体の信頼度が上がります。

テストと計測の最小セット

ロジック単体テストで「動作不変」を確認する

' ModTest
Option Explicit

Public Sub Test_NormalizeAndFlag()
    Dim data(1 To 3, 1 To 5) As Variant
    data(1, 1) = "EmpNo": data(1, 5) = "Score"
    data(2, 1) = "0001":  data(2, 5) = 80
    data(3, 1) = "0002":  data(3, 5) = 60

    Dim out As Variant: out = NormalizeAndFlag(data, 70, 5)
    Debug.Print out(2, 6) ' ○
    Debug.Print out(3, 6) ' ×
End Sub
VB

区間計測で効果を数値で見る

Public Sub Measure_Run()
    Dim t As Double: t = Timer
    Run_DailyScoreFlag
    Debug.Print "Run_DailyScoreFlag:", Format(Timer - t, "0.000"), "s"
End Sub
VB

重要ポイントの深掘り

「直して速くなった」「壊していない」の確認は、テストと計測が最短です。小さく回して積み上げると、初心者でも安心して改良できます。数値で効果が見えると、現場への説得力が上がります。

導入手順の道筋

先に枠を入れてからロジックを載せる

最初にModApp・ModConfig・ModIO・ModProgress・ModLogを作り、入口をRun_XXXXに統一します。その上で、配列I/Oと純粋ロジックへ差し替え、必要ならCSVやファイル命名の部品を追加します。テストと計測を用意して、変更のたびに確認します。

小さく導入して確実に前進する

長い処理を一気に置き換えず、重いループを配列化、次に前提検証、次にロジック純粋化と段階的に進めます。各段で動作不変を確かめ、ログに記録を残すと、後戻りがなくなります。

まとめ：枠が最強、ロジックは載せ換え自由

この最強テンプレ構造は「枠」の力で運用を守り、「配列I/O＋純粋関数」で速度と保守性を両立します。入口統一・設定外出し・進捗・ログ・復帰が揃えば、どの業務でも安心してVBAを組み込めます。

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