和風スパゲティのレシピ

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

トップ > 完成品マクロ > 帳票出力マクロを簡単に書くためのクラスを作る

帳票出力マクロを簡単に書くためのクラスを作る

完成品マクロ クラスモジュール サンプルコード VBA

「処理ごとの解説ではなく、完成品の全体が見たい！」

派の方のための、完成品マクロのサンプルコードです。


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


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

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


本コードでは、クラスモジュールを活用した上で、

「適当なデータと帳票を渡されたときに、
　いかにスピーディーに即席出力ツールを作れるか」

をテーマに、

「このクラスを用意しておけば、あとちょっと標準モジュールを書くだけで帳票出力マクロが完成する」

ようなクラスを設計します。


クラスの知識がなくても、活用することができる書き方をしていますので、

クラスモジュールの作り方の参考にするなり、
中身を見ずにコピペして使うなり、

お好きな方法でご活用ください。

マクロの仕様とクラスの設計について

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

◇ データシート
データシート

◇ 領収書
領収書サンプル

出力列が「1」のものを、1つのブックにまとめて出力します。


冒頭で書いた通り、このデータと帳票の組の専用クラスではなく、
どんなデータと帳票の組にも使えるクラスを設計しておき、

「あとはちょっと標準モジュールを書くだけ」

を目指してクラスを作ります。

標準モジュール

まずはその「ちょっとだけ」の標準モジュールを掲載します。

Option Explicit

Sub 購入歴データから領収書を出力する()
    With New Class一覧表→帳票出力
        
        Set .TplWS帳票テンプレート = Worksheets("領収書テンプレート")
        Set .Range一覧表データ = Worksheets("購入歴データ").AutoFilter.Range
        
        Call .Add帳票アドレスとデータ列の紐づけ("D3", "No")
        Call .Add帳票アドレスとデータ列の紐づけ("C7", "購入者")
        Call .Add帳票アドレスとデータ列の紐づけ("F9", "売上")
        Call .Add帳票アドレスとデータ列の紐づけ("F11", "品物")
        Call .Add帳票アドレスとデータ列の紐づけ("G3", "購入日")
        
        Call .Set出力するレコードの条件を設定する("出力", 1)
        
        Call .CreateWB設定された帳票出力を実行する

    End With
End Sub

　
これだけでOKです。


このプロシージャを実行すると、

表紙のシート

という表紙を第1シートに、
第2シート以降に領収書が続くブックが出力されます。


クラスに渡している引数を見るとなんとなくわかると思いますが、

  • 帳票のテンプレートシート(これをコピーして出力)
  • 印字するデータが搭載されている一覧表のセル範囲(Rangeオブジェクト)
  • 帳票のアドレスと、そこに出力するデータの見出しの組

を登録していくと、
その設定の通りに帳票を出力してくれるクラスです。


全データを出力せず、出力するデータを絞る場合は、
「どの列の何のデータを出力するか」を設定でき、
↑の例では「出力列」が「1」のデータが印字されます。

例えば、

Call .Set出力するレコードの条件を設定する("品物", "みかん")

これなら、「E列がみかんのデータ」が帳票に出力されるプログラムになります。


適当なデータと帳票を渡されたときに、

  1. クラスモジュールをコピーして持ってくる
  2. この標準モジュールのテンプレートもコピーしてくる
  3. 引数（黄色いハイライトの部分）を書き換える

だけで出力ツールが完成するので、
まずまずのスピードが出せるクラスではないでしょうか。


また、このクラスの利点として、「見出し名」をセットするため、

  • 購入歴データの列順などが変更されても、改修の必要なし
  • 必要な見出し名がそろっていれば、複数のデータで同じコードを使用可能

この辺も結構便利なポイントになります。


レイアウトの違う複数のデータから帳票を出力するときのために、

