和風スパゲティのレシピ

日本語でコーディングするExcelVBA

トップ > VBA問題集 > VBA問題#3「月次請求書を作成」完成版解答

VBA問題#3「月次請求書を作成」完成版解答

VBA問題集 読みやすいコードの書き方 プロシージャ分割 VBA

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商品マスタ.価格

' 販売データ
Function GetDate販売データ_購入日(R As Long)
    GetDate販売データ_購入日 = DateSerial年度版( _
        WS販売データ.Range(Adrs販売データ_年度), _
        WS販売データ.Cells(R, CNo販売データ.月), _
        WS販売データ.Cells(R, CNo販売データ.日))
End Function

標準モジュール:Pr0_月次請求書作成_共通コード

Option Explicit

' 対象データ抽出シートの作成
' 販売データのうち出力の対象となるデータだけに絞ったシートをまずは作成する
' 元データにない「種別」「販売日」の情報もここで追加する
Function 販売データから対象データのみを抽出したシートを新規ブックに出力する _
    (対象取引先コード As Long, 開始日 As Date, 終了日 As Date) As Worksheet

    TplWS対象データ.Copy
    Dim ws対象データ As Worksheet: Set ws対象データ = ActiveSheet
    
    ' 対象データを抽出
    Dim R_対象データ As Long: R_対象データ = R1st対象データ
    Dim R_販売データ As Long
    For R_販売データ = R1st販売データ To Get最終行(WS販売データ)

        Dim 購入日 As Date: 購入日 = GetDate販売データ_購入日(R_販売データ)

        If WS販売データ.Cells(R_販売データ, CNo販売データ.取引先コード) = 対象取引先コード _
        And 開始日 <= 購入日 And 購入日 <= 終了日 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対象データ.購入日) = 購入日
            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

    ' データがあれば作成シートを返す
    If R_対象データ > R1st対象データ Then
        Set 販売データから対象データのみを抽出したシートを新規ブックに出力する = ws対象データ

    ' データが0件ならブックを保存せず閉じる
    Else
        ws対象データ.Parent.Close False
    End If

End Function

' 対象データ → 請求書
Sub 指定の対象データから請求書を出力する(ws対象データ As Worksheet _
    , 開始日 As Date, 終了日 As Date, 対象年 As Long, 対象月 As Long, 発行日 As Date, 支払日設定 As String)

    ' 請求書シートをテンプレートからコピーして対象データの次シートに挿入
    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請求書_摘要) = "対象取引期間：" & Format(開始日, "yyyy/m/d") _
                                                & "～" & Format(終了日, "yyyy/m/d")
    Select Case 支払日設定
    Case "翌月": ws請求書.Range(Adrs請求書_お支払い期限) = DateSerial(対象年, 対象月 + 2, 1) - 1
    Case "翌々月": ws請求書.Range(Adrs請求書_お支払い期限) = DateSerial(対象年, 対象月 + 3, 1) - 1
    End Select
    
    ' 表部分の出力
    Dim 種別No As Long: 種別No = 1
    Dim range対象データ種別 As Range: Set range対象データ種別 = GetRange指定列のデータ部分(ws対象データ, CNo対象データ.種別)
    Dim range対象データ売上 As Range: Set range対象データ売上 = GetRange指定列のデータ部分(ws対象データ, CNo対象データ.売上)
    Dim range請求書種別 As Range: Set range請求書種別 = ws請求書.Cells(R1st請求書, CNo請求書.種別).Resize(RCount請求書)
    Dim R_対象データ As Long
    For R_対象データ = R1st対象データ To Get最終行(ws対象データ)
        
        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請求書.件数) = Fx.CountIf(range対象データ種別, key種別)
            ws請求書.Cells(R_請求書, CNo請求書.金額) = Fx.SumIf(range対象データ種別, key種別, range対象データ売上)
            種別No = 種別No + 1
        End If
    
    Next
    
End Sub

