Microsoft has introduced a structure into VBA to categorize the plethora of symbolic constants. This structure is called an enum , which is short for enumeration. For instance, the built-in enum for the constant values that can be returned when the user dismisses a message box (by clicking on a button) is:

Enum VbMsgBoxResult
     vbOK = 1
     vbCancel = 2
     vbAbort = 3
     vbRetry = 4
     vbIgnore = 5
     vbYes = 6
     vbNo = 7
End Enum

When the user hits the OK button on a dialog box (assuming it has one), VBA returns the value vbOK. Certainly, it is a lot easier to remember that VBA will return the symbolic constant vbOK than to remember that it will return the constant 1. (We will discuss how to get and use this return value later.)

VBA also defines some symbolic constants that are used to set the types of buttons that will appear on a message box. These are contained in the following enum (which includes some additional constants not shown):

Enum VbMsgBoxStyle
     vbOKOnly = 0
     vbOKCancel = 1
     vbAbortRetryIgnore = 2
     vbYesNoCancel = 3
     vbYesNo = 4
     vbRetryCancel = 5
End Enum

To illustrate, consider the following code:

If MsgBox("Proceed?", vbOKCancel) = vbOK Then
   ' place code to execute when user hits OK button
Else
 ' place code to execute when user hits any other button
End If

In the first line, the code MsgBox("Proceed?", vbOKCancel) causes Access to display a message box with an OK button and a Cancel button and the message “Proceed?”, as shown in Figure 10-1.

Figure 10-1. Example message box

If the user clicks the OK button, Access returns the constant value vbOK; otherwise, it returns the value vbCancel. Thus, the If statement in the first line distinguishes between the two responses. (We will discuss the If statement in detail in Chapter 13. Here we are interested in the role of symbolic constants.)

In case you are not yet convinced of the value of symbolic constants, consider the following enum for color constants:

Enum ColorConstants
     vbBlack = 0
     vbBlue = 16711680
     vbMagenta = 16711935
     vbCyan = 16776960
     vbWhite = 16777215
     vbRed = 255
     vbGreen = 65280
     vbYellow = 65535
End Enum

Which would you rather type:

ATextBox.ForeColor = vbBlue

or:

ATextBox.ForeColor = 16711680

Need I say more?

To declare a variable means to define its data type. Variables are declared with the Dim keyword (or with the keywords Private and Public, which we will discuss later in this chapter). Here are some examples:

Dim Name As String
Dim Holiday As Date
Dim Age As Integer
Dim Height As Single
Dim Money As Currency
Dim db as Database
Dim rs as Recordset

The general syntax of a variable declaration is:

Dim VariableName As DataType

If a particular variable is used without first being declared, or if it is declared without a data type mentioned, as in Dim Age, then VBA will treat the variable as having type Variant. As we can see from Table 10-1, this is generally a waste of memory, since variants require more memory than most other types of variables.

For instance, an integer variable requires 2 bytes, whereas a variant that holds the same integer requires 16 bytes, which is a waste of 14 bytes. It is common to have hundreds or even thousands of variables in a complex program, and so the memory waste could be significant. For this reason, it is a good idea to declare all variables.

Perhaps more importantly, much more overhead is involved in maintaining a Variant than its corresponding String or Integer, for example. This in turn means that using Variants typically results in worse performance than using an equivalent set of explicit data types.

We can place more than one declaration on a line to save space. For instance, the line:

Dim Age As Integer, Name As String, Money As Currency

declares three variables. Note, however, that a declaration such as:

Dim Age, Height, Weight As Integer

is legal, but Age and Height are declared as Variants, not Integers. In other words, we must specify the type for each variable explicitly.

It is also possible to tell VBA the type of the variable by appending a special character to the variable name. In particular, VBA allows the type-declaration suffixes shown in Table 10-2. (I personally dislike these suffixes, but they do save space.)

For instance, the line:

Dim Name$

declares a variable called Name$ of type String. We can then write:

Name$ = "Donna"

Finally, let us note that although Access allows variable and constant declarations to be placed anywhere within a procedure (before the item is used, that is), it is generally good programming practice to place all such declarations at the beginning of the procedure. This improves code readability and makes housekeeping much simpler.

I have said that using the Variant data type generally wastes memory and often results in poorer performance, and that all variables are assumed to be variants unless you specify otherwise. There is an additional, even more important reason to declare all variables explicitly. This has to do with making typing errors, which we all do from time to time. In particular, if we accidentally misspell a variable name, VBA will think we mean to create a new variable!

Option Explicit

The numeric data types include Integer, Long, Single, Double, and Currency. A long is also sometimes referred to as a long integer.

A Boolean variable is a variable that takes on one of two values: True or False. This is a very useful data type that was only recently introduced into VBA. Prior to its introduction, VBA recognized 0 as False and any nonzero value as True, and you may still see this usage in older code.

