Excel VBA 逆引き集 | マクロ専用設定シートの作成

Excel VBA
スポンサーリンク

マクロ専用設定シートの作成

「設定値を一か所に集約」「安全に読み書き」「表示は隠して保護」——初心者でも迷わず使えるテンプレートと例題で、実務で使える“設定シート”をゼロから作ります。

設定シートの考え方（運用のコツ）

  • 役割分離: 処理のパラメータ（対象フォルダ、出力有無、色、パスワード等）を「設定シート」に集めると、コードを直さずに運用変更できます。
  • 名前の例: シート名は「Settings」や「Control」など分かりやすく。VeryHidden＋保護で誤操作を防ぎましょう。
  • 命名規則: 取り出しやすいように「キー」「値」「説明」の3列構成が定番です。

ひな形の自動作成（初期セットアップ）

Sub SetupSettingsSheet()
    Dim ws As Worksheet

    '存在チェック→なければ作成
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Settings")
    On Error GoTo 0
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = "Settings"
    End If

    '初期レイアウト
    With ws
        .Range("A1").Value = "キー"
        .Range("B1").Value = "値"
        .Range("C1").Value = "説明"
        .Range("A1:C1").Font.Bold = True

        'サンプル設定
        .Range("A2").Value = "OutputFolder": .Range("B2").Value = ThisWorkbook.Path: .Range("C2").Value = "出力フォルダのパス"
        .Range("A3").Value = "EnableExport":  .Range("B3").Value = True:              .Range("C3").Value = "エクスポートの有効/無効（True/False）"
        .Range("A4").Value = "AccentColor":   .Range("B4").Value = "#0070C0":         .Range("C4").Value = "テーマカラー（#RRGGBB）"
        .Range("A5").Value = "Password":      .Range("B5").Value = "secret":          .Range("C5").Value = "操作用の簡易パスワード"

        .Columns("A:C").AutoFit
    End With

    'データ検証（True/False のみ許可）
    With ws.Range("B3").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="TRUE,FALSE"
        .IgnoreBlank = True
        .InCellDropdown = True
    End With

    '保護＆完全非表示（実運用では必要に応じて）
    ws.Protect Password:="lock", AllowFiltering:=True
    ws.Visible = xlSheetVeryHidden

    MsgBox "Settingsシートを準備しました。"
End Sub
VB
  • ポイント:
    • 検証の仕込み: True/Falseをプルダウンにして入力ミスを防ぐ。
    • 保護＋VeryHidden: 画面からの再表示を防いで誤編集を抑止。解除はVBAでのみ。

設定値の読み取り関数（キー指定で即取得）

Function GetSettingValue(key As String, Optional defaultValue As Variant) As Variant
    Dim ws As Worksheet, last As Long, r As Long
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Settings")
    On Error GoTo 0

    If ws Is Nothing Then
        GetSettingValue = defaultValue
        Exit Function
    End If

    last = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For r = 2 To last
        If StrComp(CStr(ws.Cells(r, "A").Value), key, vbTextCompare) = 0 Then
            GetSettingValue = ws.Cells(r, "B").Value
            Exit Function
        End If
    Next

    GetSettingValue = defaultValue
End Function
VB
  • ポイント:
    • defaultValue: キーが見つからないときの初期値を用意しておけば、運用が堅くなります。

よくある型の取り出し（ブール／色／フォルダパス）

Function GetSettingBool(key As String, Optional defaultBool As Boolean = False) As Boolean
    Dim v As Variant: v = GetSettingValue(key, defaultBool)
    GetSettingBool = (UCase$(CStr(v)) = "TRUE" Or v = True)
End Function

Function RGBFromHex(hex As String) As Long
    Dim s As String: s = Replace$(Trim$(hex), "#", "")
    If Len(s) <> 6 Then RGBFromHex = RGB(0, 0, 0): Exit Function
    RGBFromHex = RGB(CLng("&H" & Left$(s, 2)), CLng("&H" & Mid$(s, 3, 2)), CLng("&H" & Right$(s, 2)))
End Function

Function GetSettingColor(key As String, Optional defaultHex As String = "#0070C0") As Long
    GetSettingColor = RGBFromHex(CStr(GetSettingValue(key, defaultHex)))
End Function

Function EnsureFolderPath(pathText As String) As String
    Dim p As String: p = CStr(pathText)
    If Right$(p, 1) <> "\" Then p = p & "\"
    EnsureFolderPath = p
End Function
VB
  • ポイント:
    • 型変換: 文字列設定を想定し、ブール・色・パスなど使いやすい形に変換。
    • Hexカラー対応: 設定側は#RRGGBB形式で覚えやすく。

設定の書き込み（キーがなければ追加）