' 対象データ → 請求明細書
Sub 指定の対象データから請求明細書を出力する(ws対象データ As Worksheet)

    ' 請求明細書シートをテンプレートからコピーして出力ブック末尾に挿入
    TplWS請求明細書.Copy After:=Get最終シート(ws対象データ.Parent)
    Dim ws請求明細書 As Worksheet: Set ws請求明細書 = ActiveSheet
    
    ' 各定数の入力（既に同梱の請求書シートより取得）
    Dim ws請求書 As Worksheet: Set ws請求書 = ws対象データ.Parent.Worksheets("請求書")
    ws請求明細書.Range(Adrs請求明細書_発行日) = ws請求書.Range(Adrs請求書_発行日)
    ws請求明細書.Range(Adrs請求明細書_取引先名) = ws請求書.Range(Adrs請求書_取引先名)
    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請求明細書
    Dim R_対象データ As Long
    For R_対象データ = R1st対象データ To Get最終行(ws対象データ)
    
        ' 最終行を超えたら次ページへ
        If R_請求明細 > RLast請求明細書 Then
        
            ページNo = ページNo + 1
        
            TplWS請求明細書.Copy After:=Get最終シート(ws対象データ.Parent)
            Set ws請求明細書 = ActiveSheet
            ws請求明細書.Range(Adrs請求明細書_発行日) = ws請求書.Range(Adrs請求書_発行日)
            ws請求明細書.Range(Adrs請求明細書_取引先名) = ws請求書.Range(Adrs請求書_取引先名)
            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

End Sub

' 対象データ → CSVファイル
Sub 指定の対象データシートをCSVファイルとして出力する(ws対象データ As Worksheet, 対象年 As Long, 対象月 As Long, path出力フォルダ As String)

    Dim 出力CSVファイル名 As String
    出力CSVファイル名 = "請求データ" & 対象年 & "年" & 対象月 & "月" _
        & "(" & ws対象データ.Cells(R1st対象データ, CNo対象データ.取引先名) & ").csv"
        
    ws対象データ.Copy
    Dim wb出力CSV As Workbook: Set wb出力CSV = ActiveWorkbook
    
    Application.DisplayAlerts = False
    wb出力CSV.SaveAs path出力フォルダ & "\" & 出力CSVファイル名, xlCSV
    Application.DisplayAlerts = True
    
    wb出力CSV.Close False
    
End Sub

' 出力ブック → PDFファイル
Sub 指定の対象データ同梱ブックをPDFファイルとして出力する(ws対象データ As Worksheet, 対象年 As Long, 対象月 As Long, path出力フォルダ As String)

    ' ファイル名を設定
    Dim 出力ファイル名 As String
    出力ファイル名 = "請求書" & 対象年 & "年" & 対象月 & "月" _
                        & "(" & ws対象データ.Cells(R1st対象データ, CNo対象データ.取引先名) & ").pdf"
    
    ' 対象データシートを非表示
    ws対象データ.Visible = xlSheetHidden
    
    ' ブック全体をPDFに出力
    ws対象データ.Parent.ExportAsFixedFormat xlTypePDF, path出力フォルダ & "\" & 出力ファイル名
                
End Sub

標準モジュール: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
    
    ' データの対象範囲を日付型として取得
    Dim 開始日 As Date, 終了日 As Date
    開始日 = DateSerial(対象年, 対象月, 1)
    終了日 = DateSerial(対象年, 対象月 + 1, 1) - 1
    
    ' ■ 対象データシートを新規ブックに出力
    Dim ws対象データ As Worksheet
    Set ws対象データ = 販売データから対象データのみを抽出したシートを新規ブックに出力する(対象取引先コード, 開始日, 終了日)
    
    If ws対象データ Is Nothing Then MsgBox "指定年月のデータはありません。": Exit Sub
    
    ' ■ 請求書シートの出力
    Call 指定の対象データから請求書を出力する(ws対象データ, 開始日, 終了日, 対象年, 対象月, Date, "翌月")
    
    ' ■ 請求明細書シートの出力
    Call 指定の対象データから請求明細書を出力する(ws対象データ)
    
    ' 出力フォルダの取得(作成)
    Dim path出力フォルダ As String: path出力フォルダ = ThisWorkbook.Path & "\請求書"
    If Dir(path出力フォルダ, vbDirectory) = "" Then MkDir path出力フォルダ
    
    ' ■ PDFファイルの出力
    Call 指定の対象データ同梱ブックをPDFファイルとして出力する(ws対象データ, 対象年, 対象月, path出力フォルダ)
    
    ' 出力ブックを保存せず閉じる
    ws対象データ.Parent.Close False

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

