VBA問題#3「月次請求書を作成」マクロ①解答

ExcelVBA練習問題シリーズ#3「月次請求書を作成」マクロ①解答コードです。

今回は請求書の月次作成業務全体を自動化する総合問題です。

もちろんこれが正解という訳ではなく、いろいろなやり方あると思いますが、
ひとつの解答としてご参考ください。

※ 挑戦ポイントには対応していない基本コードになります。

◇ 出題ページはこちら
www.limecode.jp
◇ 解答ページはこちら
VBA問題#3「月次請求書を作成」マクロ①解答【本ページ】
VBA問題#3「月次請求書を作成」マクロ②解答
VBA問題#3「月次請求書を作成」解答完成版(挑戦ポイント制覇)
VBA問題#3「月次請求書を作成」別解(低コスト開発版)
　

解答ファイル

【VBA解答】#3「月次請求書を作成」マクロ①.xlsm

ソースコード

標準モジュール:Df_シートレイアウト

Option Explicit

' 販売データ
Public Const Adrs販売データ_年度 = "H2"
Public Const R1st販売データ = 5
Public Const C1st販売データ = 2
Public Enum CNo販売データ
    No = C1st販売データ
    取引先コード
    取引先名
    月
    日
    商品コード
    品物
    価格
    個数
    売上
End Enum
Public Const CLast販売データ = CNo販売データ.売上

' 対象データ
Public Const R1st対象データ = 2
Public Const C1st対象データ = 1
Public Enum CNo対象データ
    No = C1st対象データ
    取引先コード
    取引先名
    購入日
    商品コード
    品物
    種別
    価格
    個数
    売上
End Enum
Public Const CLast対象データ = CNo対象データ.売上

' 請求書
Public Const Adrs請求書_発行日 = "H2"
Public Const Adrs請求書_取引先名 = "B6"
Public Const Adrs請求書_件名 = "B8"
Public Const Adrs請求書_ご請求金額 = "D11"
Public Const Adrs請求書_お支払い期限 = "D13"
Public Const Adrs請求書_合計税別 = "I21"
Public Const Adrs請求書_消費税 = "I22"
Public Const Adrs請求書_合計税込 = "I23"
Public Const Adrs請求書_摘要 = "B26"
Public Const R1st請求書 = 16
Public Const RLast請求書 = 20
Public Const RCount請求書 = RLast請求書 - R1st請求書 + 1
Public Const C1st請求書 = 2
Public Enum CNo請求書
    No = C1st請求書
    種別 = 3
    件数 = 7
    金額 = 9
End Enum
Public Const CLast請求書 = CNo請求書.金額

' 請求明細書
Public Const Adrs請求明細書_取引先名 = "B5"
Public Const Adrs請求明細書_発行日 = "J5"
Public Const Adrs請求明細書_ページNo = "B32"
Public Const Adrs請求明細書_合計税別 = "K28"
Public Const Adrs請求明細書_消費税 = "K29"
Public Const Adrs請求明細書_合計税込 = "K30"
Public Const Adrs請求明細書_2ページ目以降非表示行 = "28:30"
Public Const R1st請求明細書 = 8
Public Const RLast請求明細書 = 27
Public Const RCount請求明細書 = 20
Public Const C1st請求明細書 = 2
Public Enum CNo請求明細書
    No = C1st請求明細書
    ご購入日 = 3
    商品 = 5
    単価 = 8
    数量 = 10
    金額 = 11
End Enum
Public Const CLast請求明細書 = CNo請求明細書.金額

' 実行シート
Public Const Adrs単独実行_取引先コード = "C2"
Public Const Adrs単独実行_取引先名 = "C3"
Public Const Adrs単独実行_対象年 = "C4"
Public Const Adrs単独実行_対象月 = "C5"
Public Const Adrs一括実行_請求日 = "C8"
Public Const Adrs一括実行_対象年 = "C9"
Public Const Adrs一括実行_対象月 = "C10"
Public Const Adrs一括実行_対象締日 = "C11"

' 取引先マスタ
Public Const R1st取引先マスタ = 2
Public Const C1st取引先マスタ = 1
Public Enum CNo取引先マスタ
    取引先コード = C1st取引先マスタ
    取引先名
    締日
    支払日
    明細書
    CSV
End Enum
Public Const CLast取引先マスタ = CNo取引先マスタ.CSV

