User Defined Data Type – Type VBA

We learned about pre-defined data types in VBA in one of our previous articles. Today we will learn about User Defined Data Type in VBA using the Type statement. We will learn with the help of suitable examples.

What are User Defined Types?

We know about a number of native or pre defined data types in VBA. These include Byte, Single, Integer, Long, Double, Currency, Decimal, Boolean, Date, and String. A User Defined Type is nothing but a group of pre-defined data types. We can declare a user-defined type and then use its sub-variables with the help of the dot operator.

User Defined Types are often abbreviated as UDT.

Syntax to Declare UDT

'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

A type combines multiple built-in data types or fields into a single User Defined Type. The above declaration acts as a blueprint to create instances of this type. <var1>, <var2> or <varN> are the sub variables of <UDT_name>. Note that we have not used the Dim keyword in the Type declaration. To use this UDT, we must create its instance.

Dim <UDT_var_name> As <UDT_name>

Once we create an instance, we can use the sub-variables for the instance. Not that for any number of instances of a UDT, each of the sub-variables would be allocated separate space in the computer’s memory and would not be affected by each other.

<UDT_var_name>.<var1>=expression
<UDT_var_name>.var2=expression

Here the result of the expression is assigned to a sub-variable value using the assignment operator (=). Assigning values to UDT is known as instantiation.

Why do we Need User Defined Types?

There are a number of reasons behind the usage of User Defined Types.

• They are suitable for record arrangement in a suitable format.
• They are very efficient to process, more than Collections.
• They can act as a mini database to store information using a combination of built-in types.

Example – Employee Type to Store Employee Record

In this example, we would create a user-defined type for Employee. An Employee will have a Name, Age, and Salary. We can store the name in String, Age in Byte, and Salary in the long variable. Follow these steps to perform the operation.

• Press Alt and F11 keys to open the Visual Basic Editor. Go to the Insert tab in VBE and Insert a new module named Module1.
• Now insert a Type declaration for Employee Data Type in the new module as follows.

Private Type Employee
    'declaring the sub variables for employee
    Name As String
    Age As Byte
    Salary As Long
End Type

This type is named Employee and now it has three sub variables named Name, Age, and Salary.

• Now we create a sub procedure named as exampleTypeEmployee and declare an Employee type variable instance named emp1.

Sub exampleTypeEmployee()
    'declare an instance of Type employee
    Dim emp1 As Employee
End Sub

• We would now access the sub-variables of emp1 using the dot operator followed by the name of sub variable. When we hit the dot operator, it displays the list of all the sub-variables of that type.

Sub exampleTypeEmployee()
    'declare an instance of Type employee
    Dim emp1 As Employee
    emp1.Name = "Chetna Dua"
    emp1.Age = 20
    emp1.Salary = 55000
End Sub

• Now we have created a record for an employee that has name, age, and salary. We can access them using the dot operator and print using the Message Box.

Sub exampleTypeEmployee()
    'declare an instance of Type employee
    Dim emp1 As Employee
    'initialize the sub variables
    emp1.Name = "Chetna Dua"
    emp1.Age = 20
    emp1.Salary = 55000
    'print the values of sub variables
    MsgBox "Name: " & emp1.Name, , "Employee Details"
    MsgBox "Age: " & emp1.Age, , "Employee Details"
    MsgBox "Salary: " & emp1.Salary, , "Employee Details"
End Sub

• Now close the Visual Basic Editor and press Alt and F8 keys to open the list of available Macros/Sub Procedures. Select the exampleTypeEmployee and click on the Run button.

You can also learn about an array of user-defined types to store multiple records of information.

This brings us to an end.

Thank you for reading.