Excel VBA 逆引き集 | 実務テンプレ完全版（超再利用部品） – 帳票生成ツール

Excel VBA
スポンサーリンク

ねらい：請求書・納品書・見積書などの「帳票」をテンプレ＋一覧から量産する

帳票は「毎回ほぼ同じ形で、差し込む中身だけ違うもの」です。
だからこそ、人がコピペで作るとミスりやすく、時間もかかります。

帳票生成ツールのゴールはこうです。

Excel に
「どの帳票テンプレを」「どのデータで」「何枚作って」「どこに保存するか」
を書いておき、VBA で

テンプレコピー → ヘッダ差し込み → 明細差し込み → 印刷設定 → PDF/印刷

までを一括で回す“型”を作ることです。

ここでは、請求書を例にしつつ、納品書・見積書にもそのまま流用しやすい「帳票生成テンプレ」を、初心者向けにかみ砕いて解説します。

設計：テンプレシート＋ヘッダ／明細の差し込みルールをはっきり決める

帳票テンプレシート（Invoice_Template）の設計

まずは「見た目」を決めます。
例えば「Invoice_Template」シートを用意し、こんな感じでセルの役割を固定します。

B2：帳票タイトル（例：請求書）
E2：帳票番号（InvoiceNo）
E3：発行日（IssueDate）
B5：得意先名（CustomerName）
B6：得意先住所（CustomerAddress）
B7：得意先担当者（CustomerPerson）

明細行（複数行）はこうします。

明細ヘッダ：A10～E10
明細データ：A11～E30（最大20行と決めておく）

A列：行番号
B列：品目
C列：数量
D列：単価
E列：金額

ここは自由に変えて良いですが、「どのセルに何を入れるか」は必ずメモしておきます。
VBA はここに向かって「値を流し込むだけ」で動きます。

明細データシート（InvoiceLines）の構成

明細データは、一覧型の別シートに持ちます。
例えば「InvoiceLines」シートを

A列：InvoiceNo
B列：RowNo（行番号）
C列：ItemName
D列：Qty
E列：UnitPrice
F列：Amount（数量×単価。計算式でも良いし、後でVBAで計算しても良い）

という形で揃えます。

帳票一覧シート（InvoiceList）の構成

どの帳票を何枚作るかを管理するシートです。
「InvoiceList」シートを用意し、例えばこうします。

A列：Enable（Y の行だけ生成）
B列：InvoiceNo
C列：CustomerName
D列：CustomerAddress
E列：CustomerPerson
F列：IssueDate
G列：OutputSheetName（帳票シート名。例：INV_0001）
H列：OutputFolder（PDF保存先フォルダ）
I列：OutputFileName（PDFファイル名）
J列：Status（結果）
K列：Message（メッセージ）

この 3 シート（テンプレ／明細／一覧）が揃うと、「帳票生成エンジン」がかなり書きやすくなります。

共通基盤：配列I/O・フォルダ作成・PDF出力のユーティリティ

基本ユーティリティ

' ModReport_Base.bas
Option Explicit

Public Function ReadRegion(ws As Worksheet, Optional topLeft As String = "A1") As Variant
    ReadRegion = ws.Range(topLeft).CurrentRegion.Value
End Function

Public Sub WriteBlock(ws As Worksheet, a As Variant, startCell As String)
    ws.Range(startCell).Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub

Public Sub FormatBlock(ws As Worksheet, startCell As String)
    With ws.Range(startCell).CurrentRegion
        .Columns.AutoFit
        .Borders.LineStyle = xlContinuous
    End With
End Sub

Public Function EnsureFolder(ByVal folderPath As String) As Boolean
    On Error Resume Next
    If Len(folderPath) = 0 Then
        EnsureFolder = False
        Exit Function
    End If
    If Dir(folderPath, vbDirectory) = "" Then
        MkDir folderPath
    End If
    EnsureFolder = (Dir(folderPath, vbDirectory) <> "")
    On Error GoTo 0
