(cache)エクセルVBAでテーブルと列挙体を組み合わせると最強のコードが書ける理由

photo credit: Sheep”R”Us Wedding decorations via photopin (license)

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

エクセルVBAでテーブルを活用して請求書を作成するマクロを作成しています。

前回の記事はコチラ。

エクセルVBAで請求書作成マクロをテーブルを活用して作る

エクセルVBAで請求書作成マクロのテーブルを活用したバージョンを作成しています。今回は、請求書に必要な情報を転記する、ファイル名を決めて別名で保存して閉じるなどの処理を追加し、仕上げをして完成をさせます。

もう、完成しちゃいました。

で、なぜ続きを書いているかというと、テーブルと列挙体の組み合わせが最強だからです。

なんじゃそりゃ？って感じですよね。列挙体を取り入れると、請求書マクロの可読性とメンテナンス性が一気に上るんです。

ということで本記事では、エクセルVBAを使う際にテーブルと列挙体とを組み合わせるメリットについてお伝えしていきますよ。

では、行ってみましょう。

前回のおさらい
マジックナンバーを避けるべき理由
テーブルの列挙体を定義する
列挙体を使った請求書作成マクロ
1. テーブルの列番号を列挙体で表すメリット
まとめ
連載目次：エクセルVBAでテーブルを活用した請求書マクロを作る

前回のおさらい

では、まず前回のおさらいからです。

作成したコードはコチラ。

Sub 請求書作成()

Dim tbData As ListObject: Set tbData = wsData.ListObjects("請求データ")
Dim tbClient As ListObject: Set tbClient = wsData.ListObjects("取引先マスタ")
Dim rowClient As ListRow

For Each rowClient In tbClient.ListRows
    Dim client As String: client = rowClient.Range(1).Value
    wsTemplate.Copy
    
    Dim wb As Workbook: Set wb = ActiveWorkbook
    With ActiveSheet
        .Name = client
        Dim tbTemplate As ListObject: Set tbTemplate = .ListObjects("ひな形データ")
        
        Dim i As Long
        For i = 1 To tbData.ListRows.Count
            Dim rowData As ListRow: Set rowData = tbData.ListRows(i)
            If rowData.Range(2).Value = client Then
                With tbTemplate.ListRows.Add
                    .Range(1).Value = rowData.Range(3).Value
                    .Range(2).Value = rowData.Range(4).Value
                    .Range(3).Value = rowData.Range(5).Value
                End With
            End If
        Next i

        Dim total As Long: total = .Range("D21").Value + .Range("D22").Value
        .Range("A19").Value = "ご請求金額: " & Format(total, "#,##0") & " 円"
        
        .Range("A3").Value = client & " 御中"
        .Range("A5").Value = rowClient.Range(2).Value
        .Range("A6").Value = rowClient.Range(3).Value
        .Range("A7").Value = rowClient.Range(4).Value

    End With
     
    Dim fileName As String
    fileName = ThisWorkbook.Path & "\" & Format(Date, "yyyymm") & "請求書_" & client & ".xlsx"
    wb.SaveAs fileName
    wb.Close
    
Next rowClient

End Sub

Sub 請求書作成()

Dim tbData As ListObject: Set tbData = wsData.ListObjects("請求データ")

Dim tbClient As ListObject: Set tbClient = wsData.ListObjects("取引先マスタ")

Dim rowClient As ListRow

For Each rowClient In tbClient.ListRows

Dim client As String: client = rowClient.Range(1).Value

wsTemplate.Copy

Dim wb As Workbook: Set wb = ActiveWorkbook

With ActiveSheet

.Name = client

Dim tbTemplate As ListObject: Set tbTemplate = .ListObjects("ひな形データ")

Dim i As Long

For i = 1 To tbData.ListRows.Count

Dim rowData As ListRow: Set rowData = tbData.ListRows(i)

If rowData.Range(2).Value = client Then

With tbTemplate.ListRows.Add

.Range(1).Value = rowData.Range(3).Value

.Range(2).Value = rowData.Range(4).Value

.Range(3).Value = rowData.Range(5).Value

End With

End If

Next i

Dim total As Long: total = .Range("D21").Value + .Range("D22").Value

.Range("A19").Value = "ご請求金額: " & Format(total, "#,##0") & " 円"

.Range("A3").Value = client & " 御中"

.Range("A5").Value = rowClient.Range(2).Value

.Range("A6").Value = rowClient.Range(3).Value

.Range("A7").Value = rowClient.Range(4).Value

End With

Dim fileName As String

fileName = ThisWorkbook.Path & "\" & Format(Date, "yyyymm") & "請求書_" & client & ".xlsx"

wb.SaveAs fileName

