帳票出力マクロをクラスを使って書く

「処理ごとの解説ではなく、完成品の全体が見たい！」
派の方のための、完成品マクロのサンプルコードです。

今回のテーマは「データシート ⇒ 帳票への印字・出力」です。

帳票は請求書や納品書などなんでもあり。

実務の登場頻度が高く、ワークシート関数だけでは自動化が難しいので、
多くの人の「VBAを勉強したいと思うきっかけ」になっているくらい、メジャーな処理ですね。

なお、本コードは「クラスモジュール基本編」ということで、
クラスモジュールの基本的な書き方だけを使いつつ、
その上でも少しは便利に使えるような書き方をしています。

クラスモジュールを勉強中の方は、
ちょうどいい参考になるのではないかな、と思います。

マクロの仕様について

以下のデータシートから、帳票を出力します。

◇ データシート

◇ 領収書

　
データシートと、領収書のテンプレートを同じブックに置き、
「WS購入歴データ」「TplWS領収書」というオブジェクト名を設定します。

出力する領収書は、1枚ずつ別のブックに出力します。

クラスモジュールの設計について

クラスモジュールの設計の狙いを、先に書いておきます。

クラスモジュールの一番大事な仕組みは「カプセル化」、
すなわち「一緒に使うデータをまとめて持っておく」ことです。

ExcelVBAでクラスを使うとなると「一覧表データ」で使うことが多いですかね。
表計算ソフトですし。

さてここで、「一覧表データのどこをカプセルにするか」を考えましょう。

これには大きく分けて二つの考え方があります。

ひとつは「レコードをカプセルだと思う方式」

もう一つは「ワークシートをカプセルだと思う方式」です。

どちらにもメリットがあるのですが、個人的な意見としては、

クラスを学び始めた方は、後者の「ワークシートをカプセル化」

をやってみるといい気がします。

「便利！」って実感しやすいので、その分学びやすいのかな～と。

理由は単純で、その方がExcelの機能を使いやすいからです。

すぐ思いつく例だけでも、「列のブロック」を使って、

みかんの売上 = SUMIF(品物ブロック,"みかん",売上ブロック)

とか書けるだけも、結構便利ですからね。

前者の「レコードをカプセル化」は、配列やメモリ上で自分の描くクラスを実装していくので、プログラマらしいスキルを磨くにはもってこいです。

しかしワークシートから離れてしまうことで、学び始めはどうしても、
クラスを使うとかえって不便になってしまいます。

そもそも、「Worksheet」もMicrosoftさんが書いた「クラス」ですからね。

代わりのクラスを1から作るより、既存の(超優秀な)クラスをカスタマイズしたほうが手っ取り早いのは、イメージできると思います。

ということで今回のクラスは後者の「Worksheetカプセル」を採用します。

ひとまずプロパティには、データ用の変数は用意せず、
シート内のRangeオブジェクトを指定することに限定してクラスを設計します。

それを頭の片隅に置いて、ソースコードを読んでいただければと思います。

ソースコード

クラスモジュール「Class購入歴データ」

Option Explicit

' 購入歴データのシートレイアウト
Private Const Adrs購入歴データ_年度 = "E2"
Private Const R1st購入歴データ = 5
Private Enum CNo購入歴データ
    No = 2
    月
    日
    購入者
    品物
    価格
    個数
    売上
    出力
End Enum
Private Const C1st購入歴データ = CNo購入歴データ.No
Private Const CLast購入歴データ = CNo購入歴データ.出力

' メインオブジェクトはワークシート
Public ws As Worksheet

' シートのレイアウトを返すプロパティ
Property Get R1st() As Long
    R1st = R1st購入歴データ
End Property
Property Get RLast() As Long
    RLast = ws.UsedRange.Rows.Count + ws.UsedRange.Row - 1
End Property
Property Get RCount() As Long
    RCount = RLast - R1st + 1
End Property
Property Get C1st() As Long
    C1st = C1st購入歴データ
