[Excel][VBA] Type ←→ Collection を相互に変換する関数を用意して扱いやすくする試み

0.README.md

[Excel][VBA] Type ←→ Collection を相互に変換する関数を用意して扱いやすくする試み

VBA におけるユーザー定義型（Type）は Collection に追加できない・Variant にセットできない等、扱いづらい局面が多い。
そこで、ユーザー定義型 ←→ Collection を相互に変換するような関数（PackXXX()、UnpackXXX()）を用意しておけば、扱いやすくなるのではないかと思いついたので、試してみた。

メリット

• ユーザー定義型のメリット（インテリセンスが効く等）と Collection のメリット（Collection 等に追加したり Variant にセットできる等）をそれぞれ享受可能
• ユーザー定義型の代わりにクラスモジュールを使った場合、大量にインスタンスを生成した際に破棄に時間がかかる問題があるが、本手法の場合には発生しない

デメリット

• ユーザー定義型毎に Pack（Type→Collection）／Unpack（Collection→Type）関数を用意したりメンテナンスしたりするのが手間
• 同関数で変換する際のオーバーヘッドがある

サンプルコード

「もっと知りたい「動的配列」 - bimori466のエクセルブログ」の『４　クラスモジュールの自作コレクションを使う』を元に作成。
データシート（Sheet4）の作り方はこちらのツイートを参照のこと。

• 標準モジュール: MyProductCollection

参考

• かずやん @y8bV4ty1wbkTjPd さんのツイート からの一連の流れ
• もっと知りたい「動的配列」 - bimori466のエクセルブログ
• ことりちゅん @KotorinChunChun さんのツイート からの一連の流れ
• 20210203_クラスを量産すると破棄に時間かかる

MyProductCollection.vba

Option Explicit

' 元ネタ：[もっと知りたい「動的配列」 - bimori466のエクセルブログ](https://bimori466-1.hatenablog.com/entry/2021/01/29/233008)
Private Type Product
    ProductCode As String
    UnitPrice As Currency
    StockNumber As Long
    TotalPrice As Currency
End Type

' Type → Collection 変換
Private Function PackProduct(Target As Product) As Collection
    Set PackProduct = New Collection
    With PackProduct
        .Add Target.ProductCode, "ProductCode"
        .Add Target.UnitPrice, "UnitPrice"
        .Add Target.StockNumber, "StockNumber"
        .Add Target.TotalPrice, "TotalPrice"
    End With
End Function

' Collection → Type 変換
Private Function UnpackProduct(Target As Collection) As Product
    With UnpackProduct
        .ProductCode = Target("ProductCode")
        .UnitPrice = Target("UnitPrice")
        .StockNumber = Target("StockNumber")
        .TotalPrice = Target("TotalPrice")
    End With
End Function

Sub Test_MyProductCollection()
    Application.ScreenUpdating = False
    
    Dim startTime As Double
    Dim endTime As Double
    Dim processTime As Double
    
    startTime = Timer

    Dim MyProductCollection As Collection: Set MyProductCollection = New Collection
    Dim ws As Worksheet: Set ws = Worksheets("Sheet4")
    Dim lastRow As Long: lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    Dim i As Long
    Dim AddProductCode As String
    Dim AddProduct As Product
    
    For i = 2 To lastRow
        AddProductCode = ws.Cells(i, 1)
        
        With AddProduct
            .ProductCode = AddProductCode
            .UnitPrice = ws.Cells(i, 2)
            .StockNumber = ws.Cells(i, 3)
            .TotalPrice = .UnitPrice * .StockNumber
        End With
            
        ' Type → Collection に変換することで、データを Collection に追加できるようにする
        MyProductCollection.Add PackProduct(AddProduct), AddProductCode
        
        ' Collection から取り出したデータ（Collection）を Type に変換する（インテリセンスも効くようになる）
        ws.Cells(i, 4) = UnpackProduct(MyProductCollection(AddProductCode)).TotalPrice
    Next
    
    endTime = Timer
    processTime = endTime - startTime
    Debug.Print "end 処理時間＝" & Format(processTime, "0.0000") & "秒"
    
    startTime = Timer
    Set MyProductCollection = Nothing ' データが大量にあっても解放に時間がかからない
    endTime = Timer
    processTime = endTime - startTime
    Debug.Print "end 解放時間＝" & Format(processTime, "0.0000") & "秒"
    
    Application.ScreenUpdating = True
End Sub