photo credit: trpnblies7 Homemade Extracts (Day 1) 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
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
実行すると…
いい感じに抽出できましたね！
まとめ
以上、エクセルVBAでクラスとコレクションを使って簡単に請求書のデータ抽出をする方法についてお伝えしました。
前回までに定義したクラスとシートモジュールのコレクションがかなり有効ですね。
簡単にデータを取り出せるようになりました。
さて、次回は年月を比較するなど、日付関連のところを進めていきます。
どうぞお楽しみに！