End Function

Public Sub ExportSheetToPdf(ByVal ws As Worksheet, ByVal pdfPath As String)
    With ws.PageSetup
        .Orientation = xlPortrait
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .LeftMargin = Application.CentimetersToPoints(1)
        .RightMargin = Application.CentimetersToPoints(1)
        .TopMargin = Application.CentimetersToPoints(1)
        .BottomMargin = Application.CentimetersToPoints(1)
    End With
    
    ws.ExportAsFixedFormat Type:=xlTypePDF, _
                           Filename:=pdfPath, _
                           Quality:=xlQualityStandard, _
                           OpenAfterPublish:=False
End Sub
VB

ここが“土台”です。

帳票ごとに毎回 PDF 出力やフォルダ作成のコードを書くのは無駄なので、ここにまとめておきます。
これで、「帳票側」は「シートを渡すだけ・パスを渡すだけ」で済むようになります。

明細の抽出ロジック：InvoiceNo で InvoiceLines をフィルタして配列にする

1枚の帳票に必要な明細だけを抽出

' ModInvoice_Data.bas
Option Explicit

Public Function GetInvoiceLines(ByVal invoiceNo As String) As Variant
    Dim ws As Worksheet
    Set ws = Worksheets("InvoiceLines")
    
    Dim a As Variant
    a = ReadRegion(ws)
    
    Dim out() As Variant
    ReDim out(1 To 1, 1 To UBound(a, 2))
    
    Dim r As Long
    Dim rows As Long
    rows = 1
    
    Dim c As Long
    For c = 1 To UBound(a, 2)
        out(1, c) = a(1, c)
    Next
    
    For r = 2 To UBound(a, 1)
        If CStr(a(r, 1)) = CStr(invoiceNo) Then
            rows = rows + 1
            ReDim Preserve out(1 To rows, 1 To UBound(a, 2))
            For c = 1 To UBound(a, 2)
                out(rows, c) = a(r, c)
            Next
        End If
    Next
    
    GetInvoiceLines = out
End Function
VB

ここでのポイントは、InvoiceNo一致で単純に全行拾っているだけです。
「1枚の帳票に載る最大行数」は後で制御します（テンプレート側で20行、など）。

行数が多い場合は、
RowNo順に並んでいる前提で、最大20行だけ貼るなどの制御を追加します。

帳票生成の心臓部：テンプレコピー→ヘッダ差し込み→明細差し込み

1枚の請求書を作る Sub（InvoiceList の1行分）

' ModInvoice_BuildOne.bas
Option Explicit

Public Sub BuildOneInvoice(ByVal invoiceNo As String, _
                           ByVal customerName As String, _
                           ByVal customerAddress As String, _
                           ByVal customerPerson As String, _
                           ByVal issueDate As Date, _
                           ByVal templateSheet As String, _
                           ByVal outSheetName As String)
    Dim wsTemplate As Worksheet
    Set wsTemplate = Worksheets(templateSheet)
    
    Dim wsOut As Worksheet
    On Error Resume Next
    Set wsOut = Worksheets(outSheetName)
    On Error GoTo 0
    
    If wsOut Is Nothing Then
        wsTemplate.Copy After:=wsTemplate
        Set wsOut = ActiveSheet
        wsOut.Name = outSheetName
    Else
        wsOut.Cells.Clear
        wsTemplate.UsedRange.Copy Destination:=wsOut.Range("A1")
    End If
    
    wsOut.Range("E2").Value = invoiceNo
    wsOut.Range("E3").Value = issueDate
    wsOut.Range("B5").Value = customerName
    wsOut.Range("B6").Value = customerAddress
    wsOut.Range("B7").Value = customerPerson
    
    Dim lines As Variant
    lines = GetInvoiceLines(invoiceNo)
    
    Dim maxRows As Long
    maxRows = 20
    
    Dim r As Long
    Dim outRow As Long
    outRow = 11
    
    Dim total As Double
    total = 0#
    
    For r = 2 To UBound(lines, 1)
        If r - 1 > maxRows Then Exit For
        
        wsOut.Cells(outRow, 1).Value = lines(r, 2)
        wsOut.Cells(outRow, 2).Value = lines(r, 3)
        wsOut.Cells(outRow, 3).Value = lines(r, 4)
        wsOut.Cells(outRow, 4).Value = lines(r, 5)
        
        Dim amt As Double
        If IsNumeric(lines(r, 6)) Then
            amt = CDbl(lines(r, 6))
        Else
            amt = CDbl(lines(r, 4)) * CDbl(lines(r, 5))
        End If
        wsOut.Cells(outRow, 5).Value = amt
        
        total = total + amt
        outRow = outRow + 1
    Next
    
    wsOut.Range("E32").Value = total
    
    wsOut.Range("A11:E30").NumberFormatLocal = "@"
    wsOut.Range("C11:D30").NumberFormatLocal = "#,##0"
    wsOut.Range("E11:E30").NumberFormatLocal = "#,##0"
    wsOut.Range("E32").NumberFormatLocal = "#,##0"
