Array of User Defined Type in VBA

In the previous article, we learned how to use the User Defined Types. User Defined Type refers to a named grouping of built-in data types. Today we will learn to create an Array of User Defined Types. We will learn how to use a UDT and store records from a Table into an Array of UDT.

What is an Array of User Defined Type?

A UDT is a collection of different built-in data type variables known as sub-variables. We need to instantiate a User Defined Type. We can create several user defined type variables by using an array. Each variable of type UDT would have its copy of sub-variables in the computer’s memory. An array of variables of UDT would correspond to several records and each record would correspond to a single element of an array.

You can also download the practice workbook to follow along.

Syntax – UDT Array

Here were have defined the syntax to use an Array of User Defined Data Type.

Type Declaration

To create a User Defined Array type, we must declare a Type.

'syntax of type declaration
Type <UDT_name>
    <var1> As <data_type>
    <var2> As <data_type>
    <var3> As <data_type>
    .
    .
    .
    <varN> As <data_type>
End Type

Here, <var1> or <varn> refer to the sub-variables of the Type.

Creating an Array of User Defined Type

Once we define a UDT, we can declare an array of variables to use it.

Dim <arr_name>([<lower_limit> to] <upper_limit) As <UDT_name>

The following points contain information to make an array of UDT.

• <arr_name> – This is the name of the UDT array.
• <lower_limit> – It is the index of the first array element. This is an optional argument and by default, its value is zero.
• <upper_limit> – This is the index of the last element of the User Defined Type array.
• <UDT_name> – This is the name of the UDT whose array we are about to create.

Accessing Sub Vairbles of Array Elements

The array <arr_name> would contain several elements. We can access the specific elements of an array using its index. Thereafter, we use the dot operator followed by the name of the sub-variable to access it.

<arr_name>(index).<varn>=expression

Example – Store Values from Table into an Array of UDT

In this example, you are going to learn how to store each record of the table in the elements of UDT Array. Follow these steps to learn.

• Prepare a student record data as follows.

• Select the range A1:E6 and press the Ctrl T keys to create a table from this data. Make sure you mark the checkbox for Table Headers.

• Now select the table and go to the Table Design tab on the ribbon. Change the name of the table to MyData.

• Press Alt and F11 keys to open the VBE. Go to the Insert tab and take a new module. Open the module and add the following code to the module.

'define the user defined type
Private Type Student
    rno As Integer
    name As String
    class As String
    marks As Byte
    fname As String
End Type

Sub exDataFromTableIntoUDTArray()
'array to hold data from table
Dim myArray As Variant
'store the table records into myArray, sheet1 contains the table
myArray = ThisWorkbook.Worksheets("Sheet1").Range("MyData[#Data]").value
'declare an array of UDT
Dim student_arr(4) As Student
'store value from myArray into User Defined Type Array
For i = LBound(myArray, 1) To UBound(myArray, 1)
    With student_arr(i - 1)
        'accessing the sub variables and assigning values from myArray
        .rno = myArray(i, 1)
        .name = myArray(i, 2)
        .class = myArray(i, 3)
        .marks = myArray(i, 4)
        .fname = myArray(i, 5)
    End With
Next i
For i = 0 To UBound(student_arr, 1)
    With student_arr(i)
        'printing the values stored in sub variables of elements of student_arr UDT
        MsgBox "Roll Number: " & .rno
        MsgBox "Student's Name: " & .name
        MsgBox "Class: " & .class
        MsgBox "Marks: " & .marks
        MsgBox "Father Name: " & .fname
    End With
Next i
End Sub

Here myArr is variant type.

The UBOUND Function returns the upper limit of specified dimension of an array. It takes two arguments, the array name and the dimension number.

• Place the cursor in sub procedure exDataFromTableIntoUDTArray and press F8 key to execute this line by line or you can just run this sub procedure.

This brings us to an end.

Thank you for reading.