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:
Company As String * 25 Contact As String * 15 RegionCode As Integer Sales As Long End Type
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 Customerlnfo
Each of the 100 elements in this array consists of four components (as specified by the user-defined data type, Customerlnfo). 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 = 150677
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
Was this article helpful?