End Sub
VB

ここが帳票生成の“心臓”です。

テンプレートのどのセルに何を置くかは、テンプレ設計で決めた通りにコードに書きます。
行数が足りない場合（最大20行）は、その時点で打ち切ります。
金額は明細にすでに金額列があればそれを使い、なければ「数量×単価」で計算する二段構えにしています。

この Sub は「1枚の請求書を作るだけ」です。
一覧とつなげるのは次のステップです。

一括帳票生成：InvoiceList をループして BuildOneInvoice を繰り返す

帳票一覧から一気にシート＋PDFを生成するマクロ

' ModInvoice_Bulk.bas
Option Explicit

Public Sub Run_InvoiceBatch()
    Dim wsList As Worksheet
    Set wsList = Worksheets("InvoiceList")
    
    Dim a As Variant
    a = wsList.Range("A1").CurrentRegion.Value
    
    Dim r As Long
    For r = 2 To UBound(a, 1)
        Dim enable As String
        enable = UCase$(Trim$(CStr(a(r, 1))))
        
        If enable = "Y" Then
            Dim invoiceNo As String
            Dim custName As String
            Dim custAddr As String
            Dim custPerson As String
            Dim issueDate As Date
            Dim outSheet As String
            Dim outFolder As String
            Dim outFile As String
            
            invoiceNo = CStr(a(r, 2))
            custName = CStr(a(r, 3))
            custAddr = CStr(a(r, 4))
            custPerson = CStr(a(r, 5))
            issueDate = CDate(a(r, 6))
            outSheet = CStr(a(r, 7))
            outFolder = CStr(a(r, 8))
            outFile = CStr(a(r, 9))
            
            On Error GoTo ErrHandler
            
            If Not EnsureFolder(outFolder) Then
                wsList.Cells(r, 10).Value = "NG"
                wsList.Cells(r, 11).Value = "フォルダ作成失敗: " & outFolder
                GoTo NextRow
            End If
            
            Call BuildOneInvoice(invoiceNo, custName, custAddr, custPerson, issueDate, _
                                 "Invoice_Template", outSheet)
            
            Dim pdfPath As String
            pdfPath = outFolder & "\" & outFile & ".pdf"
            
            Call ExportSheetToPdf(Worksheets(outSheet), pdfPath)
            
            wsList.Cells(r, 10).Value = "OK"
            wsList.Cells(r, 11).Value = "作成: " & pdfPath
            
            GoTo NextRow
ErrHandler:
            wsList.Cells(r, 10).Value = "NG"
            wsList.Cells(r, 11).Value = "エラー: " & Err.Number & " - " & Err.Description
            Err.Clear
        End If
        
NextRow:
    Next r
    
    MsgBox "帳票の一括生成が完了しました。", vbInformation
