おすすめのVBA定数定義方法4選（enum、定数クラス、シートに書く、Find）

ある程度綺麗にVBAを書こうと思っている時、必須と言っても過言ではないのが「定数定義」です。

例えば以下のようなコードがあったとします。

    Dim sh As Worksheet
    
    Set sh = ThisWorkbook.Worksheets("Sheet2")
    MsgBox Cells(1, 2).Value

「Sheet2」というワークシートの「B1」セルをメッセージボックスで表示するというだけのプログラムです。

しかし、仮にワークシート名が変更されたり、セルの挿入によって「B1」セルのアドレスが変わってしまえば、途端に動かなくなってしまいます。

そのため、定数を使わずに直でパラメータを書いていった場合は保守が非常に大変になってしまいます。

そこで定数で宣言しておくのがベターなのですが、おすすめの方法について記載していきます。

VBAで作成するツールの目的によって違う

結論から書くと、自分がオススメしたい定数定義の4選は以下となります。

社内、自分用ツール向け

• ワークシートに書く
• そもそも定義しない（Find）

顧客に渡すツール向け

• enum
• 定数クラス

「 社内、自分用ツール向け 」と「顧客に渡すツール向け」を分けたのは、

「 社内、自分用ツール向け 」 →　保守が簡単だけど、少し危険なコード

「顧客に渡すツール向け」 →　安全なコード

とすべきだからです。

社内チームで使う用なのに毎回面倒な定数クラスを作るのは怠いし、逆に顧客に渡す時はエラーがでるような書き方はできないので堅くいきます。

まずは「 社内、自分用ツール向け」について記載します。これは保守時にコードの変更が必要ないという方法という観点で選定しました。

ワークシートに書く

コードではなくワークシート上に定数の定義を書いてしまうという方法です。セルにデータを保持しておけるEXCELの特性を生かした定義方法ですね。

例として、以下のような表があったと仮定します。

この表の、商品と値段をすべて取得するプログラムを作りたいとすると、定数として必要なのは

• 表が開始される行番号
• 商品列の列番号
• 値段列の列番号

になるかなと思います。

これを取得するサンプルコードは以下になります。

定数用ワークシートの定義

コード

Private Const SHEET_NAME = "定数"
Private Const START_ROW = 1
Private Const NAME_COLUMN = 1
Private Const VALUE_COLUMN = 2

Private Consts As Object

'定数定義をシートから設定する処理
Private Sub setConsts()
    Dim sh As Worksheet
    
    'ワークシートを取得
    Set sh = ThisWorkbook.Worksheets(SHEET_NAME)
    
    '連想配列に変数名と変数値を入れていく
    Set Consts = CreateObject("Scripting.Dictionary")
    For i = START_ROW To sh.Cells(Rows.Count, 1).End(xlUp).Row
        Consts.Add sh.Cells(i, NAME_COLUMN).Value, sh.Cells(i, VALUE_COLUMN).Value
    Next i
End Sub

'定数定義を取得する処理
Public Function getConsts() As Object
    '定数定義が未取得であれば、取得処理へ
    If Consts Is Nothing Then
        Call setConsts
    End If
    
    Set getConsts = Consts

End Function

わざわざPrivateにしているのは、定数用のプロシージャを作る想定のためです。

Publicで宣言したgetConstを呼び出して連想配列を取得します。

Sub test()
    Dim a As Object
    
    Set a = getConsts
End Sub

取得結果

この処理はWorkBookOpenなどで書きたくなるのですが、このようにプロシージャを独立させて、「オブジェクトがなかったら取る」としたほうがセーフティーです。

もしプロジェクトでエラーが発生した場合に、プロジェクトを停止されてしまうと定数値が吹っ飛んでしまうからです。

このワークシートから取得する方法のメリット・デメリットは以下のとおりです。

メリット

• 定数値を変更するときに、コードは一切弄らなくていい（ワークシートの値を変更するだけ）
• 定数がすべてワークシートに集まるので、開発者が理解しやすい
• 他のVBAプロジェクトでもコードを使い回しやすい

デメリット

• ワークシートをイジられる危険がある場合に、保護やシート非表示が必須
• 定数未読み込みを起こしやすい

というところです。

