
photo credit: Phototravelography Reading enlightens via photopin (license)
みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。
エクセルVBAでクラスを使って請求書マクロを作ろう!のシリーズをお送りしております。
前回の記事はこちら。

請求書マクロのシートへの転記処理の部分を進めました。
さて、いよいよクライマックスです。
続いて、シートの書き込みをする前にクリアをする処理と、書き込み後に別ブックに保存して閉じる処理を追加していきます。
ということで、今回はエクセルVBAでクラスを使った請求書マクロでシートのコピー&保存処理を作成していき、請求書マクロを完成させます。
では、行ってみましょう!
前回のおさらい
では、まずは前回までのおさらいから。
「請求データ」シート
請求書マクロの「請求データ」シートは、作成する請求書の元となるデータを蓄積するシートです。
これらのデータの1行分を表すクラスとして、Dataクラスを作成しています。
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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に作成しました。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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になります。
1 2 3 4 5 6 7 8 9 10 11 |
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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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に仕込んでいるコードが以下の通りです。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
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がこちらです。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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 |
請求書マクロの完成フローは以下の通りです。
- 取引先ごとに繰り返す
- 「請求データ」から対象のデータを抽出する
- 「請求書」シートをクリアする
- 「請求書」シートに抽出したデータを書き込む
- 「請求書」シートを新規ブックにコピーして別名で保存して閉じる
前回までで、取引先ごとに抽出したデータをコレクションtargetDataに追加して、それをwsTemplateモジュールのWriteDataメソッドに渡す部分、つまり手順の1と3を作成しました。
今回は、残りの2と4を作成していきます。
「請求書」シートのクリア
とある取引先のデータを「請求書」シートに書き込む際に、前回書き込んだ内容が残ってしまうことがあります。
なので、書き込みのWriteDataメソッドを呼び出す前に、「請求書」シートをクリアする必要があります。
これも「請求書」シート固有の処理なので、wsTemplateに追加していきます。以下ClearDataメソッドです。
1 2 3 4 5 6 7 8 |
Public Sub ClearData() Range("A3:A7").ClearContents Range("A21:C50").ClearContents Rows.Hidden = False End Sub |
行の非表示も行っていたので、これも解除しておきますね。
「請求書」シートをコピーして別名で保存
「請求書」シートをClearDataメソッドでクリアして、WriteDataメソッドで書き込みをしたら、そのシートを新規ブックにコピーして、別名で保存して、閉じるという手順を行います。
これで、取引先ごとの請求書を作成できますね。
その処理を行うのが以下のSaveAsNewBookメソッドです。これもシートモジュールwsTemplateに記述しています。
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 |
シートを新規ブックにコピーする
このメソッドで1つ目のポイントが、3行目のCopyメソッドですね。
WorksheetオブジェクトのCopyメソッドは、対象のシートを新しいブックにコピーするというもので、本来は以下の構文なのです。
ですが、ここではシートモジュールに書いているので自身のオブジェクトが対象であれば省略して単純に「Copy」と書けます。
そして、新しく作成されたブックはActiveになりますので、すかさず4行目でオブジェクト変数wbにセットさせてもらっています。
アラートダイアログを非表示にする
もう1つのポイントが、9行目と12行目。
ApplicationオブジェクトのDisplayAlertsプロパティです。
これはアラートダイアログを表示するかどうかを指定するプロパティです。
以下の構文でTrueにすると表示、Falseにすると非表示にします。
アラートダイアログをオンにしたままSaveAsNewBookメソッドを実行すると、以下のような「次の機能はマクロなしのブックに保存できません」というダイアログが都度表示されて、「はい」を押さないといけなくなります。
面倒っす。
これが表示されてしまう理由ですが、Copyメソッドでシートをコピーするのですが、このときシートモジュールに書き込まれているコードもコピーされるんですね。
コードも含まれているので、Closeメソッドでファイルを閉じるときに「マクロなしのブックには保存できませんけど、いいですね?」と問われるようになってしまうということです。
これを表示させないように、DisplayAlertsプロパティをいったんFalseに設定しているのです。
請求書マクロの実行結果
では、これらのメソッドをNew請求書マクロから呼び出せるようにしましょう。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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の題材として実務でもよく利用される「請求書マクロ」。ある程度スキルを磨いたら「これをクラスを使って実装したら?」という疑問が出てくるもの…このシリーズで実際に作ってみましょう。- エクセルVBAでクラスを使った請求書マクロの概要と使用するクラスの定義
- エクセルVBAでシートモジュールに請求書マクロのデータをコレクション化する処理を書く
- エクセルVBAでクラスとコレクションを使うと簡単に請求書のデータ抽出できる
- エクセルVBAでクラスを使った請求書でクロの日付関連の処理を作る
- エクセルVBAでクラスを使った請求書マクロでシートのコピー&保存処理を作る
投稿者プロフィール
- 株式会社プランノーツ 代表取締役
-
株式会社プランノーツ代表、コミュニティ「ノンプロ研」主宰。1976年こどもの日生まれ。東京板橋区在住。「ITで日本の『働く』の価値を上げる!」をテーマに、VBA&GASの開発、講師、執筆などをしております。→詳しいプロフィールはコチラ
★ご依頼・ご相談はお気軽にどうぞ!→お問い合わせはコチラ
★フォロー頂ければ嬉しいです。
コメント