【VBA】複数値の格納パターン

配列＋Type

※ForEach不可
※Typeは標準モジュールに定義すること

Type typPerson
    id As String
    name As String
End Type

Sub test()
    Dim person() As typPerson
    ReDim person(1 To 1000000)
    
    Dim i As Long
    For i = 1 To 100000
        person(i).id = i
        person(i).name = "name" & i
    Next

    Debug.Print person(1).id
    Debug.Print person(1).name
End Sub

コレクション＋クラス

※GetとLetを省略する場合はPrivateをPublicにする
※多すぎると応答なしになる。10万件くらいまでにしておく
※コレクション＋Typeは不可（Typeはコレクションに格納できない）
クラス

'clsPerson
Private myId As String
Private myName As String

'Get
Public Property Get Id() As String
    Id = myId
End Property
Public Property Get Name() As String
    Name = myName
End Property

'Let
Public Property Let Id(Id As String)
    myId = Id
End Property
Public Property Let Name(Name As String)
    myName = Name
End Property

Sub test()
    Dim person As New clsPerson
    Dim col As New Collection
    Dim i As Long
    
    For i = 1 To 100000
        person.Id = i
        person.Name = "name" & i
        col.Add person
        Set person = Nothing
    Next
    
    Debug.Print col(1).Id
    Debug.Print col(1).Name
End Sub

Recordset

※項目の桁数を指定する必要あり
※参照設定：Microsoft ActiveX Data Objects 2.8 Libraryを追加

Sub test()
    Dim person As New ADODB.Recordset
        
    With person.Fields
        .Append "id", adVarWChar, 10
        .Append "name", adVarWChar, 10
    End With
    person.Open
    
    Dim i As Long
    For i = 1 To 100000
        person.AddNew
        person("id") = i
        person("name") = "name" & i
        person.Update
    Next
    
    person.MoveFirst
    Debug.Print person!id
    Debug.Print person!name
    
    person.MoveNext
    Debug.Print person!id
    Debug.Print person!name
End Sub