User-Defined Types

You can also define your own type of variable by employing existing variable types using the Type keyword. This must be entered in the declarations section of a module:

Type Employee
      Name as String
      Salary as Currency
      Years as Integer
End Type

This creates a new type called Employee, which holds information for Name, Salary, and Years of Service.

You can then use this type in exactly the same way as the built-in variable types. It is even automatically included in the drop-down lists within the VBA editor. You can use these as normal variables, as seen in the following:

Dim temp As employee
temp.Name = "Michael Red"
temp.Salary = 10000
temp.Years = 5
MsgBox temp.Name
MsgBox temp.Salary
MsgBox temp.Years

Note that the variable name has a list box showing the properties or fields for this data type as you type in the variable name within the code.

You can also create an array of this type and effectively use it as an object in its own right. Notice that after specifying the name of the array and the subscript index that will receive the data, a dot is used so Name appears as a property:

Dim temp(10) as employee
temp(0).Name = "Richard Shepherd"