はじめに
複数の人で利用するマクロ付きのエクセル、プログラムのソースコード内部に書いてしまうような設定情報をワークシート上に残しておきたい。ワークシート上で設定情報を変更してふるまいを制御したい、そんな状況に陥ったことはありませんか？私は毎回です。
- 複数の人で設定を変更しながら、同じファイルを使う
- マクロに習熟していない人が使う
上記のような状況の方にはぴったり。設定情報をシートから読み出す VBA です。つまり、下記のようなエクセルシートがあって：
ソースコードで:
getConfig("target_file")
とすると、
sample.txt
と帰ってくるような、そんなヤツです。何度も作る羽目になりましたので、皆さんにも紹介します。
準備するもの
上記を実現するには設定情報用のワークシートと、呼び出し用のモジュール、この二つが必要になりますので皆さんの環境にも準備をしてください。
設定情報用のワークシート
このシートへのアクセスはシート名で行います。今回の例では、シート名を Conf としておきます。任意のワークシート上に、下記のような VBA の中から呼び出したい名前と、その設定情報をつけた一覧表を用意します。前述の例では KeyやValueといった名前がついていますが今回の呼び出しには全く使用しません。その代わり、設定テーブルが何行目の何列目から始まっているのかが必要です。
| 項目 | 設定値 |
|---|---|
| シート名 | Conf |
| 設定テーブル開始列 | 2列目 |
| 設定テーブル開始行 | 3行目 |
モジュール
先般の設定情報を呼び出す関数を作ります。シート内部に記載してしまってもよいですが、間違ってシート削除するとソースコードまで一緒になくなるのでモジュール化しておきましょう。モジュールの中には、下記の情報をコピペすれば大丈夫です。
' mConf Module
Option Explicit
' getConfigを使用するために必要な情報
' テーブルがあるシート名
Private Const gC_WS_NAME As String = "Conf"
' 1. テーブル「開始行」(初期値:4行目から開始) ヘッダは入れない
Private Const gC_i_ROW_START As Integer = 3
' 2. 「キー」となる列
Private Const gC_i_COL_KEY As Integer = 2
' 3. 「値」の列
Private Const gC_i_COL_VALUE As Integer = 3
' getConfigで使用するHashmap Object
Private hashMap As Object
'
' getConfig 本体
' ----------------------------------------------------
' シートにある設定項目を読み出す。入力は文字列
' 出力も文字列に限定
Public Function getConfig(ByRef key As String) As String
Dim hm As Object ' hash map object 取得用の変数
Set hm = getInstance()
getConfig = hm.Item(key)
End Function
' getInstance - singleton pattern
Private Function getInstance() As Object
' getInstanceを呼び出して、何度もHashMapの生成をさせない
If hashMap Is Nothing Then
Call initHM(hashMap)
End If
Set getInstance = hashMap
End Function
' initHM - initialize HashMap Object
' getConfig を呼び出した際に、hashMap が初期化されていない場合に
' 限り呼び出される初期化サブルーチン
Private Sub initHM(ByRef hm As Object)
Set hm = CreateObject("Scripting.Dictionary")
' テーブル開始行からデータ取得開始
Dim i As Integer: i = gC_i_ROW_START
Dim key As String: key = ""
Dim value As String: value = ""
' 該当するワークシートがないとエラー
Dim ws As Worksheet: Set ws = Worksheets(gC_WS_NAME)
' key列に情報が入っている限り、key / value のペア取得を続ける
Do While ws.Cells(i, gC_i_COL_KEY) <> ""
key = ws.Cells(i, gC_i_COL_KEY)
value = ws.Cells(i, gC_i_COL_VALUE)
' set into Key-Value-Store and increase counter number
hm.add key, value
i = i + 1
Loop
End Sub
モジュール名を mConf にした場合だと、mConf.getConfig("target_file")という呼び出しになると思います。同じ悩みを思っている方はぜひお試しください。