VBA マクロで利用する設定値をシートに保存し、文字列でアクセスするテクニック

今回はマクロで利用する設定値をシートに保存し、汎用的に文字列でアクセスできるようにするテクニックの紹介。

作り方

標準モジュールを挿入し、プロパティウィンドウからオブジェクト名をConfgに変更する。

Configモジュールに以下のコードを張り付ける。

Option Explicit
Private configurations As Variant   '<- configulations is keepd here as variant array.

Private Property Get InitialSettings() As Collection
    Set InitialSettings = New Collection
    InitialSettings.Add Array("Name", "Value", "Description")   '←見出しです。
    
    '以下のサンプルに従って設定を定義してください。
    '書式は　InitialSettings.Add Array([設定名] As String, [値] As Variant, [説明] As String)　です。
    'ここに示したサンプルは削除して構いません。
    InitialSettings.Add Array("BackgroundColor", rgbWheat, "rgbWheat")
    InitialSettings.Add Array("Margin", 5, "how many blank cells put between pictures")
    InitialSettings.Add Array("InsertTime", True, "Write scraptime or not")
    InitialSettings.Add Array("StartRow", 5, "")
    InitialSettings.Add Array("StartColumn", 3, "")
End Property

Sub LoadConfig(Optional ByRef void = Empty)
    If Not existConfigSheet Then ResetConfig
    configurations = ThisWorkbook.Worksheets("Config").Range("a1").CurrentRegion.Value
End Sub

Public Sub ShowConfig()
    If Not existConfigSheet Then ResetConfig
    With ThisWorkbook.Worksheets("Config")
        .Visible = xlSheetVisible
        .Activate
    End With
End Sub

Public Sub HideConfig()
    If Not existConfigSheet Then ResetConfig
    ThisWorkbook.Worksheets("Config").Visible = xlSheetVeryHidden
End Sub

Public Property Get Value(conf_name As String)
    Dim i As Long
    For i = LBound(configurations, 1) To UBound(configurations, 1)
        If UCase(conf_name) = UCase(configurations(i, 1)) Then
            Value = configurations(i, 2)
        End If
    Next
End Property

Public Function LetValue(conf_name As String, conf_value As Variant) As Boolean
    Dim sh As Worksheet: Set sh = ThisWorkbook.Worksheets("Config")
    Dim i As Long
    For i = 1 To sh.Range("A" & Rows.Count).End(xlUp).Row
        With sh.Range("A" & i)
            If UCase(.Value) = UCase(conf_name) Then
                .Offset(0, 1).Value = conf_value
                LetValue = True
                Exit Function
            End If
        End With
    Next
End Function

Public Sub ResetConfig()
    If existConfigSheet Then
        With ThisWorkbook.Sheets("Config")
            Application.DisplayAlerts = False
            .Visible = xlSheetHidden
            .Delete
            Application.DisplayAlerts = True
        End With
    End If
    
    Dim configSheet As Worksheet
    With ActiveSheet
        Set configSheet = ThisWorkbook.Worksheets.Add(Sheets(1))
        .Activate
    End With
    configSheet.Name = "Config"
    Dim i, j
    Dim c As Collection: Set c = InitialSettings
    For i = 1 To c.Count
        Dim arr: arr = c(i)
        For j = LBound(arr) To UBound(arr)
            configSheet.Cells(i, j + 1).Value = arr(j)
        Next
    Next
    configSheet.Cells.EntireColumn.AutoFit
End Sub

Private Function existConfigSheet() As Boolean
    Dim ret As Boolean
    Dim sh As Object
    For Each sh In ThisWorkbook.Sheets
        If UCase(sh.Name) = UCase("Config") Then
            existConfigSheet = True
            Exit Function
        End If
    Next
End Function

ResetConfigマクロをF5で実行すると、Configシートが作成され、InitialSettingsプロパティで定義した内容が転記される。

使い方

別の標準モジュールを挿入し、次のコードを張り付けて実行すると、アクティブシートの背景色がConfigシートで定義されたBackgroundColorの値になる。

Sub hoge()
    Call Config.LoadConfig
    ActiveSheet.Cells.Interior.Color = Config.Value("BackgroundColor")
End Sub

ResetConfigは設定をめちゃくちゃにしてしまった時のための初期化用マクロなので普段は実行せずに、手でシートを書き換えるか、マクロからConfig.LetValueを呼び出して値を書き換える。

カスタマイズ方法

InitialSettingsプロパティで指定されている5つのサンプルと同じように設定名、値、説明を定義する。
サンプルは消してOK。
ResetConfigを実行するとConfigシートが再作成される。

一度LoadConfigを読んでおけば、以降はConfig.Value([設定名])とすることで設定を参照できる。

説明

Config.LoadConfigを呼び出すと、Configシートの内容が2次元配列としてConfigモジュールに保持される。
Cellへのアクセスは低速なのでこのように配列に保持する仕組みにしている。

Config.Value([設定名])を呼び出すと、その設定名に応じた値を配列から検索して返す。

先日プロシージャの呼び出し順について言及したが、InitialSettingsはResetConfigから呼び出されるにも関わらずあえて先頭に置いた。
これはInitialSettingsがプロシージャの形をとっているものの実質は定数定義の代替手段に過ぎないためだ。
仕様が許すならGeneral領域に置きたいけどプロシージャである以上そうもいかないので呼び出しの原則を破って先頭に置いた。

Configをシートモジュールに統合してオブジェクト名でアクセスするというアイデアを採用すればもっとシンプルに出来たけれど、今回その手段を取らなかったのは人に配布するマクロではConfigシートそのものが誤って消されてしまう可能性があり、オブジェクト名によるアクセスが破綻する危険があるため。

標準モジュールだって消されるだろうって？
それはマクロに対する直接的な破壊行為なので、そもそもプログラムで対処すべき問題領域ではない。

以上