' 商品マスタ　※1 Enum非対応数式注意
Public Const R1st商品マスタ = 2
Public Const C1st商品マスタ = 1
Public Enum CNo商品マスタ
    商品コード = C1st商品マスタ
    商品名
    種別
    価格
End Enum
Public Const CLast商品マスタ = CNo商品マスタ.価格

標準モジュール:Pr1_月次請求書作成_単独実行

Option Explicit

' マクロ① 月次請求書の単独出力
Sub 指定取引先コード対象月の月次請求書を出力する()
    
    ' 実行シートのパラメータを取得
    Dim 対象取引先コード As Long: 対象取引先コード = WS実行シート.Range(Adrs単独実行_取引先コード)
    Dim 対象年 As Long: 対象年 = WS実行シート.Range(Adrs単独実行_対象年)
    Dim 対象月 As Long: 対象月 = WS実行シート.Range(Adrs単独実行_対象月)
    If 対象取引先コード = 0 Then MsgBox "取引先コードを指定してください。": Exit Sub
    If 対象年 = 0 Then MsgBox "対象年を指定してください。": Exit Sub
    If 対象月 = 0 Then MsgBox "対象月を指定してください。": Exit Sub
    
    
    ' 【対象データの抽出】
    ' 販売データのうち出力の対象となるデータだけに絞ったシートをまずは作成する
    ' 元データにない「種別」「販売日」の情報もここで追加する
    
    ' 対象年が該当の年度でなければExit
    Dim 対象年度 As Long: 対象年度 = 対象年 - IIf(対象月 <= 3, 1, 0)
    If 対象年度 <> WS販売データ.Range(Adrs販売データ_年度) Then MsgBox "指定年月のデータはありません。": Exit Sub
    
    ' 対象データシートを新規出力
    TplWS対象データ.Copy
    Dim ws対象データ As Worksheet: Set ws対象データ = ActiveSheet
    Dim wb出力ブック As Workbook: Set wb出力ブック = ActiveWorkbook
    
    ' 対象データを抽出
    Dim R_対象データ As Long: R_対象データ = R1st対象データ
    Dim R_販売データ As Long
    Dim RLast販売データ As Long: RLast販売データ = WS販売データ.UsedRange.Rows.Count - WS販売データ.UsedRange.Row + 1
    For R_販売データ = R1st販売データ To RLast販売データ
    
        If WS販売データ.Cells(R_販売データ, CNo販売データ.取引先コード) = 対象取引先コード _
        And WS販売データ.Cells(R_販売データ, CNo販売データ.月) = 対象月 Then
        
            ws対象データ.Cells(R_対象データ, CNo対象データ.No) = R_対象データ - R1st対象データ + 1
            ws対象データ.Cells(R_対象データ, CNo対象データ.取引先コード) = WS販売データ.Cells(R_販売データ, CNo販売データ.取引先コード)
            ws対象データ.Cells(R_対象データ, CNo対象データ.取引先名) = WS販売データ.Cells(R_販売データ, CNo販売データ.取引先名)
            ws対象データ.Cells(R_対象データ, CNo対象データ.購入日) = DateSerial(対象年 _
                                                                        , WS販売データ.Cells(R_販売データ, CNo販売データ.月) _
                                                                        , WS販売データ.Cells(R_販売データ, CNo販売データ.日))
            ws対象データ.Cells(R_対象データ, CNo対象データ.商品コード) = WS販売データ.Cells(R_販売データ, CNo販売データ.商品コード)
            ws対象データ.Cells(R_対象データ, CNo対象データ.品物) = WS販売データ.Cells(R_販売データ, CNo販売データ.品物)
            ws対象データ.Cells(R_対象データ, CNo対象データ.種別) = WorksheetFunction.VLookup _
                (ws対象データ.Cells(R_対象データ, CNo対象データ.商品コード), WS商品マスタ.Columns("A:C"), 3, False) ' ※1
            ws対象データ.Cells(R_対象データ, CNo対象データ.価格) = WS販売データ.Cells(R_販売データ, CNo販売データ.価格)
            ws対象データ.Cells(R_対象データ, CNo対象データ.個数) = WS販売データ.Cells(R_販売データ, CNo販売データ.個数)
            ws対象データ.Cells(R_対象データ, CNo対象データ.売上) = WS販売データ.Cells(R_販売データ, CNo販売データ.売上)
            R_対象データ = R_対象データ + 1
        End If
    Next
    
    Dim RLast対象データ As Long: RLast対象データ = R_対象データ - 1
    Dim 対象データ数 As Long: 対象データ数 = RLast対象データ - R1st対象データ + 1
    
    If 対象データ数 = 0 Then MsgBox "指定年月のデータはありません。": Exit Sub
    
    ' 【請求書シートの出力】
    
    ' 請求書シートをテンプレートからコピーして作成
    TplWS請求書.Copy after:=ws対象データ
    Dim ws請求書 As Worksheet: Set ws請求書 = ActiveSheet
    
    ' 各定数の入力
    ws請求書.Range(Adrs請求書_発行日) = Date
    ws請求書.Range(Adrs請求書_取引先名) = ws対象データ.Cells(R1st対象データ, CNo対象データ.取引先名)
    ws請求書.Range(Adrs請求書_件名) = "件名：" & 対象年 & "年" & 対象月 & "月分について"
    ws請求書.Range(Adrs請求書_お支払い期限) = DateSerial(対象年, 対象月 + 2, 1) - 1
    ws請求書.Range(Adrs請求書_摘要) = "対象取引期間：" & Format(DateSerial(対象年, 対象月, 1), "yyyy/m/d") _
                                                & "～" & Format(DateSerial(対象年, 対象月 + 1, 1) - 1, "yyyy/m/d")
    
    ' 表部分の出力
    Dim 種別No As Long: 種別No = 1
    Dim range対象データ種別 As Range: Set range対象データ種別 = ws対象データ.Cells(R1st対象データ, CNo対象データ.種別).Resize(対象データ数)
    Dim range対象データ売上 As Range: Set range対象データ売上 = ws対象データ.Cells(R1st対象データ, CNo対象データ.売上).Resize(対象データ数)
    Dim range請求書種別 As Range: Set range請求書種別 = ws請求書.Cells(R1st請求書, CNo請求書.種別).Resize(RCount請求書)
    For R_対象データ = R1st対象データ To RLast対象データ
        
        Dim key種別 As String: key種別 = ws対象データ.Cells(R_対象データ, CNo対象データ.種別)
        
        ' 新しい種別が登場した際に出力
        On Error Resume Next
        Dim R_請求書 As Long: R_請求書 = 0
        R_請求書 = WorksheetFunction.Match(key種別, range請求書種別, 0)
        On Error GoTo 0
        
        ' 計算して出力
        If R_請求書 = 0 Then
            R_請求書 = R1st請求書 + 種別No - 1
            ws請求書.Cells(R_請求書, CNo請求書.No) = 種別No
            ws請求書.Cells(R_請求書, CNo請求書.種別) = ws対象データ.Cells(R_対象データ, CNo対象データ.種別)
            ws請求書.Cells(R_請求書, CNo請求書.件数) = WorksheetFunction.CountIf(range対象データ種別, key種別)
            ws請求書.Cells(R_請求書, CNo請求書.金額) = WorksheetFunction.SumIf(range対象データ種別, key種別, range対象データ売上)
            種別No = 種別No + 1
        End If
    
    Next
    
    ' 【請求明細シートの出力】
    
    ' 請求明細書シートをテンプレートからコピーして作成
    TplWS請求明細書.Copy after:=ws請求書
    Dim ws請求明細書 As Worksheet: Set ws請求明細書 = ActiveSheet
    
    ' 各定数の入力
    ws請求明細書.Range(Adrs請求明細書_発行日) = Date
    ws請求明細書.Range(Adrs請求明細書_取引先名) = ws対象データ.Cells(R1st対象データ, CNo対象データ.取引先名)
    ws請求明細書.Range(Adrs請求明細書_合計税別) = ws請求書.Range(Adrs請求書_合計税別)
    ws請求明細書.Range(Adrs請求明細書_消費税) = ws請求書.Range(Adrs請求書_消費税)
    ws請求明細書.Range(Adrs請求明細書_合計税込) = ws請求書.Range(Adrs請求書_合計税込)
    
    ' 表部分の出力
    Dim ページNo As Long: ページNo = 1
    Dim R_請求明細 As Long: R_請求明細 = R1st請求明細書
    For R_対象データ = R1st対象データ To RLast対象データ
    
        ' 最終行を超えたら次ページへ
        If R_請求明細 > RLast請求明細書 Then
        
            ページNo = ページNo + 1
        
            TplWS請求明細書.Copy after:=wb出力ブック.Worksheets(wb出力ブック.Worksheets.Count)
            Set ws請求明細書 = ActiveSheet
            ws請求明細書.Range(Adrs請求明細書_発行日) = Date
            ws請求明細書.Range(Adrs請求明細書_取引先名) = ws対象データ.Cells(R1st対象データ, CNo対象データ.取引先名)
            ws請求明細書.Range(Adrs請求明細書_ページNo) = "ー　" & ページNo & "　ー"
            
            ws請求明細書.Rows(Adrs請求明細書_2ページ目以降非表示行).Hidden = True
            
            R_請求明細 = R1st請求明細書
            
        End If
            
        ' 対象データ→請求明細への転記
        ws請求明細書.Cells(R_請求明細, CNo請求明細書.No) = ws対象データ.Cells(R_対象データ, CNo対象データ.No)
        ws請求明細書.Cells(R_請求明細, CNo請求明細書.ご購入日) = ws対象データ.Cells(R_対象データ, CNo対象データ.購入日)
        ws請求明細書.Cells(R_請求明細, CNo請求明細書.商品) = ws対象データ.Cells(R_対象データ, CNo対象データ.品物)
        ws請求明細書.Cells(R_請求明細, CNo請求明細書.単価) = ws対象データ.Cells(R_対象データ, CNo対象データ.価格)
        ws請求明細書.Cells(R_請求明細, CNo請求明細書.数量) = ws対象データ.Cells(R_対象データ, CNo対象データ.個数)
        ws請求明細書.Cells(R_請求明細, CNo請求明細書.金額) = ws対象データ.Cells(R_対象データ, CNo対象データ.売上)
        R_請求明細 = R_請求明細 + 1
    
    Next
    
    ' 【PDFファイルの出力】
    
    ' 出力フォルダのチェックと作成
    Dim path出力フォルダ As String: path出力フォルダ = ThisWorkbook.Path & "\請求書"
    If Dir(path出力フォルダ, vbDirectory) = "" Then MkDir path出力フォルダ
    
    ' ファイル名を設定
    Dim 出力ファイル名 As String
    出力ファイル名 = "請求書" & 対象年 & "年" & 対象月 & "月" _
                        & "(" & ws請求書.Range(Adrs請求書_取引先名) & ").pdf"
    
    ' 対象データシートを非表示
    ws対象データ.Visible = xlSheetHidden
    
    ' ブック全体をPDFに出力
    wb出力ブック.ExportAsFixedFormat xlTypePDF, path出力フォルダ & "\" & 出力ファイル名
    
    ' 出力ブックを保存せず閉じる
    wb出力ブック.Close False

    MsgBox "指定コードの請求書出力を完了しました。"
    
