一覧表データから帳票を出力するマクロ

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

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

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

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

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

なお、本コードはVBA初級者の方にも扱いやすいよう、
この処理を動かすための最低限の書き方をしております。

中身を書き換えるだけで他の帳票にも流用できますので、
「データ ⇒ 帳票」をサクッと作りたい方は、
このソースコードを参考にしてください。

マクロの仕様について

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

◇ データシート

◇ 領収書

この2つのシートが同じブックにあり、
そのブックにマクロも書いていくとしましょう。

サンプルに「領収書」を使いましたが、中身をちょっと書き替えれば、「見積書」「請求書」など、いろいろな帳票に使えます。

ボタンを押して一括で出力するパターン

領収書の出力を、

こんなイメージで実行します。

出力列に「1」を立てたすべてのデータが、
別々の領収書シートに印字され、1つのブックにまとめて出力されます。

出力が終わると、出力列の表示が「済」に変わるようにしましょう。

このマクロを実装するためには、
どこでもいいので「標準モジュール」内に以下のコードを書きます。

' ボタンを押して一括実行パターン
Sub 購入歴データの出力列1のデータから領収書を一括作成する()

    Dim wsデータ As Worksheet
    Set wsデータ = ThisWorkbook.Worksheets("購入歴データ")

    Dim データ最終行 As Long
    データ最終行 = wsデータ.UsedRange.Rows.Count + wsデータ.UsedRange.Row - 1

    ' 実行前に1が立っているデータがなければマクロを終了
    If WorksheetFunction.CountIf(wsデータ.Range("J5:J" & データ最終行), 1) = 0 Then
        MsgBox ("出力列に「1」の立っている行がないため、処理を終了します。")
        Exit Sub
    End If
    
    ' メイン処理を開始
    Application.ScreenUpdating = False
    
    ' 出力先のブックを新しく開く（この時できる空のシートは後で消す）
    Dim wb出力先 As Workbook
    Set wb出力先 = Workbooks.Add
    
    ' データシートの出力列が1のデータを対象に処理
    Dim R As Long
    For R = 5 To データ最終行
        If wsデータ.Cells(R, 10) = 1 Then

            ' 帳票のテンプレートを出力ブックの末尾へコピー
            ThisWorkbook.Worksheets("領収書テンプレート").Copy _
                after:=wb出力先.Worksheets(wb出力先.Worksheets.Count)

            ' コピーしたシート(アクティブシート)を印字シートにセット
            Dim ws印字シート As Worksheet
            Set ws印字シート = ActiveSheet

            ' データから帳票への印字
            ws印字シート.Range("D3") = wsデータ.Cells(R, 2) ' No
            ws印字シート.Range("C7") = wsデータ.Cells(R, 5) ' 購入者
            ws印字シート.Range("F9") = wsデータ.Cells(R, 9) ' お支払い
            ws印字シート.Range("F11") = wsデータ.Cells(R, 6) ' 品物

            ' 購入日は計算してから印字（E2が「年度」なので、月が1~3の時は1を足す）
            If ws印字シート.Cells(R, 3) >= 4 Then
                ws印字シート.Range("G3") = DateSerial(Left(wsデータ.Range("E2"), 4) _ 
                                                    , wsデータ.Cells(R, 3), wsデータ.Cells(R, 4))
            Else
                ws印字シート.Range("G3") = DateSerial(Left(wsデータ.Range("E2"), 4) + 1 _
                                                    , wsデータ.Cells(R, 3), wsデータ.Cells(R, 4))
            End If
            
            ' シート名は領収書Noに
            ws印字シート.Name = wsデータ.Cells(R, 2)
            
            ' 立っていた「1」を「済」に
            wsデータ.Cells(R, 10) = "済"
            
        End If
    Next ' データシートのループここまで
    
    ' 最初にブックを出力した際のいらない空のシートを消す
    Application.DisplayAlerts = False
    wb出力先.Worksheets(1).Delete
    Application.DisplayAlerts = True
    
    ' メインの処理ここまで
    Application.ScreenUpdating = True
    
    MsgBox ("領収書の一括出力を完了しました。")

