１．はじめに

ファイルの内容をExcel VBA内に取り込む場合、シート内のセルへの展開は比較的処理が重く、また、読み込んだだけでシートの内容も変更されてしまうため、
ファイルの内容をシート（セル）には展開せずに、メモリ内（配列・Collection等）に読み込み、メモリ上のみで処理を行う機会は多いと思います。

そこで、読み込みの際によく利用される（動的）配列とCollectionについて処理速度を比較してみました。

２．比較した処理内容

実験用に作成したプログラムは下記のリポジトリに配置しています。
https://github.com/NobuyukiInoue/Comparison_Array_and_Collection

今回は、下記の３つの方法の処理時間を計測してみました。

標準モジュールで宣言した（動的）配列を使用する
（動的）配列をClass化して使用する
Collectionを利用する

２－１．標準モジュールで宣言した（動的）配列を使用する

１つめの方法は、（動的）配列を使用する方法です。
読み込むデータ量（行数）に応じて必要とされる配列の要素数も変わってくるため、（上限を決めなければならない）静的配列ではなく、
Redim Presereを使って、読み込む行数に応じて配列の要素数を（1個ずつではなく、4096個といったまとまった個数で）増やせるようにしておきます。
（ちなみに、追加する要素数の単位を65536にしても試してみましたが、逆に遅くなりました。）

また、各関連プロシージャに引数で渡す場合に値渡しだと処理が遅くなってしまうので、下記のように標準モジュール内で構造体を定義しておき、参照渡しができるようにしておきます。

M_OperateArray.bas（その１）

Type ReadArray
    Item() As String
    Count As Long
    ArraySize As Long
End Type

Private Const BLOCK_SIZE As Long = 4096

'------------------------------------------------------------------------------
' ReadArrayを初期化する
'------------------------------------------------------------------------------
Public Function ArrayInit() As ReadArray
    Dim lines As ReadArray

    ReDim Preserve lines.Item(0 To BLOCK_SIZE - 1)
    lines.Count = 0
    lines.ArraySize = BLOCK_SIZE

    ArrayInit = lines
End Function

M_OperateArray.bas（その２）

'------------------------------------------------------------------------------
' ReadArray内の配列に要素を追加する
'------------------------------------------------------------------------------
Public Sub AddItem(ByRef lines As ReadArray, ByRef value As String)
    If lines.Count >= lines.ArraySize Then
        lines.ArraySize = lines.ArraySize + BLOCK_SIZE
        ReDim Preserve lines.Item(0 To lines.ArraySize)
    End If

    lines.Item(lines.Count) = value
    lines.Count = lines.Count + 1
End Sub

'------------------------------------------------------------------------------
' ReadArray内の配列の指定した番号の要素を削除する
'------------------------------------------------------------------------------
Public Sub RemoveItem(ByRef lines As ReadArray, index As Long)
    Dim i As Long
    
    For i = index To lines.Count - 2
        lines.Item(i) = lines.Item(i + 1)
    Next
    lines.Count = lines.Count - 1
End Sub

M_OperateArray.bas（その３）

'------------------------------------------------------------------------------
' ファイルをReadArrayに読み込む
'------------------------------------------------------------------------------
Public Function ArrayFileLoad(fileNamePath As String) As ReadArray
    Dim lines As ReadArray
    lines = ArrayInit()
    
    Dim fileNum As Long
    fileNum = FreeFile()
    
    Open fileNamePath For Input As #fileNum

    Dim buf As String
    Do While Not EOF(fileNum)
        Line Input #fileNum, buf
        AddItem lines, buf
    Loop
    
    ArrayFileLoad = lines
End Function

２－２．（動的）配列をClass化して使用する

プログラムの規模が小さいうちは、標準モジュールのままでも良いのですが、
規模が大きくなってくるとグローバル変数の管理が複雑になってくるので、必要な機能だけをまとめてClass化すると、コードを管理しやすくなります。

さきほどの（動的）配列に関する処理をクラス化したものが下記になります。

ClassArray.cls

Option Explicit

Private Item() As String
Public Count As Long
Public ArraySize As Long

Private Const BLOCK_SIZE As Long = 4096

'------------------------------------------------------------------------------
' クラスを初期化する
'------------------------------------------------------------------------------
Private Sub Class_Initialize()
    ReDim Preserve Item(0 To BLOCK_SIZE - 1)
    Count = 0
    ArraySize = BLOCK_SIZE
End Sub

'------------------------------------------------------------------------------
' Item()に値を追加する
'------------------------------------------------------------------------------
Public Sub AddItem(value As String)
    If Count >= ArraySize Then
        ArraySize = ArraySize + BLOCK_SIZE
        ReDim Preserve Item(0 To ArraySize - 1)
    End If
    
    Item(Count) = value
    Count = Count + 1
End Sub

'------------------------------------------------------------------------------
' Item(i)に値を格納する
'------------------------------------------------------------------------------
Public Function SetItem(i As Long, ByRef value As String) As String
    Item(i) = value
End Function

'------------------------------------------------------------------------------
' Item(i)の値を取得する
'------------------------------------------------------------------------------
Public Function GetItem(i As Long) As String
    GetItem = Item(i)
End Function

'------------------------------------------------------------------------------
' 指定した番号の要素を削除する
'------------------------------------------------------------------------------
Public Sub RemoveItem(index As Long)
    Dim i As Long
    
    For i = index To Count - 2
        Item(i) = Item(i + 1)
    Next
    Count = Count - 1
