In VBA, you can create your own data types using the Type keyword. It’s very similar to struct data type from other programming languages.
It consists of one or multiple existing types into one new type. A good example of using this data types is a database record. If you want to keep a record of your employees, it’s a good idea to use the Type structure.
|
1 2 3 4 5 6 |
Type Employee first_name As String last_name As String id_number As Integer salary As Long End Type |
A variable declared as Employee will have all these data types built-in. You declare user-defined types the same way you declare standard built-in VBA types.
|
1 |
Dim emp As Employee |
Now, you can access individual fields of this structure by using the dot (.) operator. You can assign a value to any of the Employee types.
|
1 2 3 4 |
emp.first_name = "John" emp.last_name = "Brown" emp.id_number = 1 emp.salary = 50000 |
Now, you can display these elements. In this example, we are going to do it inside MsgBox.
In order to run this function, you have to create a new module inside the VBA editor (Left Alt + F11).
Copy the following code into the module you created.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Type Employee first_name As String last_name As String id_number As Integer salary As Long End Type Sub CheckEmployee() Dim emp As Employee emp.first_name = "John" emp.last_name = "Brown" emp.id_number = 1 emp.salary = 50000 MsgBox "Employee " + emp.first_name + " " + emp.last_name + ", identified by id " + CStr(emp.id_number) + " earns " + CStr(emp.salary) + " dollars a year." End Sub |
Code explanation
The code is pretty straightforward. What probably needs explanation is the Long type in salary and the Cstr function used in MsgBox.
The Long type is used, instead of Int because we deal with big numbers.
The Cstr function converts the numeral value into a string. Otherwise, the code will result in an error.
This code will return the following result.
