Standard modules

Standard modules are independent from other Access objects, like forms and reports. Standard modules store code that is used from anywhere within your application. By default, these procedures are often called global or public because they're accessible to all elements of your Access application.

Use public procedures throughout your application in expressions, macros, event procedures, and other VBA code. To use a public procedure, you simply reference it from VBA code in event procedures or any other procedure in your application.

Standard modules are stored in the Module section of the Navigation pane. Form and report modules (see the next section) are attached to their hosts and are accessed through the Form Property Sheet or Report Property Sheet.

Class modules

The other type of module is called a class module. A class defines how an object behaves. You can create your own classes, called custom classes, but the most common class module you'll use is a class module that's bound to a form or report.

In the above example, you created a standard module using the Ribbon. For class modules that are bound to a form or report, the module is created automatically by Access whenever you add VBA code to the form or report.

The most important difference between standard modules and class modules is that class modules support events. Events respond to user actions and run VBA code that's contained within the event procedure.

Module sections

You may have noticed that when you created the module, there was already code in it. Depending on the options you have set for your environment, Access will insert code into new modules automatically.

The area above the first procedure in a module is called the declaration section. The declaration section is used to store options and variables that will apply to every procedure in the module. Two common option declarations are Option Compare Database and Option Explicit. Option Compare Database determines how two strings are compared to each other and directs VBA to use the same comparison method that the database uses. The other options for comparing strings are Option Compare Text and Option Compare Binary. Basically, Option Compare Text doesn't care whether the letters are uppercase or lowercase and Option Compare Binary does care. It's a little more complicated than that, but Option Compare Database is usually the best option.

Option Explicit directs VBA to warn you if you have undeclared variables. By setting this option, you're telling VBA that you intend to explicitly declare any variables that you'll use. You'll learn more about declaring variables later.

Everything below the declarations section is known as the procedure section or code section. This section contains the subprocedures and functions of the module. It's important to understand the differences between these two sections because you can't put statements that belong in the declaration section into the code section, nor can you put code in the declaration section. If you do, the VBE will let you know that it's not allowed.

Subprocedures

A subprocedure (or sub) is the simplest type of procedure in a VBA project. A subprocedure is nothing more than a container for VBA statements that typically perform a task such as opening a form or report or running a query.

Subprocedures have two required statements: Sub procname and End Sub. If those were the only two statements you had, it would be a pretty uninteresting sub, but it would be legal. In our example, the procedure is started with the Sub SquareIt() statement. The procedure ends with the End Sub statement.

When determining the name of your procedure, there are a few rules that you must follow. The most important rules to remember are that the name must begin with a letter, can't contain most punctuation, and can't be more than 255 characters long. Rules aside, you should pick names for your procedures that describe what they do in a way that will be obvious to you when you read them later. Procedure names like GetData() will likely be hard to understand later, but ReadDataFromEmployeeTable() will be crystal clear. You probably don't want to push the 255-character limit on procedure names, but don't be afraid to make long, descriptive names.

Variable declaration

The first statement in our simple subprocedure is a variable declaration statement. It starts with the Dim keyword, which is short for dimension. The variable's name, lNumber, comes next. Variable names follow the same rules as procedure names, described in the previous section. The As keyword follows the name, which is followed by the data type. In this case, lNumber was declared as a Long Integer data type.

Variables hold data that you can use later in your procedure. When you declare a variable with the Dim keyword, you're telling VBA to reserve a spot in the computer's memory to store that data. The amount of memory that VBA will reserve depends on the data type. In this example, you told VBA to hold enough memory to store a Long Integer, or 32 bits of memory.

The As datatype portion of the variable declaration statement is optional. You could declare lNumber with the statement:

Dim lNumber

When you omit the data type, VBA will determine an appropriate data type when you assign a value to the variable. That may seem like a handy service VBA is providing, but it's not a very good practice. VBA will assign a data type to the variable based on the first time you use it, but it doesn't know all the plans you have in mind for that variable. It may end up assigning a data type that's too small for what you need. Having VBA assign data types is also slower than if you assign them as you write the code.

Variable assignment

By declaring a variable with the Dim statement, you've reserved a place in memory where you can store data for later use. The next line in the procedure stores the number 2 in the variable lNumber. Here's what that line looks like in the procedure:

lNumber = 2

Assigning values to variables is easy. There are really only two things you need to remember:

• You must assign a value that is appropriate for the variable's data type. In this case, you're storing a number without a decimal in a variable declared as Long. If you tried to store data that isn't appropriate for the variable's data type, VBA would do its best to convert the value into the appropriate data type. If you tried to store the value 8.26, for example, in a Long variable, VBA would convert it to 8 by truncating the number to remove the decimals. If VBA were unable to convert the data, you'd get an error.

• The variable name goes on the left of the equal sign, and the value goes on the right. Everything on the right of the equal sign is evaluated before it's assigned to the variable. For this example, there's not much to evaluate because it's simply the number 2. Consider the following statement, which computes the product of two numbers and assigns it to a variable.

  dProduct = 3 * 6.1

  In that statement, 3 is multiplied by 6.1, and the result, 18.3, is assigned to the variable. That's still pretty straightforward, but consider yet another example.

  bIsEqual = dProduct = 18.3

  In this statement, bIsEqual is a variable declared as Boolean, and dProduct is a variable declared as Double. But there are two equal signs. The first equal sign is the assignment operator—setting a variable equal to a value. Any other equal signs (there's only one other, in this case) are comparison operators. Comparison operators return True or False. If dProduct were equal to 18.3, then bIsEqual would get the value True. Everything to the right of the first equal sign (the assignment operator) is evaluated first, and the result is assigned to the variable.

Functions

A function is very similar to a subprocedure, with one major exception: A function returns a value when it ends. A simple example is the built-in VBA Now() function, which returns the current date and time. Now() can be used virtually anywhere your application needs to use or display the current date and time. An example is including Now() in a report header or footer so that the user knows exactly when the report was printed.

Now() is just one of several hundred built-in VBA functions. As you'll see throughout this book, the built-in VBA functions provide useful and very powerful features to your Access applications.

In addition to built-in functions, you might add custom functions that return values required by your applications. An example is a data transformation routine that performs a mathematical operation (such as currency conversion or calculating shipping costs) on an input value. It doesn't matter where the input value comes from (table, form, query, and so on). The function always returns exactly the correct calculated value, no matter where the function is used.

Within the body of a function, you specify the function's return value by assigning a value to the function's name (and, yes, it does look pretty strange to include the function's name within the function's body). You then can use the returned value as part of a larger expression. The following function calculates the square footage of a room:

Function SquareFeet(dHeight As Double, _
    dWidth As Double) As Double


  'Assign this function's value:
  SquareFeet = dHeight * dWidth


End Function

This function receives two parameters: dHeight and dWidth. Notice that the function's name, SquareFeet, is assigned a value within the body of the function. The function is declared as a Double data type, so the return value is recognized by the VBA interpreter as a Double.

The main thing to keep in mind about functions is that they return values. The returned value is often assigned to a variable or control on a form or report:

dAnswer = SquareFeet(dHeight, dWidth)
Me!txtAnswer = SquareFeet(dHeight, dWidth)

If the function (or subroutine, for that matter) requires information (such as the Height and Width in the case of the SquareFeet function), the information is passed as arguments within the parentheses in the function's declaration.

The If…Then construct

An If statement can take a few different forms. The first form is the If…Then construct. It is a one line statement where the condition and the action are all in the same statement. In the following example, the sState variable is set if the sCity variable contains certain data.

If sCity = "Dallas" Then sState = "Texas"

VBA first evaluates sCity = "Dallas" and determines if the conditional is true or false. If it's true (that is, if sCity has been assigned the value Dallas), the portion of the statement after the Then keyword is executed. In this case, the sState variable is assigned the value Texas. If the conditional is false, the program moves on to the next line in the procedure and the sState variable doesn't change.

The If…End If construct

The next form is the If…End If construct. This construct, and the ones that follow, are commonly known as If blocks because they can contain more than one line of code (a block of code), unlike the If…Then construct that can only execute one line. The previous example can be rewritten as an If…End If.

If sCity = "Dallas" Then
   sState = "Texas"
End If

This example is exactly the same as the one before it. If the conditional statement is true, the single line in the If block is executed. The difference is when the conditional is false. In that case, the program braches to the line immediately following the End If statement and program execution continues.

The benefit of the If…End If construct is that you can execute multiple statements when a conditional is true. In the next example, two variables are assigned values when the conditional is true.

If sCity = "Dallas" Then
  sState = "Texas"
  dTaxRate = 0.075
End If

If the conditional is true, both statements are executed. Otherwise, the program branches to the line just below End If and continues executing.

The If…Else…End If construct

In the previous examples, one or more statements are executed when the conditional is true and nothing happens when the conditional is false. The Else keyword can be included in an If block to identify statements that should run when the conditional is false.

If sCity = "Dallas" Then
    sState = "Texas"
    dTaxRate = 0.075
Else
    sState = "Michigan"
    dTaxRate = 0.05
End If

When the conditional is true, the first two statements are executed (setting sState to Texas and dTaxRate to 0.075). Then the program branches to the line below End If and continues executing. The two statements between Else and End If aren't executed.

If the conditional is false, however, the program branches to the statement below the Else statement and skips the first two. It's very common to see a construct like this that executes certain lines of code when a condition is met and certain other lines when it's not.

You must use an If block to use an Else statement. Else statements do not work with the first construct (the If…Then construct).

The If…ElseIf…End If construct

The final If construct is yet another If block type of construct. Instead of only one conditional, there are multiple conditionals. The ElseIf statement defines as many other conditionals as you need.

If sCity = "Dallas" Then
    sState = "Texas"
    dTaxRate = 0.075
ElseIf sCity = "Detroit" Then
    sState = "Michigan"
    dTaxRate = 0.05
Else
    sState = "Oregon"
    dTaxRate = 0.0625
End If

The program flows through an If…ElseIf…EndIf construct much like it does through the others. If the first conditional is true, the statements in the first section are executed and the program branches to the line just below the End If. If the first conditional is false, the program branches to the second conditional (the first ElseIf) and tests that conditional. If none of the conditionals is true, the statements in the Else section are executed. The Else statement is optional when using ElseIf. If you omit the Else statement and none of the conditionals is true, no statements in the If block are executed.

Nested If statements

Nesting statements refers to putting statements inside a block of other statements. In the case of If, nesting means that one If block is inside another If block.

If sState = "Michigan" Then
    If sCity = "Detrioit" Then
        dTaxRate = 0.05
    ElseIf sCity = "Kalamazoo" Then
        dTaxRate = 0.045
    Else
        dTaxRate = 0
    End If
End If

The outer If block tests the sState variable. If that condition is true, the inner If block is executed and the sCity variable is tested. If the conditional in the outer If block is false, the program branches to the line below the End If statement that matches the If statement being evaluated. Proper indenting, though not required, is helpful to see which Else and End If statements go with which If statements.

Boolean values and conditionals

If statements are wonderful, and you'll see them in almost every piece of code you write. However, there are two situations in which they're misused. Consider this code fragment:

If bIsBuyer = True Then
    bIsInPurchasing = True
Else
    bIsInPurchasing = False
End If

This is a simple If…Else…End If construct where the conditional checks to see if the Boolean variable bIsBuyer is True. Based on the result of that conditional, another Boolean variable is set to True or False. There's nothing wrong with the code—it will compile and run just fine—but there is a way to simplify the code and make it more readable. First, comparing a Boolean variable to True or False is unnecessary because the variable already is true or false. The first line can be simplified to:

If bIsBuyer Then

Assuming bIsBuyer is True, then in the first example, the compiler evaluates bIsBuyer = True, which reduces to True = True, and of course that returns True. In the simpler example, bIsBuyer is evaluated and returns True. Because bIsBuyer is a Boolean variable, comparing it to a Boolean value is redundant.

The second simplification step is to remove the If statement altogether. Whenever you're setting a Boolean value in an If block, you should consider whether the Boolean value can be set directly.

bIsInPurchasing = bIsBuyer

This one line of code does the same things as the five lines we started with. If bIsBuyer is True, bIsInPurchasing will also be True. If bIsBuyer is False, bIsInPurchasing will also be False. There may be situations in which you need to set one variable to the opposite of the other. VBA provides the Not keyword to convert Booleans from True to False and vice versa.

bIsInPurchasing = Not bIsTruckDriver

The variable bIsTruckDriver is evaluated as either true or false and the Not keyword returns the opposite. If bIsTruckDriver is True, bIsInPurchasing will be assigned the value False.

When you have many conditions to test, the If…Then…ElseIf…Else conditions can get rather unwieldy. A better approach is to use the Select Case…End Select construct.

Auto Indent

Auto Indent causes code to be indented to the current depth in all successive lines of code. For example, if you indented the current line of code with two tabs, the next line of code that you create when you press Enter will automatically be indented the same amount.

Auto Syntax Check

When the Auto Syntax Check option is selected, Access checks each line of code for syntax errors as you enter it in the code editor and displays a message box when it finds one. Many experienced developers find this behavior intrusive and prefer to keep this option disabled. With Auto Syntax Check disabled, lines with syntax errors are colored red, but no message box is displayed. The benefit of the message box is that it gives you a little more information about the error it found, although often these messages are hard to understand.

Require Variable Declaration

This setting automatically inserts the Option Explicit directive into all new VBA modules in your Access application. This option is not selected by default. It's almost universally accepted that Option Explicit should be used and this option should be enabled. Any modules created before this option is set will not be updated to include Option Explicit. The good news is, you can simply type the directive into those modules.

Auto List Members

This option pops up a list box containing the members of an object's hierarchy in the code window. In Figure 24.6, the list of the DoCmd object's members appeared as soon as we typed as the period following DoCmd in the VBA statement. You select an item from the list by continuing to type it in or scrolling the list and pressing the spacebar.

Auto Quick Info

When Auto Quick Info has been selected, Access pops up syntax help (refer to Figure 24.7) when you enter the name of a procedure (function, subroutine, or method) followed by a period, space, or opening parenthesis. The procedure can be a built-in function or subroutine or one that you've written yourself in Access VBA.

Auto Data Tips

The Auto Data Tips option displays the value of variables when you hold the mouse cursor over a variable with the module in break mode. Auto Data Tips is an alternative to setting a watch on the variable and flipping to the Debug window when Access reaches the break point.

Project name

Certain changes in an application's structure require Access to recompile the code in the application. For example, changing the code in a standard module affects all statements in other modules using that code, so Access must recompile all the code in the application. Until the code is recompiled, Access “decompiles” the application by reverting to the plain-text version of the code stored in the ACCDB file and ignoring the compiled code in the ACCDB. This means that each line of the code must be interpreted at run time, dramatically slowing the application.

Sometimes insignificant modifications, such as changing the name of the project itself, are sufficient to cause decompilation. This happens because of the hierarchical nature of Access VBA. Because all objects are “owned” by some other object, changing the name of a high-level object might change the dependencies and ownerships of all objects below it in the object hierarchy.

Access maintains a separate, independent project name for the code and executable objects in the application. Simply changing the name of the ACCDB file is not enough to decompile the code in an Access application. By default, the project name is the same as the name of the ACCDB, but it's not dependent on it. You can assign a unique name to the project with the Project Name text box in the General tab of the Project Properties dialog box.

Project description

The project description is, as its name implies, a description for the project. Because this area is so small, it isn't possible to add anything of significance that might be helpful to another developer.

Conditional compilation arguments

Compiler directives instruct the Access VBA compiler to include or exclude portions of code, depending on the value of a constant established in the module's declarations section. Code lines that begin with # are conditionally compiled.

One of the limitations of using compiler directives is that the constant declaration is local to the module. This means that you have to use the #Const compiler directive to set up the constant in every module that includes the #If directive. This limitation can make it difficult to remove all the #Const compiler directives to modify the code at the conclusion of development.

For example, consider a situation in which you want to use conditional compilation to include certain debugging statements and functions during the development cycle. Just before shipping the application to its users, you want to remove the compiler directives from the code so that your users won't see the message boxes, status bar messages, and other debugging information. If your application consists of dozens of forms and modules, you have to make sure you find every single instance of the #Const directive to make sure you successfully deactivated the debugging code. (This is why it's such a good idea to apply a naming convention to the identifiers you use with the #Const directive.)

Fortunately, Access provides a way for you to set up “global” conditional compilation arguments. The General tab of the Project Properties dialog box contains the Conditional Compilation Arguments text box, where you can enter arguments to be evaluated by the conditional compilation directives in your code.

As an example, assume you've set up the following sort of statements in all the modules in your application:

#If CC_DEBUG2 Then
  MsgBox "Now in ProcessRecords()"
#End If

Instead of adding the constant directive (#Const CC_DEBUG2 = True) to every module in the application, you might enter the following text into the Conditional Compilation Arguments text box:

CC_DEBUG2 = -1

This directive sets the value of CC_DEBUG2 to –1 (True) for all modules (global and form and report class modules) in the application. You need to change only this one entry to CC_DEBUG2=0 to disable the debugging statements in all modules in the application.

Separate multiple arguments with colons—for example: CC_DEBUG1=0 : CC_DEBUG2=-1.

Project protection

The Protection tab contains a check box that, when checked, asks for a password when you (or someone else) tries to open the module. You must supply a password (and confirm it) when using this option. Don't forget your password, because there's no official way to get it back.