Pr2_月次請求書作成_一括実行

Option Explicit

' マクロ② 月次請求書の一括出力
Sub 対象締日対象年月の全取引先の月次請求書を一括出力する()

    ' 実行シートのパラメータを取得
    Dim 請求日 As Date: 請求日 = WS実行シート.Range(Adrs一括実行_請求日)
    If 請求日 = 0 Then 請求日 = Date
    Dim 対象年 As Long: 対象年 = WS実行シート.Range(Adrs一括実行_対象年)
    Dim 対象月 As Long: 対象月 = WS実行シート.Range(Adrs一括実行_対象月)
    Dim 対象締日 As String: 対象締日 = WS実行シート.Range(Adrs一括実行_対象締日)
    If 対象年 = 0 Then MsgBox "対象年を指定してください。": Exit Sub
    If 対象月 = 0 Then MsgBox "対象月を指定してください。": Exit Sub
    If 対象締日 = "" Then MsgBox "対象締日を指定してください。": Exit Sub
    
    ' 別年度の指定はExit
    Dim 対象年度 As Long: 対象年度 = 対象年 - IIf(対象月 <= 3, 1, 0)
    If 対象年度 <> WS販売データ.Range(Adrs販売データ_年度) Then MsgBox "指定年月のデータはありません。": Exit Sub
    
    ' 締日から開始日と終了日を取得
    Dim 開始日 As Date, 終了日 As Date
    If 対象締日 = "月末締め" Then
        開始日 = DateSerial(対象年, 対象月, 1)
        終了日 = DateSerial(対象年, 対象月 + 1, 1) - 1
    Else
        Dim 締日 As Long: 締日 = Replace(対象締日, "日締め", "")
        開始日 = DateSerial(対象年, 対象月 - 1, 締日 + 1)
        終了日 = DateSerial(対象年, 対象月, 締日)
    End If
    
    ' 出力フォルダのチェックと作成
    Dim path出力フォルダ As String: path出力フォルダ = ThisWorkbook.Path & "\請求書" & 対象年 & "年" & 対象月 & "月" & 対象締日
    If Dir(path出力フォルダ, vbDirectory) = "" Then MkDir path出力フォルダ

    ' マスタをループして対象の取引先を検索
    Dim R_取引先マスタ As Long
    Dim RLast取引先マスタ As Long: RLast取引先マスタ = Get最終行(WS取引先マスタ)
    For R_取引先マスタ = R1st取引先マスタ To RLast取引先マスタ
    
        ' 締日が合致した取引先を処理
        If WS取引先マスタ.Cells(R_取引先マスタ, CNo取引先マスタ.締日) = 対象締日 Then

            ' 取引先マスタの情報を取得
            Dim 対象取引先コード As Long: 対象取引先コード = WS取引先マスタ.Cells(R_取引先マスタ, CNo取引先マスタ.取引先コード)
            Dim 支払日設定 As String: 支払日設定 = WS取引先マスタ.Cells(R_取引先マスタ, CNo取引先マスタ.支払日)
            Dim is要明細 As Boolean: is要明細 = WS取引先マスタ.Cells(R_取引先マスタ, CNo取引先マスタ.明細書) <> ""
            Dim is要CSV As Boolean: is要CSV = WS取引先マスタ.Cells(R_取引先マスタ, CNo取引先マスタ.CSV) <> ""

            ' ■ 対象データシートを新規ブックに出力
            Dim ws対象データ As Worksheet
            Set ws対象データ = 販売データから対象データのみを抽出したシートを新規ブックに出力する(対象取引先コード, 開始日, 終了日)
            If ws対象データ Is Nothing Then GoTo 次の取引先へ

            ' ■ 請求書シートの出力
            Call 指定の対象データから請求書を出力する(ws対象データ, 開始日, 終了日, 対象年, 対象月, 請求日, 支払日設定)
            
            ' ■ 請求明細書シートの出力
            If is要明細 Then Call 指定の対象データから請求明細書を出力する(ws対象データ)
            
            ' ■ CSVファイルの出力
            If is要CSV Then: Call 指定の対象データシートをCSVファイルとして出力する(ws対象データ, 対象年, 対象月, path出力フォルダ)

            ' ■ PDFファイルの出力
            Call 指定の対象データ同梱ブックをPDFファイルとして出力する(ws対象データ, 対象年, 対象月, path出力フォルダ)

            ' 出力ブックを保存せず閉じる
            ws対象データ.Parent.Close False
        
