エクセルVBAでクラスとコレクションを使うと簡単に請求書のデータ抽出できる

photo credit: trpnblies7 Homemade Extracts (Day 1) via photopin (license)

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

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

前回の記事はこちらです。

エクセルVBAでシートモジュールに請求書マクロのデータをコレクション化する処理を書く

エクセルVBAでクラスを使って請求書マクロを作るシリーズをお送りしています。今回は、エクセルVBAで請求書マクロのシートのデータをシートモジュール上のインスタンスのコレクションとして格納する方法です。

各シートのデータを、そのシートモジュールが持つプロパティにコレクションとして格納するところまでをお伝えしました。

今回はその続きで、請求データの中から該当の取引先のデータだけを抽出する方法です。

これは、クラスとコレクションを使うと、実にスマートに実現できるんです。

ということで、エクセルVBAでクラスとコレクションを使って簡単に請求書のデータ抽出をする方法です。

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

前回のおさらい
請求書を作るまでの処理の流れ
取引先のループにコレクションが有効
該当の取引先の請求データを抽出する
まとめ
連載目次：エクセルVBAでクラスを使って請求書マクロを作る

前回のおさらい

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

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

請求書マクロは３つのシートから成り立っていまして、取引先別の請求書を作成するためのもととなるデータが蓄積されているのが、以下の「請求データ」シートです。

この「請求データ」の１行分を表すDataクラスというのを定義していまして、それがこちら。

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

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

その１行分のデータを表すDataクラスのインスタンス、つまりDataオブジェクトをコレクション化することで、データ全体を表現できます。

それを「請求データ」シートのシートモジュールwsDataに持ってしまおう、ということで作ったのが以下のDataプロパティと、Storeメソッドです。

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

「取引先マスタ」シートとそのコレクション化の処理

続いて、「取引先マスタ」シートです。

ここに記載されている、取引先の分だけ請求書を作ります。

これも「請求データ」シートと同じノリで、１行分のデータを表すClientクラスと、シートモジュールにコレクション化する処理を作成しました。

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

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

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

ひな形となる「請求書」シート

もうひとつが「請求書」シートで、これをひな形として請求書を作ります。

クラスとコレクション化の動作確認用マクロ

以上までの動作確認をするために前回作成したのが以下の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

請求書を作るまでの処理の流れ

請求書を作る処理の流れを確認しておきましょう。

まず、取引先の数だけ請求書を作る必要がありますので、「取引先の数だけループする」という処理が必要ですね。

その上で、「請求データ」からその該当の取引先のデータを抽出して、それぞれの取引先用の請求書に転記したいわけです。

あ、あと年月も重要ですね。

今の「請求データ」には2019年1月のデータしか入っていませんが、月ごとに締めたいので、Mainを実行時に年月を入力して、その年月のデータかどうかという判定も必要です。

つまり、以下のような流れを作りたいです。

実行時にユーザーに年月を入力してもらう
取引先の数だけループする
- 請求データの数だけループする
  - 請求データの年月が入力した年月と等しく、かつ、該当の取引先のデータであれば
    - そのデータは転記対象となる

てなわけですね。

ややこしそうですか？

取引先のループにコレクションが有効

まず、取引先の分だけループするというところを作ってみましょう。

クラスを使わないマクロであれば、i=2から最終行まで…みたいなループのしかたをさせていたのですが、今回はデータをコレクション化しているものですから、もっとクールにいけます。

コレクションに対してFor Each～Next文を使ってループできます。

それで、取引先のコレクションはどこにあるかというと、シートモジュールwsClientのClientsプロパティに確保したのでした。

それでループのたびにClientsプロパティから取り出す要素は、Clientオブジェクトですから、Clientクラスで定義した各プロパティを使って、取引先のデータにアクセスできます。

さらに、打っていただくとわかりますが、Clientオブジェクトのメンバーである各プロパティは、自動メンバー表示の候補に上がるので、コーディングもさくさく進みます。

以下のような処理を作成して、実行すると…

Sub New請求書マクロ()

wsClient.Store
Dim c As Client
For Each c In wsClient.Clients
    Debug.Print c.Name, c.PostalNumber, c.Address1, c.Address2
Next c

End Sub

ほら、取引先についてループして、各データを取り出せましたね。

該当の取引先の請求データを抽出する

続いて、前述の取引先のループの中に、請求データのループを入れ子にしていきます。

それで、ループの対象となっている取引先と、請求データの取引先が等しければ、そのデータを抽出するのです。

請求データのほうもwsDataのDataプロパティにコレクションが格納されているので、それでループを回せますね。

かつ、その要素はDataオブジェクトですから、定義したプロパティで好きにデータを取り出すことができます。例えば、取引先名であれば、「Dataオブジェクト.ClientName」です。

それが、「c.Name」と等しければヒット！データを抽出すればよいわけです。

それを実現したのが、以下のコードですね。

Sub New請求書マクロ()

wsClient.Store
wsData.Store

Dim c As Client, d As Data
For Each c In wsClient.Clients
    
    Debug.Print "■", c.Name
    For Each d In wsData.Data
        If c.Name = d.ClientName Then
            Debug.Print d.ClientName, d.DeliveryDate, d.ItemName, d.Price, d.Quantity
        End If
    
    Next d
Next c

End Sub

実行すると…