End Sub

マクロの解説

基本的な機能だけを用いていますので、
コード一つ一つの解説は省略します。

実務で使うコードにする場合は、

ThisWorkbook.Worksheets("購入歴データ")
ThisWorkbook.Worksheets("領収書テンプレート")

これらのWorksheetを実際に使うシート名に変更し、

' データから帳票への印字
ws印字シート.Range("D3") = wsデータ.Cells(R, 2) ' No
ws印字シート.Range("C7") = wsデータ.Cells(R, 5) ' 購入者
 ws印字シート.Range("F9") = wsデータ.Cells(R, 9) ' お支払い
ws印字シート.Range("F11") = wsデータ.Cells(R, 6) ' 品物

' 購入日は計算してから印字（E2が「年度」なので、月が1~3の時は1を足す）
If ws印字シート.Cells(R, 3) >= 4 Then
    ws印字シート.Range("G3") = DateSerial(Left(wsデータ.Range("E2"), 4) _ 
                                        , wsデータ.Cells(R, 3), wsデータ.Cells(R, 4))
Else
    ws印字シート.Range("G3") = DateSerial(Left(wsデータ.Range("E2"), 4) + 1 _
                                        , wsデータ.Cells(R, 3), wsデータ.Cells(R, 4))
End If

このあたりの印字コードを書き換えれば、
好きな帳票への印字マクロにすることができます。

使いたい帳票に合わせてカスタマイズしてください。

では次の、
「ダブルクリックで選択したデータから帳票を出力パターン」
の解説に移ります。

ダブルクリックで選択したデータを出力するパターン

続いて、ダブルクリックしたデータを新しいブックに出力するパターンのコードです。

↑この「⇒」をダブルクリックしてもらうイメージです。

このマクロを実装するには、
「購入歴データ」シートのシートモジュールに以下のコードを書きます。

' ダブルクリックしたデータを領収書に出力
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    ' ダブルクリックされたセルの行・列番号・シートを変数に入れておく
    Dim R As Long
    R = Target.Row
    Dim C As Long
    C = Target.Column
    Dim wsデータ As Worksheet
    Set wsデータ = Thisworkbook.Worksheets("購入歴データ")
    
    ' 出力列がダブルクリックされた際、売上が入力済なら実行
    If R >= 5 Then
        If C = 10 Then
            If wsデータ.Cells(R, 9) = 0 Then
                MsgBox("売上が入力されていないデータは出力できません。")
            Else    

                ' 帳票テンプレートを新しいブックにコピーし、印字先にセット
                Worksheets("領収書テンプレート").Copy
                Dim ws印字シート As Worksheet
                Set ws印字シート = ActiveSheet
    
                ' データから帳票への印字
                ws印字シート.Range("D3") = wsデータ.Cells(R, 2) ' No
                ws印字シート.Range("C7") = wsデータ.Cells(R, 5) ' 購入者
                ws印字シート.Range("F9") = wsデータ.Cells(R, 9) ' お支払い
                ws印字シート.Range("F11") = wsデータ.Cells(R, 6) ' 品物
    
                ' 購入日は計算してから印字（E2が年度なので、月が1~3の時は1を足す）
                If ws印字シート.Cells(R, 3) >= 4 Then
                    ws印字シート.Range("G3") = DateSerial(Left(wsデータ.Range("E2"), 4) _
                                             , wsデータ.Cells(R, 3), wsデータ.Cells(R, 4))
                Else
                    ws印字シート.Range("G3") = DateSerial(Left(wsデータ.Range("E2"), 4) + 1 _
                                             , wsデータ.Cells(R, 3), wsデータ.Cells(R, 4))
                End If
                
                ' シート名は領収書Noに
                ws印字シート.Name = wsデータ.Cells(R, 2)
                
                ' 出力した行を「済」に
                wsデータ.Cells(R, 10) = "済"
                    
                MsgBox ("領収書の出力を完了しました。")
                Cancel = True

            End If
        End If
    End If

