Chapter 11: Mastering VBA Data Types and Procedures
425
In this example code, the txtSupervisor text box on the form is always empty and is never
assigned a value. A line near the bottom of this procedure assigns the value of an implicitly
declared variable named Superviser to the txtSupervisor text box. Notice that the name of
the variable (Superviser) is a misspelling of the intended variable (Supervisor). Because the
source of the assignment appears to be a variable, VBA simply creates a new variant named
Superviser and assigns its value (which is, literally, nothing) to the txtSupervisor text
box to it. And, because the new Superviser variable has never been assigned a value, the text
box always ends up empty. Misspellings such as this are very common and easy to overlook in
long or complex procedures.Furthermore, the code shown in this example runs fine, and causes no problem. Because this pro-
cedure uses implicit variable declaration, Access raises no error because of the misspelling, and the
problem isn’t detected until someone notices the text box is always empty. Imagine the problems
you’d encounter in a payroll or billing application if variables went missing because of simple
spelling errors!When you declare a variable, Access sets up a location in the computer’s memory for storing a
value for the variable ahead of time. The amount of storage allocated for the variable depends on
the data type you assign to the variable. More space is allocated for a variable that will hold a cur-
rency amount (such as $1,000,000) than for a variable that will never hold a value greater than,
say, 255. This is because a variable declared with the currency data type requires more storage
than another variable declared as a byte data type. (Data types are discussed later in this chap-
ter, in the “Working with Data Types” section.)Even though VBA doesn’t require you to declare your variables before using them, it does provide
various declaration commands. Getting into the habit of declaring variables is good practice. A
variable’s declaration assures that you can assign only a certain type of data to it — always a
numeric value or only characters, for example. In addition, you attain real performance gains by
pre-declaring variables.Tip
A programming best practice is to explicitly declare variables at the top of the procedure; this makes the pro-
gram easier for other programmers to work with later on.
The Dim keyword
To declare a variable, you use the Dim statement. (Dim is an abbreviation of the archaic Dimension
programming term — because you’re specifying the dimension of the variable.) When you use the
Dim statement, you must supply the variable name that you assign to the variable. Here’s the for-
mat for the Dim keyword:Dim [VariableName] [As DataType]The following statement declares the variable xBeeps as an integer data type:Dim xBeeps As Integer