みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。
エクセルVBAでテーブルを活用して請求書を作成するマクロを作成しています。
前回の記事はコチラ。
もう、完成しちゃいました。
で、なぜ続きを書いているかというと、テーブルと列挙体の組み合わせが最強だからです。
なんじゃそりゃ?って感じですよね。列挙体を取り入れると、請求書マクロの可読性とメンテナンス性が一気に上るんです。
ということで本記事では、エクセル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
ちょいと長いですが、簡単に解説をしますね。
以下の「データ」シート(オブジェクト名は「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
はい、ここだけ見て、何をどこに転記しているか、わかりますか?
わかりませんよね?
何が悪さをしているかというと、「数字」です。
1とか2とか3とか4とか書かれても、それが何者なのか、実際のテーブルの様子を見ないとわからないのです。
このように、コード内にベタ打ちで書かれた数値を「マジックナンバー」といいます。
マジックナンバーは数値以外の情報を持たないので、可読性を低下させる要因になりがち…、ですから基本的に避けるべきです。
それを防ぐ方法として、列挙体を使うという方法が有効です。
テーブルの列挙体を定義する
さて、列挙体の定義の方法をお伝えします。
モジュールの宣言セクションで以下のように定義をします。
メンバー名1[ = 値1]
メンバー名2[ = 値2]
…
End 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
テーブルの列番号を列挙体で表すメリット
ご覧いただければわかりますが、各列挙体は以下のテーブルの列番号を表すものです。
- 「請求データ」テーブルの列番号: eData
- 「取引先マスタ」テーブルの列番号: eClient
- 「ひな形データ」テーブルの列番号: eTemplate
例えば、44~46行目をご覧いただければ、「請求データ」テーブルの品目から数量の値を、「ひな形データ」テーブルの品目から数量の列に転記しているというのが、なんとなくわかりますよね。
つまり、どの列を表すのかの情報を直接見て取れるようになります。
また、それぞれテーブルになっていますので、列番号を1から整然と順に列挙することができます。
テーブルじゃなかったら、例えば取引先マスタの取引先名の列番号は「8」になったりするのです。
テーブルにしているから「1」にすれば済みますし、それ以降も順番に列挙すればOKです。
つまり、テーブルと列挙体の組み合わせ…最強、なわけです。
まとめ
以上、エクセルVBAでテーブルと列挙体を活用した請求書マクロを紹介しました。
このように、テーブルと列挙体をうまく組み合わせることにより、可読性が上がりますし、メンテナンス性も高く保持できます。
ぜひご活用くださいね。
以上で本シリーズは終了となります。
また、便利なテクニックを見つけたら紹介しますね、どうぞお楽しみに!
連載目次:エクセルVBAでテーブルを活用した請求書マクロを作る
エクセルのとっても便利な機能「テーブル」。VBAで操作するときも、テーブルならではの便利さを味わうことができます。請求書マクロを題材にVBAによるテーブルの操作方法をお伝えしていきます。- エクセルVBAでテーブル操作~そのメリットと変換方法&ListObjectの取得
- エクセルVBAでテーブル名を使用してテーブルを取得する方法
- エクセルVBAでテーブルの見出し行・データ行・集計行の範囲の取得をする方法
- エクセルVBAでテーブルのデータ行についてループ処理を行う方法
- エクセルVBAでテーブルのデータ行を追加して値を入力をする方法
- エクセルVBAでテーブルを走査して取引先ごとのブックを作るマクロ
- エクセルVBAでテーブル上の請求データを新規ブックに転記するマクロの作り方
- エクセルVBAで請求書作成マクロをテーブルを活用して作る
- エクセルVBAでテーブルと列挙体を組み合わせると最強のコードが書ける理由