End Sub

解説

各コードの解説と工夫しているポイントは以下の通りです。

処理全体の流れ(処理は区切れるように1つずつ実行)

本マクロは「データ→帳票2種からなるPDFファイル」を目的とするマクロですが、
この処理を以下の流れで実装しています。

1. 元データから対象データだけを抽出したシートを新規ブックに出力
2. 対象データシートからまずは「請求書」を作成
3. 続いて対象データシートから「請求明細書」を作成
4. 対象データシートを非表示にしてからブック全体をPDFに
5. 完了後作成したブックを保存せずに閉じる

　
この流れにしている一番の理由は「テストしやすさ」です。

この規模のマクロになると、コードを書く時間よりも、
「テスト→コードを読んで書き換え」の時間の方が長くなります。

よって「テストのしやすさ」と「読みやすさ」を重視した方が、
書きやすさ(総コード量が少ないなど)を重視するより仕上がりが早く正確になります。

そしてそのために一番重要になるのが「処理を細かく区切れているか」です。

例えば今回のマクロを、

1. 販売データの全行をループして請求書と明細書を同時に出力する

という組み方をしたとしましょう。

まずは初回実行をしてみて、出力結果におかしい部分があったとります。

このとき「すべてを一括で処理するマクロ」を作ってしまった弊害として、
データ判定でミスをしたのか、転記ミスをしたのか、集計ミスをしたのか、
原因がどこにあるのかがわからなくなってしまいます。

