You can combine variables of several different data types to create user-defined data types (known as structures in the C programming language). A user-defined type is useful when you want to create a single variable that records several related pieces of information. The user-defined type encapsulates the information and makes it easier to process; for example, you can pass the entire structure to a procedure or assign all the values at once by assigning one variable to another if they're both of the same user-defined type.
Tip
Because the Variant data type can store many different types of data, a Variant array can be used in many situations where you might expect to use a user-defined type. A Variant array is actually more flexible than a user-defined type because you can change the type of data you store in each element at any time, and you can make the array dynamic so that you can change its size as necessary. However, a Variant array always uses more memory than an equivalent user-defined type.
You create a user-defined type using the Type statement, which must be placed in the declarations section of a module. User-defined types can be declared as either Private or Public with the appropriate keyword, as in the following example.
Private Type yourDataType Public Type myDataType
For example, you could create a user-defined type that records information about a computer system.
Private Type systemInfo
cpu As Variant
memory As Long
videoColors As Integer
cost As Currency
purchaseDate As Variant
End Type
You can declare local, private, or public variables of the same user-defined type.
Dim mySystem As systemInfo, yourSystem As systemInfo
The rules for determining the scope and lifetime of a variable with a user-defined type are identical to those for other types of variables. For more information, see "Specifying Variable Scope and Lifetime" earlier in this chapter.
Note
Notice the difference between declaring the user-defined data type (using the Type statement) and declaring a variable of the user-defined type (using the Dim statement). Declaring the user-defined type simply tells Visual Basic how the data type is structured; declaring a variable actually reserves space for information organized according to the user-defined type. The scope of the Type declaration and the scope of the declared variable are completely independent; for example, you can declare a local variable using a data type that has been declared as Public.
You access an individual variable within a structure by writing the name of the variable that has a user-defined type, followed by a period and the name of the individual variable.
mySystem.cpu = "486" If mySystem.purchaseDate > #1/1/92# Then
You can also assign one variable to another if they're both of the same user-defined type. This assigns all the elements of one variable to the same elements in the other variable, as in the following example.
yourSystem = mySystem
A user-defined type can contain a static (fixed-size) array, as in the following example.
Type systemInfo
cpu As Variant
memory As Long
diskDrives(25) As String ' Fixed-size array.
videoColors As Integer
cost As Currency
purchaseDate As Variant
End Type
It can also contain a dynamic array.
Type systemInfo
cpu As Variant
memory As Long
diskDrives() As String ' Dynamic array.
videoColors As Integer
cost As Currency
purchaseDate As Variant
End Type
You can access an array within a user-defined type just as you access any individual variable within the user-defined type, that is, by writing the name of the structured variable that contains the array, followed by a period and then the name of the array. You can then specify an individual element in the array with index numbers, just as you normally access an array member.
Dim mySystem As systemInfo mySystem.diskDrives(0) = "1.44 MB"
You can also declare an array of user-defined types.
Dim allSystems(100) As systemInfo
Follow the same rules to access the components of this data structure.
allSystems(5).CPU = "386SX" allSystems(X).DiskDrives(2) = "100M SCSI"
User-defined types can contain objects.
Private Type inputData
wkInput as Worksheet
rInput as Range
End Type
User-defined types can contain other user-defined types, as shown in the following example. To make your macros more readable and easier to debug, try to keep all the code that defines user-defined data types in one module.
Type driveInfo
type As String
size As Long
End Type
Type systemInfo
cpu As Variant
memory As Long
diskDrives(26) As driveInfo
cost As Currency
purchaseDate As Variant
End Type
Dim allSystems(100) As systemInfo
allSystems(1).diskDrives(0).type = "Floppy"