wb.Close

Next rowClient

End Sub

ちょいと長いですが、簡単に解説をしますね。

以下の「データ」シート（オブジェクト名は「wsData」）の「取引先マスタ」テーブルにある取引先の分だけ請求書を作ります。

それで、上記の「請求データ」テーブルの各データを各取引先ごとの請求書に転記していきたいんですね。

そして、その転記をする先となるひな形が以下の「ひな形」シート（オブジェクト名は「wsTemplate」）です。

マジックナンバーを避けるべき理由

上記のプログラムで十分なのですが、もうちょっと良くしていきたいんですね。

どう良くしていきたいか…例えば、前述のプログラムの20～24行目をご覧ください。

With tbTemplate.ListRows.Add
    .Range(1).Value = rowData.Range(3).Value
    .Range(2).Value = rowData.Range(4).Value
    .Range(3).Value = rowData.Range(5).Value
End With

With tbTemplate.ListRows.Add

.Range(1).Value = rowData.Range(3).Value

.Range(2).Value = rowData.Range(4).Value

.Range(3).Value = rowData.Range(5).Value

End With

はい、ここだけ見て、何をどこに転記しているか、わかりますか？

わかりませんよね？

何が悪さをしているかというと、「数字」です。

1とか2とか3とか4とか書かれても、それが何者なのか、実際のテーブルの様子を見ないとわからないのです。

このように、コード内にベタ打ちで書かれた数値を「マジックナンバー」といいます。

マジックナンバーは数値以外の情報を持たないので、可読性を低下させる要因になりがち…、ですから基本的に避けるべきです。

それを防ぐ方法として、列挙体を使うという方法が有効です。

テーブルの列挙体を定義する

さて、列挙体の定義の方法をお伝えします。

モジュールの宣言セクションで以下のように定義をします。

Enum 列挙体名
　メンバー名1[ = 値1]
　メンバー名2[ = 値2]
　…
End Enum

これを宣言しておくことで、以下のように各メンバーの値を取り出すことができます。

列挙体名.メンバー名

詳しい定義の仕方は以下の記事をご覧くださいね。

エクセルVBAでシートの列の挿入も簡単に対応できちゃう列挙体の使い方

エクセルVBAで請求データ一覧から請求書を自動で作成する方法のシリーズ。今回は、Enumステートメントで定義する「列挙体」というものを使って、シートの列の挿入も簡単に対応する方法をお伝えします。

列挙体を使った請求書作成マクロ

では、列挙体を活用した場合に、請求書作成マクロがどのようなコードになるか紹介しましょう。

こうなります。

Enum eData
    納品日 = 1
    取引先名
    品目
    単価
    数量
    価格
End Enum

Enum eClient
    取引先名 = 1
    郵便番号
    住所1
    住所2
End Enum

Enum eTemplate
    品目 = 1
    単価
    数量
    価格
End Enum

Sub 請求書作成()

Dim tbData As ListObject: Set tbData = wsData.ListObjects("請求データ")
Dim tbClient As ListObject: Set tbClient = wsData.ListObjects("取引先マスタ")
Dim rowClient As ListRow

For Each rowClient In tbClient.ListRows
    Dim client As String: client = rowClient.Range(eClient.取引先名).Value
    wsTemplate.Copy
    
    Dim wb As Workbook: Set wb = ActiveWorkbook
    With ActiveSheet
        .Name = client
        Dim tbTemplate As ListObject: Set tbTemplate = .ListObjects("ひな形データ")
        
        Dim i As Long
        For i = 1 To tbData.ListRows.Count
            Dim rowData As ListRow: Set rowData = tbData.ListRows(i)
            If rowData.Range(eData.取引先名).Value = client Then
                With tbTemplate.ListRows.Add
                    .Range(eTemplate.品目).Value = rowData.Range(eData.品目).Value
                    .Range(eTemplate.単価).Value = rowData.Range(eData.単価).Value
                    .Range(eTemplate.数量).Value = rowData.Range(eData.数量).Value
                End With
            End If
        Next i

        Dim total As Long: total = .Range("D21").Value + .Range("D22").Value
        .Range("A19").Value = "ご請求金額: " & Format(total, "#,##0") & " 円"
        
        .Range("A3").Value = client & " 御中"
        .Range("A5").Value = rowClient.Range(eClient.郵便番号).Value
        .Range("A6").Value = rowClient.Range(eClient.住所1).Value
        .Range("A7").Value = rowClient.Range(eClient.住所2).Value

    End With
     
    Dim fileName As String
    fileName = ThisWorkbook.Path & "\" & Format(Date, "yyyymm") & "請求書_" & client & ".xlsx"
    wb.SaveAs fileName
    wb.Close
    
