🦒

Excel VBAをクラスを使って改善しよう：定数や関数をオブジェクトとして定義する（As New の有効的な利用方法）

2023/11/30に公開

Excel

動機

定数多すぎて名前に困る問題

Excel VBAではプロジェクト全体で利用する定数を定義する際、CONSTを使って定義するのが一般的です。

Public Const BEGIN_CELL_ADDRESS = "A10"

これで問題ない場合がほとんどですが、例えばたくさんのアドレスを管理したくなった場合、名前が重複しないよう、定数名に悩む場合があります。

Public Const SHEET1_BEGIN_CELL_ADDRESS = "A10"
Public Const SHEET1_END_CELL_ADDRESS = "X100"

Public Const SHEET2_BEGIN_CELL_ADDRESS = "B10"
Public Const SHEET2_END_CELL_ADDRESS = "Y100"

Public Const SHEET3_BEGIN_CELL_ADDRESS = "C10"
Public Const SHEET3_END_CELL_ADDRESS = "Z100"

このくらいならいいですが、エラーメッセージなんかは100くらいあったら、ズラッと並んでコメントでも入れないとどういうグルーピングになっているか分からない、みたいなことはありがちです（長くなるので書かないですが）。

似たような関数で命名が長くなりやすい問題

また、オブジェクト生成用の関数を標準モジュールに記述することはよくあります。
これも、生成ルートで関数名を分けて記載しますが、結構長くなりがちです。

Public Function CreateXXX(pName As String) As XXX
    '...
End Function

Public Function CreateXXXViaSheet(pWorksheet As Worksheet) As XXX
    '...
End Function

Public Function CreateXXXViaDatabase(pConnection As ADODB.Connection, pQuery As String) As XXX
    '...
End Function

Public Function CreateYYY() As YYY
    '...
End Function

標準モジュールを分けて記載することである程度の整理が出来ますが、接頭辞として標準モジュール名を指定することは任意であるため、利用時の入力補助ではたくさんのCreate...が並んでしまいます。

状態を持たないオブジェクトも毎度生成しないといけない問題

ユーティリティ的な利用や設定情報を保持するようなオブジェクトを生成する場合、一般的には標準モジュール＋Privateなグローバル変数で定義することが多いかと思います。

これも特に問題とはなりませんが、前述のとおり関数名は標準モジュールが異なっている場合でも一意にする必要があり、同じ名前を使いたければ標準モジュール名で修飾してあげる必要があります。
ただ、標準モジュール名は任意であるため、使うときに戸惑う場合があったり、別の担当者が気が付かない場合もあります。

そこで、オブジェクトを使うことに慣れてきた開発者はそのような便利な関数や設定情報をクラスとして定義することを検討します。

AppSettingクラス

Option Explicit

Public Property Get UserName() As String
    UserName = Environ("USERNAME")
End Property

Public Property Get Driver() As String
    Driver = ThisWorkbook.Worksheets("ODBC_SETTING").Range("A1").Value
End Property

Public Property Get Server() As String
    Server = ThisWorkbook.Worksheets("ODBC_SETTING").Range("A2").Value
End Property

Public Property Get Port() As String
    Port = ThisWorkbook.Worksheets("ODBC_SETTING").Range("A5").Value
End Property

Public Property Get DBName() As String
    DBName = ThisWorkbook.Worksheets("ODBC_SETTING").Range("A3").Value
End Property

Public Property Get Password() As String
    Password = ThisWorkbook.Worksheets("ODBC_SETTING").Range("A4").Value '危ないがサンプルなので
End Property

AppUtilクラス

Option Explicit

Public Function Format年月日() As String
    Format年月日 = Format年月日For(Now)
End Function


Public Function Format年月日For(pDate As Date) As String
    Format年月日For = Format(pDate, "YYYY年MM月DD日")
End Function

Public Function GetODBCString(pSetting As AppSetting) As String
    GetODBCString = "Driver=" & pSetting.Driver & ";UID=" & pSetting.UserName & ";Port=" & pSetting.Port & ";Server=" & pSetting.Server & ";Database=" & pSetting.DBName & ";PWD=" & pSetting.Password
End Function

Public Sub TestODBC()
    Dim vSetting As AppSetting
    Set vSetting = New AppSetting
    
    Dim vUtil As AppUtil
    Set vUtil = New AppUtil
    
    Debug.Print vUtil.GetODBCString(vSetting)
End Sub