End Property
Property Get CLast() As Long
    CLast = CLast購入歴データ
End Property
Property Get CCount() As Long
    CCount = CLast - C1st + 1
End Property
Property Get DataRange() As Range
    Set DataRange = ws.Cells(R1st, C1st).Resize(RCount, CCount)
End Property

' セルを取得するプロパティ
Property Get Cell_No(R As Long) As Range
    Set Cell_No = ws.Cells(R, CNo購入歴データ.No)
End Property
Property Get Cell_月(R As Long) As Range
    Set Cell_月 = ws.Cells(R, CNo購入歴データ.月)
End Property
Property Get Cell_日(R As Long) As Range
    Set Cell_日 = ws.Cells(R, CNo購入歴データ.日)
End Property
Property Get Cell_購入者(R As Long) As Range
    Set Cell_購入者 = ws.Cells(R, CNo購入歴データ.購入者)
End Property
Property Get Cell_品物(R As Long) As Range
    Set Cell_品物 = ws.Cells(R, CNo購入歴データ.品物)
End Property
Property Get Cell_価格(R As Long) As Range
    Set Cell_価格 = ws.Cells(R, CNo購入歴データ.価格)
End Property
Property Get Cell_個数(R As Long) As Range
    Set Cell_個数 = ws.Cells(R, CNo購入歴データ.個数)
End Property
Property Get Cell_売上(R As Long) As Range
    Set Cell_売上 = ws.Cells(R, CNo購入歴データ.売上)
End Property
Property Get Cell_出力(R As Long) As Range
    Set Cell_出力 = ws.Cells(R, CNo購入歴データ.出力)
End Property

' 計算で取得するプロパティ
Property Get GetYear購入年(R As Long) As Long
    If Cell_月(R) = 0 Or Cell_日(R) = 0 Then Exit Property
    GetYear購入年 = Left(ws.Range(Adrs購入歴データ_年度), 4) + IIf(Cell_月(R) <= 3, 1, 0)
End Property
Property Get GetDate購入日(R As Long) As Date
    If Cell_月(R) = 0 Or Cell_日(R) = 0 Then Exit Property
    GetDate購入日 = DateSerial(GetYear購入年(R), Cell_月(R), Cell_日(R))
End Property

' 行のブロックを取得するプロパティ
Property Get Rows(ブロック開始行 As Long, Optional ブロック最終行 As Long = -1) As Range
    If ブロック最終行 = -1 Then ブロック最終行 = ブロック開始行 ' 省略時は開始行1行を取得
    Set Rows = DataRange.Rows(ブロック開始行 - R1st + 1).Resize(ブロック最終行 - ブロック開始行 + 1)
End Property

' 列のブロックを取得するプロパティ
Property Get Column_No() As Range
    Set Column_No = DataRange.Columns(CNo購入歴データ.No - C1st + 1)
End Property
Property Get Column_月() As Range
    Set Column_月 = DataRange.Columns(CNo購入歴データ.月 - C1st + 1)
End Property
Property Get Column_日() As Range
    Set Column_日 = DataRange.Columns(CNo購入歴データ.日 - C1st + 1)
End Property
Property Get Column_購入者() As Range
    Set Column_購入者 = DataRange.Columns(CNo購入歴データ.購入者 - C1st + 1)
End Property
Property Get Column_品物() As Range
    Set Column_品物 = DataRange.Columns(CNo購入歴データ.品物 - C1st + 1)
End Property
Property Get Column_価格() As Range
    Set Column_価格 = DataRange.Columns(CNo購入歴データ.価格 - C1st + 1)
End Property
Property Get Column_個数() As Range
    Set Column_個数 = DataRange.Columns(CNo購入歴データ.個数 - C1st + 1)
End Property
Property Get Column_売上() As Range
    Set Column_売上 = DataRange.Columns(CNo購入歴データ.売上 - C1st + 1)
