A user-defined type, known as a
struct or
record , allows you to create a variable containing several pieces of information. You will often use user-defined types to hold information from one or more records in memory. You can also use them to hold related information that you would otherwise store in several unrelated variables. Because you can instruct each element of a user-defined type to hold a particular type of data, you can define each element in the type to correspond to the type of data stored in a specific field of a table. A user-defined type might look like this:
Public Type TimeCardInfo TimeCardDetailID As Long TimeCardID As Long DateWorked As Date ProjectID As Long WorkDescription As String * 255 BillableHours As Double BillingRate As Currency WorkCodeID As Long End Type
This user-defined type stores time-card information for an employee. Notice that the code explicitly declares the type of data stored in each element. The code declares the element containing the string WorkDescription with a length of 255. User-defined types make code cleaner by storing related data as a unit. A user-defined type exists only in memory and is, therefore, temporary. It's excellent for information that needs to be temporarily tracked at runtime. Because it's in memory, it can be quickly and efficiently read from and written to.
NOTE
The code snippets shown in the previous example are located in the basDataHandling module on the CD that accompanies this book. |
You declare a user-defined type by using a Type statement that must be placed in the module's Declarations section. You can declare types as Public or Private within a standard module. You can use types in Form and Report modules, but you cannot declare them there.
A Type variable is an instance of the type in memory; you must declare it before you can use the type. To declare a Type variable, create a Local, Private, Module-Level, or Public variable based on the type. Depending on where you place this declaration and how you declare it (using keywords Dim, Private, or Public), you determine its scope. The same rules for any other kind of variable apply to Type variables. The Dim statement in the code that follows creates a variable called mtypTimeCardData. If you place this Dim statement in the module's General section, it's visible to all routines in that module (notice the m, indicating that it is declared at the module level). If you place it in a subroutine or function, it's local to that particular routine:
Dim mtypTimeCardData As TimeCardInfo Chapter 7, "VBA: An Introduction."
After you have declared a Type variable, you can store data in each of its elements. The following code in the frmTimeCardHours form stores information from the form into a Type variable called mtypTimeCardData. The code declares the Type variable as a Private variable in the General Declarations section of the form. The code declares the Type structure in basDataHandling.
Private Sub cmdWriteToType_Click() 'Retrieve control values and place them in the type structure mtypTimeCardData.TimeCardDetailID = Me.txtTimeCardDetailID mtypTimeCardData.TimeCardID = Me.txtTimeCardID mtypTimeCardData.DateWorked = Me.txtDateWorked mtypTimeCardData.ProjectID = Me.cboProjectID mtypTimeCardData.WorkDescription = Me.txtWorkDescription mtypTimeCardData.BillableHours = Me.txtBillableHours mtypTimeCardData.BillingRate = Me.txtBillingRate mtypTimeCardData.WorkCodeID = Me.cboWorkCodeID End Sub
NOTE
You can find the code for this chapter in the CHAP12EX.MDB database on the book's CD-ROM. |
The advantage of this code is that, rather than creating eight variables to store these eight pieces of related information, it creates one variable with eight elements. This method keeps things nice and neat.
To retrieve information from your Type variable, simply refer to its name, followed by a period, and then the name of the element. The following code displays a message box containing all the time card-hour information:
Private Sub cmdDisplayFromType_Click() 'Retrieve information from the type structure MsgBox "Timecard Detail ID Is " & mtypTimeCardData.TimeCardDetailID & Chr(13) & _ "Timecard ID Is " & mtypTimeCardData.TimeCardID & Chr(13) & _ "Date Worked Is " & mtypTimeCardData.DateWorked & Chr(13) & _ "Project ID Is " & mtypTimeCardData.ProjectID & Chr(13) & _ "Work Description Is " & Trim(mtypTimeCardData.WorkDescription) & Chr(13) & _ "Billable Hours Is " & mtypTimeCardData.BillableHours & Chr(13) & _ "Billing Rate Is " & mtypTimeCardData.BillingRate & Chr(13) & _ "Workcode ID Is " & mtypTimeCardData.WorkCodeID End Sub Chapter 16, "Error Handling: Preparing for the Inevitable," an exercise shows a user-defined type used to hold pertinent error information. The example then replaces the user-defined type with properties of a custom error class. Although user-defined types are still useful and are, in fact, necessary for many Windows API function calls, custom class modules have replaced much of their functionality.