和風スパゲティのレシピ

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

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

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

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


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


帳票は請求書や納品書、職員向けの総務資料などなんでもありですね。

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


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


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

マクロの仕様について

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


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

◇ 領収書
領収書サンプル


この2つのシートが同じブックにあり、
そこにマクロも書いていきます。


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

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

領収書の出力を、

「1」を立てて実行

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


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


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

' ボタンを押して一括実行パターン
Sub 出力列に1を立てたデータの領収書を一括作成する()

    ' データシートを変数に入れておく
    Dim ws購入歴 As Worksheet
    Set ws購入歴 = 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 出力するブック As Workbook
    Set 出力するブック = Workbooks.Add
    
    ' データシートのすべての行をループして
    Dim R As Long
    For R = 5 To データの最終行
        ' 出力列が1のデータを判定
        If ws購入歴.Cells(R, 10) = 1 Then

            ' 帳票テンプレートをコピーして、出力するブックの現在の最終シートへ
            ThisWorkbook.Worksheets("領収書テンプレート").Copy _
                after:=出力するブック.Worksheets(出力するブック.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
    出力するブック.Worksheets(1).Delete
    Application.DisplayAlerts = True
    
    ' メインの処理ここまで(高速化終了)
    Application.ScreenUpdating = True
    
    MsgBox ("領収書の一括出力を完了しました。")

End Sub

 
ボタンにマクロを設置する方法は割愛します。

ダブルクリックで出力するパターン

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


ダブルクリックイベント

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


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

' ダブルクリックしたデータを領収書に出力
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購入歴 = Worksheets("購入歴データ")
    
    ' データエリア
    If R >= 5 Then

        ' 出力列
        If C = 10 Then

            ' 売上列が空でないデータ
            If ws購入歴.Cells(R, 9) > 0 Then
    
                ' 帳票テンプレートを新しいブックにコピー
                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)
                
                ' 立っていた「1」を「済」に
                ws購入歴.Cells(R, 10) = "済"
                    
                MsgBox ("領収書の出力を完了しました。")
                Cancel = True
            End If

        End If
    End If ' データエリア

End Sub

 
シートモジュールの仕組みについては割愛します。




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

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

このマクロを実務に使用する方へ

実務で使えるコードにする場合は、ひとまず

' データから帳票への印字
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) ' 品物

この部分を書き換えれば、
好きな帳票への印字マクロにすることができます。


ちなみにセルアドレスだけ変えても動きますが、
「ws領収書」「ws購入歴」という両シートの変数名を、
実際のデータ、帳票の名前に置換してください。


変数名はしっかりと分かるものにしておかないと、
後で見返したときに訳が分からなくなってしまいますので、
そこだけはサボらないでマクロを作りましょう。

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

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

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


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

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

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

今回の例では「Withステートメント」「シートのオブジェクト名」を活用すると、
↓のように、コードをすっきり書くことができるようになります。

' データシートを変数に入れるのはやめて、オブジェクト名で定義しておく
' これらは廃止 Dim ws購入歴 As Worksheet
'                  Set ws購入歴 = Worksheets("購入歴データ")


' オブジェクト名を定義しておいたシートを、Withステートメントでまとめる
With WS購入歴データ

    ' 最終行はこう変わる
    Dim データの最終行 As Long
    データの最終行 = .UsedRange.Rows.Count + .UsedRange.Row - 1

    ' 印字はこう変わる
    ws領収書.Range("D3") = .Cells(R, 2) ' No
    ws領収書.Range("C7") = .Cells(R, 5) ' 購入者
    ws領収書.Range("F9") = .Cells(R, 9) ' お支払い
    ws領収書.Range("F11") = .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

' 領収書の印字
ws領収書.Range("D3") = .Cells(R, 2) ' No
ws領収書.Range("C7") = .Cells(R, 5) ' 購入者
ws領収書.Range("F9") = .Cells(R, 9) ' お支払い
ws領収書.Range("F11") = .Cells(R, 6) ' 品物

                                        ' ↑これが、↓に変わる

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

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

  • コメントがなくても読める
  • ワークシートを見に行かなくても何列目が何のデータかわかる
  • 変更時に宣言部の1箇所をいじるだけになる

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

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

これを、

「えーっと12列目はJ、K、Lか。ってことは売上だな」

みたいにシートを見に行って読み解くのが面倒だな~と感じている方は、
是非トライしてみてください。


列挙型の入力選択肢

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

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

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

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

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



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


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

私もそうでしたが。


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

ですがVBAは違います。


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


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


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


さすがに
「列見出しを[行列入替貼り付け]で縦にすれば、ほぼ完成するEnum定数」
をサボることは、もうありませんけどね。

今回の「全部1プロシージャのベタ打ちコード」も、普通に役に立ちますよ。


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




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



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



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

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

    ' ↓ Evaluateを使って書き換え

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

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

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


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



あとは、

If WorksheetFunction.CountIf(ws購入歴.Range("J5:J" & データの最終行), 1) = 0 Then

    ' ↓ Evaluateを使って書き換え

If [COUNTIF(J:J,1)] = 0 Then

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


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


人に引き継ぐコードで書くのは、やめてあげてくださいね。