クラスモジュールで生成したCollectionオブジェクトをモジュール間で自由に受け渡したい

Question

前提・実現したいこと

ExcelVBAでクラスモジュールとCollectionオブジェクトを組み合わせた活用法を模索しています。
実現したいことは画像のデータをSheet1からSheet2へそのまま転記することです。

実行のイメージとしては、表のデータ1行分を1つのオブジェクト(後述のDataクラス)と捉え、
Collection(後述のItemsクラス)へ次々と格納し、Sheet２へFor Each構文を使用して転記をします。

これまではSheet2へ転記を行うコードである、後述の「M_Original」の「ApplayToSheet」プロシージャを
「C_Items」クラスに記述していました。

上述の方法を実務で試した結果、当然のことながら転記がこんなに単純なはずがなく、
また、Items(Collectionオブジェクト)を何度も使用しなければならないため、
「C_Items」クラス内の記述が増え可読性が落ちてしまいました。

そこでItems(Collectionオブジェクト)を標準モジュールへ渡すことができれば
様々な機能の切り離しが可能になり、より可読性の高い構成にできると考え以下の通り再編成してみました。

つきましては以下の構成が最善であるかという観点からアドバイス等いただけたら幸いです。

構成

■標準モジュール

• 「M_Main」モジュール：このプロシージャからマクロを実行します。

Sub 転記マクロ()
    
    Dim myItems As C_Items: Set myItems = New C_Items
    Dim items As Collection
    Set items = myItems.getItems
    myItems.DataStore
    Call TenkiData(items)
    
End Sub

• 「M_Original」モジュール：Collectionに溜め込んだデータの転記を行います。

Public Sub ApplayToSheet(ByVal items As Collection)

    Dim i As Long
    Dim d As C_Data
    i = 1
    For Each d In items
        With Sheet2
            .Cells(i, 1) = d.name
            .Cells(i, 2) = d.age
            .Cells(i, 3) = d.location
            i = i + 1
        End With
    Next d
End Sub

■クラスモジュール

• 「C_Data」クラス：表1行分のデータを表すオブジェクトになります。

Public name As String
Public age As Integer
Public location As String

Public Sub Initialize(ByVal rng As Range)
    name = rng(1)
    age = rng(2)
    location = rng(3)
End Sub

• 「C_Items」クラス：items(Collection)へデータの格納などitemsの管理を行います。

Private items As Collection

Private Sub Class_Initialize()
    Set items = New Collection
End Sub

Public Property Get getItems() As Collection
    Set getItems = items
End Property

Public Sub DataStore()
    Dim i As Long
    For i = 1 To 11
        Dim d As C_Data: Set d = New C_Data
        With Sheet1
            d.Initialize .Range(.Cells(i, 1), .Cells(i, 3))
            items.Add d
        End With
    Next i
End Sub

※以下修正したコード

■標準モジュール

• M_Mainモジュール

Option Explicit

Sub Tenkisyori()
    Call TenkiData
End Sub

• M_Originalモジュール

Option Explicit

Private items As Collection

Public Sub TenkiData()
    '## Sheet2へデータの転記を実行する
    Dim i As Long: i = 1
    Dim d As C_Data
    
    For Each d In GetPersonCollectionFrom(Sheet1)
        With Sheet2
            .Cells(i, 1) = d.name
            .Cells(i, 2) = d.age
            .Cells(i, 3) = d.location
            i = i + 1
        End With
    Next d
    
End Sub

Function GetPersonCollectionFrom(ByRef TargetSheet As Worksheet) As Collection
    '## Sheet1のデータをCollectionへ順次格納する
    Set GetPersonCollectionFrom = New Collection
    Dim i As Long
    
    For i = 2 To 6
        Dim personItem As C_Data: Set personItem = New C_Data
        With personItem
            .name = TargetSheet.Cells(i, 1)
            .age = TargetSheet.Cells(i, 2)
            .location = TargetSheet.Cells(i, 3)
        End With
       GetPersonCollectionFrom.Add personItem
    Next i
    
End Function

■クラスモジュール

• C_Dataクラス

Option Explicit

Public name As String
Public age As Integer
Public location As String

Answer 1

おそらくサンプルコードなので、もっと別の処理があるのでしょうが転記するだけなら、Sheet1の内容をSheet2に全コピーするコードを組むのが一番速いですけどね💦

