photo credit: photoibserver Multi Colored Large Group Of Objects Backgrounds In A Row Abundance Variation Full Frame Choice Collection For Sale Arrangement Sale Rack Display Order Extreme Close Up Colorful No People Retail Shelf via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
エクセルVBAで請求データ一覧から請求書を自動で作成する方法をシリーズでお伝えしています。
前回はこちらの記事。
ワークシートをオブジェクト名で取り扱う方法についてお伝えしました。
請求書作成ツールとしては、既に機能はしている状態ではあるのですが、ツールのメンテナンスを超絶楽ちんにする便利なテクニックがあるのでお伝えしたいと思います。
その名も列挙体です。
VBAではエクセルシートに列の挿入があった場合、セルの指定をしている箇所をいちいち変更しなくてはいけなくなったりしますよね？
しかし、列挙体を上手に使うと、エクセルシートに列の挿入があっても、簡単に対応ができるようになります。
では、エクセルVBAでシートの列の挿入も簡単に対応できちゃう列挙体の使い方です。
行ってみましょう！
シートで列の挿入があったらどうなる？
請求書を作成する元データが、このようなシートに記録されているとします。
本ツールでは、この「請求データ」シートを「wsData」というオブジェクト名で取り扱っており、取引先ごとのデータを集めてそれぞれの請求書を作ります。
プログラムとしては
strClient = wsData.Cells(i, 2).Value '現在の行のクライアント名を取得
dayData = wsData.Cells(i, 1).Value '現在の行の納品日を取得
とか
With wsData
If WorksheetFunction.SumIfs( _
.Range("F:F"), _
.Range("A:A"), ">=" & dayCutoff, _
.Range("A:A"), "<" & DateAdd("m", 1, dayCutoff), _
.Range("B:B"), wsClient.Cells(n, 1).Value _
) > 0 Then
'中略
End If
End With
といったように、様々な形でシートの列やセルを指定しているんですね。
ですが、ツールを運用する上で、列の挿入をしたくなるときがあります。
例えば、こんな感じです。
A列に「No」というフィールド列が挿入されちゃったんですね。
こうなってしまうと、さあ大変。
プログラム内の列指定をいちいち変更するのは大変
先ほど紹介したプログラムの一部もそれぞれ
strClient = wsData.Cells(i, 3).Value '現在の行のクライアント名を取得
dayData = wsData.Cells(i, 2).Value '現在の行の納品日を取得
というように、列番号をそれぞれプラス1の値に修正したり
With wsData
If WorksheetFunction.SumIfs( _
.Range("G:G"), _
.Range("B:B"), ">=" & dayCutoff, _
.Range("B:B"), "<" & DateAdd("m", 1, dayCutoff), _
.Range("C:C"), wsClient.Cells(n, 1).Value _
) > 0 Then
'中略
End If
End With
「”A:A”」は「”B:B”」、「”F:F”」は「”G:G”」…といったように、列範囲の指定も変更します。
ちまちま、ちまちま、とっても面倒です！
また、面倒なだけでなく、修正ミスや修正忘れが発生しちゃいそうです。
列挙体の使い方
もし、そんなことが発生しそうなのであれば、列の指定を列挙体でしておくのがおすすめです。
Enumステートメントで列挙体を定義する
列挙体は、変更がない数値をグループとして扱う場合に使うことができる便利なもので、以下のようにEnumステートメントで定義します。
メンバー名1[ = 値1]
メンバー名2[ = 値2]
…
End Enum
列挙体名は変数名のように、任意の文字列で指定します。
値1,値2,…は整数値を指定します。値1を省略すると0が入り、値2以降を省略すると、直前のメンバーの値にプラス1した整数が入ります。
また、列挙体の宣言はプロシージャ内ではなく、モジュールレベルで行います。
例えば、以下のような列挙体eDataを定義しましょう。
Enum eData
納品日 = 1
取引先名
品目
単価
数量
価格
End Enum
これで、それぞれのメンバーに値が定数のように格納された状態となります。
列挙体の値を取り出す
そして、列挙体の任意のメンバーの値を取り出す場合は
で取り出すことができます。
例えば、前述のEnumステートメントによるeDataの定義をした上で
Sub 列挙体の表示()
Debug.Print eData.納品日
Debug.Print eData.取引先名
Debug.Print eData.品目
Debug.Print eData.単価
Debug.Print eData.数量
Debug.Print eData.価格
End Sub
こんなプロシージャを実行すると
こんなふうに出力されるわけですね。
シートの列を列挙体として定義する
もうおわかりですよね。
列挙体の各メンバーに列番号を割り当てておくんです。
先ほどの列挙体eDataを定義しておき、冒頭のプログラムの一部を
strClient = wsData.Cells(i, eData.取引先名).Value '現在の行のクライアント名を取得
dayData = wsData.Cells(i, eData.納品日).Value '現在の行の納品日を取得
であったり
With wsData
If WorksheetFunction.SumIfs( _
.Columns(eData.価格), _
.Columns(eData.納品日), ">=" & dayCutoff, _
.Columns(eData.納品日), "<" & DateAdd("m", 1, dayCutoff), _
.Columns(eData.取引先名), wsClient.Cells(n, eClient.取引先名).Value _
) > 0 Then
'中略
End If
End With
というように、列番号の指定を「列挙体.メンバー名」で指定しておきます。
もしA列に「No」という列が挿入されたとしても、列挙体eDataの定義の箇所を
Enum eData
No = 1
納品日
取引先名
品目
単価
数量
価格
End Enum
このように変更するだけで、列挙体の各メンバーに格納されている列番号が変更されてくれるので、プロシージャ内は一切変更をしなくても対応ができるようになるわけです。
便利ですよね！
列挙体を使用した請求書作成プログラム
では、請求書プログラムを、列挙体を使用して書き換えてみましょう。
Option Explicit
Enum eData
納品日 = 1
取引先名
品目
単価
数量
価格
End Enum
Enum eClient
取引先名 = 1
郵便番号
住所1
住所2
End Enum
Sub 請求書作成()
Dim i As Long, j As Long, k As Long, n As Long 'For～Nextカウント用整数型変数
Dim dayData As Date '納品日格納用変数
Dim dayCutoff As Date '締月入力用変数
Dim strClient As String '取引先格納用変数
Dim strFile As String 'コピー先パス＆ファイル名
Dim rowsData As Long '「請求データ」の行数
Dim rowsClient As Long '「取引先マスタ」の行数
rowsData = wsData.Cells(Rows.Count, eData.取引先名).End(xlUp).Row '「請求データ」の最後の行数を取得
rowsClient = wsClient.Cells(Rows.Count, eClient.取引先名).End(xlUp).Row '「取引先マスタ」の最後の行数を取得
Dim wsInvoice As Worksheet 'ひな形は別ファイルなのでオブジェクト変数を使う
dayCutoff = InputBox("締月を入力してください（例：2015/5）")
For n = 2 To rowsClient
With wsData
If WorksheetFunction.SumIfs( _
.Columns(eData.価格), _
.Columns(eData.納品日), ">=" & dayCutoff, _
.Columns(eData.納品日), "<" & DateAdd("m", 1, dayCutoff), _
.Columns(eData.取引先名), wsClient.Cells(n, eClient.取引先名).Value _
) > 0 Then
strFile = ThisWorkbook.Path & "\" & Format(dayCutoff, "yyyymm") & "_" & wsClient.Cells(n, eClient.取引先名).Value & ".xlsx" 'コピー先ファイル名
FileCopy ThisWorkbook.Path & "\請求書ひな形.xlsx", strFile 'ファイルをコピー
Workbooks.Open strFile 'コピーにて作成したファイルを開く
Set wsInvoice = ActiveSheet '開いたファイルのワークシートをセット
k = 21 '請求書ひな形シート用カウント変数、スタートは21行目
For i = 2 To rowsData 'iは請求データ用のカウント変数、最終値の設定にrowsDataを使う
strClient = .Cells(i, eData.取引先名).Value '現在の行のクライアント名を取得
dayData = .Cells(i, eData.納品日).Value '現在の行の納品日を取得
If strClient = wsClient.Cells(n, eClient.取引先名).Value Then
If Year(dayData) = Year(dayCutoff) And Month(dayData) = Month(dayCutoff) Then '年が2015でかつ月が5の場合は処理を実行
wsInvoice.Cells(k, 1).Value = .Cells(i, eData.品目).Value
wsInvoice.Cells(k, 2).Value = .Cells(i, eData.単価).Value
wsInvoice.Cells(k, 3).Value = .Cells(i, eData.数量).Value
k = k + 1
End If
End If
Next i
wsInvoice.Rows(k & ":50").Hidden = True 'データがない行を隠す
wsInvoice.Calculate '「請求書ひな形」シートを再計算する
wsInvoice.Range("A18").Value = "ご請求金額：" & Format(wsInvoice.Range("D54").Value, "#,##0") & " 円"
wsInvoice.Range("D15").Value = DateSerial(Year(dayCutoff), Month(dayCutoff) + 1, 0) '請求日
wsInvoice.Range("D16").Value = DateSerial(Year(dayCutoff), Month(dayCutoff) + 2, 0) 'お支払期限
End If
End With
Next n
End Sub
文字数は若干増えた感じはありますが、列の指定がただの数値から、意味のある列挙体名とメンバー名の組み合わせになっていますので、コードの可読性も上がっていますよね。
まとめ
以上、エクセルVBAでシートに列挿入があっても列挙体で簡単に対応ができるよ、ということをお伝えしました。
お伝えしてきた通り、列挿入があっても列挙体の定義の修正だけで済むようになりますので、その際の作業効率やミスを減らす意味でもメリットがあります。
加えて、可読性も上がりますしね。
列を列挙体で定義する…オススメです！
連載目次：データ一覧から請求書を自動で作る
お仕事において特定のデータ一覧から必要な情報を抽出するということは頻繁にありうると思います。ここではデータ一覧から請求書を作るということを目標に、実務で使えるスキルをまっすぐに身に着けることを目的としています。- 【初心者向けエクセルVBA】データ一覧から請求書を自動で作る
- 【初心者向けエクセルVBA】ワークシートをオブジェクト名で取り扱う方法
- 【初心者向けエクセルVBA】For～Next文で簡潔にプログラムを書く
- 【初心者向けエクセルVBA】行の数をカウントする＆不要な行を隠す
- 【初心者向けエクセルVBA】文字列の連結＆Format関数での書式変更
- 【初心者向けエクセルVBA】セル範囲を一気にまとめてコピーする方法
- 【初心者向けエクセルVBA】ワークシートのデータのある範囲だけをピッタリ取得する方法
- 【初心者向けエクセルVBA】セル範囲の平行移動をする方法・リサイズをする方法
- 【初心者向けエクセルVBA】日付データから年・月・日を取り出す
- 【初心者向けエクセルVBA】If～Thenを使った条件分岐の超入門
- 【初心者向けエクセルVBA】For～Next文でセル範囲を一行ずつ移動させる
- 【初心者向けエクセルVBA】セル範囲のクリア～ClearContentsメソッドとClearメソッド
- 【初心者向けエクセルVBA】ワークシート・セルを選択する方法の色々について
- 【初心者向けエクセルVBA】入力ダイアログを表示するInputBoxメソッドの使い方
- 【初心者向けエクセルVBA】日付データから月末日と翌月末日を自動算出する
- 【初心者向けエクセルVBA】ワークシートをコピーする方法とそのシート名を変更する方法
- 【初心者向けエクセルVBA】オブジェクトを変数にセットして取り扱う方法
- 【初心者向けエクセルVBA】Openメソッドで新たなブックを開く方法
- 【初心者向けエクセルVBA】現在マクロを書いているブックのフォルダパスを取得する
- 【初心者向けエクセルVBA】開いたブックとそのワークシートをオブジェクト変数にセットする
- 【初心者向けエクセルVBA】ワークブックを別名で保存して閉じる方法
- 【初心者向けエクセルVBA】取引先別に請求書を作成するマクロを作る
コメント
いつも参考にさせていただいております。ありがとうございます。
ひとつお伺いなのですが、今回の例ですと挿入した「no」列は一番左に位置するわけですが、間に挿入された列でも対応できるのでしょうか？例えば、今回のvbaを社内に公開した後、現場担当者が「取引先名の右に『取引先担当者名』列を入れたいんだよね。勝手に列挿入しちゃえ！」というような場合です。理想としては、列挿入されたとしてもコードは変えなくて済むことだと思ってはいるのですが…。自分でも試してみようと思いますが、もしお時間ございましたらご教授ください。
よぶびえ様
コメントありがとうございます！
そんなときのために、列挙体をお使いくださいませ。
Enum eData No = 1 納品日 取引先名 取引先担当者名 品目 単価 数量 価格 End Enumとすれば対応できるかと思います。