photo credit: RÉTRO BUS RENNAIS Collection Orain (03) via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
エクセルVBAでクラスを使って請求書マクロを作るシリーズをお送りしています。
前回の記事はこちら。
シリーズ最初の記事でしたが、作成するマクロの概要とクラスの定義までを進めました。
今回は作成したクラスのインスタンスをコレクション化して、シートのデータを格納するまでを進めていきます。
エクセル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のクラスモジュール」のシリーズでは、コレクション用のクラスを別途作ってたんですね。
しかし、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を初期化
- 「取引先マスタ」の各行について
- Clientクラスのインスタンスを生成
- ClientのインスタンスのInitメソッドにRangeオブジェクトを渡して初期データを投入
- コレクション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
なんとなく似たような処理なので共通化したい気もするのですが…難しい気もしますし、そこまで効果的に使い回せるのか、ちょっと疑問があるのでひとまずここで着地させときます。
まとめ
以上、エクセルVBAで請求書マクロのシートデータをインスタンスのコレクションとして格納する方法をお伝えしました。
コレクションの置き場として、シートモジュールを使ってみました。
シートからのデータの読み取りなので、わりとこの方法も良さそうな気がします。
では、次回は生成したコレクションを使って取引先ごとの請求データを取り出す処理を作っていきます。
どうぞお楽しみに！
