You can combine variables of several different types to create user-defined types (known as structures in the C programming language). User-defined types are useful when you want to create a single variable that records several related pieces of information.
You create a user-defined type with the Type statement, which must be placed in the Declarations section of a standard module. User-defined types can be declared as Private or Public with the appropriate keyword, as shown in the following examples:
Private Type YourType
Public Type YourType
For example, you could create a user-defined type that records information about a computer system by placing the following code in the Declarations section of any standard module:
Public Type SystemInfo
varCPU As Variant
lngMemory As Long
intVideoColors As Integer
curCost As Currency
dtePurchase As Variant
End Type
You can declare local, private module-level, or public module-level variables of the same user-defined type. For example:
Dim sysMine As SystemInfo, sysYours As SystemInfo
The following table illustrates where, and with what scope, you can declare user-defined types and their variables.
| Procedure/Module | You can create a user-defined type as | Variables of a user-defined type can be |
| Procedures | Not applicable | Not applicable |
| Standard modules | Private or public | Private or public |
| Form or report modules | Private only | Private only |
Assigning and retrieving values from the elements of a user-defined variable is similar to setting and getting properties.
sysMine.varCPU = "486"
If sysMine.dtePurchase > #1/1/92# Then
You can also assign one variable to another if they are both of the same user-defined type. This assigns all the elements of one variable to the same elements in the other variable.
sysYours = sysMine
A user-defined type can contain an ordinary (fixed-size) array, as shown in the following example:
Type SystemInfo
varCPU As Variant
lngMemory As Long
strDiskDrives(25) As String ' Fixed-size array.
intVideoColors As Integer
curCost As Currency
dtePurchase As Variant
End Type
It can also contain a dynamic array:
Type SystemInfo
varCPU As Variant
lngMemory As Long
strDiskDrives() As String ' Dynamic array.
intVideoColors As Integer
curCost As Currency
dtePurchase As Variant
End Type
You can access the values in an array within a user-defined type in the same way that you access the property of an object. For example:
Dim sysMine As SystemInfo
sysMine.strDiskDrives(0) = "1.44 MB"
You can also declare an array of user-defined types:
Dim sysAll(100) As SystemInfo
Follow the same rules to access the components of each element in the array:
sysAll(5).varCPU = "386SX"
sysAll(intX).strDiskDrives(2) = "100M SCSI"
See Also For more information on arrays, see “Arrays” later in this chapter.
You can declare procedure arguments with a user-defined type.
Sub FillSystem(sysAny As SystemInfo)
sysAny.varCPU = "486"
sysAny.lngMemory = "24"
sysAny.curCost = "$3000.00"
sysAny.dtePurchase = Now
End Sub
Note If you want to pass a user-defined type in a form or report module, the procedure must be private.
You can return user-defined types from functions, and you can pass a user-defined type variable to a procedure as one of the arguments. Because user-defined types are always passed by reference, the procedure can modify the argument and return it to the calling procedure, as illustrated in the previous example.
User-defined types can also contain objects. For example:
Private Type AccountPack
frmInput As Form
dbsPayRollAccount As Database
End 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 may 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.
Nesting data structures can get as complex as you want. In fact, user-defined types can contain other user-defined types, as shown in the following example. To make your code more readable and easier to debug, try to keep all the code that defines user-defined types in one module. For example:
Type DriveInfo
Type As String
Size As Long
End Type
Type SystemInfo
varCPU As Variant
lngMemory As Long
strDiskDrives(26) As DriveInfo
curCost As Currency
dtePurchase As Variant
End Type
The following code demonstrates how you can refer to a nested user-defined type:
Dim sysAll(100) As SystemInfo
sysAll(1).strDiskDrives(0).Type = "Floppy"