１．はじめに
ファイルの内容をExcel VBA内に取り込む場合、シート内のセルへの展開は比較的処理が重く、また、読み込んだだけでシートの内容も変更されてしまうため、
ファイルの内容をシート（セル）には展開せずに、メモリ内（配列・Collection等）に読み込み、メモリ上のみで処理を行う機会は多いと思います。
そこで、読み込みの際によく利用される（動的）配列とCollectionについて処理速度を比較してみました。
２．比較した処理内容
実験用に作成したプログラムは下記のリポジトリに配置しています。
https://github.com/NobuyukiInoue/Comparison_Array_and_Collection
今回は、下記の３つの方法の処理時間を計測してみました。
- 標準モジュールで宣言した（動的）配列を使用する
- （動的）配列をClass化して使用する
- Collectionを利用する
- Dictionaryを利用する
２－１．標準モジュールで宣言した（動的）配列を使用する
１つめの方法は、（動的）配列を使用する方法です。
読み込むデータ量（行数）に応じて必要とされる配列の要素数も変わってくるため、（上限を決めなければならない）静的配列ではなく、
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(fileName As String, code As String, separator As String) As ReadArray
Dim lines As ReadArray
lines = ArrayInit()
With CreateObject("ADODB.Stream")
.Charset = code
Select Case separator
Case vbLf:
.LineSeparator = 10
Case vbCr:
.LineSeparator = 13
Case Else:
.LineSeparator = -1
End Select
.Open
.LoadFromFile fileName
Do Until .EOS
AddItem lines, .ReadText(-2) ' １行取り出す
Loop
.Close
End With
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(fileName As String, code As String, separator As String)
With CreateObject("ADODB.Stream")
.Charset = code
Select Case separator
Case vbLf:
.LineSeparator = 10
Case vbCr:
.LineSeparator = 13
Case Else:
.LineSeparator = -1
End Select
.Open
.LoadFromFile fileName
Do Until .EOS
AddItem .ReadText(-2) ' １行取り出す
Loop
.Close
End With
End Sub
２－３． Collectionを利用する
Collectionを利用する場合、Addメソッドの引数に、格納したい値を渡すだけです。
要素の追加・削除等が簡単にでき、要素数の上限などを気にする必要もありません。
M_OperateCollection.bas
Option Explicit
'------------------------------------------------------------------------------
' ファイルをCollectionに読み込む
'------------------------------------------------------------------------------
Public Function CollectionFileLoad(fileName As String, code As String, separator As String) As Collection
Dim lines As Collection
Set lines = New Collection
With CreateObject("ADODB.Stream")
.Charset = code
Select Case separator
Case vbLf:
.LineSeparator = 10
Case vbCr:
.LineSeparator = 13
Case Else:
.LineSeparator = -1
End Select
.Open
.LoadFromFile fileName
Do Until .EOS
lines.Add .ReadText(-2) ' １行取り出す
Loop
.Close
End With
Set CollectionFileLoad = lines
End Function
２－４． Dictionaryを利用する
Dictionaryを利用する場合、Addメソッドの引数に、格納したい値のほかに、キーを指定する必要があります。
Collection同様、要素の追加・削除等が簡単にでき、要素数の上限などを気にする必要もありません。
M_OperateDictionary.bas
Option Explicit
'------------------------------------------------------------------------------
' ファイルDictionaryyに読み込む
'------------------------------------------------------------------------------
Public Function DictionaryFileLoad(fileName As String, code As String, separator As String) As Object
Dim lines As Object
Set lines = CreateObject("Scripting.Dictionary")
With CreateObject("ADODB.Stream")
.Charset = code
Select Case separator
Case vbLf:
.LineSeparator = 10
Case vbCr:
.LineSeparator = 13
Case Else:
.LineSeparator = -1
End Select
.Open
.LoadFromFile fileName
Dim i As Long
i = 1
Do Until .EOS
lines.Add i, .ReadText(-2) ' １行取り出す
i = i + 1
Loop
.Close
End With
Set DictionaryFileLoad = lines
End Function
３．実験結果（処理時間比較）
参考までに、（処理時間の目安として）今回の実験の環境を記載しておきます。
検証環境
| 項目 | 値 |
|---|---|
| CPU | 13th Gen Intel(R) Core(TM) i5-1335U (1.30 GHz) |
| メモリ | 16.0GB(LPDDR3/2133MHz) |
| OS | Windows11 Pro 24H2 |
| Excel | Microsoft® Excel® 2021 MSO (バージョン 2507 ビルド 16.0.19029.20136) 64 ビット |
| Strage | NVMe BC901 NVMe SK hynix 512GB |
３－１．結果１（読み込み・読み出し）
配列(Struct)と配列(Class)とでは、読み込みと読み出し時間はほぼ変わらずですが、
読み込みについては、配列(Class)のほうがわずかに速く、読出しについては配列(Class)の方がわずかに遅くなりました。
配列(Class)では、 ArrayFileLoad()を実行した後に値を返す必要がありませんが、
配列(Struct)版については、ArrayFileLoad()時に、戻り値の構造体（データ量22MByte）をコピーして返しているための差だと考えられます。
また、読み出しが遅くなった理由については、配列(Struct)版では、プロシージャを呼び出さずに直接、構造体内の配列を参照することができますが、配列(Class)版ではメソッドを経由しているため、その呼び出しの分だけ処理が遅くなったと考えられます。
Collectionについては、読み込み自体は配列(Class)版と同等ですが、要素の取り出しについては配列よりかなり処理時間がかかっています。
Dictionaryについては、読み込み自体は配列よりも1.5～2倍程度遅くなっています。要素の取り出しについても、配列よりも処理時間が３倍程度遅くなっていますが、キーとして行番号（整数型）を利用していることもあり、Collectionほど遅くはありません。
- Array(Struct) - Load/Read
| 処理時間合計(s) | 読み込み処理時間(s) | 読み出し処理時間(s) | |
|---|---|---|---|
| 1回目 | 0.425 | 0.351 | 0.074 |
| 2回目 | 0.350 | 0.293 | 0.057 |
| 3回目 | 0.355 | 0.299 | 0.056 |
| 4回目 | 0.323 | 0.267 | 0.056 |
| 5回目 | 0.338 | 0.277 | 0.061 |
| 平均 | 0.358 | 0.297 | 0.061 |
- Array(Class) - Load/Read
| 処理時間合計(s) | 読み込み処理時間(s) | 読み出し処理時間(s) | |
|---|---|---|---|
| 1回目 | 0.321 | 0.246 | 0.075 |
| 2回目 | 0.373 | 0.300 | 0.073 |
| 3回目 | 0.341 | 0.273 | 0.068 |
| 4回目 | 0.343 | 0.262 | 0.081 |
| 5回目 | 0.335 | 0.266 | 0.069 |
| 平均 | 0.343 | 0.269 | 0.073 |
- Collection - Load/Read
| 処理時間合計(s) | 読み込み処理時間(s) | 読み出し処理時間(s) | |
|---|---|---|---|
| 1回目 | 13.491 | 0.256 | 13.235 |
| 2回目 | 13.211 | 0.286 | 12.925 |
| 3回目 | 13.244 | 0.268 | 12.976 |
| 4回目 | 13.106 | 0.259 | 12.847 |
| 5回目 | 13.403 | 0.260 | 13.141 |
| 平均 | 13.291 | 0.266 | 13.025 |
- Dictionary - Load/Read
| 処理時間合計(s) | 読み込み処理時間(s) | 読み出し処理時間(s) | |
|---|---|---|---|
| 1回目 | 0.686 | 0.455 | 0.231 |
| 2回目 | 0.671 | 0.465 | 0.206 |
| 3回目 | 0.632 | 0.428 | 0.204 |
| 4回目 | 0.654 | 0.439 | 0.215 |
| 5回目 | 0.631 | 0.417 | 0.214 |
| 平均 | 0.655 | 0.441 | 0.214 |
３－２．結果（読み込み・先頭の要素を削除）
先頭要素の削除については、配列(Struct)と配列(Class)でほぼ変わらず。
配列と比較すると、Collection/Dictionaryは（内部でデータを移動させていく必要がないため）、かなり速くなっています。
- Array(Struct) - Load/Remove
| 処理時間合計(s) | 読み込み処理時間(s) | 先頭削除処理時間(s) | |
|---|---|---|---|
| 1回目 | 0.290 | 0.278 | 0.012 |
| 2回目 | 0.278 | 0.270 | 0.008 |
| 3回目 | 0.279 | 0.257 | 0.022 |
| 4回目 | 0.276 | 0.260 | 0.016 |
| 5回目 | 0.269 | 0.257 | 0.012 |
| 平均 | 0.278 | 0.264 | 0.014 |
- Array(Struct) - Load/Remove
| 処理時間合計(s) | 読み込み処理時間(s) | 先頭削除処理時間(s) | |
|---|---|---|---|
| 1回目 | 0.276 | 0.253 | 0.023 |
| 2回目 | 0.272 | 0.262 | 0.010 |
| 3回目 | 0.280 | 0.266 | 0.014 |
| 4回目 | 0.282 | 0.270 | 0.012 |
| 5回目 | 0.273 | 0.273 | 0.000 |
| 平均 | 0.277 | 0.265 | 0.012 |
- Collection - Load/Remove
| 処理時間合計(s) | 読み込み処理時間(s) | 先頭削除処理時間(s) | |
|---|---|---|---|
| 1回目 | 0.258 | 0.251 | 0.007 |
| 2回目 | 0.249 | 0.242 | 0.007 |
| 3回目 | 0.243 | 0.243 | 0.000 |
| 4回目 | 0.246 | 0.244 | 0.002 |
| 5回目 | 0.248 | 0.247 | 0.001 |
| 平均 | 0.249 | 0.245 | 0.003 |
- Dictionary - Load/Remove
| 処理時間合計(s) | 読み込み処理時間(s) | 先頭削除処理時間(s) | |
|---|---|---|---|
| 1回目 | 0.456 | 0.452 | 0.004 |
| 2回目 | 0.450 | 0.447 | 0.003 |
| 3回目 | 0.454 | 0.450 | 0.004 |
| 4回目 | 0.430 | 0.425 | 0.005 |
| 5回目 | 0.450 | 0.434 | 0.016 |
| 平均 | 0.448 | 0.442 | 0.006 |
データの追加・削除の頻度が多い場合はCollection/Dictionaryが向いてそうですが、
ファイルの内容をメモリに読み込むだけの処理では、自分で要素数を管理するのが苦にならないようにすれば、（動的）配列でも十分そうです。
Class化しておくと、別の処理で再利用する際にも便利だと思います。