End Property
Property Get Column_出力() As Range
    Set Column_出力 = DataRange.Columns(CNo購入歴データ.出力 - C1st + 1)
End Property

　
長たらしいですが、中身はコピペだらけなので、実際は数種類だけのコードです。

唯一のPublic変数であるワークシートに購入歴データのシートをセットすると、
そのシートのセル・行・列のRangeオブジェクトを返すクラスです。

先ほどの画像↓のまんまですね。

「こいつらみんなプロパティ」ってのが全部書いてあって、
他に「表全体の範囲と位置情報」「年度の日付変換」があるだけです。

クラスモジュール「Class領収書」

' メインオブジェクトはワークシート
Public ws As Worksheet

Property Get wb() As Workbook
    Set wb = ws.Parent
End Property


' 領収書のセルアドレス
Function Cell_No() As Range
    Set Cell_No = ws.Range("D3")
End Function
Function Cell_購入者() As Range
    Set Cell_購入者 = ws.Range("C7")
End Function
Function Cell_購入日() As Range
    Set Cell_購入日 = ws.Range("G3")
End Function
Function Cell_品物() As Range
    Set Cell_品物 = ws.Range("F11")
End Function
Function Cell_お支払い() As Range
    Set Cell_お支払い = ws.Range("F9")
End Function

' 新規出力
Sub 新規の領収書をテンプレートから出力する()

    TplWS領収書.Visible = True
    
    TplWS領収書.Copy
    Set ws = ActiveSheet

    TplWS領収書.Visible = False

End Sub

　
こちらも同じく領収書シートをセットすると、
印字するセルがプロパティとして呼び出せるクラスです。

「テンプレートをコピーして、できた新シートをクラスにセット」
も関数にして内包しています。

標準モジュール「選択領収書の一括実行」

Option Explicit

' 選択データ⇒別のブック
Sub 購入歴データの出力指定行をそれぞれ別のブックの領収書に一括出力する()
    ' 使用するクラスのインスタンスを生成
    Dim cls購入歴 As Class購入歴データ: Set cls購入歴 = New Class購入歴データ
    Dim cls領収書 As Class領収書: Set cls領収書 = New Class領収書
    
    ' 購入歴データのインスタンスにワークシートをセット
    Set cls購入歴.ws = WS購入歴データ
    
    ' ユーザー入力チェックと出力フォルダの受け取り
    Dim Path出力先フォルダ As String
    Path出力先フォルダ = _
        ユーザーの出力指定に誤りがなければダイアログボックスからフォルダを受け取る(cls購入歴)
    If Path出力先フォルダ = "" Then Exit Sub

    Call エクセルの自動更新を停止する(False)
    
    ' データの走査
    Dim R As Long
    For R = cls購入歴.R1st To cls購入歴.RLast
        
        ' 出力列が1のデータを処理
        If cls購入歴.Cell_出力(R) = 1 Then
            
            ' 新規領収書を生成
            Call cls領収書.新規の領収書をテンプレートから出力する
            
            ' 各項目を印字
            cls領収書.Cell_No = cls購入歴.Cell_No(R)
            cls領収書.Cell_購入日 = cls購入歴.GetDate購入日(R)
            cls領収書.Cell_購入者 = cls購入歴.Cell_購入者(R)
            cls領収書.Cell_品物 = cls購入歴.Cell_品物(R)
            cls領収書.Cell_お支払い = cls購入歴.Cell_売上(R)
            
            ' ブック名を「領収書No_yyyymmdd.xlsx」で保存
            cls領収書.wb.SaveAs Path出力先フォルダ & "\領収書No" & cls購入歴.Cell_No(R) _
                & "_" & Format(cls購入歴.GetDate購入日(R), "yyyymmdd") & ".xlsx"
                
            ' 生成ブックは閉じる
            cls領収書.wb.Close False
    
        End If
    Next
    
    Call エクセルの自動更新を開始する
    
    MsgBox ("領収書の一括出力を完了しました。")

End Sub

