エクセルVBAでクラスを使った請求書マクロのシートへの転記処理の作り方

photo credit: Homedust Office – Credit to http://homedust.com/ via photopin (license)

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

エクセル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クラスのインスタンスをコレクションとして取り出す処理を、「請求データ」シートのシートモジュール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

「取引先マスタ」シートとその関連処理

以下が「取引先マスタ」シートです。

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

「取引先マスタ」シートも、DataクラスとwsDataモジュールと同様に、1行分を表すクラスの作成と、それをコレクションとして取り込み処理を作成しました。

以下のクラスモジュールClientと、シートモジュールwsClientです。

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

「請求書」シートとその関連処理

さらに、以下が請求書のひな形となる「請求書」シートです。

このシートのシートモジュールwsTemplateにもコードを記述しております。

Private dayCutoff_ As Date

Public Property Let DayCutoff(ByVal newDayCutoff As Date)
    
    Range("D15").Value = DateSerial(Year(newDayCutoff), Month(newDayCutoff) + 1, 0)
    Range("D16").Value = DateSerial(Year(newDayCutoff), Month(newDayCutoff) + 2, 0)
    
    dayCutoff_ = newDayCutoff
    
End Property

Public Property Get DayCutoff() As Date
    DayCutoff = dayCutoff_
End Property

これは前回作成した処理ですね。

請求書マクロを動作させたときに、InputBoxメソッドで請求書を作成する対象年月をユーザーに入力してもらうのですが、それを格納するプロパティDayCutoffを定義しています。

このプロパティの設定時に、請求書の請求日と支払日も入力しています。

請求書作成マクロ

そして、まだ作成途中ではありますが、メインの請求書作成マクロを記述している標準モジュールがこちらです。

Sub New請求書マクロ()

wsTemplate.DayCutoff = Application.InputBox("年月を入力してください", "対象年月を入力", Format(Date, "yyyy/mm"))

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 MonthEquals(wsTemplate.DayCutoff, d.DeliveryDate) And (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

Public Function MonthEquals(ByVal d1 As Date, ByVal d2 As Date) As Boolean
    MonthEquals = (Year(d1) = Year(d2) And Month(d1) = Month(d2))
End Function

各取引先ごとに転記するデータを抽出してイミディエイトウィンドウに出力するとこまで作成しました。

抽出したデータの転記と請求書作成の流れ

目標としては、抽出したデータをイミディエイトウィンドウではなく、「請求書」シートに転記をする必要がありますね。

それで、取引先ごとにそれを行って、それぞれ別のエクセルファイルとして保存したいわけです。

手順としては以下の手順になります。

取引先ごとに繰り返す
1. 「請求データ」から対象のデータを抽出する
2. 「請求書」シートをクリアする
3. 「請求書」シートに抽出したデータを書き込む
4. 「請求書」シートを新規ブックにコピーして別名で保存して閉じる

前回は1の部分を作りました。

今回は3の部分の処理を作っていきますよ。

抽出したデータを「請求書」シートに転記する

まずは、取引先ごとに抽出したデータを「請求書」シートに転記していきたいと思います。

せっかく今回はシートモジュールを大活用しているので、そのデータもシートモジュールのプロシージャに渡して処理してあげたいですよね。

では、そのデータをどういう形で渡すのが良いでしょうか。

データの集合をシートモジュールに渡す

「請求データ」の１行分のデータが、複数ある集合になりますよね…

集合なら、配列か、辞書か、コレクション…

…！

ということは、Dataクラスのコレクションで渡せば良さそうですね。

targetDataというCollection型のオブジェクト変数を用意して、そこにAddしていって渡すようにしましょう。

標準モジュールMainのSubプロシージャNew請求書マクロを以下のように変更しました。

Sub New請求書マクロ()

wsTemplate.DayCutoff = Application.InputBox("年月を入力してください", "対象年月を入力", Format(Date, "yyyy/mm"))

wsClient.Store
wsData.Store

Dim c As Client, d As Data
For Each c In wsClient.Clients

    Dim targetData As Collection: Set targetData = New Collection 'ひな形に貼り付けるデータのコレクション
    For Each d In wsData.Data
        If MonthEquals(wsTemplate.DayCutoff, d.DeliveryDate) And (c.Name = d.ClientName) Then
            targetData.Add d
        End If
    Next d
    wsTemplate.WriteData targetData, c
Next c

End Sub

targetDataにすべてのDataオブジェクトが追加できたら、wsTemplateのWriteDataというメソッドを呼び出します。

あと、ついでにオブジェクト変数c、これは現在対象となっている取引先のデータを表すClientオブジェクトですが、これも渡しておきます。

後々、便利ですので。

受け取ったコレクションからシートに書き込む

一方で、受け取ったコレクションtargetDataとClientオブジェクトを使って、シートに転記するのが、以下のwsTemplateのWriteDataメソッドです。

Public Sub WriteData(ByVal targetData As Collection, ByVal myClient As Object)
    
    Dim i As Long: i = 21
    Dim d As Object
    For Each d In targetData
        Range(Cells(i, 1), Cells(i, 3)) = Array(d.ItemName, d.Price, d.Quantity)
        i = i + 1
    Next d
        
    Rows(i & ":50").Hidden = True  'データがない行を隠す
        
    With myClient
        ClientName = .Name
        Range("A3").Value = .Name & "御中"
        Range("A5").Value = "〒" & .PostalNumber
        Range("A6").Value = .Address1
        Range("A7").Value = .Address2
    End With
    
    Stop
    
End Sub

受け取ったコレクションtargetDataについてループをしてその要素の数だけ、21行目から順番に、品目、価格、数量を書き込んでいきます。

終わったら、Hiddenプロパティを使って、余計な行を隠します。

また、ついでにA3セルからA7セルに取引先情報を書き込みます。

このために、Clientオブジェクトを引数で渡していたんですね。

また、これをいったんClientNameプロパティに確保しておきます。また後で使いますので。