Creating User-Defined Data Types

VBA's built-in data types cover a lot of ground and should be sufficient to meet most of your needs. However, VBA also lets you set up user-defined data types. These are handy for storing similar types of data in a single structure. For example, suppose your program is working with car makes and models. In this case, you might need to work with values for the manufacturer, the model, the year the car was made, and the purchase price. One way to go about this would be to set up variables for each item of data, like so:

Dim carMake As String
Dim carModel As String
Dim yearMade As Integer
Dim carPrice As Currency

This approach works, but what if you need to work with the data from multiple cars at once? You could set up new variables for each car, but that seems too inefficient. A better way is to define a "CarInfo" data type that holds all the required information. Here's how you would do it:

Type CarInfo
    make As String
    model As String
    made As Integer
    price As Currency
End Type

The Type keyword tells VBA that you're creating a user-defined data type. In this example, the new data type is named CarInfo. The statements between Type and End Type define the various elements within the new data type. Note that you need to place this definition at the module level; VBA doesn't let you define new data types within a procedure.

Now you use the data type as you would any other. For example, the following statement declares a new variable named myCar to be of type CarInfo:

From here, you refer to the various elements within the data type by separating the variable name and the element name with a period (.), like so:

myCar.make = "Porsche"
myCar.model = "911 Turbo"
myCar.made = 2007
myCar.price = 122000