デメリットの「定数未読み込みを起こしやすい」とは、ワークシートから値を取得せずに定数を呼び出してしまう、というバグを仕込みやすいです。

そのため、シッカリとしたプログラムを書きたいときには不向きですが、やはりコードを変更せずに動きを変えられるのが魅力なので、私はよく使っています。

そもそも定義しない（Find）

定数なんて定義せずにシート上から探してしまうという力技です。

ワークシートから取得のときと同様に以下の表から、値を取得する場合を考えます。

Findを使って無理矢理取得するサンプルソースは以下になります。

Private Const SHEET_NAME = "商品表"
Private Const ITEM_COLUMN_KEYWORD = "商品"

Sub test()
    Dim r As Range
    Set sh = ThisWorkbook.Worksheets(SHEET_NAME)
    Set r = sh.Cells.Find(What:=ITEM_COLUMN_KEYWORD)
    START_ROW = r.Row + 1
    ITEM_COLUMN = r.Column
    
End Sub

ワークシートを”商品”というキーワードで検索します。

すると商品というセル（B2）が取得できるので、そのセルのアドレスを利用して行番号や列番号の定義を作成するという方法です。

この方法のメリット・デメリットは以下のとおりです。

メリット

• 列の場所が変わってもコードを変更しなくて良い
• コードの記載量が少なくて単純
• 似たようなシートやブックを大量に読み込む際に、汎用的に作れる

デメリット

• 項目名自体を変更されたら動かなくなる
• 他のセルに項目名と同じ値を入れられると誤動作する可能性がある

メリットに書いた「似たようなシートやブックを大量に読み込む際に、汎用的に作れる」というのは、例えば上記の商品表のコピーで「商品表2」、 「商品表３」というシートを作成したとします。

仮に、 「商品表2」 と「商品表３」で開始行に違いがあった場合には、 通常の方法では定数定義を別にしなければいけません。

しかし、Findを使用した方法では動的に「商品」という列のアドレスから定数を取得するため使い回しが効く可能性が高いです。

私は、このFindによる方法をよく設計書の読み込みに使っています。

DB定義書とかはフォーマットは同じにすべきですが、微妙に作成者によって列が足されていたりする場合があります…。

その時に、Findを使用する方法であれば柔軟に対応できることが多いです。

社内や自分向けツール用のまとめ

社内や自分向けツール用として以下の方法をオススメしました。

• ワークシートに書く
• そもそも定義しない（Find）

これらの方法はコードを修正しなくてもよいのが魅力ですが、反面エラーには弱いので気をつける必要がありそうです。

次項から顧客に渡すツール向け（硬い書き方）について説明します。

enumを使用して定数定義する

例として、以下のような表があり、 商品と値段をすべて取得するプログラムを作りたい とします。

この表のデータを取得するために、定義しておきたい定数は以下になるかなと思います。

• 表が開始される行番号
• 商品列の列番号
• 値段列の列番号

上記を素直に定数定義してみると

Private Const START_ROW As Long = 2
Private Const ITEM_COLUMN As Integer = 2
Private Const PRICE_COLUMN As Integer = 3

こんな感じでしょうか。

※今回の記事の話題とは外れてしまうのですが、個人的に行番号は定数であってもLong型にしておくのがわかりやすいです。（行番号はIntegerの範囲外があり得るので変数の時はLong型にしがちのため、定数も型を合わせたい）

このような普通にConstを使う方法も悪くない方法だと思うのですが、より保守性を良くするならenumを使用するべきです。

enumを使用して記載すると以下のようになります。

Private Const START_ROW As Long = 2

Private Enum Columns
    Item = 2
    Price
End Enum

同じ種類である列の定義番号をColumnsというenumにまとめました。このようにするメリットとして以下の２点があります。

• Constを２つ記載するより可読性が高い（両方カラムについての定数と一目見てわかる）
• 予測変換を使用できる

特に予測変換を使えるとミスが無くなるだけでなく、コーディングのスピードもグンッと上がります。

更に、enumにはこのような書き方も出来ます。

Private Const START_ROW As Long = 2

Private Enum Columns
    Item = 2
    Price
End Enum

enumのItemという列に対しては、2という数字が割り当てられていますが、Priceには割当がありません。

