Excel VBA 逆引き集 | Configシート運用

Excel VBA
スポンサーリンク

ねらい：Configシートで「設定をコードから切り離す」運用を作る

毎回コードを書き換えずに、シート名・保存先・閾値・ON/OFFなどを変更できたら運用は格段に楽になります。Configシートは「人が編集できる設定表」。コードはこの表を読むだけにして、現場の変更を安全に吸収します。初心者にも貼って使えるテンプレと、重要ポイントを深掘りして解説します。

  • 目的: 設定を可視化・一元管理し、変更はシート編集で完結
  • 基本方針: キー–値形式で保存し、読み取りヘルパー関数で型付け＆検証
  • 重要ポイント（深掘り）:
    • コードから設定を分離: 変更はConfigだけ。マクロは不変に近づく
    • 検証を必ず挟む: 誤設定は即検出して丁寧に指摘
    • VeryHidden＋保護: 誤操作を防ぎつつ、必要時だけ表示して編集

設計の骨子（Configシート構成）

  • 基本構造（推奨）:
    • A列=Key（例: INPUT_SHEET, OUTPUT_FOLDER, THRESHOLD_SCORE, ENABLE_LOG）
    • B列=Value（値）
    • C列=Type（string/number/boolean/path/url/date など）
    • D列=Note（説明）
  • 例（Config!A1:D）:
    • INPUT_SHEET | Input | string | 取込シート名
    • OUTPUT_FOLDER | C:\Export\ | path | 出力フォルダ
    • THRESHOLD_SCORE | 70 | number | 合格点
    • ENABLE_LOG | TRUE | boolean | ログ出力ON/OFF
    • RUN_MODE | PROD | string | 実行モード（DEV/PROD）
  • 重要ポイント（深掘り）:
    • Type列で型付けを明示: 文字/数値/真偽を区別して読み取りミスを防止
    • Keyは英数＋アンダースコア: コード側で扱いやすく誤入力が減る
    • Noteで人向け説明: 運用者が迷わない

読み取りヘルパー（高速・型安全・検証付き）

' ConfigReader.bas
Option Explicit

Private Function ConfigSheet() As Worksheet
    On Error Resume Next
    Set ConfigSheet = ThisWorkbook.Worksheets("Config")
    On Error GoTo 0
    If ConfigSheet Is Nothing Then Err.Raise 700, , "Configシートがありません"
End Function