Next rowClient

End Sub

Enum eData

納品日 = 1

取引先名

品目

単価

数量

価格

End Enum

Enum eClient

取引先名 = 1

郵便番号

住所1

住所2

End Enum

Enum eTemplate

品目 = 1

単価

数量

価格

End Enum

Sub 請求書作成()

Dim tbData As ListObject: Set tbData = wsData.ListObjects("請求データ")

Dim tbClient As ListObject: Set tbClient = wsData.ListObjects("取引先マスタ")

Dim rowClient As ListRow

For Each rowClient In tbClient.ListRows

Dim client As String: client = rowClient.Range(eClient.取引先名).Value

wsTemplate.Copy

Dim wb As Workbook: Set wb = ActiveWorkbook

With ActiveSheet

.Name = client

Dim tbTemplate As ListObject: Set tbTemplate = .ListObjects("ひな形データ")

Dim i As Long

For i = 1 To tbData.ListRows.Count

Dim rowData As ListRow: Set rowData = tbData.ListRows(i)

If rowData.Range(eData.取引先名).Value = client Then

With tbTemplate.ListRows.Add

.Range(eTemplate.品目).Value = rowData.Range(eData.品目).Value

.Range(eTemplate.単価).Value = rowData.Range(eData.単価).Value

.Range(eTemplate.数量).Value = rowData.Range(eData.数量).Value

End With

End If

Next i

Dim total As Long: total = .Range("D21").Value + .Range("D22").Value

.Range("A19").Value = "ご請求金額: " & Format(total, "#,##0") & " 円"

.Range("A3").Value = client & " 御中"

.Range("A5").Value = rowClient.Range(eClient.郵便番号).Value

.Range("A6").Value = rowClient.Range(eClient.住所1).Value

.Range("A7").Value = rowClient.Range(eClient.住所2).Value

End With

Dim fileName As String

fileName = ThisWorkbook.Path & "\" & Format(Date, "yyyymm") & "請求書_" & client & ".xlsx"

wb.SaveAs fileName

wb.Close

Next rowClient

End Sub

テーブルの列番号を列挙体で表すメリット

ご覧いただければわかりますが、各列挙体は以下のテーブルの列番号を表すものです。

「請求データ」テーブルの列番号: eData
「取引先マスタ」テーブルの列番号: eClient
「ひな形データ」テーブルの列番号: eTemplate

例えば、44～46行目をご覧いただければ、「請求データ」テーブルの品目から数量の値を、「ひな形データ」テーブルの品目から数量の列に転記しているというのが、なんとなくわかりますよね。

つまり、どの列を表すのかの情報を直接見て取れるようになります。

また、それぞれテーブルになっていますので、列番号を1から整然と順に列挙することができます。

テーブルじゃなかったら、例えば取引先マスタの取引先名の列番号は「8」になったりするのです。

テーブルにしているから「1」にすれば済みますし、それ以降も順番に列挙すればOKです。

つまり、テーブルと列挙体の組み合わせ…最強、なわけです。

まとめ

以上、エクセルVBAでテーブルと列挙体を活用した請求書マクロを紹介しました。

このように、テーブルと列挙体をうまく組み合わせることにより、可読性が上がりますし、メンテナンス性も高く保持できます。

ぜひご活用くださいね。

以上で本シリーズは終了となります。

また、便利なテクニックを見つけたら紹介しますね、どうぞお楽しみに！

連載目次：エクセルVBAでテーブルを活用した請求書マクロを作る

エクセルのとっても便利な機能「テーブル」。VBAで操作するときも、テーブルならではの便利さを味わうことができます。請求書マクロを題材にVBAによるテーブルの操作方法をお伝えしていきます。

　　投稿者プロフィール

タカハシノリアキ株式会社プランノーツ　代表取締役

株式会社プランノーツ代表、コミュニティ「ノンプロ研」主宰。1976年こどもの日生まれ。東京板橋区在住。「ITで日本の『働く』の価値を上げる！」をテーマに、VBA＆GASの開発、講師、執筆などをしております。→詳しいプロフィールはコチラ
★ご依頼・ご相談はお気軽にどうぞ！→お問い合わせはコチラ
★フォロー頂ければ嬉しいです。

エクセルVBAでテーブルと列挙体を組み合わせると最強のコードが書ける理由

前回のおさらい

マジックナンバーを避けるべき理由

テーブルの列挙体を定義する

列挙体を使った請求書作成マクロ

テーブルの列番号を列挙体で表すメリット

まとめ

連載目次：エクセルVBAでテーブルを活用した請求書マクロを作る

投稿者プロフィール

最新の投稿

コメント