対して今回のように一つ一つ区切った処理の書き方をしておけば、
まずは対象データ抽出部分が書けた時点で一旦テストをすることができます。

この「マクロの初回実行までに要する時間が減る」というのも大きいメリットですし、
データの判定がおかしいなら、この時点でそれが判明して直すことができます。

さらに大きいのが「請求書」を出力する段階で、

1. データの抽出は正確であるという担保がある上でコードが書ける
2. データが単純(他社/他月なし)になっているためロジックが簡単になる

という恩恵を得ることができる点です。

実際に2の恩恵で、請求書の種別ごとの合計を取る部分が、
SUMIF/COUNTIF一発で終わっていますからね。

このように処理を分割しておくことで、
簡潔なコードで作成でき、テストもしやすい設計にすることができます。

またこのとき「請求書と請求明細書を同じループで作成しない」というのも重要です。

どうしても人間感覚で「まとめてやった方が効率的」に感じがちですが、

For R = R1st対象データ To RLast対象データ
    処理A
    処理B
Next

For R = R1st対象データ To RLast対象データ
    処理A
Next

For R = R1st対象データ To RLast対象データ
    処理B
Next

この2つにFor文上の速度差はありません。

「Rを1足す」処理なんてたとえ1億回増えても処理時間としては0ですからね。