Public Function GetConfigRaw(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 ' 1行目ヘッダー想定
        If StrComp(CStr(ws.Cells(r, "A").Value), key, vbTextCompare) = 0 Then
            GetConfigRaw = CStr(ws.Cells(r, "B").Value)
            Exit Function
        End If
    Next
    Err.Raise 701, , "Configキーが見つかりません: " & key
End Function

Public Function GetConfigString(ByVal key As String) As String
    Dim v As String: v = Trim$(GetConfigRaw(key))
    If Len(v) = 0 Then Err.Raise 702, , "空文字です: " & key
    GetConfigString = v
End Function

Public Function GetConfigNumber(ByVal key As String) As Double
    Dim v As String: v = Trim$(GetConfigRaw(key))
    If Not IsNumeric(v) Then Err.Raise 703, , "数値ではありません: " & key & " = " & v
    GetConfigNumber = CDbl(v)
End Function

Public Function GetConfigBoolean(ByVal key As String) As Boolean
    Dim v As String: v = UCase$(Trim$(GetConfigRaw(key)))
    If v = "TRUE" Or v = "1" Or v = "YES" Then
        GetConfigBoolean = True
    ElseIf v = "FALSE" Or v = "0" Or v = "NO" Then
        GetConfigBoolean = False
    Else
        Err.Raise 704, , "真偽値ではありません（TRUE/FALSE）: " & key & " = " & v
    End If
End Function

Public Function GetConfigPath(ByVal key As String) As String
    Dim p As String: p = GetConfigString(key)
    ' 禁則文字チェック
    Dim bad As Variant: bad = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
    Dim i As Long
    For i = LBound(bad) To UBound(bad)
        If InStr(p, CStr(bad(i))) > 0 Then Err.Raise 705, , "禁則文字を含みます: " & key & " = " & p
    Next
    GetConfigPath = p
End Function
VB
  • 重要ポイント（深掘り）:
    • GetConfigRaw→型別関数へ: 最初に生値を取り、型関数で検証して返す
    • エラーは明確なメッセージで: “数値ではありません: THRESHOLD_SCORE=abc” のように指摘
    • 禁則チェック: パスやファイル名の失敗を未然に防ぐ

使い方テンプレ（業務マクロから呼ぶ）

' Main.bas
Option Explicit

Public Sub Run_Export()
    On Error GoTo ErrHandler
    
    ' 1. 設定読み込み
    Dim inputSheetName As String: inputSheetName = GetConfigString("INPUT_SHEET")
    Dim outputFolder As String: outputFolder = GetConfigPath("OUTPUT_FOLDER")
    Dim threshold As Double: threshold = GetConfigNumber("THRESHOLD_SCORE")
    Dim enableLog As Boolean: enableLog = GetConfigBoolean("ENABLE_LOG")
    
    ' 2. 前提チェック
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets(inputSheetName)
    If ws Is Nothing Then Err.Raise 710, , "入力シートが存在しません: " & inputSheetName
    EnsureFolder outputFolder
    
    ' 3. 処理
    Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Dim r As Long, passed As Long
    For r = 2 To lastRow
        If Val(ws.Cells(r, "E").Value) >= threshold Then passed = passed + 1
    Next
    
    ' 4. 出力（例）
    Dim path As String
    path = outputFolder & "結果_" & Format(Now, "yyyy-mm-dd_HHNNSS") & ".txt"
    WriteText path, "合格件数: " & passed
    
    If enableLog Then LogToSheet "ExportFinish", "件数 " & passed & " → " & path
    MsgBox "エクスポート完了: " & path
    Exit Sub
    
ErrHandler:
    MsgBox "失敗: " & Err.Description, vbExclamation
End Sub

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

Private Sub WriteText(ByVal path As String, ByVal text As String)
    Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
    Dim ts As Object: Set ts = fso.CreateTextFile(path, True, True) ' overwrite, Unicode
    ts.WriteLine text
    ts.Close
End Sub

Private Sub LogToSheet(ByVal action As String, ByVal detail As String)
    On Error Resume Next
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Log")
    If ws Is Nothing Then Exit Sub
    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
VB
  • 重要ポイント（深掘り）:
    • Config→Guard→Process→Output→Log: 一連の中で設定を軸に処理が分岐
    • ON/OFF設計: ENABLE_LOGのように機能切替をConfigで制御する

検証テンプレ（Type列で自動チェック）

Type列を使い、設定表自体を「セルで検証」します。誤設定を先に弾く。

Sub ValidateConfigSheet()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Config")
    Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim r As Long
    For r = 2 To lastRow
        Dim key As String: key = CStr(ws.Cells(r, "A").Value)
        Dim val As String: val = CStr(ws.Cells(r, "B").Value)
        Dim typ As String: typ = UCase$(CStr(ws.Cells(r, "C").Value))
        
        Select Case typ
            Case "STRING"
                If Len(Trim$(val)) = 0 Then MarkBad ws, r, "空STRING"
            Case "NUMBER"
                If Not IsNumeric(val) Then MarkBad ws, r, "非数値"
            Case "BOOLEAN"
                If InStr("TRUE,FALSE,YES,NO,1,0", UCase$(val)) = 0 Then MarkBad ws, r, "非BOOLEAN"
            Case "PATH"
                If HasBadChars(val) Then MarkBad ws, r, "禁則文字"
            Case "DATE"
                If Not IsDate(val) Then MarkBad ws, r, "非DATE"
            Case Else
                MarkBad ws, r, "未知TYPE"
        End Select
    Next
    MsgBox "Config検証完了"
End Sub

Private Sub MarkBad(ByVal ws As Worksheet, ByVal r As Long, ByVal msg As String)
    ws.Cells(r, "E").Value = msg ' E列にメモ
    ws.Cells(r, "A").Resize(1, 4).Interior.Color = RGB(255, 220, 200)
End Sub

Private Function HasBadChars(ByVal s As String) As Boolean
    Dim bad As Variant: bad = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
    Dim i As Long
    For i = LBound(bad) To UBound(bad)
        If InStr(s, CStr(bad(i))) > 0 Then HasBadChars = True: Exit Function
    Next
End Function
VB
  • 重要ポイント（深掘り）:
    • 人が編集した瞬間に検証: ボタンで検証→NGセルに色を付け、原因をE列へ
    • コード実行前に弾く: 「先に設定表を直す」運用が定着する

運用のセキュリティと可視性（VeryHidden＋保護＋ログ）

  • VeryHidden運用:
    • Configは通常VeryHidden: 誤編集防止。管理者作業時のみ表示
    • 例: Worksheets("Config").Visible = xlSheetVeryHidden
  • 保護の方針:
    • ヘッダー行・Type列は保護: 値列のみ編集可能にすると事故が減る
  • 変更ログ:
    • 変更時刻・キー・旧値・新値をLogに記録する簡易監査を入れると安心
Sub ShowConfigForAdmin()
    Worksheets("Config").Visible = xlSheetVisible
    Worksheets("Config").Unprotect
    MsgBox "Configを表示しました（管理者作業）"
End Sub

Sub HideConfigAfterAdmin()
    Worksheets("Config").Protect
    Worksheets("Config").Visible = xlSheetVeryHidden
    MsgBox "Configを再度非表示にしました"
End Sub
VB
  • 重要ポイント（深掘り）:
    • セキュリティ機能ではないが効果的: VeryHidden＋保護で誤操作をほぼ防げる
    • 監査性: “誰がいつ何を変えたか”はLog連携で残すと安心

例題で練習（貼って試せる）

  • 例1（設定読み）: Configに INPUT_SHEET/OUTPUT_FOLDER/THRESHOLD_SCORE を作り、Run_Exportで反映されるのを確認
  • 例2（型検証NG）: THRESHOLD_SCOREに「abc」を入れて ValidateConfigSheet → E列に「非数値」表示
  • 例3（ON/OFF）: ENABLE_LOGをFALSEにして、Run_Export後にLog出力が抑制されることを確認
  • 例4（表示/非表示）: ShowConfigForAdmin→編集→HideConfigAfterAdmin の流れを体験

実務の落とし穴と対策（ここが肝）

  • 落とし穴1：キー名の表記ゆれ
    • 対策: 英数＋大文字/小文字無視で統一。キーは配列で棚卸しできるように。
  • 落とし穴2：設定が増えすぎて分からない
    • 対策: セクション分割（ひとつのConfig内で区分）やシート分割（Config_Export/Config_Import）。
  • 落とし穴3：誤設定で本番事故
    • 対策: ValidateConfigSheetを必ず実行→NGなら処理停止。重要キーは必須チェック。
  • 落とし穴4：環境差でパスが通らない
    • 対策: 相対パス／ユーザーのドキュメント配下を使う、起動時に存在確認＆自動作成。
  • 落とし穴5：Configを直してもコードがキャッシュ保持
    • 対策: 実行開始時に毎回読み直す（グローバルキャッシュしない）。再読み込み関数を標準化。

スターター手順（最短導入）

  1. Configシートを作成（A:Key/B:Value/C:Type/D:Note）。
  2. ConfigReader.basを貼る（GetConfigString/Number/Boolean/Path）。
  3. 業務マクロでGetConfigXXXを使用し、コードから定数を排除。
  4. ValidateConfigSheetを追加して、誤設定を先に弾く。
  5. VeryHidden＋保護＋管理者ボタンで誤操作を防止。

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