Sub SetSettingValue(key As String, value As Variant, Optional description As String = "")
    Dim ws As Worksheet, last As Long, r As Long
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Settings")
    On Error GoTo 0
    If ws Is Nothing Then
        SetupSettingsSheet
        Set ws = ThisWorkbook.Worksheets("Settings")
        'VeryHiddenのまま操作可能（表示は不要）
    End If

    last = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For r = 2 To last
        If StrComp(CStr(ws.Cells(r, "A").Value), key, vbTextCompare) = 0 Then
            ws.Unprotect Password:="lock"
            ws.Cells(r, "B").Value = value
            If Len(description) > 0 Then ws.Cells(r, "C").Value = description
            ws.Protect Password:="lock", AllowFiltering:=True
            Exit Sub
        End If
    Next

    '見つからなければ末尾に追加
    ws.Unprotect Password:="lock"
    ws.Cells(last + 1, "A").Value = key
    ws.Cells(last + 1, "B").Value = value
    ws.Cells(last + 1, "C").Value = description
    ws.Columns("A:C").AutoFit
    ws.Protect Password:="lock", AllowFiltering:=True
End Sub
VB
  • ポイント:
    • 一時解除→再保護: 変更時の基本パターン。
    • 柔軟追加: 運用中にキーを足していけます。

例題で練習：設定に応じて動作を切り替える

例題1：出力先フォルダとエクスポートON/OFF

Sub ExportCSV_IfEnabled()
    Dim enabled As Boolean, folder As String
    enabled = GetSettingBool("EnableExport", True)
    folder = EnsureFolderPath(GetSettingValue("OutputFolder", ThisWorkbook.Path))

    If Not enabled Then
        MsgBox "エクスポートは無効です（SettingsのEnableExportを変更）。": Exit Sub
    End If

    'ここにエクスポート処理（例：アクティブシートをCSV）
    ActiveSheet.Copy
    With ActiveWorkbook
        .SaveAs Filename:=folder & ActiveSheet.Name & ".csv", FileFormat:=xlCSV
        .Close SaveChanges:=False
    End With

    MsgBox "CSVを出力しました → " & folder
End Sub
VB

例題2：設定色をタブ色に適用

Sub ApplyAccentColorTabs()
    Dim col As Long: col = GetSettingColor("AccentColor", "#0070C0")
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then ws.Tab.Color = col
    Next
    MsgBox "設定のアクセントカラーをタブに適用しました。"
End Sub
VB

例題3：簡易パスワードで保護解除して更新→再保護

Sub UpdateWithPassword()
    Dim pwd As String: pwd = CStr(GetSettingValue("Password", ""))
    Dim inputPwd As String: inputPwd = InputBox("パスワードを入力してください")
    If pwd <> "" And inputPwd <> pwd Then
        MsgBox "パスワードが違います。": Exit Sub
    End If

    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("レポート")
    On Error Resume Next: ws.Unprotect Password:=pwd: On Error GoTo 0

    ws.Range("B2").Value = Date
    ws.Range("B3").Value = Environ$("Username")

    ws.Protect Password:=pwd, AllowFiltering:=True, AllowSorting:=True
    MsgBox "レポートを更新して再保護しました。"
End Sub
VB

実務の落とし穴と対策

  • 表示から触られたくない:
    • 対策: SettingsはVeryHidden＋保護。編集したい時だけ一時解除。ボタンから開く補助マクロを用意しても良い。
  • 型の揺れ（True/Falseや色表記）:
    • 対策: 取り出し関数でブール変換／Hex→RGB変換を必ず挟む。
  • キー名の誤記:
    • 対策: 参照側は既定値（default）を入れて耐性を持たせる。キー名は固定の英語＋説明列で補足。
  • 保護の戻し忘れ:
    • 対策: 書き込み後に必ず再保護。失敗時も保護が戻るように構造化する。
  • シートが存在しない:
    • 対策: 読み取り・書き込みともに「なければSetup」を組み込んで自己修復する。

便利なおまけ：設定編集メニュー（一時表示→編集→再非表示）

Sub OpenSettingsForEdit()
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Settings")
    On Error GoTo 0
    If ws Is Nothing Then
        SetupSettingsSheet
        Set ws = ThisWorkbook.Worksheets("Settings")
    End If

    '一時表示して編集（保護解除→編集→閉じると再非表示のほうが安全）
    ws.Visible = xlSheetVisible
    ws.Unprotect Password:="lock"
    ws.Activate
    MsgBox "Settingsを編集してください。編集後に「CloseSettings」を実行。"
End Sub

Sub CloseSettings()
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Settings")
    On Error GoTo 0
    If Not ws Is Nothing Then
        ws.Protect Password:="lock", AllowFiltering:=True
        ws.Visible = xlSheetVeryHidden
        MsgBox "Settingsを閉じました（保護＆完全非表示）。"
    End If
End Sub
VB
スポンサーリンク
タイトルとURLをコピーしました