もちろん販売日の計算や種別の取得を2回ずつやってしまえばその分遅くなりますが、
今回のように「共通の下ごしらえは先にやっておく」ことでその問題は起きません。

このループを分けておくことで、

• 請求書と請求明細書を別々に作成/テストできる
• 「明細は不要」などの改修に強くなる
• 片方の改修が発生したとき、改修がお互いに干渉しない

というメリットを得ることができます。

並行して作業できると効率的なことが多い実世界と違って、
プログラム上では「いかにシングルタスクにできるか」が重要になります。

その点を意識してマクロを作ってみてください。

ログを残せるように作る

「テストしやすいコード」を目的に行った工夫ポイントとして、
「一旦新しいブックに出力してからそれをPDFを出力した」点があります。

この設計にしたことの最大のメリットは「テスト中はログとして使える」ことです。

マクロが完成するまでは、
「最後にブックを閉じる」部分を↓のようにコメントアウトしておきましょう。

' 出力ブックを保存せず閉じる
'wb出力ブック.Close False

　
こうすることでマクロ完成まではPDF元のブックを見ることができるため、
不具合修正や仕様の調整が格段に楽にできるようになります。

このテクニックを知っていると、テストしやすい設計でマクロを作ることができます。
是非採用してみて下さい。

シート指定＆セルアドレス指定の変数＆定数化

今回のマクロは規模も大きいため、
メンテナンス性を考慮して各種レイアウトを変数＆定数化しています。

Worksheets("請求明細書").Cells(R_請求明細, 5) = Worksheets("対象データ").Cells(R_対象データ, 6)
' ⇩ 変数＋定数で書き換え
ws請求明細書.Cells(R_請求明細, CNo請求明細書.商品) = ws対象データ.Cells(R_対象データ, CNo対象データ.品物)

　
上のコードの「5」や「6」のような値をそのままの数字で扱うのは、
規模の大きいマクロだととんでもない労力がかかりますからね。

定数や変数にして名前を付けておけばコードが読めるようになる上、
頭文字を英字で揃えれば選択入力も使えるため書きやすくもなります。




　
これなら変数の取り違えも格段に減らせますし、
日本語入力をONにする必要もないのでタイプももしろ楽になっています。

さらには「列挿入などの改修時は定数の宣言部分をいじればよい」ため、
コードを見渡して5→6に書き換える地獄のような作業から解放されます。

いいことずくめですね！

このあたりをしっかり定義しておくとコーディング効率が劇的に向上しますので、
ぜひとも修得してみてください。

定数化については詳しく書いた記事がありますのでご参考下さい。
www.limecode.jp

またEnum(+定数化)勉強会のYoutube配信アーカイブもありますので、
こちらもあわせてご活用いただければと思います。

以上でマクロ①の解説を終わります。

基本コードの詰め合わせでしたが、
読みやすく書こうとすると、工夫できるポイントがたくさんありましたね。

参考にしながら、マクロ②の方にも取り組んでみてください。