このエントリは、以下のような状況において、力を発揮する。

ツールから成果物への要求が頻繁にある
ツールの保守エンジニアや、ツールを使用する現場スタッフに、引き継ぎの可能性がある
中長期間使われるツールである
Excelの使える現場スタッフで、ちょっとしたツールの仕様変更に対応したい

限定された状況とはいえ、いわゆるバックオフィスのEUA/EUC（エンドユーザーシステム）開発においては、ありがちな条件だろう。この前提条件は整理してまた別エントリに記載する。

テーブルを作る

たとえば、以下のような構成の設定シートを作る。

実際のツールでは、必要に応じて、条件付き書式やデータの入力規則を設定し、入力エラーを防ぐと良いだろう。設定シートの組み方のポイントはまた後日。

【共通関数】検索関数の作成

セル位置（行・列）の特定

まずは、変数名の入った行や、変数の入った列がどこかを特定する共通関数を作ろう。

'X列目を対象に検索し、行を特定する
Function Row_TgtCol(ByVal ValName As Variant, ByVal TgtCol As Long)
    With ActiveSheet
        Row_TgtCol = WorksheetFunction.Match(ValName, .Columns(TgtCol), 0)
    End With
End Function

'X行目を対象に検索し、列を特定する
Function Col_TgtRow(ByVal ValName As Variant, ByVal TgtRow As Long)
    With ActiveSheet
        Col_TgtRow = WorksheetFunction.Match(ValName, .Rows(TgtRow), 0)
    End With
End Function

VBAにおけるワークシート関数は、たいてい実行速度もそこそこだし、人によって書き方が変わる部分が少ないので、引き継ぎや改修の工数を減らせるのが利点と言える。

【共通関数】変数操作関数の作成

固定値の宣言・設定

まずはPublicかConstで、変数名や変数を入れる列と、タイトル行を指定する行の、変数を宣言しよう。
そして、ツールの実行時に、必ず最初に実行するプロシージャも作っておこう。ここにはワークシートの略称などを適宜設定しておくと良いだろう。

'設定シート固定行
Public Row_SetTitle As Long
'設定シート固定列
Public Col_SetVN As Long
Public Col_SetVal As Long

'開始設定（例）
Sub SetHack()
    Set Add_B = ThisWorkbook
    Add_B.Activate
    
    'シート名（シート名に合わせる）
    Set Add_S_GlobSet = Worksheets("GlobalSetting")
    Set Add_S_AddProf = Worksheets("AddinProf")
    '設定シート固定行
    Row_SetTitle = 1
    '設定シート固定列
    Col_SetVN = Col_TgtRow("VariableName",  Row_SetTitle)
    Col_SetVal = Col_TgtRow("Value",  Row_SetTitle)
End Sub

Public宣言してあるRow_SetTitle は、 Const Row_SetTitle As Long = 1 としても良い。

ただ、自分なら、このツールでは定数として扱うべきであっても、原則Publicで表記を統一する。なぜなら、同一の現場に Row_SetTitle と同じ役割を担う行が他の値であったり、自分や他人が作った関数を使うケースがありうると考えるからだ。なるべく書き方は統一し、保守コストを抑えたい。

また、決め打ちでの指定か、検索関数を使うかどうかも、現場で将来ありそうな仕様変更と、日頃のツールの使い方をヒアリングして決めよう。

変数の取得

次は、変数を取得する関数を作成する。

'変数取得_標準
Function GetVal_Dft(ByVal ValName As Variant)
    With ActiveSheet
        GetVal_Dft = WorksheetFunction.Index(.Cells, Row_TgtCol(ValName, Col_SetVN), Col_SetVal)
    End With
End Function

変数の書き換え

自動で変数の値を変更する処理が発生する場合（このシートでは、「連番」の値が該当するだろう）のために、変数を書き換える関数を作っておこう。

'変数記入_標準
Function SetVal_Dft(ByVal ValName As Variant, ByVal Val As Variant)
    With ActiveSheet
        .Cells(Row_TgtCol(ValName, Col_SetVN), Col_SetVal) = Val
    End With
End Function

人が変数値を決定するシーンでは、わざわざ上記の関数を使うことは勧めない。VBAのエラー発生のリスク低減や、VBAによる値チェックでコードを無駄に増やさないためだ。

名前の定義の削除/設定/取得（2019/1/23更新）

シートにDefinedNameという列と、変数Col_SetVNを追加した。

'名前の定義削除_標準
Function DltDN_Dft(ByVal ValName As Variant)
    With ActiveSheet
        Range(.Cells(Row_TgtCol(ValName, Col_SetVN), Col_SetVal).Address).Name.Delete
    End With
End Function

'名前の定義設定_標準
Function SetDN_Dft(ByVal ValName As Variant)
    With ActiveSheet
        Range(.Cells(Row_TgtCol(ValName, Col_SetVN), Col_SetVal).Address).Name = GetDN_Dft(ValName)
    End With
End Function

'名前の定義取得_標準
Function GetDN_Dft(ByVal ValName As Variant)
    With ActiveSheet
        GetDN_Dft = WorksheetFunction.Index(.Cells, Row_TgtCol(ValName, Col_SetVN), Col_SetDN)
    End With
End Function

SetHackについては、いちいちVBAから指定するよりは、「シート上で毎回実行するマクロとして明記して、Application.RunまたはCallする」、または「フォームボタンにSetHackを仕込んで、Captionに実行したいプロシージャ名を記載しておき、SetHack後にプロシージャを呼び出す」方がスマートなケースが多いだろう。この話も別エントリで。

神Excelの雑記

闇Excelでバックオフィスの人です。メモ帳的な。

ワークシートのデータベース的利用①設定シート編