Call .Add帳票アドレスとデータ列の紐づけ("D3", "No")
Call .Add帳票アドレスとデータ列の紐づけ("C7", "購入者")
Call .Add帳票アドレスとデータ列の紐づけ("F9", "お支払い")
Call .Add帳票アドレスとデータ列の紐づけ("F9", "売上") ' ← アドレスダブり
Call .Add帳票アドレスとデータ列の紐づけ("F11", "商品名")
Call .Add帳票アドレスとデータ列の紐づけ("F11", "品物") ' ← これもダブり
Call .Add帳票アドレスとデータ列の紐づけ("G3", "購入日")
.Min条件_必要アドレス数 = 5 ' ← 最終的に何個になればいいのかを登録しておける

このように同じアドレスをたくさん読み込ませることができ、
先に見つかった見出しが採用されます。


見つからなかった見出しはスキップされますが、
必須のデータが欠落しないよう、「Min条件」のプロパティを設定しておけます。

紐づけ完了数がこのMin条件を下回る場合は、
警告が出て、帳票は出力されずに終了します。



という仕様のクラスです。


コピペして使いたいだけの方は、後述のクラスモジュールは書き換えずにそのまま使い、
↑の標準モジュールだけを仕様通りに書き換えて使ってください。

それができるというのも、クラスモジュールの存在意義ですからね。


ついでですが、クラスを1つのプロシージャでだけ使う場合は、
クラス用の変数を用意せず、「With New」とやれば、
Withステートメントを変数代わりに使うことができます。

今回のような汎用クラスにはもってこいの機能ですので、
是非ご活用ください。

クラスモジュール「Class一覧表→帳票出力」

コピペだけしたい方は、クラスモジュールを挿入し、
クラスモジュールの名前「Class1」などを、↑の名前に変更してください。

また、「Microsoft Scripting Runtime」の参照設定も行ってください。
やり方はググればすぐ出てきますし、操作は超簡単です。


中身についての解説ですが、せっかくなので割愛します。

コードの目的（↑の仕様の通り）がわかっていて、
変数・メソッド名が日本語であれば、説明は不要。

という本サイトのテーマを、ゴリ押ししていきます。


1つ読む前にお伝えしたいのが、

  • 「Shift + F2」で、その変数・関数を定義している場所に飛ぶ
  • 「Ctrl + Shift + F2」で、飛ぶ前のさっきの場所に戻る

というショートカットキーです。


「この変数のDimはどこや」「このCallのSubはどこや」
を便利にするショートカットキーですが、

特にクラスのように細かくプロシージャが分かれているものは、
このショートカットを知らないとマウスのホイールが持ちません。


よく読みたい方は、ブログ上ではなくVBE上にコピーして、
↑のショートカットを駆使しながら、プロシージャの結びつきを読み解くのをおすすめます。


ではソースコードに入ります。

Option Explicit

' 帳票と一覧表データを紐づける連想配列
' Keyに帳票のセルアドレスを、Itemに一覧表データの列番号を記憶
Public Dicアドレスと列番号のペア As Dictionary

Public Min条件_必要アドレス数 As Long

Public TplWS帳票テンプレート As Worksheet
Public ws印字帳票 As Worksheet

Public Range一覧表データ As Range

Public CNo出力対象の判定列 As Long
Public Key出力対象の判定値 As Variant

Property Get ws一覧表データ() As Worksheet
    Set ws一覧表データ = Range一覧表データ.Parent
End Property

Property Get Column出力判定列() As Range
    Set Column出力判定列 = Intersect(Range一覧表データ _
        , ws一覧表データ.Columns(CNo出力対象の判定列))
End Property

Sub Add帳票アドレスとデータ列の紐づけ(セルアドレス As String, 列番号または見出し名 As String)

    If Not Is入力セルアドレスが正しい文字列(セルアドレス) Then
        MsgBox ("「" & セルアドレス & "」は正しいアドレスではありません。")
        Exit Sub
    End If

    Dim 列番号 As Long
    
    If IsNumeric(列番号または見出し名) Then
        列番号 = 列番号または見出し名
    Else
        On Error Resume Next
        列番号 = Range一覧表データ.Rows(1).Find _
            (列番号または見出し名, lookat:=xlWhole).Column
        On Error GoTo 0
    End If
    
    If 列番号 > 0 Then Call Addアドレスと列番号のペア(セルアドレス, 列番号)
    
End Sub

