User-Defined Types

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

 Type Employee 
 Name as String 
 Salary as Currency 
 Year 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 pull-down lists within the VBA editor. You can set use these as normal variables , as seen in the following:

 Dim temp As employee 
 temp.Name = "Richard Shepherd" 
 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 the variable name in 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 is to receive the data, a dot is used so that Name appears as a property:

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