エクセルVBAでシートモジュールに請求書マクロのデータをコレクション化する処理を書く


collection

photo credit: RÉTRO BUS RENNAIS Collection Orain (03) via photopin (license)

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

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

前回の記事はこちら。

エクセルVBAでクラスを使った請求書マクロの概要と使用するクラスの定義
新シリーズとして「エクセルVBAでクラスを使って請求書マクロを作る」を開始します。今回は、エクセルVBAでクラスを使った請求書マクロの概要と使用する2つのクラスの定義ということでお送りします。

シリーズ最初の記事でしたが、作成するマクロの概要とクラスの定義までを進めました。

今回は作成したクラスのインスタンスをコレクション化して、シートのデータを格納するまでを進めていきます。

エクセルVBAで請求書マクロのシートデータをインスタンスのコレクションとして格納する方法です。

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

前回のおさらい

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

目標とするマクロとしては、以下の「請求データ」に蓄積されたデータを

請求データシート
これは、各社へ請求すべき元データを品目別にまとめているシートになります。

この「取引先マスタ」にある取引先の分だけ

取引先マスタシート

この「請求書」シートをひな形として請求書を作りたい、というものです。

請求書シート

それで、まずは「請求データ」シートの1行分のデータを表すクラスDataと

Public DeliveryDate As Date
Public ClientName As String
Public ItemName As String
Public Price As Long
Public Quantity As Long

「取引先マスタ」シートの1行分のデータを表すClientクラスを作成しました。

Public Name As String
Public PostalNumber As String
Public Address1 As String
Public Address2 As String

Collectionオブジェクトをどこに持つか

Dataクラスのインスタンスは「請求データ」シートの1行分のデータを格納できますから、各行をインスタンスに放り込んだものをコレクションに追加していけば、表全体のデータを格納できます。

Clientクラスについても同様の考え方で、表全体のデータを取り扱えます。

それで、Collectionオブジェクトをどこに持つかなのですが、「初心者でもわかるエクセルVBAのクラスモジュール」のシリーズでは、コレクション用のクラスを別途作ってたんですね。

エクセルVBAでクラスモジュールを使って独自のコレクションを作る方法
「初心者でもわかるエクセルVBAのクラスモジュール」ということでシリーズでお伝えしております。今回は、エクセルVBAでクラスモジュールを使って独自のコレクションを作る方法をお伝えします。

しかし、Clientsとか、Datas(というかそもそも単語がおかしい)とかのクラスをさらに追加…ってちょっとモジュールが増えすぎて邪魔くさい感じがします。

シートモジュールにデータをコレクション化して持つ

それで、各データはもともとはシートにあるんだから、そしたらシートオブジェクトにCollectionオブジェクト持てばいいんじゃね?

というアイデアを思いつきました。

というのも、シートモジュールは、既にインスタンス化されているという点を除くと、クラスモジュールに近いイメージで使用できます。

つまり、そのシートオブジェクトに対して以下のようなことが実現できます。

  • プロパティやメソッドを追加できる
  • イベントプロシージャを追加できる

例えば、シートから取得したデータ群をコレクションとして格納して、プロパティとして持って置けば、他のモジュールからアクセスすることができますよね。

シートモジュールのコード

それでは、まず簡単そうな「取引先マスタ」のほうから。

「取引先マスタ」シートのシートオブジェクト名をwsClientに変更して、そのシートモジュールwsClientに以下のようなコードを作りました。

Public Clients As Collection

Public Sub Store()
    Set Clients = New Collection
    
    Dim i As Long: i = 2
    Do While Cells(i, 1) <> ""
        Dim c As Client: Set c = New Client
        c.Init Range(Cells(i, 1), Cells(i, 4))
        Clients.Add c, c.Name
        i = i + 1
    Loop

End Sub

パブリック変数ClientsをCollection型で宣言します。

これが、Clientクラスのインスタンス、つまりClientオブジェクトを追加していくコレクションになります。

パブリック変数なので、そのまま読み書き可能なプロパティとして外部からアクセスできます。

SubプロシージャStoreは、シートのデータを取り込むためのメソッドで、以下のような処理を行います。

  1. コレクションClientsを初期化
  2. 「取引先マスタ」の各行について
    1. Clientクラスのインスタンスを生成
    2. ClientのインスタンスのInitメソッドにRangeオブジェクトを渡して初期データを投入
    3. コレクションClientsにClientのインスタンスを追加

このStoreメソッドをマクロの最初に実行して、シートのデータをオブジェクトのコレクションとして持たせちゃおうという作戦です。

インスタンスに初期データを投入するInitメソッド

それで、上記wsClientのStoreメソッド内で使用していた、Clientのインスタンスに初期データを投入するInitメソッドを定義していませんでしたね…

以下のメソッドをクラスモジュールClientに追加します。

Public Sub Init(ByVal values As Range)
    Name = values(1).Value
    PostalNumber = values(2).Value
    Address1 = values(3).Value
    Address2 = values(4).Value
End Sub

Rangeオブジェクトを受け取って、1番目のセルから順番に各プロパティに格納していきます。

データをコレクション化の動作確認

では、標準モジュールに動作確認用のプロシージャを作って確認しましょう。

ちなみに、標準モジュールのモジュール名を「Main」に変更しています。

Sub New請求書マクロ()

wsClient.Store
Dim myClients As Collection: Set myClients = wsClient.Clients
Stop

End Sub

実行してStop時にローカルウィンドウを確認すると、以下のようにデータが取得できていることを確認できます。

取引先マスタのコレクションを確認

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

「請求データ」シートも全く同様の流れで作っていきます。

まず、「請求データ」シートのシートモジュールのモジュール名をwsDataとして、そこに書くコードはこちら。

Public Data As Collection

Public Sub Store()
    Set Data = New Collection
    
    Dim i As Long: i = 2
    Do While Cells(i, 1) <> ""
        Dim d As Data: Set d = New Data
        d.Init Range(Cells(i, 1), Cells(i, 5))
        Data.Add d
        i = i + 1
    Loop

End Sub

こちらもインスタンスのデータ初期化用のInitメソッドが必要なので、同じノリでクラスモジュールDataに追加。

Public Sub Init(ByVal values As Range)
    DeliveryDate = values(1).Value
    ClientName = values(2).Value
    ItemName = values(3).Value
    Price = values(4).Value
    Quantity = values(5).Value
End Sub

標準モジュールMainを以下のようにコード追加して、動作確認します。

Sub New請求書マクロ()

wsClient.Store
Dim myClients As Collection: Set myClients = wsClient.Clients

wsData.Store
Dim myData As Collection: Set myData = wsData.Data
Stop

End Sub

請求データシートのコレクションを確認

なんとなく似たような処理なので共通化したい気もするのですが…難しい気もしますし、そこまで効果的に使い回せるのか、ちょっと疑問があるのでひとまずここで着地させときます。

まとめ

以上、エクセルVBAで請求書マクロのシートデータをインスタンスのコレクションとして格納する方法をお伝えしました。

コレクションの置き場として、シートモジュールを使ってみました。

シートからのデータの読み取りなので、わりとこの方法も良さそうな気がします。

では、次回は生成したコレクションを使って取引先ごとの請求データを取り出す処理を作っていきます。

エクセルVBAでクラスとコレクションを使うと簡単に請求書のデータ抽出できる
エクセルVBAでクラスを使って請求書マクロを作るシリーズをお送りしています。今回は、請求データの中から該当の取引先のデータだけを抽出する方法です。これは、クラスとコレクションを使うと、実にスマートに実現できるんです。

どうぞお楽しみに!

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

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

  投稿者プロフィール

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

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