Microsoft Visual Basic for Applications (VBA) is a high-level programming language developed for the purpose of creating Windows applications. A common set of VBA instructions can be used with all Microsoft Office products, and each product also has its own set. VBA includes hundreds of commands and can be extended indefinitely with third-party controls and routines you write yourself.
You can use VBA to integrate features of Microsoft Word, Microsoft Excel, Microsoft Outlook, and other applications, as well as Access. If you work with Office applications and have any interest in programming, VBA is well worth learning. This topic provides an overview of VBA. For more information about this subject, see the VBA online Help file and other books from Microsoft Press.
Tip
The VBA online Help file is not installed in the default Office setup. However, if you attempt to use it by clicking Microsoft Visual Basic Help on the Help menu displayed from within the Visual Basic Editor, you will be prompted to insert the installation CD, and the files will be installed.
VBA programs are called procedures or simply code. Access refers to VBA procedures as modules and represents them with the Modules object on the Objects bar. In VBA itself, there are two types of modules: class modules, which are associated with a specific form or report, and standard modules, which contain general procedures that are not associated with any object. When you use the Switchboard Manager or the Command Button tool in the Toolbox, VBA code is automatically attached to your forms, so you might have already used VBA without realizing it.
When you write or edit VBA code, you do so in the Visual Basic Editor, sometimes referred to as the Visual Basic Integrated Development Environment (IDE). If you are working in Access and you have selected a form, report, or module in the database window, a Code button becomes available on the Access window’s toolbar. Clicking this button opens the Visual Basic Editor and places the insertion point in the code for the highlighted object. If you are working in Access without having selected an object and want to switch to the Visual Basic Editor, press 
. (This method works for all Microsoft Office applications.)
Here is the Visual Basic Editor as it would look if you selected the Switchboard form in the GardenCo database and clicked the Code button:
You can display or hide most of the VBE components by clicking the appropriate button or a command on the View menu in the editor. You can also use the following buttons to change how information is displayed:
When you click the Project Explorer button, the pane for the Project Explorer is displayed. You use the Project Explorer to find and display the VBA objects in the active database.
When you click the Toggle Folders button at the top of the Project Explorer to toggle it on, the VBA objects are categorized in the Class Objects and Modules folders. When the Toggle Folders button is toggled off, the objects are listed without being categorized.
When you click the Full Module View button at the bottom of the Code window, all the procedures from the module selected in the Project Explorer pane are displayed.
When you click the Procedures View button, only the active procedure—the one containing the insertion point—is displayed.
The Object box, on the upper-left side of the Code window, lists all the objects, such as command buttons, labels, and text boxes that appear in the form. Even the form itself is an object. When you select an object in this list, a placeholder for the most common event associated with that object is created in the Code window below.
The Procedure box, on the upper-right side of the Code window, lists all the procedures associated with the currently selected object. These procedures are associated with events, such as a mouse click on a command button. When you select a procedure in this list, the name of the event is added to the first line of the object’s placeholder in the Code window below.
In the Code window itself, everything above the first horizontal line is the Declarations section of the module. This section sets the module’s requirements and defaults. Two declarations, Option Compare Database and Option Explicit, are usually included by default. You can add others.
Important
When you use the Option Explicit declaration, you must explicitly declare all variables before using them. If you attempt to use an undeclared variable, an error occurs when the code is compiled. Variables are discussed later in this chapter.
The Code window below the Declarations section displays the procedures included in the module. Procedures can be categorized as follows:
Sub procedures, which are a series of VBA statements enclosed by Sub and End Sub statements that perform actions but don’t return a value.
Function procedures, which are enclosed in Function and End Function statements and return a value.
Each procedure is a block of code that accomplishes a specific task. In the previous graphic, each procedure was created by the Switchboard Manager to respond to requests to create a switchboard, and respond to the click of a button on the switchboard page.
The VBA statements in a procedure are often interspersed with comments. These are notes that help someone reading the code understand the code’s purpose. Comments are declared by an apostrophe; anything after an apostrophe in a line of code is a comment. The Visual Basic Editor makes comments obvious by formatting them as green text.
Within each line of code, you will see that some words are blue and others are black. The blue words are keywords, reserved as part of the VBA programming language. The black words are variables or values supplied by the programmer.
One of the first things done in many procedures is to use Dim (dimension) statements to define (declare) the variables that will be used in the procedure. Declaring a variable sets its type. (VBA supports the data types used for Access fields and other types.) Declaring a variable also sets the exact appearance of the word representing the variable—the combination of uppercase and lowercase characters.
Tip
If you always include at least one uppercase character in variable declarations and always type the variable name in lowercase, you can take advantage of the fact that when VBA recognizes a variable or keyword, it changes it to the capitalization style of its definition. So if you misspell a word, you won’t see it changed, which is a hint to check your spelling.
Every programming language has certain formatting conventions. Most of them have no impact on whether the code runs, but many make it easier to visually follow what is going on in the code and locate problems. Indenting is one such convention. When typing VBA code, use a tab to indent lines that are part of a larger element. In the switchboard code shown earlier, everything between the beginning and end of the procedures is indented by one tab, and the code for some statements, such as For... Next loops and If... Then... Else statements, is indented another tab.
As you type words that are part of the VBA programming language (keywords), the Visual Basic Editor often offers hints and autocomplete options. If, for example, you are using the DoCmd statement, when you type the period after DoCmd, a list of all possible methods appears.
You can either continue to type, or scroll down the list and select the method you want. When you complete the command and press the 
, a box displays the syntax for the rest of the command.
If a line of code extends beyond the edge of the screen, it will still run, but it is hard to read. You can break a long line of code by typing a space and an underscore, and then pressing 
. (You can press 
to set the second line of code off from the first, but that’s not a requirement.) Although the code will continue on the next line, it will be treated as one line of code.
You can get more information about any VBA command by highlighting it in the Visual Basic Editor and pressing 
to open Visual Basic Help.