エクセルVBAでクラスとコレクションを使うと簡単に請求書のデータ抽出できる


extract

photo credit: trpnblies7 Homemade Extracts (Day 1) via photopin (license)

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

エクセルVBAでクラスを使って請求書マクロを作るシリーズをお送りしています。

前回の記事はこちらです。

エクセルVBAでシートモジュールに請求書マクロのデータをコレクション化する処理を書く
エクセルVBAでクラスを使って請求書マクロを作るシリーズをお送りしています。今回は、エクセルVBAで請求書マクロのシートのデータをシートモジュール上のインスタンスのコレクションとして格納する方法です。

各シートのデータを、そのシートモジュールが持つプロパティにコレクションとして格納するところまでをお伝えしました。

今回はその続きで、請求データの中から該当の取引先のデータだけを抽出する方法です。

これは、クラスとコレクションを使うと、実にスマートに実現できるんです。

ということで、エクセルVBAでクラスとコレクションを使って簡単に請求書のデータ抽出をする方法です。

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

前回のおさらい

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

「請求データ」シートとそのコレクション化の処理

請求書マクロは3つのシートから成り立っていまして、取引先別の請求書を作成するためのもととなるデータが蓄積されているのが、以下の「請求データ」シートです。

請求データシート

この「請求データ」の1行分を表すDataクラスというのを定義していまして、それがこちら。

その1行分のデータを表すDataクラスのインスタンス、つまりDataオブジェクトをコレクション化することで、データ全体を表現できます。

それを「請求データ」シートのシートモジュールwsDataに持ってしまおう、ということで作ったのが以下のDataプロパティと、Storeメソッドです。

「取引先マスタ」シートとそのコレクション化の処理

続いて、「取引先マスタ」シートです。

取引先マスタシート

ここに記載されている、取引先の分だけ請求書を作ります。

これも「請求データ」シートと同じノリで、1行分のデータを表すClientクラスと、シートモジュールにコレクション化する処理を作成しました。

ひな形となる「請求書」シート

もうひとつが「請求書」シートで、これをひな形として請求書を作ります。

請求書シート

クラスとコレクション化の動作確認用マクロ

以上までの動作確認をするために前回作成したのが以下のMainのコードです。

請求書を作るまでの処理の流れ

請求書を作る処理の流れを確認しておきましょう。

まず、取引先の数だけ請求書を作る必要がありますので、「取引先の数だけループする」という処理が必要ですね。

その上で、「請求データ」からその該当の取引先のデータを抽出して、それぞれの取引先用の請求書に転記したいわけです。

あ、あと年月も重要ですね。

今の「請求データ」には2019年1月のデータしか入っていませんが、月ごとに締めたいので、Mainを実行時に年月を入力して、その年月のデータかどうかという判定も必要です。

つまり、以下のような流れを作りたいです。

  1. 実行時にユーザーに年月を入力してもらう
  2. 取引先の数だけループする
    • 請求データの数だけループする
      • 請求データの年月が入力した年月と等しく、かつ、 該当の取引先のデータであれば
        • そのデータは転記対象となる

てなわけですね。

ややこしそうですか?

取引先のループにコレクションが有効

まず、取引先の分だけループするというところを作ってみましょう。

クラスを使わないマクロであれば、i=2から最終行まで…みたいなループのしかたをさせていたのですが、今回はデータをコレクション化しているものですから、もっとクールにいけます。

コレクションに対してFor Each~Next文を使ってループできます。

それで、取引先のコレクションはどこにあるかというと、シートモジュールwsClientのClientsプロパティに確保したのでした。

それでループのたびにClientsプロパティから取り出す要素は、Clientオブジェクトですから、Clientクラスで定義した各プロパティを使って、取引先のデータにアクセスできます。

さらに、打っていただくとわかりますが、Clientオブジェクトのメンバーである各プロパティは、自動メンバー表示の候補に上がるので、コーディングもさくさく進みます。

以下のような処理を作成して、実行すると…

ほら、取引先についてループして、各データを取り出せましたね。

コレクションをループしてデータを取り出す

該当の取引先の請求データを抽出する

続いて、前述の取引先のループの中に、請求データのループを入れ子にしていきます。

それで、ループの対象となっている取引先と、請求データの取引先が等しければ、そのデータを抽出するのです。

請求データのほうもwsDataのDataプロパティにコレクションが格納されているので、それでループを回せますね。

かつ、その要素はDataオブジェクトですから、定義したプロパティで好きにデータを取り出すことができます。例えば、取引先名であれば、「Dataオブジェクト.ClientName」です。

それが、「c.Name」と等しければヒット!データを抽出すればよいわけです。

それを実現したのが、以下のコードですね。

実行すると…

コレクションの入れ子でデータを抽出

いい感じに抽出できましたね!

まとめ

以上、エクセルVBAでクラスとコレクションを使って簡単に請求書のデータ抽出をする方法についてお伝えしました。

前回までに定義したクラスとシートモジュールのコレクションがかなり有効ですね。

簡単にデータを取り出せるようになりました。

さて、次回は年月を比較するなど、日付関連のところを進めていきます。

エクセルVBAでクラスを使った請求書でマクロの日付関連の処理を作る
エクセルVBAでクラスを使って請求書マクロを作ろう!のシリーズです。今回は、ユーザーが入力した対象の年月から、月末日と翌月末日を求める処理、対象の年月のデータだけを抽出する判定処理を作っていきます。

どうぞお楽しみに!

連載目次:エクセルVBAでクラスを使って請求書マクロを作る

エクセルVBAの題材として実務でもよく利用される「請求書マクロ」。ある程度スキルを磨いたら「これをクラスを使って実装したら?」という疑問が出てくるもの…このシリーズで実際に作ってみましょう。
  1. エクセルVBAでクラスを使った請求書マクロの概要と使用するクラスの定義
  2. エクセルVBAでシートモジュールに請求書マクロのデータをコレクション化する処理を書く
  3. エクセルVBAでクラスとコレクションを使うと簡単に請求書のデータ抽出できる
  4. エクセルVBAでクラスを使った請求書でクロの日付関連の処理を作る
  5. エクセルVBAでクラスを使った請求書マクロでシートのコピー&保存処理を作る

  投稿者プロフィール

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

タイトルとURLをコピーしました