End Sub

　
書き替え方法は「一括出力」パターンと同じですが、
コードを書く場所が「ダブルクリックを検知するデータシート」のシートモジュールであることに注意してください。

シートモジュールとBeforeDoubleClickイベントの説明は割愛します。

ソースコードについては以上です。

2つのパターンを両方同時に使っても動きますので、
お好きなように実装ください。

このマクロを参考に勉強する方へ

今回のマクロは基本的な書き方だけで構成しました。

わからない単語は読み飛ばしてわかる部分だけ読んでいき、
全体を見終わった後に分からない部分を調べると、
理解が早くなるかもしれません。

さて中身が大体理解できましたら、このコードを進歩させてみましょう。
まずは以下の機能を勉強してみてください。

Worksheetオブジェクトの指定方法

まずは「Worksheetオブジェクトの指定方法」を工夫すると、
コードが簡単にきれいにできます。

今回の例では、まずは何度も出てくる印字シートを「Withステートメント」でまとめることができます。

With ws印字シート
    .Range("D3") = wsデータ.Cells(R, 2) ' No
    .Range("C7") = wsデータ.Cells(R, 5) ' 購入者
    .Range("F9") = wsデータ.Cells(R, 9) ' お支払い
    .Range("F11") = wsデータ.Cells(R, 6) ' 品物
End With

　
また、もう一方の「wsデータ」という変数は、

Dim wsデータ As Worksheet
Set wsデータ = Thisworkbook.Worksheets("購入歴データ")

と「シート名」の指定でセットしていましたが、
データシートに「WS購入歴データ」というオブジェクト名を設定することで、

With ws印字シート
    .Range("D3") = WS購入歴データ.Cells(R, 2) ' No
    .Range("C7") = WS購入歴データ.Cells(R, 5) ' 購入者
    .Range("F9") = WS購入歴データ.Cells(R, 9) ' お支払い
    .Range("F11") = WS購入歴データ.Cells(R, 6) ' 品物
End With

と、変数を介さず、直接シートを指定することもできます。
シートオブジェクト名については調べてみてください。

この二つの活用だけでも、コードが結構キレイになりますね。

やり方も簡単ですし、オブジェクトを扱う練習にもなるので、
まずはここから取り組みましょう。

なお、今回のWorksheetオブジェクトの注意点ですが、
Copyメソッドのあと、作った領収書がActiveSheetになることを活用して、

Range("D3") = WS購入歴データ.Cells(R, 2) ' No
Range("C7") = WS購入歴データ.Cells(R, 5) ' 購入者
Range("F9") = WS購入歴データ.Cells(R, 9) ' お支払い
Range("F11") = WS購入歴データ.Cells(R, 6) ' 品物

こう書こうと思っても、ダブルクリック実行パターンでは失敗します。

Range、Cellsの親Worksheetを省略した場合に、
親が自動的にActiveSheetになるのは「標準モジュール」の話です。

「シートモジュール」内で親シートを省略した場合は、
たとえアクティブが変わっても、「そのモジュールのシート」が親として適用されます。

「ダブルクリックバージョン」でこの書き方をすると、
データシートに印字してしまうバグを起こしてしまうわけですね。

ActiveSheetの省略は手っ取り早くていいですが、
バグの温床でもあるので、しっかりと勉強した上で使っていきましょう。

列番号の定数化

シートの指定がうまくできるようになったら、次は列番号の定数化がおすすめです。

' 列番号を定数として定義(Enum:列挙型定数を活用)
Enum 購入歴データの列
    No = 2
    月
    日
    購入者
    品物
    価格
    個数
    お支払い
    出力
End Enum

' 定数を使わない場合
.Range("D3") = WS購入歴データ.Cells(R, 2) ' No
.Range("C7") = WS購入歴データ.Cells(R, 5) ' 購入者
.Range("F9") = WS購入歴データ.Cells(R, 9) ' お支払い
.Range("F11") = WS購入歴データ.Cells(R, 6) ' 品物

' ↓のコードに書き換えることができる

