Excel VBA Type

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.

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 as you declare standard built-in VBA types.

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.

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 VBA editor (Left Alt + F11).

Copy the following code into the module you created.

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.

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.

Posted in vba