Private Sub Addアドレスと列番号のペア(セルアドレス As String, 列番号 As Long)
    If Not Dicアドレスと列番号のペア.Exists(セルアドレス) Then
        Dicアドレスと列番号のペア.Add セルアドレス, 列番号
    End If
End Sub

Private Function Is入力セルアドレスが正しい文字列(アドレステキスト As String) As Boolean
    On Error Resume Next
    Is入力セルアドレスが正しい文字列 _
        = (TplWS帳票テンプレート.Range(アドレステキスト).Cells.Count = 1)
End Function

Sub Output一覧表データの指定行を帳票へ印字する(R As Long)
    With Dicアドレスと列番号のペア
    
        Dim i As Long
        For i = 0 To .Count - 1
            ws印字帳票.Range(.Keys(i)) = ws一覧表データ.Cells(R, .Items(i))
        Next
        
    End With
End Sub

Sub Set出力するレコードの条件を設定する(列番号または見出し名 As String, 判定値 As Variant)

    Key出力対象の判定値 = 判定値
    
    If IsNumeric(列番号または見出し名) Then
        CNo出力対象の判定列 = 列番号または見出し名
    Else
        On Error Resume Next
        CNo出力対象の判定列 = Range一覧表データ.Rows(1).Find _
            (列番号または見出し名, lookat:=xlWhole).Column
        On Error GoTo 0
    End If

End Sub

' 全プロパティを想定通り使用した場合のメインメソッド
Function CreateWB設定された帳票出力を実行する _
    (Optional is終了メッセージを表示する As Boolean = True) As Workbook
    
    If GetMsgText実行不可の警告 <> "" Then
        If is終了メッセージを表示する Then MsgBox (GetMsgText実行不可の警告)
        Exit Function
    End If
    
    Dim wb出力ブック As Workbook
    Set wb出力ブック = CreateWB新規ブックを出力して第1シートに実行内容の概要を記載する
    
    Dim 出力判定セル As Range
    For Each 出力判定セル In Column出力判定列.Cells
        
        If 出力判定セル.Value = Key出力対象の判定値 Then
            
            Call CreateWS帳票テンプレートを新しいシートにコピーする(wb出力ブック)
            
            Call Output一覧表データの指定行を帳票へ印字する(出力判定セル.Row)
        
            ws印字帳票.Name = wb出力ブック.Worksheets.Count - 1
            
        End If
    Next
    
    Set CreateWB設定された帳票出力を実行する = wb出力ブック
    If is終了メッセージを表示する Then MsgBox ("帳票の出力を完了しました。")

End Function

Sub CreateWS帳票テンプレートを新しいシートにコピーする(Optional 出力先ブック As Workbook)
    
    If 出力先ブック Is Nothing Then
        TplWS帳票テンプレート.Copy
    Else
        TplWS帳票テンプレート.Copy after:=出力先ブック.Worksheets(出力先ブック.Worksheets.Count)
    End If
    
    Set ws印字帳票 = ActiveSheet
    
End Sub

Function CreateWB新規ブックを出力して第1シートに実行内容の概要を記載する() As Workbook

    Set CreateWB新規ブックを出力して第1シートに実行内容の概要を記載する = Workbooks.Add
    With CreateWB新規ブックを出力して第1シートに実行内容の概要を記載する.Worksheets(1)
    
        .Name = "概要"
        .Cells(2, 2) = "出力した帳票の概要"
        .Cells(4, 2) = "使用したテンプレート：": .Cells(4, 3) = TplWS帳票テンプレート.Name
        .Cells(5, 2) = "参照したブック：": .Cells(5, 3) = ws一覧表データ.Parent.Name
        .Cells(6, 2) = "参照したシート：": .Cells(6, 3) = ws一覧表データ.Name
        .Cells(7, 2) = "出力したデータ：": .Cells(7, 3) = _
            "列「" & Column出力判定列.Cells(1) & "」が「" & Key出力対象の判定値 & "」だったデータ"
            
        .Columns(2).Resize(, 2).AutoFit
        
    End With
End Function

