Excel VBAで設定値はコード直書きか、設定シート管理か

Excel VBAで業務用ツールを作っていると、

設定値をどこで管理するかで迷うことがあります。

例えば、

フォルダ名
出力ファイル名
上限件数
シート名
テンプレート名
表示文言
処理対象の選択肢

などです。

これらをVBAコード内に直接書くのか。

それとも、

Excel上に設定シートを作って管理するのか。

どちらにもメリットがあります。

ただし、

使い分けを間違えると、

配布後に保守しづらくなったり、

利用者に設定セルを壊されたりします。

結論：誰が変更する値かで決める

まず結論です。

開発者しか変更しない値は、

コード内定数

でよいです。

一方で、

利用者が変更する可能性のある値は、

設定シート

で管理した方が分かりやすいです。

ただし、

設定シートは万能ではありません。

利用者がセルを消したり、

値の形式を変えたり、

必要な項目を上書きしたりする可能性があります。

そのため、

実務配布用のExcelでは、

開発者が固定する値
利用者が変更する値

を分けることが重要です。

迷ったら、

処理の根幹はコード内定数、利用者が変える値は設定シート

と考えるのが無難です。

コード直書きと設定シート管理の違い

方法	向いている値	メリット	注意点	実務でのおすすめ用途
コード直書き	利用者に変更してほしくない値	利用者が誤編集しにくい	変更時にVBA修正が必要	アプリ名、バージョン、必須シート名、内部上限
設定シート管理	利用者が変更する値	コードを触らず変更できる	セル削除・誤入力で壊れやすい	フォルダ名、表示文言、処理条件、軽微な上限

どちらが正しいという話ではありません。

重要なのは、

その値を

誰が変更するのか

です。

コード直書きとは

コード直書きとは、

VBAコードの中に設定値を直接書く方法です。

例えば、

Private Const APP_NAME As String = "Sample Tool"
Private Const MAX_COUNT As Long = 10

のような書き方です。

このように Const で定数化しておけば、

コード内で意味を持った値として扱えます。

コード直書きは、

利用者に変更してほしくない値に向いています。

例えば、

アプリ名
バージョン番号
内部処理用の固定上限
必須シート名
テンプレートファイル名
変更すると処理が壊れる値

などです。

利用者がシート上で誤って触れないため、

内部設定としては安全です。

ただし、

値を変更するにはVBAコードを修正する必要があります。

設定シート管理とは

設定シート管理とは、

Excelシート上に設定値を書いておき、

VBAから読み取る方法です。

例えば、

設定シートに次のような表を作ります。

設定名	設定値
出力フォルダ名	03_output
最大処理件数	10
表示文言	処理を開始します

この方式なら、

VBAコードを触らずに設定を変更できます。

利用者ごとに変わる値や、

運用上たまに変更する値には向いています。

ただし、

設定シートには弱点もあります。

セルを消されたり、

位置を移動されたり、

値の形式を変えられたりすると、

VBA側の処理が壊れる可能性があります。

そのため、

設定シートを作るなら、

説明欄、

入力規則、

シート保護、

空欄チェックもセットで考える必要があります。

コード直書きが向いている値

コード直書きが向いているのは、

利用者に変更してほしくない値です。

例えば、

アプリ名
バージョン番号
内部処理用の固定上限
必須シート名
テンプレートファイル名
ログシート名
処理上必要な固定値

などです。

こうした値は、

利用者が自由に変更できると危険です。

例えば、

必須シート名を設定シートで自由に変えられるようにすると、

シート名の表記ゆれだけで処理が止まることがあります。

また、

テンプレート名や内部上限値なども、

不用意に変えられると不具合の原因になります。

このような値は、

コード内で Const として固定した方が安全です。

設定シートが向いている値

設定シートが向いているのは、

利用者が変更する可能性のある値です。

例えば、

出力フォルダ名
出力ファイル名の接頭辞
処理対象の選択肢
表示文言
軽微な上限値
会社ごとに変わる設定
入力候補リスト

などです。

利用者や運用によって変わる値を、

毎回VBAコードで修正するのは面倒です。

このような値は、

設定シートに置いた方が扱いやすくなります。

ただし、

設定シートに置く以上、

利用者が触れる前提になります。

そのため、

どのセルを変更してよいのか、

どのセルは触ってはいけないのかを、

分かるようにしておく必要があります。

設定シートで起きやすい事故

設定シートは便利ですが、

実務では事故も起きやすいです。

よくあるのは、

次のようなケースです。

設定セルを削除される
セル位置を移動される
入力値の形式が変わる
全角半角や空白の違いで判定できなくなる
設定シートを非表示にしたら存在を忘れる
保護していないため利用者が壊してしまう

特に多いのは、

「ちょっと見やすくしよう」として、

行や列を移動されるケースです。

VBA側がB1セルを読む前提なのに、

設定値がC1セルへ移動されていたら、

処理は正しく動きません。

設定シートは、

見える化できる反面、

壊される可能性もあります。

何でも設定シート化しない

設定シートは便利ですが、

何でも設定シート化すればよいわけではありません。

設定項目が増えすぎると、

利用者も開発者も把握しきれなくなります。

また、

「この設定を変えると何が起きるのか」

が分からない項目が増えると、

かえって危険です。

設定シートは、

利用者が理解できる範囲に絞るべきです。

内部処理の都合で使っている値まで設定シートへ出すと、

利用者が触って壊す原因になります。

コピペで試せる設定シート読み取りコード

次のコードは、

設定シートから設定値を読み取るためのサンプルです。

1枚目のシートを設定シートとして扱い、

A1セルに設定名、

B1セルに設定値がある想定です。

B1セルが空欄ならメッセージを出して処理を止めます。

値があれば、

設定名と設定値をMsgBoxで表示します。