End Sub

'------------------------------------------------------------------------------
' ファイルをReadArrayに読み込む
'------------------------------------------------------------------------------
Public Sub ArrayFileLoad(fileNamePath As String)
    Dim fileNum As Long
    fileNum = FreeFile()
    
    Open fileNamePath For Input As #fileNum

    Dim buf As String
    Do While Not EOF(fileNum)
        Line Input #fileNum, buf
        AddItem (buf)
    Loop
End Sub

２－３． Collectionを利用する

Collectionを利用する場合、Addメソッドの引数に、格納したい値を渡すだけです。
要素の追加・削除等が簡単にでき、要素数の上限などを気にする必要もありません。

M_OperateCollection.bas

Option Explicit

'------------------------------------------------------------------------------
' ファイルをCollectionに読み込む
'------------------------------------------------------------------------------
Public Function CollectionFileLoad(fileNamePath As String) As Collection
    Dim lines As Collection
    Set lines = New Collection
    
    Dim fileNum As Long
    fileNum = FreeFile()
    
    Open fileNamePath For Input As #fileNum

    Dim buf As String
    Do While Not EOF(fileNum)
        Line Input #fileNum, buf
        lines.Add buf
    Loop
    
    Set CollectionFileLoad = lines
End Function

３．実験結果（処理時間比較）

参考までに、（処理時間の目安として）今回の実験の環境を記載しておきます。

検証環境

項目	値
CPU	Inten(R) Core(TM) i7-8559U CPU @ 2.70GHz
メモリ	16GB(LPDDR3/2133MHz)
OS	Windows10 Pro 21H2
Excel	Microsoft Excel 2019
Strage	Apple APPLE SSD AP1024 SCSI Disk Device

３－１．結果１（読み込み・読み出し）

配列(Struct)と配列(Class)とでは、読み込みと読み出し時間はほぼ変わらずですが、
読み込みについては、配列(Class)のほうがわずかに速く、読出しについては配列(Class)の方がわずかに遅くなりました。

配列(Class)では、 ArrayFileLoad()を実行した後に値を返す必要がありませんが、
配列(Struct)版については、ArrayFileLoad()時に、戻り値の構造体（データ量22MByte）をコピーして返しているための差だと考えられます。
また、読み出しが遅くなった理由については、配列(Struct)版では、プロシージャを呼び出さずに直接、構造体内の配列を参照することができますが、配列(Class)版ではメソッドを経由しているため、その呼び出しの分だけ処理が遅くなったと考えられます。

Collectionについては、読み込み自体は配列よりもわずかに速くなっていますが、要素の取り出しについては配列よりかなり処理時間がかかっています。

Array(Struct) - Load/Read

	処理時間合計(s)	読み込み処理時間(s)	読み出し処理時間(s)
1回目	0.781	0.688	0.093
2回目	0.766	0.672	0.094
3回目	0.784	0.690	0.094
4回目	0.765	0.687	0.078
5回目	0.781	0.688	0.093
平均	0.775	0.685	0.090

Array(Class) - Load/Read

	処理時間合計(s)	読み込み処理時間(s)	読み出し処理時間(s)
1回目	0.768	0.674	0.094
2回目	0.750	0.640	0.110
3回目	0.734	0.640	0.094
4回目	0.750	0.656	0.094
5回目	0.750	0.641	0.109
平均	0.750	0.650	0.100

Collection - Load/Read

	処理時間合計(s)	読み込み処理時間(s)	読み出し処理時間(s)
1回目	15.392	0.703	14.689
2回目	15.256	0.656	14.600
3回目	15.277	0.656	14.621
4回目	15.246	0.657	14.589
5回目	15.695	0.640	15.055
平均	15.373	0.662	14.711

３－２．結果（読み込み・先頭の要素を削除）

先頭要素の削除については、配列(Struct)と配列(Class)でほぼ変わらず。
配列と比較すると、Collectionは（内部でデータを移動させていく必要がないため）、かなり速くなっています。

Array(Struct) - Load/Remove

	処理時間合計(s)	読み込み処理時間(s)	先頭削除処理時間(s)
1回目	0.719	0.703	0.016
2回目	0.687	0.671	0.016
3回目	0.688	0.672	0.016
4回目	0.706	0.690	0.016
5回目	0.671	0.671	0.000
平均	0.694	0.681	0.013

Array(Struct) - Load/Remove

	処理時間合計(s)	読み込み処理時間(s)	先頭削除処理時間(s)
1回目	0.688	0.672	0.016
2回目	0.661	0.645	0.016
3回目	0.656	0.640	0.016
4回目	0.656	0.641	0.015
5回目	0.641	0.625	0.016
平均	0.660	0.645	0.016

Collection - Load/Remove

	処理時間合計(s)	読み込み処理時間(s)
1回目	0.690	0.690
2回目	0.672	0.672
3回目	0.656	0.656
4回目	0.656	0.656
5回目	0.657	0.657
平均	0.666	0.666

データの追加・削除の頻度が多い場合はCollectionが向いてそうですが、
ファイルの内容をメモリに読み込むだけの処理では、自分で要素数を管理するのが苦にならないようにすれば、（動的）配列でも十分そうです。

Class化しておくと、別の処理で再利用する際にも便利だと思います。