' 定数を使った場合
.Range("D3") = WS購入歴データ.Cells(R, 購入歴データの列.No)
.Range("C7") = WS購入歴データ.Cells(R, 購入歴データの列.購入者)
.Range("F9") = WS購入歴データ.Cells(R, 購入歴データの列.品物)
.Range("F11") = WS購入歴データ.Cells(R, 購入歴データの列.お支払い)

　
このように、定数を使うと「数字に名前を付けられる」ので、

• コメントがなくても読める
• ワークシートを見に行かなくても何列目が何のデータかわかる
• 改修発生時に、定数を宣言した箇所だけを変えればよくなる

という恩恵が得られます。
　

Cells(R, 12) = Cells(R, 11) - Cells(R, 10)

このようなコードを解読するときに、小学生のように指を折りながら

「えーっと12列目ってことはA,B,C…Lか。ってことは売上だな」

みたいにシートとコードを交互ににらめっこする必要がなくなりますので、
是非習得してみましょう。

こんな風に選んで入力できるため、
読みやすいだけでなく、書くのも速くなります。

よろしければこちらの記事をどうぞ
www.limecode.jp

おまけ：このコードを見ている中級者の方へ

今回のコードを見ている中級者さんがもしいらっしゃったら、

「こんなコードを書いていた時期もあったな～」

と懐かしんでいるかもしれません。

懐かしんでいる方、
実は要注意かもしれませんよ？

コードをキレイに書けるようになってくると、
「読みやすいコード以外は許せない病」を発症する人が出てきます。

私もそうでしたが。

チーム開発の多い他のプログラミング言語では、
それもある程度仕方ないところがあるのかもしれません。

ですがVBAは違います。

ワークシートという超便利なインターフェースを使い、
超速で資料作成ができるという、その手軽さもExcelの魅力です。

そして、この魅力を最大限に生かすには、
「どんな書き方でもいいからコードをいかに早く書き上げるか」
も、なかなか侮れない技術になります。

一緒に公開した「中級編」と今回のコードを比べますと、
コードの質は良くないし、「メンテナンス性」ではもちろん完敗しますが、
コードの総量自体は半分で済んでいます。

Excelの利点を最大限に生かすためにも、
リーダブルフォルム / スピードフォルムを切り替えていける、
ハイブリットプログラマを目指しましょう。

さて「読みやすいコード以外は許せない病」を発症しそう、

そんなあなたに処方箋を。

「Evaluateメソッド」を使うと、印字の部分をこう書き換えることができます。

.Range("D3") = WS購入歴データ.Cells(R, 購入歴データの列.No)
.Range("C7") = WS購入歴データ.Cells(R, 購入歴データの列.購入者)
.Range("F9") = WS購入歴データ.Cells(R, 購入歴データの列.品物)
.Range("F11") = WS購入歴データ.Cells(R, 購入歴データの列.お支払い)

    ' ↓ Evaluateを使って書き換え

.[D3] = WS購入歴データ.Cells(R, 購入歴データの列.No)
.[C7] = WS購入歴データ.Cells(R, 購入歴データの列.購入者)
.[F9] = WS購入歴データ.Cells(R, 購入歴データの列.品物)
.[F11] = WS購入歴データ.Cells(R, 購入歴データの列.お支払い)

　
Evaluateが何なのかは適当に調べてください。

大事なのは、Range("D3")を[D3]と書けることです。

ちなみにシートの指定は、
ws印字シート.[D3]でも、[領収書!D3]でも、どっちでもいけます。

他の活用方法として、

If WorksheetFunction.CountIf(WS購入歴データ.Range("J:J"), 1) = 0 Then

    ' ↓ Evaluateを使って書き換え

If [COUNTIF(J:J,1)] = 0 Then ' ただしActiveSheet限定

という、セルと同じ数式ベタ打ちもいけるという神仕様。

Evaluateメソッド「 [ ] 」を使えば、
帳票印字用のクソコードを高速でプログラミングできます。

人に引き継ぐコードで書くのはやめた方がいいと思いますが、
使い捨てマクロをいかに早く書くかという場面では強い味方になります。

気が向いたら使ってあげてください。