エクセルVBAで請求書マクロのシートデータをインスタンスのコレクションとして格納する方法

photo credit: RÉTRO BUS RENNAIS Collection Orain (03) via photopin (license)

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

エクセルVBAでクラスを使って請求書マクロを作るシリーズをお送りしています。

前回の記事はこちら。

エクセルVBAでクラスを使った請求書マクロの概要と使用するクラスの定義

新シリーズとして「エクセルVBAでクラスを使って請求書マクロを作る」を開始します。今回は、エクセルVBAでクラスを使った請求書マクロの概要と使用する２つのクラスの定義ということでお送りします。

シリーズ最初の記事でしたが、作成するマクロの概要とクラスの定義までを進めました。

今回は作成したクラスのインスタンスをコレクション化して、シートのデータを格納するまでを進めていきます。

エクセルVBAで請求書マクロのシートデータをインスタンスのコレクションとして格納する方法です。

では、行ってみましょう！

前回のおさらい
Collectionオブジェクトをどこに持つか
シートモジュールにデータをコレクション化して持つ
「請求データ」シートのコレクション化
まとめ
連載目次：エクセルVBAでクラスを使って請求書マクロを作る

前回のおさらい

では、前回のおさらいから。

目標とするマクロとしては、以下の「請求データ」に蓄積されたデータを

これは、各社へ請求すべき元データを品目別にまとめているシートになります。

この「取引先マスタ」にある取引先の分だけ

この「請求書」シートをひな形として請求書を作りたい、というものです。

それで、まずは「請求データ」シートの１行分のデータを表すクラスDataと

Public DeliveryDate As Date
Public ClientName As String
Public ItemName As String
Public Price As Long
Public Quantity As Long

「取引先マスタ」シートの１行分のデータを表すClientクラスを作成しました。

Public Name As String
Public PostalNumber As String
Public Address1 As String
Public Address2 As String

Collectionオブジェクトをどこに持つか

Dataクラスのインスタンスは「請求データ」シートの１行分のデータを格納できますから、各行をインスタンスに放り込んだものをコレクションに追加していけば、表全体のデータを格納できます。

Clientクラスについても同様の考え方で、表全体のデータを取り扱えます。

それで、Collectionオブジェクトをどこに持つかなのですが、「初心者でもわかるエクセルVBAのクラスモジュール」のシリーズでは、コレクション用のクラスを別途作ってたんですね。

エクセルVBAでクラスモジュールを使って独自のコレクションを作る方法

「初心者でもわかるエクセルVBAのクラスモジュール」ということでシリーズでお伝えしております。今回は、エクセルVBAでクラスモジュールを使って独自のコレクションを作る方法をお伝えします。

しかし、Clientsとか、Datas（というかそもそも単語がおかしい）とかのクラスをさらに追加…ってちょっとモジュールが増えすぎて邪魔くさい感じがします。

シートモジュールにデータをコレクション化して持つ

それで、各データはもともとはシートにあるんだから、そしたらシートオブジェクトにCollectionオブジェクト持てばいいんじゃね？

というアイデアを思いつきました。

というのも、シートモジュールは、既にインスタンス化されているという点を除くと、クラスモジュールに近いイメージで使用できます。

つまり、そのシートオブジェクトに対して以下のようなことが実現できます。

プロパティやメソッドを追加できる
イベントプロシージャを追加できる

例えば、シートから取得したデータ群をコレクションとして格納して、プロパティとして持って置けば、他のモジュールからアクセスすることができますよね。

シートモジュールのコード

それでは、まず簡単そうな「取引先マスタ」のほうから。

「取引先マスタ」シートのシートオブジェクト名をwsClientに変更して、そのシートモジュールwsClientに以下のようなコードを作りました。

Public Clients As Collection

Public Sub Store()
    Set Clients = New Collection
    
    Dim i As Long: i = 2
    Do While Cells(i, 1) <> ""
        Dim c As Client: Set c = New Client
        c.Init Range(Cells(i, 1), Cells(i, 4))
        Clients.Add c, c.Name
        i = i + 1
    Loop

End Sub

パブリック変数ClientsをCollection型で宣言します。

これが、Clientクラスのインスタンス、つまりClientオブジェクトを追加していくコレクションになります。

パブリック変数なので、そのまま読み書き可能なプロパティとして外部からアクセスできます。

SubプロシージャStoreは、シートのデータを取り込むためのメソッドで、以下のような処理を行います。

コレクションClientsを初期化
「取引先マスタ」の各行について
1. Clientクラスのインスタンスを生成
2. ClientのインスタンスのInitメソッドにRangeオブジェクトを渡して初期データを投入
3. コレクションClientsにClientのインスタンスを追加

このStoreメソッドをマクロの最初に実行して、シートのデータをオブジェクトのコレクションとして持たせちゃおうという作戦です。

インスタンスに初期データを投入するInitメソッド

それで、上記wsClientのStoreメソッド内で使用していた、Clientのインスタンスに初期データを投入するInitメソッドを定義していませんでしたね…

以下のメソッドをクラスモジュールClientに追加します。

Public Sub Init(ByVal values As Range)
    Name = values(1).Value
    PostalNumber = values(2).Value
    Address1 = values(3).Value
    Address2 = values(4).Value
End Sub

Rangeオブジェクトを受け取って、1番目のセルから順番に各プロパティに格納していきます。

データをコレクション化の動作確認

では、標準モジュールに動作確認用のプロシージャを作って確認しましょう。

ちなみに、標準モジュールのモジュール名を「Main」に変更しています。

Sub New請求書マクロ()

wsClient.Store
Dim myClients As Collection: Set myClients = wsClient.Clients
Stop

End Sub

実行してStop時にローカルウィンドウを確認すると、以下のようにデータが取得できていることを確認できます。

「請求データ」シートのコレクション化

「請求データ」シートも全く同様の流れで作っていきます。

まず、「請求データ」シートのシートモジュールのモジュール名をwsDataとして、そこに書くコードはこちら。

Public Data As Collection

Public Sub Store()
    Set Data = New Collection
    
    Dim i As Long: i = 2
    Do While Cells(i, 1) <> ""
        Dim d As Data: Set d = New Data
        d.Init Range(Cells(i, 1), Cells(i, 5))
        Data.Add d
        i = i + 1
    Loop

End Sub

こちらもインスタンスのデータ初期化用のInitメソッドが必要なので、同じノリでクラスモジュールDataに追加。

Public Sub Init(ByVal values As Range)
    DeliveryDate = values(1).Value
    ClientName = values(2).Value
    ItemName = values(3).Value
    Price = values(4).Value
    Quantity = values(5).Value
End Sub

標準モジュールMainを以下のようにコード追加して、動作確認します。

Sub New請求書マクロ()

wsClient.Store
Dim myClients As Collection: Set myClients = wsClient.Clients

wsData.Store
Dim myData As Collection: Set myData = wsData.Data
Stop

End Sub