サンプルコードで一番気になったのは、
Initializeメソッドの引数がRangeであること
（ここを参考にしたのかな？👀 https://tonari-it.com/excel-vba-class-constructor/ ）

好みもあるでしょうが、私は絶対にこのようなイニシャライザは作らないですね👀
どんなプログラミング言語でも作らないです。

なぜなら、Rangeオブジェクトの順番が、「指名」「年齢」「所在地」となっていなければいけないという「決まり事」を作っていますよね？
引数に決まり事を作ると、作っている人はわかっていても、読む人からしたら訳が分からなくなります。現にエクセルシートを見ずに、VBAコードだけを見て理解できる人はいない状態です。
じゃあコメントを書けば👀...というのもあまり好きではないですね💦（よく「コメントは謝罪」と例えられるのはこのような例のことでしょう）

もう一つの理由は、そもそもRangeオブジェクトの順番が「指名」「年齢」「所在地」と並んでいることを想定で作るなら、そもそもクラスなんていらないとも言えると思います。

「可読性 = 記述が少ないコード」は大概の場合間違いだと思います。
記述が少ないということは、見る人からしたらヒントが少ないということですから👀

あと、時間がたった後は自分は他人に代わります。（昔書いたコードなんてまず覚えていないです💦）

上記のようなことを考慮すると...

クラスの名前

初めて見た人はC_Dataという名前からどんなクラスかな？...と考えますが、この名前からわかるでしょうか？
私なら...
C_DataはPersonRecordとかPersonInfoとかPerson
C_ItemsはPersonCollectionとかPersonDataSet
みたいな名前を付けますかね👀

（もちろんスネーク記法なら「person_record」とかでも構いませんよ^^）
（私はPascal記法の方が好みですが👀）

シートのオブジェクト名

シートのオブジェクト名をつけてみては？
Sheet1だと何のためのシートか分かりません。
DataSourceSheetなど、オブジェクト名をつけた方がわかりやすいと思います。
※キャプションのことではないですよ

イニシャライザ

一番気になった所ですが...

Dim personItem As C_Data: Set personItem = New C_Data
With personItem
    .name = Sheet1.Cells(i, 1)
    .age = Sheet1.Cells(i, 2)
    .location = Sheet1.Cells(i, 3)
End With

みたいにそもそもイニシャライザを使わない方法が一つ👀
この方法のメリットはクラスを使う側が、どの情報をどの値に初期化しているのかわかること。
しかし、デメリットとして、すべて初期化していなくてもエラーが出ないこと（.location～をコメントアウトしてもエラーにはならないですよね？）
このデメリットは結構痛いです💦

public Sub Initialize(_name As String, _age As String, _location As String)
    name = _name
    age = _age
    location = _location
End Sub

として外側で使う方法👀
このようにInitializeを作ればすべてのフィールドを初期化していることが保証されること。
デメリットは、引数が増えすぎると書きづらいこと。
今回の例ではこれで十分でしょう^^
下記のように呼び出せばよいだけです。

personItem.Initialize(Cells(i, 1), Cells(i, 2), Cells(i, 3))
'下のような感じで順番も変更できる
personItem.Initialize(_age := Cells(i, 2), _location := Cells(i, 3), _name := Cells(i, 1))

最後に、引数が多すぎる場合はどうすればよいか？
いろいろアプローチはありますが、Builderパターンを用いるのがよい設計だと私は思います。
Builderパターンは調べてみてください^^

C_Itemsの設計

C_Itemsクラスですが、一番気になるのはSheet1に依存してしまっていることですね。
このクラスは「Sheet1からデータを取り出すクラス」になっています。
せめて「シートからデータを取り出すクラス」にしましょう。
具体的にはDataSourceプロシージャを...
Public Sub DataSource(ByRef TargetSheet As Sheet)
みたいにして、どんなシートからでも取り出せるような設計にするべきだと思います。

ただ、そもそもC_Itemsクラスが必要なのか？
...という疑問を持つ人が多いと思いますね（サンプルコードだからかもしれませんが💦）

上記の機能しかもっていないなら、クラスにする方が可読性が下がると思います。
CollectionにC_Dataを追加して単純にCollectionの変数を使いまわす方が読みやすいです。

長文になってしまいましたが、「可読性 = 他人が見てもわかりやすいこと = 将来の自分が見てもわかりやすいこと」です。私も昔は勘違いしていた時期がありましたが、記述量を減らすことだけではないことには注意しましょう。

