Microsoft Access 2010 Bible

Chapter 14: Debugging Your Access Applications

531

FIGURE 14.7

Putting multiple declarations on one line makes finding a variable’s Dim statement difficult.

Other errors may be caused by using a variable in an inappropriate context, such as using a string

variable in a mathematical expression. In this particular case, VBA will use a numeric value stored

in the string variable without throwing an error, but a runtime error is thrown if the string variable

contains a text value (such as a person’s name).

The long declaration in Figure 14.7 contains another, more subtle error. Notice that the declara-

tion contains i, j As Integer at the end of the statement. Presumably the programmer intended

for both i and j to be the Integer data type, but this isn’t what actually happens. VBA requires

the As DataType clause for each variable declaration. If the As DataType is omitted (as it is for the

i variable), the variable is established as a Variant data type. Although the code you see in

Figure 14.7 runs without errors, because the i variable is a Variant, the code runs somewhat

more slowly than it would if i were an Integer.

Figure 14.8 shows the same declarations reconfigured as multiple Dim statements. You can much

more easily see the data type of the rs1 variable in Figure 14.8. Let your eye run down the list of

variable names until you reach rs1 and see that it’s a Recordset type variable. This is another

reason why short, descriptive variable names are preferred over long, descriptive names.

A second, less obvious change in Figure 14.8 is the fact that variables are grouped by data type. All

the Recordset variables are grouped together as are the Integer variables. You could carry this

grouping one step farther by sorting the variables alphabetically by data type.

Spreading out your variable declarations doesn’t appreciably affect compile times or runtimes.

There is no difference in code module size after the code has been reduced to a binary format by

the Access VBA compiler. In other words, you gain little by condensing variable declarations into a

few lines of code. Spreading out variable declarations makes them much easier to read without sac-

rificing execution or compilation speed.