VBA マクロの設定をシートに保存する場合のテクニック

Excelマクロの設定をシートに保存しておくことがよくある。

ただメインマクロから直接Range指定で設定を取り出すと、シートの構成が変わった場合にマクロを編集しなければならないので面倒である。
そこで、シートの設定はシート自身のプロパティとして持たせておくと、変更箇所が局所的になって後々メンテナンスしやすい。

文章だけでは意味が分からないと思うのでコードを書いて解説する。

シートの準備

まず、Book1.xlsmに設定シートを次のように作る。

f:id:t-hom:20150621015819p:plain

このとき、VBEのプロパティウインドウで、設定シートのオブジェクト名も「設定」としておく。

f:id:t-hom:20150621011840p:plain

過去に一度紹介したが、シートへのアクセス方法は3種類ある。
1つ目はシート名でのアクセス　Worksheets("設定").Range("A1").Value
2つ目はシート番号でのアクセス　Worksheets(1).Range("A1").Value
3つ目はシートのオブジェクト名でのアクセス　設定.Range("A1").Value

マクロで追加したり消したりしない固定シートの場合は、オブジェクト名を最初にきっちり決めてしまえば、3つ目の方法が一番変更に強くなる。（シート名が変更されても、シートの順番が変わっても動作する。）

シートモジュールへのコード記入

設定モジュールへは以下のコードを記入する。

Enum 設定行
    読み取りファイルパス = 2
    出力ファイルパス
End Enum
Const 値列 As Long = 2

Property Get a_読み取りファイルパス() As String
    a_読み取りファイルパス = Cells(設定行.読み取りファイルパス, 値列).Value
End Property

Property Get a_出力ファイルパス() As String
    a_出力ファイルパス = Cells(設定行.出力ファイルパス, 値列).Value
End Property

まずEnumで設定行を指定しているのはメンテナンスを楽にするため。
Enumの動作として、並び順に値が設定されること。
先頭値を2にすれば次は3となる。
設定値を追加したければ、下に設定名を書いてプロパティを追加するだけでよく、シート上で並び替えたならそのままEnumに貼り付け直して先頭行に=2を追加すればよい。

※プロパティ名をa_で始まるようにした理由はあとで説明する。

メンテナンス用に別途以下のようなプロシージャを作ってもよい。

Private Sub プロパティ追加()
    Dim p: p = "[設定名]"
    Debug.Print "Property Get a_" & p & "() As String"
    Debug.Print "    a_" & p & " = Cells(設定行." & p & ", 値列).Value"
    Debug.Print "End Property"
End Sub

これはマクロにマクロを書かせるテクニックである。
pの値を実際の設定名に変えて実行すればイミディエイトウィンドウにプロパティのコードが出力されるので、あとは切り取ってコードを張るだけでよくなる。

標準モジュール(メインマクロ)のコード

今回はサンプルなのでメインマクロはシンプルにしておく。

Sub test()
    Debug.Print 設定.a_出力ファイルパス
End Sub

プロパティ名をa始まりにしたのは、プロパティの候補がアルファベット順に並ぶためコーディングが楽になるからである。
f:id:t-hom:20150621014010p:plain
英語のプロパティ名にする場合はキーで候補を探せるが、日本語の場合はやや面倒くさいのでa_をつけた。

解説

今回のポイントは、「設定のことは設定シートに聞け」である。
設定値は設定シートに保存されているので、それを提供する設定シート自体にプロパティとして実装することで、メインマクロでは余計なことを考えずに「シートオブジェクト.プロパティ」で設定を読み出すことができる。

なお、Enumは単なる並び順に値を持たせているだけなので、シート上の設定名を変更しても問題なく動作する。
ただ、今後の並び順変更などのメンテナンスも考慮すると、設定名が変わったときはシートモジュールのコードもメンテナンスしておきたい。
たとえば出力ファイルパスという名前を出力フォルダに変えた場合はコードを次のようにしておく。

Enum 設定行
    読み取りファイルパス = 2
    出力フォルダ
End Enum
Const 値列 As Long = 2

Property Get a_読み取りファイルパス()
    a_読み取りファイルパス = Cells(設定行.読み取りファイルパス, 値列).Value
End Property

Property Get a_出力ファイルパス() As String
    a_出力ファイルパス = Cells(設定行.出力フォルダ, 値列).Value
End Property