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.
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.
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.