[VBA] 複数のVBAで参照する定数をConfigファイルで一元管理

はじめに

VBAにはConstキーワードを用いて定数を定義することができる。
定数の中には、参照する他ファイルのファイルパス・ファイル名を定義することもあるだろう。

定数を利用するエクセルファイルが1つであればよいが、たくさんのエクセルファイルから同じ定数を参照される場合どうだろう。
定数に格納されたフォルダの名前が変わった場合、全てのエクセルファイルを開いて定数を変更しなければならない。

もちろん規模にもよるだろうが、私の職場では、1か月ごとにフォルダを新しく切るという風習があり、そのたびに定数の変更が発生する。
以下の例だと簡単すぎるため、2024の箇所をYear(Now())で置き換えれば解決はしてしまうわけだが、実際には、SやTの番号なども頻繁に変わりうるため、
エクセル側で動的にパスを組み立てるのが難しいとする。

具体例

Const filePath1 = “C:\Works\2024-S001”
Const Process1 = “C:\Works\2024-S001\Process1\output.xlsx”
Const Process2 = “C:\Works\2024-S001\Process2\output.xlsx”
Const Process3 = “C:\Works\2024-S001\Process3\output.xlsx”

やりたいこと

愚直にコンフィグファイルに定義するのであれば、以下のとおりである。
しかし、実際には「C:\Works\2024-S001」の部分が重複している。
より複雑なフォルダ構成の場合、数十か所に対してこのパスを頭に付けなければならない。

filePath1=C:\Works\2024-S001
Process1=C:\Works\2024-S001\Process1\output.xlsx
Process2=C:\Works\2024-S001\Process2\output.xlsx
Process3=C:\Works\2024-S001\Process3\output.xlsx

そのため、以下のように、変数置換できるように対応する。
個人的に「@@」で囲う方が視覚的に分かりやすいのだが、ファイル名にも利用でき、処理が煩雑になってしまうため、「<>」を利用することにした。

filePath1=C:\Works\2024-S001
Process1=<filePath1>\Process1\output.xlsx
Process2=<filePath1>\Process2\output.xlsx
Process3=<filePath1>\Process3\output.xlsx

前提条件

  • コンフィグファイルは文字コードが「UTF-8」であること。
  • コンフィグファイルの名前は「env.conf」であること。
  • 実行するVBAファイル、またはアドイン化したVBAファイルの同階層に「env.conf」が配置されていること。

ソースコード

Option Explicit

' 参照設定
' Windows Script Host Object Model
'

Private params As Dictionary

' コンストラクタ
Private Sub Class_Initialize()
End Sub

' デストラクタ
Private Sub Class_Terminate()
End Sub

'
'【処理】
'   コンフィグファイルを読み込む
'
'【引数】
'   fullPath : csvファイルのファイルパス
'【返り値】
'   コンフィグパラメータ
'
Public Function LoadFile(wb As Workbook) As Variant
    
    Set params = New Dictionary
    Dim file_path As String
    file_path = wb.Path & "\env.conf"
    
    Const CHARSET As String = "UTF-8"

    ' ファイル読み込み
    Dim buf As String
    Dim ado As New ADODB.Stream
    With ado
        .CHARSET = CHARSET
        .Type = adTypeText
        .Open
        .LoadFromFile (file_path)
        buf = .ReadText
        .Close
    End With
    Set ado = Nothing
    
    Dim lines() As String
    lines = Split(buf, vbCrLf)
    Dim i
    For i = 0 To UBound(lines)
        Dim line  As String
        line = lines(i)
        line = Trim(line)
        ' コメント行
        If (Left(line, 1) = "#") Then
            ' スキップ
        ' パラメータ追加
        ElseIf (InStr(line, "=") > 1) Then
            Dim kv() As String
            kv = Split(line, "=")
            Call params.Add(kv(0), kv(1))
        End If
    Next
End Function

'
'【処理】
'  Keyに対応するValueを取得する
'【引数】
'  Key
'【返り値】
'  対応するValue
'
Public Function ResolveValue(key As String) As String
    If (params.Exists(key) = False) Then
        Debug.Print ("コンフィグ定義エラー" & vbCrLf & "キー:[" & key & "]が設定ファイルに定義されていません。")
    End If
    
    Dim val As String
    val = params.Item(key)
    
    ' Valueに含まれる置換変数を列挙する
    Dim reg As RegExp
    Set reg = New RegExp
    reg.Pattern = ("<.+?>")
    reg.Global = True
    Dim matches As MatchCollection
    Set matches = reg.Execute(val)
    Set reg = Nothing
    
    Dim m As Match
    For Each m In matches
        Dim replace_key As String
        Dim replace_val As String
        '  置換変数を取り除く
        replace_key = Mid(m.Value, 2, Len(m.Value) - 2)
        replace_val = ResolveValue(replace_key)
        val = Replace(val, m.Value, replace_val)
    Next
    ' 置換変数から定数への置き換えが完了したらValueを返却する
    ResolveValue = val