次の取引先へ:
        End If ' 締日が合致した取引先を処理
        
    Next ' マスタをループして対象の取引先を検索

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

標準モジュール:Ut_汎用関数

Option Explicit

' 最終行の取得
Function Get最終行(指定シート As Worksheet, Optional 列番号 As Long = -1) As Long
    With 指定シート
    
        ' 列番号を省略時はシートの最終行
        If 列番号 = -1 Then
            Get最終行 = .UsedRange.Rows.Count + .UsedRange.Row - 1
    
        ' 列番号を指定時は列の最終行
        Else
            Get最終行 = .Cells(.Rows.Count, 列番号).End(xlUp).Row
        End If
    
    End With
End Function

' ブック最終シートの取得
Function Get最終シート(指定ブック As Workbook) As Worksheet
    Set Get最終シート = 指定ブック.Worksheets(指定ブック.Worksheets.Count)
End Function

' 年度指定のDateSerial
Function DateSerial年度版(ByVal 年度 As Long, ByVal m As Long, ByVal d As Long) As Date
    If m <= 3 Then
        DateSerial年度版 = DateSerial(年度 + 1, m, d)
    Else
        DateSerial年度版 = DateSerial(年度, m, d)
    End If
End Function

' データ件数
Function Countデータ件数(対象シート As Worksheet) As Long
    If 対象シート.AutoFilterMode = False Then Exit Function
    With 対象シート.AutoFilter.Range
        Countデータ件数 = .Rows.Count + .Row - 2
    End With
End Function

' 各列のデータ部分
Function GetRange指定列のデータ部分(対象シート As Worksheet, 対象列 As Long) As Range
    If Countデータ件数(対象シート) = 0 Then Exit Function
    Set GetRange指定列のデータ部分 = 対象シート.Cells(対象シート.AutoFilter.Range.Row + 1, 対象列) _
                                                            .Resize(Countデータ件数(対象シート))
End Function

' WorksheetFunctionの短縮取得
Function Fx() As WorksheetFunction
    Set Fx = WorksheetFunction
End Function

解説

処理の基本部分についての解説は、
マクロ①②の解答で詳しく行っていますのでそちらをご参考ください。
VBA問題#3「月次請求書を作成」マクロ①解答
VBA問題#3「月次請求書を作成」マクロ②解答


本記事では「挑戦ポイント」への対応内容を解説していきます。


コードを読む際は、上記マクロ①②の解答ファイルと見比べることで、
コードをどのように改良したかがより分かりやすくなると思います。

よろしければ横に並べてご覧ください。

レイアウト変更への対応を考慮した設計

【挑戦ポイント】
販売データの列の変更や、領収書のレイアウトの変更に対応できるよう設計してみてください。

　

今回のマクロは規模が大きかったため、
この挑戦ポイントにはマクロ①②の解答時点で対応していました。

メンテナンス性を考慮して各種レイアウト情報を変数＆定数化しているため、
変更は「Df_シートレイアウト」モジュールをいじるだけで対応できます。

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

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


定数や変数にして名前を付けておけばコードが読めるようになる上、
頭文字を英字で揃えれば選択入力も使えるため書きやすくもなります。
wsからの選択肢
rからの選択肢
cnからの選択肢
Enumによる選択肢
　
これなら変数の取り違えも格段に減らせますし、
日本語入力をONにする必要もないのでタイプももしろ楽になっています。


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

いいことずくめですね！


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


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


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