このコードは、

標準モジュールに貼って、テスト用のxlsmで実行してください。

ご注意

以下のVBAコードは、動作イメージを確認するためのサンプルです。
実行前に必ずExcelファイルのバックアップを作成してください。
業務で使用しているファイルに、いきなり貼り付けて実行しないでください。
Excelのバージョンや設定により、動作が異なる場合があります。

Sub ReadSettingValue()

    Dim ws As Worksheet
    Dim settingName As String
    Dim settingValue As String

    Set ws = ThisWorkbook.Worksheets(1)

    settingName = Trim(ws.Range("A1").Value)
    settingValue = Trim(ws.Range("B1").Value)

    If settingValue = "" Then
        MsgBox "設定値が空欄です。B1セルを確認してください。", vbExclamation
        Exit Sub
    End If

    MsgBox "設定名: " & settingName & vbCrLf & _
           "設定値: " & settingValue, vbInformation

End Sub

このコードでは、

設定値を読み取って表示するだけです。

ファイル削除、

上書き保存、

外部通信などは行いません。

ただし、

実務ファイルではなく、

必ずテスト用ファイルで試してください。

実務配布ではどう分けるべきか

実務配布用のExcelツールでは、

次のように分けると整理しやすくなります。

開発者が固定する値

これはコード内定数にします。

例えば、

必須シート名
内部処理名
バージョン
固定上限
テンプレート名
ログシート名

などです。

利用者が変更すると壊れる値は、

基本的にシート上へ出さない方が安全です。

利用者が変更する値

これは設定シートにします。

例えば、

出力フォルダ名
会社ごとに変わる文言
処理条件
入力候補リスト
表示用メッセージ
軽微な運用設定

などです。

ただし、

設定シートには説明欄を付けます。

必要に応じて、

入力規則やシート保護も入れます。

設定シートには説明欄を付ける

設定シートを作るなら、

設定値だけを並べるのではなく、

説明欄を付けた方が安全です。

例えば、

設定名	設定値	説明
出力フォルダ名	03_output	作成ファイルの保存先フォルダ名
最大処理件数	10	無料版で処理できる件数
表示文言	処理を開始します	実行時に表示するメッセージ

このようにすると、

利用者が何を変更してよいのか分かりやすくなります。

設定値だけが並んでいると、

後から見た人が意味を理解できません。

設定シートだけを信頼しない

設定シートを使う場合でも、

設定シートだけを信頼するのは危険です。

例えば、

B1セルに数値が入る想定でも、

利用者が文字を入力するかもしれません。

フォルダ名を入力する想定でも、

空欄のままかもしれません。

そのため、

コード側でも最低限のチェックを入れる必要があります。

空欄ではないか
数値として扱えるか
許可された値か
必要な範囲内か
想定外の文字が入っていないか

こうしたチェックを入れることで、

設定シート由来の事故を減らせます。

設定シートを使うなら保護も考える

設定シートは、

利用者が変更する場所です。

しかし、

すべてのセルを自由に変更されると困ります。

そのため、

変更してよいセルと、

変更してはいけないセルを分けます。

実務では、

入力セルだけ色を付ける
説明欄を付ける
入力規則を設定する
数式セルをロックする
必要に応じてシート保護する

といった工夫が必要です。

設定シートは作って終わりではありません。

壊されにくいように作ることが大事です。

コード直書きは悪ではない

「設定値をコードに直書きするのは悪い」

と言われることがあります。

しかし、

実務配布用Excelでは、

コード直書きが安全な場面もあります。

特に、

利用者に変更してほしくない値は、

シート上に出さない方がよいです。

ただし、

コード内に直接何度も同じ値を書くのは避けた方がよいです。

その場合は、

Const

で定数化しておくと読みやすくなります。

Private Const MAX_RECORDS As Long = 10
Private Const OUTPUT_FOLDER As String = "03_output"

このようにすれば、

コード内で意味が分かりやすくなり、

変更箇所も集約できます。

まとめ

Excel VBAで設定値を管理するとき、

コード直書きと設定シート管理には、

それぞれ向き不向きがあります。

コード直書きは悪ではありません。

変えない値、

利用者に変更してほしくない値は、

コード内定数にした方が安全です。

一方で、

利用者が変更する値は、

設定シートで管理すると便利です。

ただし、

設定シートは誤編集されるリスクがあります。

説明欄、

入力規則、

保護、

コード側のチェックが必要です。

実務配布用Excelでは、

誰が変更する値か

で置き場所を決めるのが基本です。

処理の根幹はコード内定数。

利用者が変える値は設定シート。

この分け方が無難です。

Excelの名前定義は便利なのか、それとも事故の元なのか
Excelの入力規則とVBAチェックはどっちで制御すべきか
Excel VBAでRangeとCellsはどっちを使うべきか

維持DXノートについて

維持DXノートでは、

建設コンサル実務で使うExcel/VBAや、

橋梁点検調書作成支援ツールの開発メモを公開しています。

Excel作業の自動化や、

帳票作成の手間を減らす考え方を、

実務目線で整理しています。

Excel VBAで設定値はコード直書きか、設定シート管理か

Excel VBAで設定値はコード直書きか、設定シート管理か

結論：誰が変更する値かで決める

コード直書きと設定シート管理の違い

コード直書きとは

設定シート管理とは

コード直書きが向いている値

設定シートが向いている値

設定シートで起きやすい事故

何でも設定シート化しない

コピペで試せる設定シート読み取りコード

実務配布ではどう分けるべきか

開発者が固定する値

利用者が変更する値

設定シートには説明欄を付ける

設定シートだけを信頼しない

設定シートを使うなら保護も考える

コード直書きは悪ではない

まとめ

関連記事

維持DXノートについて

関連する無料Excelツール

コメント