' ユーザー入力チェックと出力フォルダの受け取り
Function ユーザーの出力指定に誤りがなければダイアログボックスからフォルダを受け取る _
    (cls購入歴 As Class購入歴データ) As String

    ' 出力に「1」が立っている行がなければExit
    If WorksheetFunction.CountIf(cls購入歴.Column_出力, 1) = 0 Then
        MsgBox ("出力列に「1」が立っている行がありませんでした。")
        Exit Function
    End If
    
    ' フォルダはダイアログボックスで選択してもらう
    ユーザーの出力指定に誤りがなければダイアログボックスからフォルダを受け取る _
        = GetPathダイアログボックスでフォルダを選択する
    If ユーザーの出力指定に誤りがなければダイアログボックスからフォルダを受け取る = "" Then
        MsgBox ("フォルダが指定されませんでしたので、処理を中断します。")
        Exit Function
    End If

End Function

　
処理の流れについての解説は割愛します。

クラスのコンセプトの通り、
このモジュールには「Range・Cells」プロパティが登場しません。

すべて「クラスの返すRangeオブジェクト」で処理を行っています。

これにより、セルアドレス・列番号の指定ミスや、
親シートの取り違えによるRangeの誤取得を防げます。

つまり、カプセル化できている、ということですね。

利便性の点では、今回は出力列に1が立っているかを判定した

If WorksheetFunction.CountIf(cls購入歴.Column_出力, 1) = 0 Then

ここでしか登場しませんでしたが、
列のブロックとWorksheetFunctionの相性の良さはやはり便利です。

みかんの売上ならこう。

みかんの売上 = WorksheetFunction.SumIf(cls購入歴.Column_品物, "みかん" _
                                                         , cls購入歴.Column_売上)

よく使う場合は、クラスのプロパティにしてしまうと、さらに便利になります。

' クラスモジュールでプロパティを作る
Property Get SUMIF商品ごとの売上(商品名 As String) As Long
    SUMIF商品ごとの売上 = _
        WorksheetFunction.SumIf(Column_品物, 商品名, Column_売上)
End Property

' 標準モジュールで使用する
みかんの売上 = cls購入歴.SUMIF商品ごとの売上("みかん")

標準モジュール「汎用関数」

このモジュールは、この関数だけが置いてあるわけではなく、
「今回使わない関数もたくさん書いてある」イメージです。

今回はマクロ高速化のONOFFと、
ダイアログボックスでのフォルダ選択を使用しました、

クラスの説明からは逸れるので、読まなくていいです。

読み飛ばせることも汎用関数化のメリットですので、
ブログでも読み飛ばしてしまってください。
　

Option Explicit

' 自動更新の開始
Function エクセルの自動更新を開始する()
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
        .EnableEvents = True
        .StatusBar = False
        .DisplayAlerts = True
    End With
End Function

' 自動更新の停止
Sub エクセルの自動更新を停止する(ブック計算をOFF As Boolean _
    , Optional 画面更新をOFF As Boolean = True, Optional イベントをOFF As Boolean = True)
    With Application
        If ブック計算をOFF Then Application.Calculation = xlCalculationManual
        If 画面更新をOFF Then Application.ScreenUpdating = False
        If イベントをOFF Then Application.EnableEvents = False
    End With
End Sub

' フォルダの選択
Function GetPathダイアログボックスでフォルダを選択する(Optional Path初期表示フォルダ As String = "") As String
    With Application.FileDialog(msoFileDialogFolderPicker)

        If Path初期表示フォルダ <> "" Then .InitialFileName = Path初期表示フォルダ

        If .Show Then: GetPathダイアログボックスでフォルダを選択する = .SelectedItems(1)
    
    End With
End Function

まとめ

今回は「データシート⇒何かの帳票への印字・出力・印刷」マクロを、
クラスモジュールの基本的な書き方だけを使って作りました。

クラスモジュールの目的は「コードの整理整頓」であり、
そのために最も重要なコンセプトは「カプセル化」です。