ただ、クラスを作るまではよかったのですが、UtilとかSettingというあいまいな名前でクラスを作成してしまったので、ODBC以外の処理もごった煮となっていく未来が見えます。
実際、ODBCとは無関係な年月日のフォーマット関数がUtilに含まれています。

こうなると、実行する際に都度、オブジェクトの初期化コードがあちこちに現れることになるでしょう。

正しく小さい責務でクラス設計すればよかったのですが、面倒になってやっちゃうことはよくあると思います。

課題整理

ということで、こんな状態でもよい解決方法がないかを考えてみたいと思います。
考えるにあたり、以下の課題を解決するものとして検討していきます。

定数をうまく分類したい
もっと分かりやすい名称を変数名や関数名に使いたい
1つのオブジェクトを定義できないか（いわゆるSingletonパターンです）

ソリューション

もったいぶっても仕方ないし、タイトルに既に回答があります。
グローバル変数として As Newを使って初期化したオブジェクトを用意するです。

一般に As New は危ない

多くの方が記事にしているようにオブジェクトの初期化は宣言と分離するほうが好ましいです。
例えばここに書いてあるようなことです。

そもそも上記記事の内容が理解できない状態では、ここで挙げたソリューションも利用すべきではありません。

私は基本的に1行で宣言と初期化を行うことはしません。

ただし、状態が変化しないなら問題にはならない

ただ、状態が変化しないオブジェクトであれば、問題にはなりません。
状態が変化しないとはどういうことかというと、以下のような場合です。

初期化したら内部のメンバー変数に変化がない、または内部で利用時に正しく初期化される
設定用のプロパティやメンバー変数を変更する手段がない
そもそも変更できるメンバー変数がない、メソッドのみの場合

今回の課題はまさにこれらに該当します。

定数の分類

定数は各クラスを定義して分けてしまいます。前述の内容ではSheet1～3で使用するアドレスを管理するクラスと考えてみます。

Sheet1RangeManagerクラス

Option Explicit

Public Property Get BeginAddress()
    BeginAddress = "A1"
End Property

Public Property Get EndAddress()
    EndAddress = "X100"
End Property

Sheet2、Sheet3も同様です。

次に標準モジュールにグローバル変数として定義します。

Public gSheet1RangeManager As New Sheet1RangeManager

これでいつでも使えるようになります。
イミディエイトウィンドウで使ってみます。

?gSheet1RangeManager.BeginAddress
A1
?gSheet1RangeManager.EndAddress
X100

正しく値が取得できていることが分かります。

分かりやすい名称を使う

オブジェクトとして利用するため、必ず接頭辞を付ける必要があります。
また、オブジェクトの変数名で分類ができるようになるため、

メソッド名を短くしたり
共通した名前を付与したり
分かりやすい名前を自由に設定できる

といったメリットがあり、視認性が向上します。

XXX生成用ファンクションも以下のように修正できます。メソッド名にXXXが無くなってすっきりした感じになります。

XXXFactoryクラス

Option Explicit

Public Function Create(pName As String) As XXX
    '...
End Function

Public Function CreateViaSheet(pWorksheet As Worksheet) As XXX
    '...
End Function

Public Function CreateViaDatabase(pConnection As ADODB.Connection, pQuery As String) As XXX
    '...
End Function

Public gXXXFactory As New XXXFactory

Public Sub TestCreate()
    Dim vXXX As XXX
    Set vXXX = gXXXFactory.Create("hoge")
    '・・・
End Sub

Singletonパターンを使う

グローバル変数として As Newで宣言した変数は、Nothing代入をしても、次の利用でオブジェクトが生成されます。

ここでは状態変化がないオブジェクトのみを扱っているため、何度生成されても同じ内容が参照できます。
その意味ではSingletonというよりはMonostateパターンのほうがしっくりくるかもしれません。
自分でNewすることも出来ますので。

先ほどのAppSettingクラスやAppUtilクラスも、グローバル変数としてしまえば、様々な場面で使用されても違和感がなくなります。

Public gUtil As New AppUtil
Public gSetting As New AppSetting

改良版

Public Sub TestODBC()
    Debug.Print gUtil.GetODBCString(gSetting)
End Sub

非常にコードがコンパクトになります。

タイプセーフEnumでも使えます

また、以前に書いたタイプセーフEnumでも適用することが出来ます。

グローバル変数として使えば、通常のEnumのような使い方が出来ます。

今後もマニアックな情報を届けます

誰も使わないクラスで面白いことを考え続けています。
また思いついたら投稿したいと思いますので、よろしくお願いいたします。