End Function
' コンフィグファイルからキーに対応する値を取得します。
Public Function GetEnv(key As String) As String
    Dim c As ConfigAccessor
    Set c = New ConfigAccessor
    Call c.LoadFile(ThisWorkbook)
    Dim val As String
    val = c.ResolveValue(key)
    Set c = Nothing
    
    GetEnv = val
End Function

'
' コンフィグファイルからパス/ファイル名を指定し、合致するファイルパスのコレクションを取得する。
' ファイル名には正規表現を指定することも出来る。
' [config]
'     template_file_path=C:\Works\
'     template_file_name=Dog*.xlsx
' [vba]
'     GetSpecificFileName("template_file_path", "template_file_name")
'    ⇒ ["C:\Works\Dog123.xlsx", "C:\Works\Dog456.xlsx", "C:\Works\Dog789.xlsx"]
'
Public Function GetFilePathList(key_file_path As String, key_file_name As String) As Collection
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    
    Dim file_path As String
    Dim file_name As String
    file_path = GetEnv(key_file_path)
    file_name = GetEnv(key_file_name)
    
    Dim c As Collection
    Set c = New Collection
    Dim match_count As Long
    Dim f As Scripting.File
    With fso
        For Each f In .GetFolder(file_path).Files
            If (f.Name Like file_name) Then
                c.Add (f.Path)
            End If
        Next
    End With
    
    Set fso = Nothing
    Set GetFilePathList = c
End Function

解説

ConfigAccessor

  • LoadFile
    「=」の前後をkeyとvalueに分けてDictionaryに追加しているだけである。
    「#」から始まる行はコメントとして、Dictionaryへの追加は行わない。
  • ResolveValue
    ①keyに対応するvalueをDictionaryから取得する。
    value内に置換変数「<>」が含まれていたら、再帰的にvalueを検索する。

    # ルートフォルダ
    RootDir=総務部/
    Category=ゲーム/
    
    # 子フォルダ
    SubFolder1=<RootDir><Category>紅魔郷/
    SubFolder2=<RootDir><Category>風神録/
    SubFolder3=<RootDir><Category>妖々夢/
    
    # 孫フォルダ
    Character1=<SubFolder1>博麗霊夢/
    Character2=<SubFolder1>霧雨魔理沙/
    

    例えば、GetEnv("Character1")を呼び出した場合、

  1. <SubFolder1>博麗霊夢/を取得。置換変数「SubFolder1」があるため、再帰検索。
  2. <RootDir><Category>紅魔郷/を取得。置換変数「RootDir」があるため、再帰検索。
  3. 総務部/を取得。置換変数がないため、総務部/を返却。
  4. ゲーム/を取得。置換変数がないため、ゲーム/を返却。
  5. 総務部/ゲーム/紅魔郷を返却。
  6. 総務部/ゲーム/紅魔郷/博麗霊夢/を返却。

Config

  • GetEnv
    呼び出すためにファイルを開いているため、パフォーマンスはよろしくない。
    1度のファイルオープンで済ますことを考えると、どこかにデータを保持しなければならず、利用する側のコードが汚れてしまう。
    呼び出す回数は少ないことを前提に、楽に呼び出せるように、毎回クラスは破棄する作りを取っている。
    また、このConfigはアドインとして既存ブックに追加することを想定しており、クラスを利用するためにはクラスのスコープを広げなければいけないといけず面倒、といった理由もある。
  • GetFilePathList
    よく末尾にタイムスタンプのついたファイルを見かける。その場合、コンフィグ内でファイル名を固定値で定義することができない。
    かつ、ファイル名の接頭語だけ各ブックに定数として持たせるというのも、結局Configを導入した意味がなくなってしまう。
    そのため、正規表現で一致するファイルパスを一覧で取得するという処理を追加した。
    Configというプロシージャの処理としては合致しない気がしないのだが、横着してそのまま同じところに定義した。

終わりに

エラー処理を全くしていないので、良きようにカスタマイズしましょう。