Subプロシージャの分割

【挑戦ポイント】
各種マスタ情報の取得をFunction化したり、締日の処理を対象期間を引数として受け取る1つのSubプロシージャで行うなど、
適切にSub/Function分割を行ってマクロ全体を設計してみてください。

　
本記事で是非とも実感していただきたいのが、
このプロシージャ(Sub/Function)分割の重要性とメリットです。


本記事との比較用に、マクロ②解答コードを
「同じロジックをひとつのSubプロシージャに書いたバージョン」
として作成していますので見比べてみてください。

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


今回のプロシージャ分割の設計としては、
「Pr0_月次請求書作成_共通コード」モジュールに、

  1. 販売データ → 対象データ
    ※ 出力対象に絞ったデータを新規ブックへ
  2. 対象データ → 請求書
  3. 対象データ → 請求明細書
  4. 対象データ → CSV
  5. 出力ブック(全出力シート) → PDF

ベースとなるこの5つのプロシージャを搭載しています。

1番の対象データ作成を「開始日・終了日」で動くようにすることで、
マクロ①、マクロ②の各締日による処理の違いに対応しました。


この設計にした効果で、マクロ①のメインロジック部分は、
↓のように5プロシージャをCallしていく構成に出来ています。

' データの対象範囲を日付型として取得
Dim 開始日 As Date, 終了日 As Date
開始日 = DateSerial(対象年, 対象月, 1)
終了日 = DateSerial(対象年, 対象月 + 1, 1) - 1

' ■ 対象データシートを新規ブックに出力
Dim ws対象データ As Worksheet
Set ws対象データ = 販売データから対象データのみを抽出したシートを新規ブックに出力する(対象取引先コード, 開始日, 終了日)

If ws対象データ Is Nothing Then MsgBox "指定年月のデータはありません。": Exit Sub

' ■ 請求書シートの出力
Call 指定の対象データから請求書を出力する(ws対象データ, 開始日, 終了日, 対象年, 対象月, Date, "翌月")

' ■ 請求明細書シートの出力
Call 指定の対象データから請求明細書を出力する(ws対象データ)

' 出力フォルダの取得(作成)
Dim path出力フォルダ As String: path出力フォルダ = ThisWorkbook.Path & "\請求書"
If Dir(path出力フォルダ, vbDirectory) = "" Then MkDir path出力フォルダ

' ■ PDFファイルの出力
Call 指定の対象データ同梱ブックをPDFファイルとして出力する(ws対象データ, 対象年, 対象月, path出力フォルダ)

　
またマクロ②も取引先ごとに5プロシージャをCallしていく形になるため、
長大だったFor文が一画面に納まり全体像を見渡せるようになっています。

' マスタをループして対象の取引先を検索
Dim R_取引先マスタ As Long
Dim RLast取引先マスタ As Long: RLast取引先マスタ = Get最終行(WS取引先マスタ)
For R_取引先マスタ = R1st取引先マスタ To RLast取引先マスタ

    ' 締日が合致した取引先を処理
    If WS取引先マスタ.Cells(R_取引先マスタ, CNo取引先マスタ.締日) = 対象締日 Then

        ' 取引先マスタの情報を取得
        Dim 対象取引先コード As Long: 対象取引先コード = WS取引先マスタ.Cells(R_取引先マスタ, CNo取引先マスタ.取引先コード)
        Dim 支払日設定 As String: 支払日設定 = WS取引先マスタ.Cells(R_取引先マスタ, CNo取引先マスタ.支払日)
        Dim is要明細 As Boolean: is要明細 = WS取引先マスタ.Cells(R_取引先マスタ, CNo取引先マスタ.明細書) <> ""
        Dim is要CSV As Boolean: is要CSV = WS取引先マスタ.Cells(R_取引先マスタ, CNo取引先マスタ.CSV) <> ""

        ' ■ 対象データシートを新規ブックに出力
        Dim ws対象データ As Worksheet
        Set ws対象データ = 販売データから対象データのみを抽出したシートを新規ブックに出力する(対象取引先コード, 開始日, 終了日)
        If ws対象データ Is Nothing Then GoTo 次の取引先へ

        ' ■ 請求書シートの出力
        Call 指定の対象データから請求書を出力する(ws対象データ, 開始日, 終了日, 対象年, 対象月, 請求日, 支払日設定)
        
        ' ■ 請求明細書シートの出力
        If is要明細 Then Call 指定の対象データから請求明細書を出力する(ws対象データ)
        
        ' ■ CSVファイルの出力
        If is要CSV Then: Call 指定の対象データシートをCSVファイルとして出力する(ws対象データ, 対象年, 対象月, path出力フォルダ)

        ' ■ PDFファイルの出力
        Call 指定の対象データ同梱ブックをPDFファイルとして出力する(ws対象データ, 対象年, 対象月, path出力フォルダ)

        ' 出力ブックを保存せず閉じる
        ws対象データ.Parent.Close False
    