A string is a sequence of characters. (An empty string has no characters, however.) A string may contain ordinary text characters (letters, digits, and punctuation), as well as special control characters such as vbCrLf (carriage return/line feed characters) or vbTab (tab character). As we have seen, a string constant is enclosed within quotation marks. An empty string is denoted by a pair of adjacent quotation marks, as in:

EmptyString = ""

There are two types of string variables in VBA: fixed-length and variable-length. A fixed-length string variable is declared as follows:

DimFixedStringVarName As String * StringLen

where StringLen specifies the number of characters reserved for the string. For instance, the following statement declares a fixed-length string of length 10 characters:

Dim sName As String * 10

Observe that the following code, which concatenates two strings:

Dim s As String * 10
s = "test"
Debug.Print s & "/"

produces the output:

test      /

This shows that the content of a fixed-length string is padded with spaces in order to reach the correct length.

A variable-length string variable is a variable that can hold strings of varying lengths (at different times, of course). Variable-length string variables are declared simply as:

DimVariableStringVarName as String

As an example, the code:

Dim s As String
s = "test"
Debug.Print s & "/"
s = "another test"
Debug.Print s & "/"

produces the output:

test/
another test/

Variable-length string variables are used much more often than fixed-length strings, although the latter have some very specific and important uses (which I will not go into in this book).

Variables of the Date data type require 8 bytes of storage and are actually stored as decimal (floating-point) numbers that represent dates ranging from January 1, 100 to December 31, 9999 (no year 2000 problem here) and times from 0:00:00 to 23:59:59.

As discussed earlier, literal dates are enclosed within number signs, but when assigning a date to a date variable, we can also use valid dates in string format. For example, the following are all valid date/time assignments:

Dim dt As Date
dt = #1/2/98#
dt = "January 12, 2001"
dt = #1/1/95#
dt = #12:50:00 PM#
dt = #1/13/76 12:50:00 PM#

VBA has a large number of functions that can manipulate dates and times. If you need to manipulate dates or times in your programs, you should probably spend some time with the Access VBA help file. (Start by looking under “Date Data Type.”)

The Variant data type provides a catch-all data type that is capable of holding data of any other type except fixed-length string data and user-defined types. I have already noted the virtues and vices of the Variant data type and discussed why variants should generally be avoided.

Access VBA/DAO has a number of additional data types that fall under the general category of Object data type. Here is a sampling:

I devote much of this book to studying the objects in the DAO object model, for it is through these objects that we can manipulate Access databases programmatically. (I will briefly describe the Access object model as well, but not go into its details, for its primary use is to manipulate Access forms and reports, not actual data. In fact, the Access object model does not even have a Table object!)

Dim rs As Object

Dim rs As Recordset

Dim int As Integer    ' nonobject (standard) variable declaration
Dim db As Database    ' object variable declaration

Set db = CurrentDb

An array variable is a collection of variables that use the same name, but are distinguished by an index value. For instance, to store 10 fields objects in variables, we could declare an array variable as follows:

Dim MyFields(1 To 10) As Field

The array variable is MyFields. It has size 10. The lower bound of the array is 1, and the upper bound is 10. Each of the variables:

MyFields(1), MyFields(2),..., MyFields(10)

are Field variables. Note that if we omit the first index in the declaration, as in:

Dim MyFields(10) As Field

then VBA will automatically set the first index to 0, so the size of the array will be 11.

The virtue of declaring array variables is clear, since it would be very unpleasant to have to declare 10 separate variables. In addition, as we will see, there are ways to work collectively with all of the elements in an array, using a few simple programming constructs. For instance, the following code sets all 10 Field types to Integer:

For i = 1 To 10
   MyFields(i).Type = dbInteger
Next i

Dim Stats(1 To 10, 1 To 100) As Integer

Dim FileName(1 To 10) As String

Dim FileName(  ) as String

ReDim FileName(1 to 10)

ReDim FileName(1 to 100)

ReDim Preserve FileName(1 to 200)

If UBound(FileName) < iNextFile Then 
   ReDim Preserve FileName(UBound(FileName) + 10)
End If

VBA programs can get very complicated, and we can use all the help we can get in trying to make them as readable as possible. In addition, as time goes on, the ideas behind the program begin to fade, and we must rely on the code itself to refresh our memory. This is why adding copious comments to a program is so important.

Another way to make programs more readable is to use a consistent naming convention for constants, variables, procedure names, and other items. In general, a name should have two properties. First, it should remind the reader of the purpose or function of the item. For instance, suppose we want to assign Field variables to some fields in an Access table. The code:

Dim fld1 As Field, fld2 as Field
Set fld1 = Fields("Sales")
Set fld2 = Fields("Transactions")

is perfectly legal, but 1,000 lines of code and 6 months later, will we remember which field is fld1 and which is fld2? Since we went to the trouble of naming the fields in a descriptive manner, we should do the same with the fld variables, as in:

Dim fldSales As Field, fldTrans as Field
Set fldSales = Fields("Sales")
Set fldTrans = Fields("Transactions")

Of course, there are exceptions to all rules, but in general, it is better to choose descriptive names for variables (as well as other items that require naming, such as constants, procedures, controls, forms, and code modules).

