Excel VBAで帳票処理を自動化するとき、最初は一つのExcelファイルだけを対象にして作ることが多いと思います。
ところが実務で使い始めると、
「別の自治体ではシート名が違う」
「この帳票だけデータ開始行が1行ずれている」
「年度が変わったら出力先の列が変わった」
「同じ処理なのに、発注者ごとに少しだけ条件が違う」
といった差分が出てきます。
このとき、差分が出るたびにVBAの中へ、
If 自治体 = "A" Then
...
ElseIf 自治体 = "B" Then
...
End Ifと条件分岐を追加していくと、最初は動いても、徐々にコードが読みにくくなります。
建設実務の帳票では、処理そのものよりも、
自治体や発注者ごとの「少しだけ違う条件」をどう管理するか
が重要になることがあります。
この記事では、Excel VBAで自治体ごとの帳票差分へ対応するときに、処理条件をコードへ直接書き込むのではなく、「設定値」として分離して管理する考え方を整理します。
自治体ごとの差分をすべてVBAへ書くと管理が難しくなる
たとえば、ある帳票処理で次のような条件があるとします。
A自治体では入力シート名が「点検結果」。
B自治体では「点検調書」。
A自治体ではデータ開始行が10行目。
B自治体では12行目。
出力先シート名も異なる。
これをそのままVBAへ書くと、自治体ごとに分岐を増やす設計になりがちです。
最初の2自治体だけなら大きな問題には見えません。
しかし対象が増えてくると、
「このIf文はどの自治体用なのか」
「この行番号を変更すると他の処理へ影響しないか」
「去年の様式だけ条件が違うのはどこか」
といった確認が必要になります。
さらに、VBAを作った本人以外が修正するときには、コードを読まなければ条件を確認できません。
つまり、
業務上の設定とプログラムの処理が混ざっている
状態です。
帳票差分が増えるほど、この混在が保守を難しくします。
「処理」と「設定」を分けて考える
そこで有効なのが、VBAの処理と自治体ごとの設定値を分ける考え方です。
たとえば、
「指定した入力シートからデータを読む」
という処理そのものは共通です。
異なるのは、
「どのシートを読むか」
という設定です。
同じように、
「指定した行からデータを読み始める」
処理は共通でも、
開始行が10なのか12なのかは自治体ごとに変わります。
この違いをVBAコードの中へ直接書かず、Excel上の「設定」シートなどへ持たせます。
たとえば、次のような形です。
| 設定項目 | 設定値 | 説明 |
|---|---|---|
| INPUT_SHEET | 点検結果 | 入力元シート名 |
| OUTPUT_SHEET | 統合マスタ | 出力先シート名 |
| START_ROW | 10 | データ開始行 |
| HEADER_ROW | 9 | 見出し行 |
| MUNICIPALITY_CODE | A001 | 自治体識別用コード |
VBA側では、この表から必要な設定値を読み込んで処理します。
すると、自治体ごとの差分は設定表へ集まり、VBA本体は共通処理として残しやすくなります。
設定シートから値を読む
設定値を取得する方法はいくつかあります。
単純な方法なら、設定シートのA列にキー、B列に値を置き、キー名から検索します。
考え方としては、次のような形です。
Function GetSetting(ByVal key As String) As String
Dim ws As Worksheet
Dim f As Range
Set ws = ThisWorkbook.Worksheets("設定")
Set f = ws.Columns("A").Find(What:=key, LookAt:=xlWhole)
If f Is Nothing Then
Err.Raise vbObjectError + 1, , "設定値が見つかりません: " & key
End If
GetSetting = CStr(f.Offset(0, 1).Value)
End Function処理側では、
inputSheetName = GetSetting("INPUT_SHEET")のように取得します。
この程度の小さな仕組みでも、
Worksheets("点検結果")とコードへ直接書く箇所を減らせます。
重要なのは、この関数そのものではありません。
どの条件をコードへ固定し、どの条件を設定として外へ出すか
を先に決めることです。
なお、実際の業務ファイルへVBAを追加・修正するときは、必ずコピーやテスト用ファイルで動作確認してから本番データへ適用してください。
設定値に向いているもの
設定シートへ出した方がよいのは、業務や発注者によって変わる可能性がある値です。
たとえば、
シート名、開始行、終了行、年度、自治体コード、処理対象フォルダ名、出力ファイル名の接頭辞、処理対象の有無などです。
反対に、どんな案件でも変わらない内部処理まで何でも設定化すると、今度は設定項目が増えすぎます。
設定化する目安は、
利用者や案件によって変更される可能性があるか
です。
変わらない処理ロジックはコードへ残し、変わる条件だけを設定値へ出します。
自治体ごとに設定を切り替える方法
対象自治体が複数ある場合は、設定シートを自治体ごとに複製する方法もあります。
ただし、自治体が増えるたびに設定シートが増えると管理しにくくなるため、ある程度増えてきたら一覧形式で持つ方法もあります。
たとえば、
| 自治体コード | INPUT_SHEET | START_ROW | OUTPUT_SHEET |
|---|---|---|---|
| A001 | 点検結果 | 10 | 統合マスタ |
| B001 | 点検調書 | 12 | マスタ |
| C001 | 点検結果表 | 11 | 統合結果 |
のように1行を1自治体の設定とします。
処理開始時に自治体コードを指定し、その行の設定値を読み込みます。
こうすると、VBA側では、
「自治体コードに対応する設定を取得する」
という処理だけを共通化できます。
新しい自治体へ対応するときも、まず設定行を追加して対応できるかを確認できます。
コード修正が必要なのは、既存設定では表現できない新しい処理が必要になった場合です。
設定値だけでは吸収できない差分もある
ここは重要です。
自治体帳票の違いは、すべて設定値だけで吸収できるわけではありません。
たとえば、
A自治体では1行につき1部材。
B自治体では1行に複数部材。
C自治体では評価結果がセルではなくShapeに入っている。
といった違いがあれば、単にシート名や行番号を変更するだけでは対応できません。
これは「設定値の差」ではなく、
帳票構造やデータ構造そのものの差
です。
この場合は、処理ロジックを分ける必要があります。
つまり、
「違いがあるから全部If文」
でもなく、
「違いは全部設定値で吸収」
でもありません。
差分の種類を見極める必要があります。
設定値とセルマッピングは分けて考える
帳票自動化では、設定値とセルマッピングを混同しない方が管理しやすくなります。
設定値は、
「どのシートを使うか」 「何行目から読むか」 「どの自治体として処理するか」
といった処理条件です。
一方、セルマッピングは、
「橋梁名はどのセルか」 「路線名はどこか」 「評価結果はどこから取得するか」
という、業務項目と帳票位置の対応関係です。
似ていますが役割が違います。
設定値までセルマッピング表へ詰め込むと、何を管理している表なのか分かりにくくなります。
逆に、セル位置をすべて設定値として持たせると、設定表が巨大になることがあります。
そのため、
動作条件を設定値として管理し、帳票項目の位置関係はマッピングとして別管理する
方が整理しやすくなります。
セルマッピングの設計は、別の記事で詳しく扱います。
設定項目には名前を付ける
設定表では、セル番地そのものを意味として使わない方が扱いやすくなります。
たとえば、
Range("B3").Valueを設定値として使うだけでは、B3が何を意味しているかコードを見ないと分かりません。
そこで、
INPUT_SHEET
START_ROW
OUTPUT_SHEETのようなキー名を付けます。
これにより、
「B3の値を読む」
ではなく、
「INPUT_SHEETという設定を読む」
という意味になります。
人間が確認するときにも分かりやすくなります。
AIへVBA修正を依頼するときも、
「B3を参照して」
より、
「INPUT_SHEET設定を参照して」
と指示した方が仕様を伝えやすくなります。
数値や真偽値は型を意識する
Excelのセルは柔軟ですが、その柔軟さが設定ミスにつながることもあります。
たとえばSTART_ROWへ本来数値を入れるべきところへ、
10行目と入力すると、そのままでは数値として使えません。
また、
YES
TRUE
有効
1など、同じ意味を複数の表現で入力できる状態にすると、VBA側の判定が複雑になります。
設定値を作るときは、
数値は数値。
ON/OFFはTRUE/FALSE。
選択肢が限られているものは入力規則。
というように形式をそろえた方が安全です。
設定シートを自由記入欄にするのではなく、簡単な仕様書として扱うイメージです。
必須設定がない場合は止める
設定値を外へ出すと、今度は設定漏れという新しい問題が生まれます。
そのため、処理開始時に必要な設定がそろっているか確認します。
たとえば、
INPUT_SHEETが空欄なら処理を中止する。
START_ROWが数値でなければ警告する。
指定したシートが存在しなければ処理を止める。
といった確認です。
設定値を使う設計では、
自由に変更できることと、間違った設定で処理を続けないこと
をセットで考える必要があります。
エラーを出さずに最後まで動くことが、必ずしも安全なVBAではありません。
設定がおかしい場合に適切に止まる方が、実務では重要なことがあります。
新しい自治体へ対応するときの手順を決めておく
設定値方式のメリットは、新しい帳票へ対応するときの確認手順を作りやすいことです。
まず既存処理と何が違うか確認します。
次に、その違いが既存の設定項目だけで表現できるか確認します。
設定変更だけで対応できるなら、新しい自治体用の設定を追加します。
対応できなければ、
「なぜ既存ロジックでは処理できないのか」
を整理してからコード側を拡張します。
この順番にすると、帳票が増えるたびに無条件でVBAを変更することを避けやすくなります。
設定シートは利用者が変更してよい範囲を明確にする
設定シートを作ったからといって、すべてのセルを自由に編集できる状態にする必要はありません。
実務では、
「変更してよい設定」
と、
「内部処理用なので通常は変更しない設定」
を分けた方が安全です。
入力セルだけ色を変える。
説明列を付ける。
入力規則を使う。
必要ならシート保護を使う。
といった方法があります。
重要なのは、VBAを作った本人だけが理解できる設定表にしないことです。
設定シートは、
利用者とプログラムの間にある小さな仕様書
でもあります。
設定値に機密情報を置かない
設定シートは利用者から見える場所にあるため、パスワード、APIキー、認証情報などを安易に保存する場所には向きません。
今回扱っているのは、
シート名、行番号、自治体コード、処理対象の選択など、
帳票処理上の条件です。
機密性のある認証情報については、別の管理方法を検討する必要があります。
「設定値として外へ出す」という考え方と、
「何でもExcelへ保存する」
ことは別です。
SaaS導入後のラストワンマイルにも同じ考え方が使える
この設計は、VBA単体の話だけではありません。
建設DX製品やSaaSを導入した後でも、
製品から出力されたCSVやExcelを、自治体独自帳票へ変換する作業が残ることがあります。
このとき自治体ごとの差分をすべて個別コードへ埋め込むと、対象が増えるほど保守が難しくなります。
一方、
標準処理は共通化し、
自治体ごとの条件を設定として切り出し、
構造そのものが違う部分だけ個別処理にする。
という分け方ができれば、ラストワンマイルの処理を整理しやすくなります。
製品本体へすべての自治体差分を持たせるのではなく、外側の変換処理で吸収する設計が適する場合もあります。
どこまでを製品へ任せ、どこからを設定や個別処理で補完するかは、対象業務や製品によって異なります。
まとめ
自治体ごとにExcel帳票が少しずつ違うと、VBAへ条件分岐を追加したくなります。
しかし、すべての差分をコードへ直接書くと、対象が増えるほど保守が難しくなります。
そこで、
共通の処理はVBAに残す。
変わりやすい条件は設定値として外へ出す。
帳票構造そのものが違う場合だけ処理ロジックを分ける。
という整理が有効です。
重要なのは、設定シートを作ること自体ではありません。
何が共通で、何が案件ごとに変わり、何が構造的に別物なのかを先に分けること
です。
この整理ができると、Excel VBAの保守だけでなく、SaaSや既存システムと自治体独自帳票を接続するときにも、どこを標準化し、どこをラストワンマイルとして補完するか考えやすくなります。
ダウンロード
維持DXでは、国交省様式を対象とした橋梁点検支援ツールを無料・機能制限版として公開しています。
実際の処理イメージを確認したい方は、以下のフォームからお申し込みください。
フォーム送信後に、ダウンロード案内をお送りします。
ご注意
ダウンロードしたExcelでマクロが実行できない場合は、
右クリック → プロパティ →「許可する」 をチェック後、再度開いてください。Windowsのセキュリティ機能により、初回実行時にマクロがブロックされる場合があります。
