Page 84 - Excel Progamming Weekend Crash Course

d540629 ch04.qxd 9/2/03 9:28 AM Page 59

Session 4 — Syntax and Data in VBA 59

User-Defined Types

A user-defined type (UDT) allows the programmer to define custom data elements that are
specifically suited for the data at hand. A UDT can contain two or more elements, each
element being a variable or array. UDTs are defined with the Type...End Type statement:
Type TypeName
Element1Name As Type
Element2Name As Type
...
End Type

Each element follows the same rule as regular VBA variables. You can mix data types in
a UDT as dictated by the needs of your program, and you can use any available type. An
element can be an array as well as another UDT. Here’s an example of a UDT designed to
hold employee information:
Type EmployeeInfo
FirstName As String
LastName As String
SSNum As String * 10
HireDate As Date
HealthPlan As Boolean
Salary As Currency
End Type
It may be useful to see exactly what the lines of this code do:
The first line begins the definition of a UDT named “EmployeeInfo.”
The second line declares a variable in the UDT named “FirstName” of type String.
The third line declares a variable in the UDT named “LastName” of type String.
Remaining lines declare other variables within the UDT.
The last line marks the end of the UDT definition.
The definition of a UDT must be placed in a module, outside of any procedures. After a
UDT is defined, you can use it in variable declarations such as VBA’s built-in data types.
Here are some examples:
Dim SomeEmployee As EmployeeInfo
Dim AllEmployees(100) As EmployeeInfo
To access the elements of a UDT, use the name.element syntax. Thus:

SomeEmployee.FirstName = “Jane”
SomeEmployee.LastName = “Jones”
AllEmployees(1).FirstName = “Sam”