End Sub
VB

ここでの大事なポイントを整理します。

Enable=Y の行だけ処理することで、「今回はこの帳票だけ再出力」などの制御が簡単にできます。
帳票生成とPDF出力を1行ごとに完結させることで、途中で1件エラーが出ても、他の帳票は生成し続けられます。
結果とメッセージを Status/Message に書き込むことで、「どの帳票が作れたか／どこでエラーが出たか」が一目で分かります。

テンプレートが「Invoice_Template」以外に増えた場合（納品書テンプレなど）は、InvoiceList にテンプレシート名の列を追加して BuildOneInvoice の引数に渡す形にすれば、同じ仕組みで量産できます。

例題：請求書・納品書・見積書を「同じ仕組み」で回すイメージ

ここまで作れたら、あとは「帳票の種類」を1列増やすだけで、世界が広がります。

InvoiceList に「FormType」列を追加し、

FormType = “INVOICE” → Invoice_Template
FormType = “DELIVERY” → Delivery_Template
FormType = “QUOTE” → Quote_Template

のようにマッピングすれば、Run_InvoiceBatch を少し改造するだけで「帳票種別ごとの生成」ができます。

さらに、OutputFolder と OutputFileName に「種別＋番号＋年月」などを組み合わせれば、
帳票の保存ルールも Excel 上でコントロールできます。

落とし穴と対策（重要ポイントの深掘り）

テンプレのセル位置とコードをズラしてしまう事故

帳票の見た目を変えたくなって、テンプレシートのセル位置を変えると、VBA側の差し込み位置との整合が崩れます。

これを防ぐには、

テンプレを変える前に「どのセルに何が入るか」を一覧化しておく。
VBAの中にもコメントで「B5=得意先名、B6=住所…」と書いておく。
大きくレイアウトを変える場合は、一旦コピーして「Invoice_Template_v2」などを作り、新しいマクロを紐づける。

という丁寧さが大事です。

明細行数オーバー（20行を超える場合）

今のコードでは「最大20行で打ち切り」です。
20行を超える明細をどう扱うかは、運用設計の話になります。

2ページ目・3ページ目を作るか、
別の帳票形式（集約版など）に誘導するか、
いずれにしても「これを超えたらどうする」を先に決めておく必要があります。

とりあえずは「最大N行まで」「超えたらNGにして Status に書く」でも構いません。
現場の要望に合わせて後から「2ページ目を作る」ロジックを追加してもOKです。

明細の並び順（RowNo と実際の順番が合わない）

GetInvoiceLines は「元シートの順番のまま」拾っています。
CSV取り込みやDB連携の関係で順番が保証できない場合は、

Excel側であらかじめ RowNo でソートしておく
または VBA側で RowNo でソートしてから貼り付ける

いずれかを入れておくと安心です。

手動編集と自動生成の“境界”をどうするか

帳票生成後に、手書きで1行だけ追記したり、メモを書き足したりしたい場面もあります。

そこは運用で、

自動生成したシートは「原本」とし、手書き修正する場合はコピーを作ってそちらを書き換える
または、「再生成されると上書きされる」ことを明示し、修正が必要なら元データ側（InvoiceLines／InvoiceList）を修正して再生成する

といったルールを決めておくと混乱が少なくなります。

まとめ：テンプレを“型”とし、一覧シートと明細シートから帳票を量産する

帳票生成ツールの本質は、

帳票テンプレ（見た目）
帳票一覧（誰に・何を・どこへ）
明細一覧（どの帳票にどの行が属するか）

をきちんと切り分けて、VBA がそこをつなぐだけの“配達役”になることです。

一度この「型」ができれば、

請求書 → 納品書 → 見積書 → 作業報告書 → 発注書…

と、テンプレと一覧を差し替えるだけで、どんどん新しい帳票を増やしていけます。

スポンサーリンク
タイトルとURLをコピーしました