Second, a variable name should reflect something about the properties of the variable, such as its data type. Many programmers use a convention in which the first few characters of a variable’s name indicate the data type of the variable. This is sometimes referred to as a Hungarian naming convention, after the Hungarian programmer Charles Simonyi, who is credited with its invention.

Table 10-3 and Table 10-4 describe the naming convention that we will generally use for nonobject and object variables, respectively. Of course, you are free to make changes for your own personal use, but you should at least try to be reasonably consistent. These prefixes are intended to remind us of the data type, but it is not easy to do this perfectly using only a couple of characters, and the longer the prefix, the less likely it is that we will use it! (Note the c prefix for integers or longs. This is a commonly used prefix when the variable is intended to count something.)

In addition to a data type, every variable has a scope and a lifetime. Some programmers advocate including a hint as to the scope of a variable in the prefix, using g for global and m for module level. For example, the variable giSize is a global variable of type Integer. I will discuss the scope and lifetime of a variable next (but I will not generally include scope prefixes in variable names).

Variables and constants have a scope, which indicates where in the program the variable or constant is recognized (or visible to the code). The scope of a variable or constant can be either procedure-level (also called local), module-level private, or module-level public. The rules may seem a bit involved at first, but they do make sense.

Procedure-level (local) variables

A local or procedure-level variable or constant is a variable or constant that is declared within a procedure, as is the case with the variable LocalVar and the constant LocalConstant in Figure 10-2. A local variable or constant is not visible outside of the procedure. Thus, for instance, if we try to run ProcedureB in Figure 10-2, we will get the error message, “Variable not defined,” and the name LocalVar will be highlighted.

Figure 10-2. Examples of variable scope

One of the advantages of local variables is that we can use the same name in different procedures without conflict, since each variable is visible only in its own procedure.

Public APubInt As Integer
Public Const APubConst = 7

Private APrivateInt As Integer
Private Const APrivateConst = 7

Variables also have a lifetime. The difference between lifetime and scope is quite simple: lifetime refers to how long (or when) the variable is valid (that is, retains a value), whereas scope refers to where the variable is accessible or visible.

To illustrate the difference, consider the following procedure:

Sub ProcedureA(  )
   Dim LocalVar As Integer
   LocalVar = 0
   Call ProcedureB
   LocalVar = 1
End Sub

Note that LocalVar is a local variable. When the line:

Call ProcedureB

is executed, execution switches to ProcedureB. While the lines of ProcedureB are being executed, the variable LocalVar is out of scope, since it is local to ProcedureA. But it is still valid. In other words, the variable still exists and has a value, but it is simply not accessible to the code in ProcedureB. In fact, ProcedureB could also have a local variable named LocalVar, which would have nothing to do with the variable of the same name in ProcedureA.

Once ProcedureB has completed, execution continues in ProcedureA with the line:

LocalVar = 1

which is a valid instruction, since the variable LocalVar is back in scope.

Thus, the lifetime of the local variable LocalVar extends from the moment that ProcedureA is entered to the moment that it is terminated. This includes the period during which ProcedureB is executed as a result of the call to this procedure, even though during that period, LocalVar is out of scope.

Incidentally, you may notice that the Microsoft help files occasionally get the notions of scope and visibility mixed up a bit. The creators of the files seem to understand the difference, but they don’t always use the terms correctly.

Sub ProcedureA(  )
   Call ProcedureB
   Call ProcedureB
   Call ProcedureB
   Call ProcedureB
   Call ProcedureB
End Sub
Sub ProcedureB(  )
   Dim x As Integer
   x = 5
   . . . 
End Sub

Sub StaticExample(  )

' Declare static Boolean variable
Static NotFirstTime As Boolean

' If first time, then run special code
If NotFirstTime = False Then

   ' Code here that runs only the first time procedure is called

   ' No longer the first time
   NotFirstTime = True

Else

   ' Not the first time
   ' Code here will run if not first time

End If

   ' Code here will always run (unless procedure is exited beforehand)

End Sub

NotFirstTime = True

If NotFirstTime = False

When a procedure begins execution, all of its local variables are automatically initialized, that is, given initial values. In general, however, it is not good programming practice to rely on this initialization, since it makes the program less readable and somewhat more prone to logical errors. Thus, it is a good idea to initialize all local variables explicitly, as in the following example:

Sub Example(  )

Dim x As Integer
Dim s As String

x = 0       ' Initialize x to 0
s = ""      ' Initialize s to empty string

' more code here . . .

End Sub

Note, however, that static variables cannot be initialized, since that defeats their purpose! Thus, it is important to know the following rules that VBA uses for variable initialization (note also that they are intuitive):

The Nothing keyword actually has several related uses in Access VBA. It is used to release an object variable, as in:

Set rs = Nothing

and to determine if an object variable references a valid object, as in:

If rs Is Nothing

It is also sometimes used as a return value for some functions, generally to indicate that some operation has failed. Finally, it is used to initialize object variables.