Declaring Variables and Constants

The key to learning VBA is the same as if you were learning a foreign language. You must learn the basics first. VBA, like any language, has its own syntax and components that you must combine in just the right way for Access to understand your message and respond. Until you learn these rules, you'll find it difficult, if not impossible, to speak to Access.

VBA uses variables and constants to represent a value. Technically, a variable is a small portion of memory that stores a piece of information, but people tend to think of them as names that represent data. Constants are similar to variables in that they store a value. What the two have in common is that both represent values or objects. The main difference between the two is that a constant represents a value that doesn't change, whereas variables can be updated at any time.

Declaring Variables

Variables are a representation of a value or an object. You assign a descriptive name using your naming convention, declare a data type for the variable and then use it, reuse it, and even change it.

Before you use a variable, declare it using the Dim statement in the form

 Dim variablename As [New] datatype 

where variablename identifies the variable by name, and datatype is one of many VBA data types. If you omit datatype, VBA defaults to a Variant. It's best to always explicitly declare a variable's data type. Variant variables require slightly more memory and are slightly slower than other types of variables. On the other hand, they don't limit the type of data you store. But you are unlikely to have the requirement to store many types of data in a single variable, which is the only good use for a variant.

The New keyword is optional and can only be used when declaring an object variable. To learn more about this issue, read Chapter 8, "Understanding Objects (p. 113).

TIP

Generally, Dim statements appear at the beginning of a procedure. This arrangement isn't required, but you'll find most developers adhere to this guideline. By grouping them at the beginning, you can find them much quicker.

You can declare a number of variables in a single line by separating them with commas, as follows:

 Dim variable1 As datatype, variable2 As datatype 

TIP

Declared variables are supported by IntelliSense. That means you can choose the variable from the completion drop-down list rather than enter it from the keyboard thus avoiding typos.

Using Option Explicit

By default, VBA lets you enter undeclared variables in your code. To illustrate, open a standard blank module and enter the following code:

 Private Function DeclarationTest()   varValue = "Undeclared variable"   Debug.Print varValu End Function 

Be sure to open the Immediate window if necessary by pressing Ctrl+G. Next, position the cursor inside the procedure and press F5 to execute it. Did you expect to see the string, "Undeclared variable" in the Immediate window? The reason you don't see the string is because varValue is misspelled in the Debug.Print statement. The variable varValue does equal the string "Undeclared variable;", but the variable varValu equals Empty at this point. Finding the problem can be difficult, especially in a long and complex procedure.

VBA can force you to declare variables to avoid such problems which is a good practice. To do so, enter the Option Explicit statement in the module's General Declarations area. After doing so, execute the procedure again. This time, VBA returns the error shown in Figure 3.1.

Figure 3.1. Undeclared variables return an error when you're using Option Explicit.

TIP

The Option Explicit statement can mean the difference between errors and no errors and all you have to do is turn it on. We strongly recommend that you enable this feature and leave it on to avoid the almost unavoidable typos that occur when writing code.

Click OK to close the error message, and then click Reset to clear the error. Declare the variable by adding the following Dim statement to the beginning of the procedure:

 Dim varValue As Variant 

Next, run the procedure a third time. This time, VBA catches the misspelled variable varValu, as shown in Figure 3.2. Click OK and then click Reset to clear the error and fix the misspelled variable. At this point, if you run the procedure, you'll see the expected string in the Immediate window, as shown in Figure 3.3.

Figure 3.2. VBA catches a misspelled variable.

Figure 3.3. After fixing the misspelled variable, VBA runs the procedure as expected.

The previous method enables the automatic variable declaration feature for only the current module. To enable this feature for all new modules, do the following:

1. In the VBE, choose Tools, Options.
   
   

2. Click the Editor tab.
   
   

3. Check the Require Variable Declaration option shown in Figure 3.4.
   
   

   Figure 3.4. Enable the automatic variable declaration feature.

   

   
   

4. Click OK to close the Options dialog box.
   
   

CAUTION

Enabling the Require Variable Declarations feature for all modules affects only new modules inserted after enabling the feature. You must update any existing modules by adding the Option Explicit statement manually.

Naming Variables

Your naming convention should also cover variables. Aside from your naming convention, there are a few inherent rules you need to consider:

• A variable name must begin with an alphabetic character.

• Don't use the following special characters in a variable name: ., %, $, !, #, @, and $.

• Variable names must be unique. It really isn't as simple as all that, but for now, knowing that you can't give two variables in the same procedure the same name is sufficient.

• A variable name can consist of up to 255 characters.

Naming conventions are covered in Chapter 2, in the section "Establishing Good Habits in Coding," (p. 28).

Declaring Constants

You'll find that the term constant has many meanings in VBA. A constant represents a literal value, much in the same way a variable represents a value or an object. The difference is, the value of a constant can't be changed while the code is executing not even by mistake.

Use the Const statement to declare a constant in the form

 [Public | Private] Const constantname As datatype = expression 

Items in square brackets are optional, and the vertical bar indicates a choice. So a constant declaration can start with Public, or Private, or just Const. In any case, constantname identifies the constant by name, datatype specifies the constant's data type, and expression is the literal value that the constant equals. The expression argument can't refer to a variable, a result of a function procedure, or include one of VBA's many built-in functions.

The following example illustrates the use of a constant:

1. In a standard module, enter the following statement in the General Declarations area:
   
   

   
   

    Const conMessage As String = "Undeclared variable" 

   
   

2. Enter the following procedure using the Insert Procedure dialog box or from the keyboard:
   
   

   
   

    Private Function ConstantTest()   Dim varValue As Variant   varValue = conMessage   Debug.Print varValue End Function 

   
   

3. With the insertion point somewhere inside ConstantTest(), press F5 to execute the procedure. As you can see in Figure 3.5, the Debug.Print statement prints the contents of the variable varValue, which refers to conMessage (the constant).
   
   

   Figure 3.5. The varValue variable equals the constant named conMessage.

   

   
   

Change the string expression in the Const statement to "Constant" and run the procedure again. This time, varValue equals "Constant", as shown in Figure 3.6. Now imagine several references to conMessage throughout your module. Instead of updating each and every reference, you change just the value of conMessage in the Const declaration statement.

Figure 3.6. Change the constant value in the Const declaration statement.

Intrinsic Constants

In addition to letting you define your own constants, VBA offers a number of predefined constants, known as intrinsic constants, that you can use to specify specific data types (and other values).

There are two advantages to using these constants. First, you don't have to define them yourself. Second, they improve the readability of your code. The intrinsic constant's name describes its value or purpose, thus making it instantly recognizable. To assign one of these subtypes to a variable, use that subtype's corresponding intrinsic constant as follows:

 varValue = vbEmpty 

Intrinsic constants are predefined by VBA; you don't define them in your code, you simply refer to them in expressions and statements. You revisit intrinsic constants in the next section.