エクセルVBAでクラスを使った請求書マクロでシートのコピー＆保存処理を作る

photo credit: Phototravelography Reading enlightens via photopin (license)

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

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

前回の記事はこちら。

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

エクセルVBAでクラスを使って請求書マクロを作ろう！ということでシリーズをお送りしています。今回は、エクセルVBAでクラスとコレクションを使った請求書マクロのシートへの転記処理の作り方です。

請求書マクロのシートへの転記処理の部分を進めました。

さて、いよいよクライマックスです。

続いて、シートの書き込みをする前にクリアをする処理と、書き込み後に別ブックに保存して閉じる処理を追加していきます。

ということで、今回はエクセルVBAでクラスを使った請求書マクロでシートのコピー＆保存処理を作成していき、請求書マクロを完成させます。

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

前回のおさらい
「請求書」シートのクリア
「請求書」シートをコピーして別名で保存
1. シートを新規ブックにコピーする
2. アラートダイアログを非表示にする
請求書マクロの実行結果
まとめ
連載目次：エクセル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

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

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 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

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 ClientName As String

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

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
    
End Sub

Private dayCutoff_ As Date

Public ClientName As String

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

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

End Sub

DayCutoffプロパティは、請求対象となる年月を表すプロパティで、ユーザーからの入力を受けてこのプロパティに値を設定する際に、「請求書」シートの「請求日」「お支払期限」もついでに入力するという処理を入れています。

WriteDataメソッドは、「請求データ」から抽出したデータや、取引先情報を「請求書」シートの該当箇所に書き込むメソッドです。

このようにシートに対して書き込む処理をシートモジュールに寄せておくと、標準モジュールがスッキリしてきます。

標準モジュールの請求書作成マクロ

それで、その標準モジュールMainがこちらです。

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

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

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

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. 「請求書」シートを新規ブックにコピーして別名で保存して閉じる

前回までで、取引先ごとに抽出したデータをコレクションtargetDataに追加して、それをwsTemplateモジュールのWriteDataメソッドに渡す部分、つまり手順の1と3を作成しました。

今回は、残りの2と4を作成していきます。

「請求書」シートのクリア

とある取引先のデータを「請求書」シートに書き込む際に、前回書き込んだ内容が残ってしまうことがあります。

なので、書き込みのWriteDataメソッドを呼び出す前に、「請求書」シートをクリアする必要があります。

これも「請求書」シート固有の処理なので、wsTemplateに追加していきます。以下ClearDataメソッドです。

Public Sub ClearData()
    
    Range("A3:A7").ClearContents
    Range("A21:C50").ClearContents
    
    Rows.Hidden = False

End Sub

Public Sub ClearData()

Range("A3:A7").ClearContents

Range("A21:C50").ClearContents

Rows.Hidden = False

End Sub

行の非表示も行っていたので、これも解除しておきますね。

「請求書」シートをコピーして別名で保存

「請求書」シートをClearDataメソッドでクリアして、WriteDataメソッドで書き込みをしたら、そのシートを新規ブックにコピーして、別名で保存して、閉じるという手順を行います。

これで、取引先ごとの請求書を作成できますね。

その処理を行うのが以下のSaveAsNewBookメソッドです。これもシートモジュールwsTemplateに記述しています。

Public Sub SaveAsNewBook()
    Dim wb As Workbook
    Copy
    Set wb = ActiveWorkbook
    
    Dim fileName As String
    fileName = ThisWorkbook.Path & "\" & Format(dayCutoff_, "yyyymm") & "請求書_" & ClientName & ".xlsx"
    
    Application.DisplayAlerts = False
    wb.SaveAs fileName
    wb.Close
    Application.DisplayAlerts = True
End Sub

Public Sub SaveAsNewBook()

Dim wb As Workbook

Copy

Set wb = ActiveWorkbook

Dim fileName As String

fileName = ThisWorkbook.Path & "\" & Format(dayCutoff_, "yyyymm") & "請求書_" & ClientName & ".xlsx"

Application.DisplayAlerts = False

wb.SaveAs fileName

wb.Close

Application.DisplayAlerts = True

End Sub

シートを新規ブックにコピーする

このメソッドで１つ目のポイントが、3行目のCopyメソッドですね。

WorksheetオブジェクトのCopyメソッドは、対象のシートを新しいブックにコピーするというもので、本来は以下の構文なのです。

Worksheetオブジェクト.Copy

ですが、ここではシートモジュールに書いているので自身のオブジェクトが対象であれば省略して単純に「Copy」と書けます。

そして、新しく作成されたブックはActiveになりますので、すかさず4行目でオブジェクト変数wbにセットさせてもらっています。

アラートダイアログを非表示にする

もう１つのポイントが、9行目と12行目。

ApplicationオブジェクトのDisplayAlertsプロパティです。

これはアラートダイアログを表示するかどうかを指定するプロパティです。

以下の構文でTrueにすると表示、Falseにすると非表示にします。

Application.DisplayAlerts = ブール値

アラートダイアログをオンにしたままSaveAsNewBookメソッドを実行すると、以下のような「次の機能はマクロなしのブックに保存できません」というダイアログが都度表示されて、「はい」を押さないといけなくなります。

面倒っす。

これが表示されてしまう理由ですが、Copyメソッドでシートをコピーするのですが、このときシートモジュールに書き込まれているコードもコピーされるんですね。

コードも含まれているので、Closeメソッドでファイルを閉じるときに「マクロなしのブックには保存できませんけど、いいですね？」と問われるようになってしまうということです。

これを表示させないように、DisplayAlertsプロパティをいったんFalseに設定しているのです。

請求書マクロの実行結果

では、これらのメソッドを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
    
    With wsTemplate
        .ClearData
        .WriteData targetData, c
        .SaveAsNewBook
    End With
    
Next c

End Sub

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

With wsTemplate

.ClearData

.WriteData targetData, c

.SaveAsNewBook

End With

Next c

End Sub

標準モジュールのほうには、2つのメソッドを追加するだけですね…スマート！

実行すると、マクロファイルと同じフォルダに以下のようにそれぞれのファイルが作成されます。

「ABC株式会社」のほうを開くと…

問題なさそうですね。

もう一社のほう、前回は他のデータが残っていたんですよね。

開いて、行の非表示を解除してみると…

前の実行で書き込まれたデータもちゃんとクリアされていますね。

まとめ

以上、エクセルVBAでクラスを使った請求書マクロでシートのコピー＆保存処理を作る方法についてお伝えしました。

以上で、クラスを使った請求書マクロは完成です。

クラスモジュールというよりは、シートモジュールが大活躍でしたね…汗

ただ、オブジェクトやシート・ブックに固有の処理は、それぞれのモジュールに独立させて書いていくことで、標準モジュールがごちゃつかずに済むようになります。

皆さんも、ぜひお手元のマクロでチャレンジをしてみてくださいね！

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

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

　　投稿者プロフィール

タカハシノリアキ株式会社プランノーツ　代表取締役

株式会社プランノーツ代表、コミュニティ「ノンプロ研」主宰。1976年こどもの日生まれ。東京板橋区在住。「ITで日本の『働く』の価値を上げる！」をテーマに、VBA＆GASの開発、講師、執筆などをしております。→詳しいプロフィールはコチラ
★ご依頼・ご相談はお気軽にどうぞ！→お問い合わせはコチラ
★フォロー頂ければ嬉しいです。