上記のような記載をするとPriceはItemの次ということで、3が自動的に定義されます。

このような書き方をしておくことで、仮にItem列の前に1列挿入された場合でも最低限の修正で済むようになります。

すべての列に番号を定義していると、それぞれを1ずつ加算しなければいけないため、連続した列番号を定義する場合は、この方法を使うとベターです。

定数クラスを使う

定数の定義方法をクラス化してしまうという方法です。

この方法は面倒ですが、堅く書くことが出来るので、大規模なアプリケーションであれば効果を発揮すると思います。

enumと同様に以下の表の行や列を定数化する例を考えます。

定数クラスを使う方法だとこんな感じ

クラスの定義

Private Const START_ROW As Long = 2
Private Const ITEM_COLUMN As Integer = 2
Private Const PRICE_COLUMN As Integer = 3

Public Function getStartRow() As Long
    getStartRow = START_ROW
End Function

Public Function getItemColumn() As Long
    getItemColumn = ITEM_COLUMN
End Function

Public Function getPriceColumn() As Long
    getPriceColumn = PRICE_COLUMN
End Function

クラスの呼び出し

Sub test()
    Dim cls As clsConst
    
    Set cls = New clsConst
    MsgBox (cls.getStartRow)
    
End Sub

私は上記のようにクラスに定義してカプセル化しています。（静的に使う方法はあるらしいのですが、わからないので…）

すごく面倒な記述だと思いますが以下のようなメリットがあります。

• 予測変換が使える
• シートごとにクラス化すれば大規模なアプリで管理しやすくなる
• コードを使い回しやすい

予測変換が使えるというのはenumと同様です。

しかし圧倒的にコードが多いので保守が大変になります。

更に言うと定数は元から変更ができないので、「値を保証する」というカプセル化のメリットが殆ど無いです。

ではクラスを使うメリットはあるのでしょうか？私は、使いまわしと管理のしやすさだと思っています。

2つのワークシートを参照するプログラムを作る時に、enumを使用していると…

Private Const SHEET1_START_ROW As Long = 2
Private Enum Sheet1_Columns
    Item = 2
    Price
End Enum

Private Const SHEET2_START_ROW As Long = 3
Private Enum Sheet2_Columns
    Item = 4
    Price
End Enum

全く同じレイアウトでない限り上記のように2つ作成し、それぞれ変数名を変更しながらプログラムを書いていくのが面倒くさい。

一方でクラスで書くと…

clsSheet1

Private Const START_ROW As Long = 2
Private Const ITEM_COLUMN As Integer = 2
Private Const PRICE_COLUMN As Integer = 3

Public Function getStartRow() As Long
    getStartRow = START_ROW
End Function

Public Function getItemColumn() As Long
    getItemColumn = ITEM_COLUMN
End Function

Public Function getPriceColumn() As Long
    getPriceColumn = PRICE_COLUMN
End Function

clsSheet2

Private Const START_ROW As Long = 3
Private Const ITEM_COLUMN As Integer = 4
Private Const PRICE_COLUMN As Integer = 5

Public Function getStartRow() As Long
    getStartRow = START_ROW
End Function

Public Function getItemColumn() As Long
    getItemColumn = ITEM_COLUMN
End Function

Public Function getPriceColumn() As Long
    getPriceColumn = PRICE_COLUMN
End Function

呼び出し側のプログラムからすると、クラス名が違うだけでメソッド名は同じであるため汎用的な関数を作りやすい。

更にクラスにすると継承なども出来るため、ワークシートの定数定義クラスを用意しておいて、そのクラスを継承して作成するなどすれば、更にアプリケーションが管理しやすくなる。

その他に、クラス開始時に連想配列に入れるなどして、コード値と名称などを管理するような動きもできます。

うまく説明できているか微妙なところですが、アプリがでかくなればなるほど力を発揮するのがクラスなので、そのようなアプリを作る機会があれば是非試してもらいたいです。

顧客に渡すツール向けのまとめ

• enumを使用することで、定数がグループ化されたりしてコードが見やすくなる
• クラスを使用することで、大規模アプリケーションの管理がしやすくなる
• コードは長くなりがちになるため、私は簡易ツールでは使わない