Property Get GetMsgText実行不可の警告() As String

    If Key出力対象の判定値 <> "" And CNo出力対象の判定列 = 0 Then
        GetMsgText実行不可の警告 = "出力列の指定が正しくありません。"
        Exit Property
    End If
        
    If CNo出力対象の判定列 > 0 Then
        If WorksheetFunction.CountIf(Column出力判定列, Key出力対象の判定値) = 0 Then
            GetMsgText実行不可の警告 = "出力対象のデータがありません。"
            Exit Property
        End If
    End If
    
    If Dicアドレスと列番号のペア.Count < Min条件_必要アドレス数 Then
        GetMsgText実行不可の警告 = "紐づけできた帳票のアドレス数が足りません。"
        Exit Property
    End If
    
End Property

Private Sub Class_Initialize()
    Set Dicアドレスと列番号のペア = New Dictionary
End Sub

　
以上です。


各パーツを細かく分けて作っていますので、メインメソッドである
「Function CreateWB設定された帳票出力を実行する」
をCallせず、標準モジュールでちまちまコーディングすることも可能です。

メインメソッドの中身を参考に、マクロをカスタマイズしてみてください。

クラスの中身をいじるのではなく、
クラスのプロパティとメソッドの呼び方を変えるだけでも、
結構自由にマクロを作れるくらいにはパーツ化してあると思います。

まとめ

今回は「データシート⇒何かの帳票への印字・出力・印刷」マクロを、
なるべく便利になるように作ってみました。

クラスモジュールの参考にするなり、
中身を見ずにコピペして使うなり、
お好きなように使ってください。


「クラスモジュールの中身は見ずに、仕様だけ読んで標準モジュールを書く」

ができることも、クラスモジュール(カプセル化)の意義です。


クラスを自分で作る場合は、これができるようにという視点でクラスを作ると、
「どこまでをクラスモジュールに、どこからを標準モジュールに書くか」
をイメージしやすくなると思います。

是非意識してみてください。

補足記事

以下は補足と、その詳細記事へのリンクです。


まずはクラスの中身がPublic変数だらけですが、
これは「いつでも変更していいプロパティ」という意味ではなく、
単に面倒だからです。


理想的にはPropertyプロシージャで書いた方がいいものもありますが、
堅牢性より作成効率が求められる場面で使うクラスということで、省いています。


そもそもメインのディクショナリーにも、クラス外からアクセスを許してますが、
だからこそ、いざとなったらクラスを書き換えずに、ディクショナリーを無理やりいじるマクロも組めるメリットでもあります。


クラスにPropertyは必須と思いがちですが、全部Pulic変数で作っても、
堅牢でないだけで、実は問題はありません。

サボれるところはしっかりサボると、クラスを作るのが億劫でなくなります。


堅牢にすべきところだけを隠蔽すればOK。

今回の例では、「見出し名の打ち間違い」と「セルアドレスが存在しない文字列」については、しっかりと堅牢に書いています。


詳しく読みたい方はこちら

www.limecode.jp


次に各メソッド・プロパティですが、
「システム上の仕様を表す英字」＋「実際の処理内容を表す日本語」
でなるべく命名しています。

これを「アプリケーションハンガリアン記法」と呼びます。


この記法で書く一番の目的は、「日本語入力が面倒」だからです。


クラスの入力は、「.」+次の1文字である程度予測入力ができますが、
1文字目が漢字だと変換するまで候補が出ません。

というかそもそも「.」も日本語入力だと打ちづらい(笑)

これを解消するために、各識別子を英字から始めています。


クラスに限らず、この記法を使えば、
日本語入力をONにせずに、日本語変数を扱うことができます。

アプリケーションハンガリアン日本語変数の入力アニメ
※ gif用意するのが面倒だったので、サンプルはクラスのものではないです


もう一つのメリットとして、

  • システム上の仕様は英字部分を読む
  • 処理の流れは日本語部分を読む

ができるのも大きいです。
これも意識して読んでみてください。

このメリットを享受するために、

TplWS帳票テンプレート

みたいな、「腹痛が痛い」変数名も使いますが、気にしないでください。

TplWS帳票

より分かりやすいのです。


「入力が面倒」を克服した日本語は最強です。
ぜひ採用のご検討を。

詳しく読みたい方はこちら
www.limecode.jp