このとき、「何をカプセルにしたクラスなのか」という視点で考えることができると、
クラスモジュールの理解が深まると思います

意識してみてください。

おまけ：カプセルから漏れている変数

今回のコードは、コンセプト「Range指定をクラスで完結させる」だったのに、
実はカプセルの外にいるやつがいます。
　

誰だ！？

' 各項目を印字
cls領収書.Cell_No = cls購入歴.Cell_No(R)
cls領収書.Cell_購入日 = cls購入歴.GetDate購入日(R)
cls領収書.Cell_購入者 = cls購入歴.Cell_購入者(R)
cls領収書.Cell_品物 = cls購入歴.Cell_品物(R)
cls領収書.Cell_お支払い = cls購入歴.Cell_売上(R)

　
よーく見るとわかりますね。

そう、「R」です。

今回はデータが1つだったため「R」でよかったのですが、複数のデータを処理する際は行のカウンタがたくさんあることも多く、ここを間違えると取り違えが起きます。

なにより、せっかくのクラスなのに、
引数に同じ変数を連打するのはナンセンスですね。

一度「この行を処理しよう」と決めたら、
基本そこで処理が完結するのがデータテーブルの特徴なので、
ここはクラスに盛り込んでおきたいところです。

この場合は「現在注目している行」をPublic変数で持っておきましょう。

「その変数を行番号に使用したセル」取得プロパティを作ることで、

Dim R As Long
For R = cls購入歴.R1st To cls購入歴.RLast
    
    cls購入歴.現在行 = R

    ' 各項目を印字
    cls領収書.Cell_No = cls購入歴.現在行のCell_No
    cls領収書.Cell_購入日 = cls購入歴.GetDate現在行の購入日
    cls領収書.Cell_購入者 = cls購入歴.現在行のCell_購入者
    cls領収書.Cell_品物 = cls購入歴.現在行のCell_品物
    cls領収書.Cell_お支払い = cls購入歴.現在行のCell_売上

Next

こんな風に(R)を消すことができます。
これで完全にカプセル化されたRangeオブジェクトになりましたね。

「何度も使う＝覚えておいてほしい」変数をプロパティにするというのが、
クラスを設計する上での一つのポイントになります。

何でもかんでもクラスにすると書くのが大変になるので、
実装はマクロの規模やデータの重要度と相談してからになりますが、
ひとつの手法として持っておくといいですね。

クラスのプロパティの命名について

プロパティ名「.現在行のCell」としましたが、
クラスのプロパティ・メソッドは、英字始まりの方がいいです。

自作のクラスでも、VBAの既定のオブジェクトでも、
「.」を打つとプロパティ・メソッドの候補が表示されます。

特に自作クラスはプロパティの候補が少ないので、
「.」＋最初の数文字だけで候補からの入力ができ便利です。

が、「現」などの漢字始まりは、変換するまで候補が出ないため、
サクサクプロパティを選択入力できない弱点があるのです。

よって、クラスのプロパティ・メソッドは、
「英字でシステム名」＋「日本語で具体的な処理内容」
で命名しましょう。

例えば「Delete対象外レコード」のようにしておけば、

• 入力は「.d + Tab」で済む
• 中で実行されるメソッドが「Range.Delete」なのを想像しやすい
• 実際の処理内容は日本語なので誤解なく読みやすい

と、日英双方のメリットを享受できます。

ちなみに今回の「現行の」については、「現在：Current」の頭文字より、
「CurR」「CurCell_売上」あたりで、私は命名しています。

前-現-次は、Prev-Cur-Nextで表現する人が多い気がしますね。

余談ですが、
データベースの話でも「ループ中の現在のレコード」のような機能があり、
「カーソル：Cursor」と呼びます。

頭文字がCurrentと被っていて、どっちの略でも通じるお得感がなんか好き。

私は脳内でカレントと読んでいますが、
DBの知識がある方は、勝手にカーソルと読んでもらってもOKです。