As is the case in any programming language, custom data types in VBA are derived from existing data types. A custom data type is a collection of related elements, possibly of different types, having a single name assigned to them.
Consider an application that is required to store and retrieve information about customers in a database. The database contains information that includes a customer's identification number, name, age, gender, and address. Certainly you could declare five separate variables for each of these items and your program could read/write information from/to the database using the five separate variables; however, this is a cumbersome approach that will end with a program that is longer, less efficient, and more difficult to read—not to mention, more difficult to write.
Of course, the answer to this problem is a custom data type derived from the data types of the original five variables. Custom data types in VBA are defined using the Type and End Type statements with the required elements declared between as shown in the following example:
Public Type CustomerInfo ID As Integer Name As String * 30 Age As Integer Gender As String * 1 Address As String * 50 End Type
In this example, I assigned the name CustomerInfo to a custom data type with five elements: two integer and three fixed length strings (see Chapter 2). A custom data type must be defined in the general declarations section of a module. The Private and Public keywords define the scope of the definition; private types are available only within the module where the declaration is made, and public types are available in all modules in the project.
It is important to distinguish between a variable declaration and a custom data type definition. The latter only defines the data type and does not create any variables. That is, defining a custom data type does not expose any data; therefore, assigning public scope to the definition of a custom data type is a perfectly reasonable thing to do. Just as you want to be able to declare integer variables throughout your program, you may also want to create variables of a custom type throughout your program.
To declare a variable of type CustomerInfo is like any other variable declaration. The following declaration creates a CustomerInfo variable named customer.
Dim customer As CustomerInfo
Individual elements for a variable of a custom data type are accessed using the dot (.) operator as shown in the following:
customer.ID = 1234 customer.Name = "Fred Flintstone" customer.Gender = "M" customer.Age = 40
Some other things you can do with custom data types include: declaring variable arrays, defining elements as arrays, and passing variables, or elements of variables, to procedures. In the Blackjack project, you will see a variable array declared from a custom data type with elements that are also declared as arrays.
Was this article helpful?