もっと知りたい「動的配列」 - bimori466のエクセルブログ

f:id:bimori466:20210129144647p:plain

目次
　１　値を保持する「ReDim Preserve」
　２　動的配列の弱点
　３　弱点を補うユーザー定義型（Type）
　４　クラスモジュールの自作コレクションを使う　
　５　終わりに…　

１　値を保持する「ReDim Preserve」

配列の宣言については過去記事を記載しております。
bimori466-1.hatenablog.com

この記事で触れていなかった動的配列の値を保持する方法について説明します。

どういうときに値の保持を使うのか？
例を出します。以下のコードをご覧ください。

Sub 配列の値を保持して再宣言()

    Dim myArr() As String
    
    ReDim myArr(1 To 1)
    
    myArr(1) = "A001"
    
    'あっ、"A002"を追加しなきゃ(;^ω^)
    
    ReDim Preserve myArr(1 To 2)
    
    myArr(2) = "A002"
    
    Debug.Print myArr(1)
    Debug.Print myArr(2)
    
End Sub

＜処理解説＞
１　配列変数myArr(1) を再定義。
２　myArr(1)にA001を代入。
３　配列変数myArr(2) を再定義。
　　＊この時、myArr(1) の値を消したくないので「ReDim Preserve myArr(1 To 2)」と記述します。「Preserve＝保持」の意味です。
４　myArr(2)にA002を代入
５　イミディエイトウィンドウに値を表示。

＜イミディエイトウィンドウの結果＞
f:id:bimori466:20210129214147p:plain

A001、A002が表示されています。つまり、myArr(1)の値は保持されていることがわかりますね！
つまり、配列の要素数を拡張できるのです！

２　動的配列の弱点

