VBA Type Statement

The Type statement is an essential tool for defining custom data types. It allows developers to create their own data structures and store related data items in a single unit, making code more organized and efficient. This post will provide an in-depth understanding of the purpose, syntax, examples, and important notes and remarks of using the Type statement in VBA programming.

The VBA Type Statement

Purpose of the Type Statement

The main purpose of the Type statement in VBA is to define custom data types that group related data items. These data items can be of different data types, such as integer, string, boolean, etc. By creating a custom data type, we can store all the necessary information in one place and access it easily. It also helps in improving code readability as the data structure’s name can directly represent what information it contains.

Syntax of the VBA Type Statement

The syntax for creating a Type statement in VBA is as follows:
Type type_name
data_item1 As datatype1
data_item2 As datatype2
…
data_itemN As datatypeN
End Type
Here, ‘type_name’ is the name of the custom data type, followed by the data items (variables) and their data types enclosed within the Type and ‘End Type’ statements. It is essential to note that we can only define one custom data type at a time, and it must be declared outside of any Sub or Function procedures.

Examples of VBA Type Statement

1. Defining a User-Defined Data Type to Store Student Information

To store a student’s information, such as name, age, and grade, in a single data structure.

Type StudentInfo
    Name As String
    Age As Integer
    Grade As Double
End Type
Sub storeStudentInfo()
    Dim student1 As StudentInfo
    student1.Name = "John Smith"
    student1.Age = 15
    student1.Grade = 8
End Sub

2. Creating an Employee Data Type to Store Employee Details

To create a data structure to hold the employee’s details, such as name, salary, and department.

Type Employee
    Name As String
    Salary As Double
    Department As String
End Type
Sub getEmployeeDetails()
    Dim emp1 As Employee
    emp1.Name = "Emily Johnson"
    emp1.Salary = 50000
    emp1.Department = "Human Resources"
End Sub

3. Using a User-Defined Data Type to Store Bank Account Information

To define a data structure that can store a customer’s bank account details, such as account number, balance, and account type.

Type BankAccount
    AccountNumber As String
    Balance As Double
    AccountType As String
End Type
Sub updateAccountBalance()
    Dim acct1 As BankAccount
    acct1.AccountNumber = "123456789"
    acct1.Balance = 1000
    acct1.AccountType = "Savings"
End Sub

4. Creating a Book Data Type to Store Book Information

To define a book data structure that can hold a book’s title, author, and publication date.

Type Book
    Title As String
    Author As String
    PublicationDate As Integer
End Type
Sub displayBookInfo()
    Dim book1 As Book
    book1.Title = "To Kill a Mockingbird"
    book1.Author = "Harper Lee"
    book1.PublicationDate = 1960
End Sub

5. Using a Custom Data Type to Store Product Details

To create a data structure that can hold a product’s name, price, and description.

Type Product
    Name As String
    Price As Single
    Description As String
End Type
Sub displayProductInfo()
    Dim prod1 As Product
    prod1.Name = "iPhone 12"
    prod1.Price = 999
    prod1.Description = "The latest smartphone from Apple"
End Sub

Important Notes & Remarks

• The Type statement is not supported in VBA for Mac.
• Each data item in the custom data type must have a unique name.
• We can use nested custom data types for more complex data structures.
• Custom data types can also be used as parameters in Sub and Function procedures.
• It is not possible to declare an array within a custom data type.

Conclusion

In summary, the Type statement in VBA provides a powerful mechanism to define custom data types. It helps in organizing related data items and improves code readability. This post covered the purpose, syntax,  examples, and important notes and remarks of using the Type statement in VBA. By understanding and leveraging this tool, we can write more efficient and organized VBA code.

Thank you for reading this post on the VBA Type statement. I hope you found it informative and helpful. Please do share your feedback and views in the comments below. Are there any other VBA topics you would like to see a post on? Your inputs and suggestions are highly appreciated.