Home > OS >  Create a record array in VBA
Create a record array in VBA

Time:12-12

I want to create a record array in VBA, for example like this:

Index     Name    Age
0         Anton   40
1         Bert    35
...       ...     ...
9         Julia   20

I did a quick and dirty implementation using two arrays:

Dim arrName(0 to 9) as String
Dim arrAge(0 to 9) as Integer

arrName(0) = "Anton"   
arrName(1) = "Bert"    
arrAge(0) = 40
arrAge(1) = 35

I could also do it as a 2-dimensional array but I'm trying to avoid using the data type variant. ("for reasons" which wouldn't benefit the question if I would explain them in length).

I thought VBA would have some kind of record data type implemented but searching for data record, data collection, etc. but most results refer me to an implementation using objects, which is fine but I want to keep the code as "simple as possible" without introducing objects for data storage (yes, I know that worksheets are objects as well, I'm still trying to avoid it).

Isn't there record as a data type implemented in VBA or am just using the wrong keyword for my search? A little pointer would be appreciated very much.

CodePudding user response:

Try with Type:

Type rec
    Index As Long
    Name As String
    Age As Byte
End Type

Sub test1()
    Dim arr(0 To 9) As rec
    With arr(0)
        .Index = 0
        .Name = "Anton"
        .Age = 40
    End With
    
    With arr(1)
        .Index = 1
        .Name = "Bert"
        .Age = 35
    End With
    
    Debug.Print "Index", "Name", "Age"
    For i = LBound(arr) To UBound(arr)
        Debug.Print arr(i).Index, arr(i).Name, arr(i).Age
    Next
End Sub
Index         Name          Age
 0            Anton          40 
 1            Bert           35 
 0                           0 
 0                           0 
 0                           0 
 0                           0 
 0                           0 
 0                           0 
 0                           0 
 0                           0 
  • Related