次の取引先へ:
    End If ' 締日が合致した取引先を処理
    
Next ' マスタをループして対象の取引先を検索

　
マクロ①、マクロ②ともに格段に読みやすくなっているのが分かりますね。


またプロシージャ分割のもう一つの重要なメリットととして、
どこに何が書いてあるかを探しやすいというのもあります。


長大なコードから該当箇所を探すのはなかなか大変なのですが、
適切にプロシージャ分割をしておけば、例えば「明細書の出力部分」を探す際、

◆ 呼出元(宣言位置)へ移動するショートカット「Shift+F2」

        ' ■ 請求明細書シートの出力
        If is要明細 Then Call 指定の対象データから請求明細書を出力する(ws対象データ)
                                         ' ↑この辺でShift+F2「呼出元にジャンプ」を実行する

◆ コードウィンドウ上部のプロシージャ選択機能
コードウィンドウ上部の移動機能
　
これらの機能を使えばそのプロシージャの先頭に移動することができます。

これでコードの改修や管理がだいぶ楽になりますね。


今回のマクロはプロシージャ分割の恩恵を実感しやすいマクロですので、
ちょうどいい機会ですし、Sub/Function分割を学ぶ題材に使ってみてください。


今回のコードとマクロ②のコードを横に並べて見比べることで、
プロシージャ分割のやり方やメリットを分かりやすく読むことができます。

プロシージャ(Sub/Function)分割を勉強をしてみたいという方は、
絶好の機会ですので是非ご活用ください。

SubとFunctionの使い分け

少し細かい設計の話になるのですが、今回のメインプロシージャ群

  1. 販売データ → 対象データ
    ※ 出力対象に絞ったデータを新規ブックへ
  2. 対象データ → 請求書
  3. 対象データ → 請求明細書
  4. 対象データ → CSV
  5. 出力ブック(全出力シート) → PDF

この5つは「一つ目をFunction、他4つはSub」という設計で組みました。


その意図としては、

  • まず一つ目のプロシージャで新規ブックを作る
  • それ以降の4つは「対象データ」シートを元に同ブックに同梱

というロジックを表現するためです。


まずは「取引先コード」「開始日」「終了日」を元に対象データシートを作り、
そのシートをもって新規ブックを出力します。

それ以降の処理は、「対象データ」シートを元に何か成果物を作って、
それを対象データと同ブックに入れたり外に保存しています。


このため第1処理は「値を引数に新規に作ったシートを返すFunction」になり、
以降の処理は「対象データシートを引数に成果物を作成するSub」になっています。


このあたりの引数や返り値をどんな設計にするかは、
マクロごとに正解も異なるため、習得が難しい部分かと思います。

自分なりのやり方を試行錯誤していく必要がある部分なので、
今回のコードを参考に、いろいろな設計で作ってみてください。



以上で挑戦ポイントを網羅した完成版の解説を終わります。

マクロ①②の解答コードと見比べると、
全く同じ処理でも全然違う書き方になっているのがわかりますね。


ある程度処理が書けるようになったら、
メンテナンス性や可読性を意識したコードに挑戦してみましょう。

マクロの改修が楽になったり、バグの発生率を落とすことができます。


コードの書き方にはいろいろな方法がありますので、
いろいろなコードを眺めて参考にしてみてください。