User-Defined Data Types

User -Defined Data Types

VBA lets you create custom, or user-defined, data types (a concept much like Pascal records or C structures). A user-defined data type can ease your work with some types of data. For example, if your application deals with customer information, you might want to create a user-defined data type named CustomerInfo , as follows :

 Type CustomerInfo     Company As String     Contact As String     RegionCode As Long     Sales As Double End Type 

Note  

You define custom data types at the top of your module before any procedures.

After you create a user-defined data type, you use a Dim statement to declare a variable as that type. Usually, you define an array. For example:

 Dim Customers(1 To 100) As CustomerInfo 

Each of the 100 elements in this array consists of four components (as specified by the user-defined data type, CustomerInfo ). You can refer to a particular component of the record as follows:

 Customers(1).Company = "Acme Tools" Customers(1).Contact = "Tim Robertson" Customers(1).RegionCode = 3 Customers(1).Sales = 150674.98 

You can also work with an element in the array as a whole. For example, to copy the information from Customers(1) to Customers(2) , use this instruction:

 Customers(2) = Customers(1) 

The preceding example is equivalent to the following instruction block:

 Customers(2).Company = Customers(1).Company Customers(2).Contact = Customers(1).Contact Customers(2).RegionCode = Customers(1).RegionCode Customers(2).Sales = Customers(1).Sales