もちろん、プロジェクトの規模などによっても変わります。「可読性はある程度諦めて、記述量を減らす方針」という場合もあるでしょうね👀

Comments

[kazuki51]

ご回答ありがとうございます。
参考サイトにつきましては仰る通り隣ITさんのサイトを参考にしています。
イニシャライザを使う、使わないという考え方は全く頭になく大変参考になりました。

また、1点ご質問になりますが、仮に「C_Items」クラスを使用せず単純にCollection変数を使いまわす場合は、
「C_Items」クラスの内容を「M_Original」モジュールの「TenkiData」プロシージャへ
入れ込むというイメージで合っておりますでしょうか…？
(「TenkiData」プロシージャ内の記述が増えることはやむを得ない）

[HiraKazu1124]

いや、私ならそういうイメージではなくて...
Function GetPersonCollectionFrom(ByRef TargetSheet As Sheet) As Collection
' C_DataのCollectionをGetPersonCollectionFrom変数に代入
End Function

みたいな関数をどこかに作って（私なら「M_Originalモジュール」に作りますね）その関数を呼ぶことで、C_DataのCollectionを指定したシートから取得する。みたいなイメージです^^
関数を作るだけでクラスを作るわけではないです。
こうすれば処理を分けられて可読性が上がるのと、関数の名前を見るだけで何をしているのか推測しやすいのが利点ですね！

※以下はかなりこだわる人向けです（正直VBAではここまで考えなくてもよいとも思います）
少し難しい話をすると、Collectionを返すのではなく「C_Data()」みたいにC_Dataの配列を返す方がよいのかも👀。なぜならCollectionだと中身にC_Dataが入っていることが表向きにはわからないので（コードを読まないとわからない）
（VBAではなくVBなら「C_Data用のCollection」みたいなこともできるんですけどね💦ジェネリッククラスという機能です。ExcelだとVBAなのでそこらへんは配列でないとできないと思います。）

配列を関数周りでつかうのは下記あたりが参考になるかと！
https://qiita.com/ryosuke0825/items/93eb8a284eb5dba59f29

注意点は
Dim array(30) As C_Data
みたいに定数の長さで初期化することはできますが、C_Dataの長さは可変なため、実行時に長さを初期化しなければなりません。そういうときは
ReDim array(長さ) As C_Data
みたいにReDimで変数を作成する必要があります！

私は、普段C#など変数の型をかなり意識する言語（静的型付け言語...実行前に型がわかっていないとエラーになる）が好きなので、こんなことを考えるのかもしれませんが、VBAはインタープリタですし、動的型付け言語と言って、実行時に型を推測する言語なので、ここまで意識しなくてもよいのかもしれません。
好き好みですね^^（私はVBAでもCollection型とかVariant型とかは嫌いなタイプですが、妥協する時もあります👀）

[kazuki51]

HiraKazu1124さんの仰るイメージ通り修正してみましたので、もしよろしければ
「※以下修正したコード」をご確認いただけたら幸いです。

こだわる人向けの内容もご教示いただきありがとうございます。
クラスを何となく使えるようになり、クラス×Collectionが最強！と近頃考えていたのですが、
そんな単純な話でもないのですね…

[HiraKazu1124]

修正コードは悪くないと思いますよ！
GetPersonCollectionFrom関数の中で、TargetSheetを使えていないのはミスでしょうけど、流れはそんな感じで大丈夫だと思います^^

[kazuki51]

ご確認ありがとうございました。
おかげ様で今後に活かすことができそうです。
※TargetSheetは修正漏れです。再修正しました。

Answer 2

実現したいことは画像のデータをSheet1からSheet2へそのまま転記することです。

Copy、PasteSpecial あるいはValueに代入でできることをわさわざクラスで作りなおすのは、車輪の再発明そのものではないかと。

これは手始めで、今後、機能を追加していくということだとしても、エクセル自体がデータ操作の機能をすでに豊富に持っているので、それを超えるものにするのはなかなか難しいかと。
可読性についても触れてますが、データ範囲をテーブル化すればより可読性が高いコードでデータを扱えます。

（使いたくなる）エクセル・VBAのテーブルのメリット・デメリット

---

上記の点は承知の上で、プログラミングの勉強のため、あるいは趣味で、ということでしょうか。

実際に実務で有効に使っているということなので、提示のコードから気になった部分を自分なりに作り直してみました。

クラスモジュール C_Data

Option Explicit

Public name As String
Public age As Integer
Public location As String
Public value As Variant 

Public Sub Initialize(rng As Range)
    name = rng.Cells(1)
    age = rng.Cells(2)
    location = rng.Cells(3)
    value = rng.value
End Sub

クラスモジュール C_Items

Option Explicit

Private items As Collection

Private Sub Class_Initialize()
    Set items = New Collection
End Sub

Public Property Get getItems() As Collection
    Set getItems = items
End Property

'シートからデータ読み込み
Public Sub DataStore(rng As Range)
    Dim r As Range, d As C_Data
    For Each r In rng.Rows
        Set d = New C_Data
        d.Initialize r
        items.Add d
    Next
End Sub

'データをシートへ出力
'FirstCell 出力先の先頭セル
Public Sub DataOutput(FirstCell As Range)
    Dim itm As C_Data, rng As Range    
    Set rng = FirstCell.Resize(1, 3)    
    For Each itm In items
       rng.value = itm.value
       Set rng = rng.Offset(1)
    Next
End Sub

標準モジュール

Sub 転記マクロ()
    With New C_Items
        .DataStore Sheet1.Range("A2:C6")
        .DataOutput Sheet2.Range("A2")
    End With
End Sub

HiraKazu1124さんの回答の指摘は、私も同意見です。（C_Dataの設計や名前付けなど）
が、そこはあまり変更せずに、C_Itemsの設計に関しての一例コードです。

Comments

[kazuki51]

ご回答ありがとうございます。
説明不足で申し訳なかったのですが、おっしゃる通りCopy等でも実現可能なことは承知しており、
勉強や趣味といった側面からも質問させていただきました。

テーブルの活用につきましては便利かと思いますが、なかなか実務でデータをテーブル化をすることが
許されない状況がほとんどのため、活用したことがないのが現状です。

また、可読性と説明しましたが、それに加えて仕様変更に強い構成にしたいと考えております。
その点クラスを活用すると仕様変更に強くなることは実務を通じて間違いないものと実感しており、
積極的に活用している次第です。

[hatena19]

実際に実務で有効に使用しているとのことですね。
仕様変更に強いという点でも、テーブル化は有効ですけど、それが許されない状況というのがよくわかりません。見た目の問題ならどうにでもなるので。

提示のコードを仕様変更に強いものとは思えません。
クラスでのCollectionの使い方に関して、サンプルコードを追加しておきますので、参考にしてください。

[kazuki51]

テーブル化が許されないというのは、例えば、請求書のようなデータを転記元データとし、
テーブルとして扱いたい場合は別シートへテーブル状に転記・加工をする作業を挟む必要があることです。

シートが増えることと、データをテーブル化をする作業が煩雑と考え、今までテーブルの活用を
避けておりました。この煩雑さに勝るメリットがあるのであれば、ぜひ活用したいと思います。

また、仕様変更に強いというのは、マクロを学習し始めた当初に比べてという意味なのでお気になさらず…

サンプルコードのご掲示ありがとうございます。早速、試してみたところ、Initializeの「name=rng(1)」で
「型が一致しません」のエラーが発生しました。
DataStoreの「For Each r In rng.Rows」の「rng.Rows」がイマイチ理解できず解決に至っておりません…

[hatena19]

> テーブル化が許されないというのは、例えば、請求書のようなデータを転記元データとし、テーブルとして扱いたい場合は別シートへテーブル状に転記・加工をする作業を挟む必要があることです。

ちょっと、具体的にどのような処理なのかわかりませんが、請求書つまり売上管理ということだとおもいますが、売上データはデータベースとして蓄積しておいて、請求書は別シートでレイアウトを決めて売上データから転記するという形になると思います。その場合、売上データはテーブル化しておいた方が扱いやすいです。

> Initializeの「name=rng(1)」で「型が一致しません」のエラーが発生しました。

これは、下記に修正してください。
name = rng.Cells(1)

回答の方も修正しておきます。

[hatena19]

「For Each r In rng.Rows」はセル範囲を行ごとに処理する場合の書き方です。下記に解説があります。

https://www.relief.jp/docs/excel-vba-loop-through-each-row.html

[kazuki51]

たしかに売上データをデータベースとして扱おうという意識はこれまで低かったので、
今後試していこうと思います。

修正いただいた回答につきましても正常に動作いたしました。
丁寧なご説明ありがとうございました。