しかし、２次元配列は行の要素数を増やすことができません。
（＊注：TRANSPOSE関数で行と列を入れ替えれば、追加することは可能です。ここでは割愛しますm(_ _"m)）

例を出します。
以下の画像のような表から商品コードが「Aで始まる行」の２次元配列を作成します。

f:id:bimori466:20210129221039p:plain

以下のコードをご覧ください。

Sub 動的配列_二次元配列の要素数は増やせない()
    
    Dim myArr() As Variant
    
    Dim ws As Worksheet: Set ws = Worksheets("sheet1")
    Dim lastRow As Long
    Dim arrCount As Long
    
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    
    Dim i As Long
    
    For i = 2 To lastRow
        If ws.Cells(i, 1).Value Like "A*" Then
            arrCount = arrCount + 1
            ReDim Preserve myArr(1 To arrCount, 1 To 3)
            
            myArr(arrCount, 1) = ws.Cells(i, 1).Value
            myArr(arrCount, 2) = ws.Cells(i, 2).Value
            myArr(arrCount, 3) = ws.Cells(i, 3).Value
        End If
    Next

End Sub

結論、以下の通り実行時エラーとなります。
f:id:bimori466:20210129223327p:plain

エラー部分のコード

ReDim Preserve myArr(1 To arrCount, 1 To 3)

arrCount＝２のときにエラーとなります。
つまり、行は要素の追加ができません。
（＊注：列の要素数の追加はできます。myArr（1 To 1,1 To 4）は可能です。横に要素を増やすことはできます。）

これが2次元配列の「ReDim Preserve」の弱点です。

３　弱点を補うユーザー定義型（Type）

じゃあ配列の行を増やすことは諦めないといけないの？？

諦めないで！ユーザー定義型（Type）というものがあります。

ユーザー定義型変数とは、複数の型・値を1つの変数名で管理する事が出来る型のことです。

では先ほどできなかった、商品コードが「Aで始まる行」の配列を作成しましょう！

＜Typeを使った処理コード＞

Type 商品
    商品コード As String
    商品名 As String
    在庫数 As Long
End Type


Sub Typeを使って配列を保持()
    
    Dim myArr() As 商品
    
    Dim ws As Worksheet: Set ws = Worksheets("sheet1")
    Dim lastRow As Long
    Dim arrCount As Long
    
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    
    Dim i As Long
    
    For i = 2 To lastRow
        If ws.Cells(i, 1).Value Like "A*" Then
            arrCount = arrCount + 1
            ReDim Preserve myArr(1 To arrCount)
            
            myArr(arrCount).商品コード = ws.Cells(i, 1).Value
            myArr(arrCount).商品名 = ws.Cells(i, 2).Value
            myArr(arrCount).在庫数 = ws.Cells(i, 3).Value
        End If
    Next
    
    Debug.Print myArr(1).商品コード; " " & myArr(1).商品名 & " " & myArr(1).在庫数
    Debug.Print myArr(2).商品コード; " " & myArr(2).商品名 & " " & myArr(2).在庫数

End Sub

＊注：Typeはモジュールの宣言セクションに記載に記載します！

＜処理結果＞
f:id:bimori466:20210129231551p:plain

画像の蛍光線部の通り、配列の値が保持されていることがわかります。

４　クラスモジュールの自作コレクションを使う

配列とは異なりますが、ユーザー定義（Type）のように、クラスモジュールを使ってコレクションに複数の要素を持たせることができます。

＊注：クラスモジュールで記述しないといけません。標準モジュールでコレクションにTypeで宣言することはできません。

クラスモジュールを使って、以下の表のSheet1のデータをSheet２に転記する処理を実行します（データ数２０万件）。

＜Sheet1のデータを、＞
f:id:bimori466:20210207165612p:plain

＜Sheet２に列D「金額」を計算したものを転記します＞
f:id:bimori466:20210207165858p:plain

準備するモジュール

クラスモジュール×２、標準モジュール×１の計３つ

＜クラスモジュール１　要素を定義する「商品コード」＞

Option Explicit

Public 商品コード As String
Public 単価 As Long
Public 在庫数 As Long
Public 金額 As Currency

＜クラスモジュール２　要素数を追加するメソッドを作る「商品コード群」＞

Option Explicit

Public 商品コード群 As Collection

Private Sub class_initialize()

Set 商品コード群 = New Collection

End Sub

Public Sub Add(ByVal new商品コード As String, ByVal new単価 As Long, ByVal new在庫数 As String, ByRef new金額 As Currency)

Dim p As 商品コード: Set p = New 商品コード

With p
    .商品コード = new商品コード
    .単価 = new単価
    .在庫数 = new在庫数
    .金額 = new金額
End With
    
商品コード群.Add p, new商品コード

End Sub

＜標準モジュール　金額を求める「My商品Collection2」＞

Sub My商品Collection2()
Dim my商品 As 商品コード群: Set my商品 = New 商品コード群
    
    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
    
    For i = 2 To lastRow
        DoEvents
        my商品.Add ws.Cells(i, 1), ws.Cells(i, 2), ws.Cells(i, 3), (ws.Cells(i, 2) * ws.Cells(i, 3))
        ws.Cells(i, 4) = my商品.商品コード群(i - 1).金額    '列Dに金額を代入
    Next
End Sub

処理の流れ

１　変数my商品を商品コード群として、インスタンス化する。
２　変数lastRowに最終行を取得する。
３　変数my商品に、クラスモジュールのメソッドADDを実行する
４　変数my商品に商品コード群を追加する。（商品コード、単価、在庫数、金額）
５　列Dに金額を求める（ws.Cells(i, 4) = my商品.商品コード群(i - 1).金額）。

結論：この処理にかかる時間は「２３分３秒」です。クソ遅いｗ。使えたもんじゃない。
このことをTwitterに投稿したところ、コードを見てもらって、なぜ遅いのかを調べてもらったところ以下の回答をもらいました。

プログラムが遅い一番の原因は、たぶん、下記の行です。
ws.Cells(i, 4) = my商品.商品コード群(i - 1).金額
Colletionからの読み出しで、読み出したい要素の番号を指定すると、一気に遅くなります。

つまり、my商品(1).金額と表示できれば速度が上がるはず！ということに気づきました。
しかし、現状のクラスモジュールではmy商品(1).金額と表示することができません。
しか～し、解決方法がパーフェクトＶＢＡに載っています。

Attribute（属性）を編集する。

Attributeとはなんぞや？
ざっくり説明すると、VBE上では操作できない設定を、対象のモジュールをエクスポートして、テキストエディタなどで「Attribute」から始まる命令文を追記して、その後インポートすることで、設定できるものです。
いやいや、VBE上で設定できないなんてありかよ～(;^ω^)って感じですが、そうらしいですｗ

で、本題の「ws.Cells(i, 4) = my商品.商品コード群(i - 1).金額」→「ws.Cells(i, 4) = my商品().金額」するのか？
具体的に説明していきます。

１）クラスモジュールに、クラスのデフォルトメンバーに追加する処理を追記する。
＜クラスモジュール　商品コード群に追記＞

Public Property Get 商品index(ByVal index As Variant) As 商品コード
    Set 商品index = 商品コード群(index)
End Property

全体的にクラスモジュールをもう一度見ると以下の通り。

＜クラスモジュール　商品コード群　Property Get追記後＞

Option Explicit

'クラスモジュール_商品コード群

Public 商品コード群 As Collection

Private Sub class_initialize()

Set 商品コード群 = New Collection

End Sub

Public Sub Add(ByVal new商品コード As String, ByVal new単価 As Long, ByVal new在庫数 As String, ByRef new金額 As Currency)

Dim p As 商品コード: Set p = New 商品コード

With p
    .商品コード = new商品コード
    .単価 = new単価
    .在庫数 = new在庫数
    .金額 = new金額
End With
    
商品コード群.Add p, new商品コード

End Sub

Public Property Get 商品index(ByVal index As Variant) As 商品コード
    Set 商品index = 商品コード群(index)
End Property

このコード（商品コード群）をエクスポートします。
f:id:bimori466:20210207225334p:plain

５　終わりに…

いかがだったでしょうか。配列に入れると処理速度はあがりますが、動的配列でまさかの行の追加がシンプルにできません(;^ω^)
TRANSPOSE関数を使って追加する方法もありますが、シンプルじゃないのでは私の好みではありません。
Typeを使うと、シンプルに行のデータを増やすことができます。
２次元配列の動的配列の行は、動的にPreserveで値を保持して再宣言できないというお話でした。

「ReDim Preserve」は処理が遅くなるのでは？と思われるかもですが、１０万件ほどのデータであれば、１秒以内に終わるようです。そんなに遅くは無いようです。

ではでは、この辺で(^^)/~~~