エクセルVBAでクラスを使った請求書でマクロの日付関連の処理を作る

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

エクセルVBAでクラスを使って請求書マクロを作ろう！のシリーズです。

前回の記事はこちら。

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

エクセルVBAでクラスを使って請求書マクロを作るシリーズをお送りしています。今回は、請求データの中から該当の取引先のデータだけを抽出する方法です。これは、クラスとコレクションを使うと、実にスマートに実現できるんです。

tonari-it.com

2019.02.02

クラスとコレクションで取り込んだ各データから、請求書に転記するデータを抽出する方法をお伝えしました。

今回は、日付について見ていきます。

具体的には、ユーザーからの対象の年月の入力をもとに、月末日、翌月末日を求める処理、対象の年月のデータだけを抽出するようにする判定処理です。

ということで、エクセル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

この「請求データ」をそれぞれ、該当の取引先ごとに、かつ該当月の分だけ抽出して請求書のひな形に転記していきたいのです。

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

その取引先のデータを記載しているのが、以下の「取引先マスタ」シートです。

「請求データ」シートと同様に、１行分のデータを表すクラスの定義をクラスモジュール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

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

もうひとつのシート、「請求書」シートは請求書のひな形となるものです。

コレクションからのデータの抽出

「請求データ」シートと「取引先マスタ」シートのデータを、オブジェクトのコレクションとしているので、そこからデータの抽出をすることになります。

その部分を前回作成したわけですが、それが以下の標準モジュールMainです。

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

今回のお題：該当の年月のデータのみを抽出したい

データの抽出条件として、もう一つ「日付」がありました。

つまり、以下のような処理を追加していきたいのです。

• ユーザーから入力ダイアログで請求書を作成する年月を入力してもらう
• 「請求データ」の１列目「納品日」が、入力した年月と等しいデータのみを対象とする

この部分を今回作成していきます。

対象月の年月を入力したときの処理

さて、請求書作成マクロを実行したらInputBoxメソッドで対象月の年月を入力してもらうのですが、普通はそのデータをDate型の変数に入れますよね？？

ですが、今回はちょっとヒネリを入れて、「請求書」シートのシートモジュールwsTemplateのプロパティに格納したいと思います。

なぜかというと、Property Letプロシージャでプロパティに格納するようにすれば、ただ格納するだけでなく、「何らかの処理をしながら」プロパティに格納できるんです。

「請求書」シートのD15セルとD16セルをご覧ください。

「請求日」と、「お支払期限」がありますよね？

これは、対象の年月を入れれば、その月末、そしてその翌月末というように、その値が一意に決まります。

ですから、その請求日とお支払期限の書き込み処理を、プロパティの設定時点でまとめてやっちゃおうという魂胆です。

シートモジュールのプロパティに設定するプロシージャ

シートモジュール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

プライベート変数dayCutoff_を実際の設定先にして、その出し入れをするために、Property Letプロシージャと、Property Getプロシージャを定義します。

合わせ技で、wsTemplateのDayCutoffプロパティが完成しますね。

Property Letプロシージャがミソで、プロパティ設定時に、請求書の請求日とお支払期限の書き込みも終えてしまおうというものです。

日付入力時の動作を確認する

では、ここまでの動作を確認してみましょう。

標準モジュールMainを以下のようにして実行します。

Sub New請求書マクロ()

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

'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

例えばInputBoxメソッドで「2019/01」と入力すると、wsTemplate.DayCutoffの値「2019/01/01」が出力されます。

さらに、「請求書」シートを覗いてみると…

ちゃんと入力がされていますね。

年月を比較する関数

さて、次はそのユーザーが入力した年月と、各請求データの年月が等しいかどうか、というのを判定する処理が必要です。

２つの日付の年をYear関数で、月をMonth関数で取り出してそれぞれ比較して両方とも等しければOKなのですが、標準コードにじゃんじゃん書いていくと邪魔くさそうです…

ということで、以下のように２つの日付の年月を比較する関数MonthEqualsを作成しました。

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

すると、データの抽出部分に日付の判定も加えると以下のようになります。

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

実行すると、以下のようにイミディエイトウィンドウにデータのリストが出力されます。

まとめ

以上、エクセルVBAでクラスを使った請求書マクロの日付関連の処理の作り方についてお伝えしました。

ここは紹介した以外に、普通に変数で日付を持って処理する方法もありますし、日付をクラスとして扱う方法も考えられるかも知れません。

色々と試してベストな方法を見つけてみてください。

次回は、該当データを請求書に転記していく部分の処理を作っていきます。

どうぞお楽しみに！

連載目次：エクセルVBAでクラスを使って請求書マクロを作る

エクセルVBAの題材として実務でもよく利用される「請求書マクロ」。ある程度スキルを磨いたら「これをクラスを使って実装したら？」という疑問が出てくるもの…このシリーズで実際に作ってみましょう。

1. エクセルVBAでクラスを使った請求書マクロの概要と使用するクラスの定義
2. エクセルVBAでシートモジュールに請求書マクロのデータをコレクション化する処理を書く
3. エクセルVBAでクラスとコレクションを使うと簡単に請求書のデータ抽出できる
4. エクセルVBAでクラスを使った請求書でクロの日付関連の処理を作る
5. エクセルVBAでクラスを使った請求書マクロでシートのコピー＆保存処理を作る