The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to the VBA language. It serves as a foundation for all of the development that you do. After reading the article, you will be familiar with the development environment. You will know how to declare and work with variables, create procedures, and determine the scope and lifetime of the procedures that you build.
VBA is the development language for Microsoft Access. It offers a consistent language for application development in the Microsoft Office suite. The core language, its constructs, and the environment are the same in Microsoft Access, Microsoft Visual Basic 6.0 and earlier, Microsoft Excel, Microsoft Word, Microsoft Outlook (for application-wide programming), and Microsoft Project. What differs among these environments are the built-in objects specific to each application. For example, Access has a CurrentProject object, but Excel has a Workbook object. Each application’s objects have appropriate properties (attributes) and methods (actions), and, in some cases, events associated with them. This article gives you an overview of the VBA language and its constructs.
Unlike macros in Word or Excel, Access macros are not subprocedures in modules; instead, they are a different type of database object, with their own interface. Because of this, you can’t use Access macros to learn to program in VBA, as you can by recording a Word or Excel macro and then examining its VBA code. You can write some Access 2010 applications by using macros. Although macros are okay for relatively basic application development, you will do most serious Access development by using the VBA language. Unlike macros, VBA enables you to do the following:
Work with complex logic structures (case statements, loops, and so on)
Take advantage of functions and actions not available in macros
Loop through and perform actions on recordsets
Perform transaction processing
Create database objects programmatically and work with them
Create libraries of user-defined functions
Call Windows API functions
Perform complex object linking and embedding (OLE) automation commands (for example to communicate with Excel)
The VBA language enables you to use complex logic structures. Macros let you perform only simple If…Then…Else logic, but the VBA language offers a wealth of logic and looping constructs, which are covered later in this article.
If you try to develop an application using only macros, you can’t take advantage of many of the rich features available in the VBA language. In addition, many of the actions available in both macros and modules can be performed much more efficiently with VBA code.
Complex Access applications often require you to loop through a recordset, performing some action on each member of the set. There’s no way to do this using Access macros. However, with the VBA language and ActiveX Data Objects (ADO) or Data Access Objects (DAO), you can add, delete, update, and manipulate data.
When manipulating sets of records, you want to ensure that all processing finishes successfully before the Access Database Engine permanently updates your data. Macros don’t enable you to protect your data with transaction processing. Using the BeginTrans, CommitTrans, and Rollback methods, you can make sure that the Access Database Engine updates your data only if all parts of a transaction finish successfully. Transaction processing, if done properly, can substantially improve your application’s performance because no data is written to disk until the process is finished.
With Access macros, you can’t create or modify database objects at runtime. Using VBA, you can create databases, tables, queries, and other database objects; you can also modify existing objects. There are many practical applications of this capability to create or modify database objects. When users are able to build queries on the fly, for example, you might want to give them the capability to design a query by using a front-end form that you provide. You can also enable users to store the query so that they can run it again later.
VBA also makes it easier for you to write code libraries of reusable functions, design and debug complex processes, and even write your own add-ins. If you’re developing even moderately complex applications, you want to be able to create generic function libraries that can be used with all your Access applications. Doing this using macros is extremely difficult, if not impossible.
Many powerful functions not available within the VBA language are available as part of Windows itself. The Windows API (Application Programming Interface) refers to the nearly 1,000 Windows functions that Microsoft exposes for use by Access programmers. You can’t take advantage of these functions from an Access macro. However, by using VBA code, you can declare and call these functions, improving both the performance and functionality of your applications.
Automation technology enables you to communicate between your Access applications and other applications. Automation is used to control Automation server applications, such as Excel and Project, and their objects (all Microsoft Office applications are Automation servers).
Although macros in Microsoft Office Access 2010 are significantly more powerful than macros in previous versions of Access it is best to use a combination of both macros and VBA for developing complex solutions. If you would ever like to convert any macro to VBA code, a Save As menu option is available when saving an existing macro.
What Are Access Class Modules, Standard Modules, Form Modules, and Report Modules?
VBA code is written in units called subroutines and functions that are stored in modules. Microsoft Access modules are either Standard modules or Class modules. Standard modules are created by clicking to select the Database Tools tab and then selecting the Visual Basic button from the Macro group. Access takes you to the Access Visual Basic Editor (VBE). Finally, select Insert, Module from the VBE menu. Class modules can be standalone objects or can be associated with a form or report. To create a standalone Class module, you choose the Class Module command from the VBE Insert menu. In addition, whenever you add code behind a form or report, Microsoft Access creates a Class module associated with that form or report that contains the code you create.
Modules specific to a form or report are generally called Form and Report Class modules, and their code is often referred to as Code Behind Forms (CBF). CBF is created and stored in that form or report and triggered from events occurring within it.
A subroutine (or subprocedure) is a routine that responds to an event or performs some action. An event procedure is a special type of subroutine that automatically executes in response to an event such as a mouse click on a command button or the loading of a form. A function is a special type of routine because it can return a value; a subroutine can’t return a value. Like a subroutine, a function can be triggered from an event.
Where Is VBA Code Written?
You write all VBA code in the Visual Basic Editor, also known as the VBE. Access places you in the VBE anytime you select Visual Basic from the Macro group on the Database Tools tab or press Alt+-F11. Figure 1 shows the Visual Basic Editor. The VBE environment in Microsoft Access is consistent with the editor interfaces in other Microsoft Office products. The VBE is a separate window from that of Microsoft Access and comprises a menu bar, toolbar, Project window, Properties window, Immediate window, Locals window, Watch window, Object Browser, and Code windows. The various components of the VBE are discussed as appropriate in this article.
The Anatomy of a Module
Whether you’re dealing with a Standard module or a Class module, all modules contain a General Declarations section (see Figure 2). As the name implies, this is the place you can declare variables and constants that you want to be visible to all the functions and subroutines in the module. You can also set options in this section. These variables are referred to as module-level or private variables. You can also declare public variables in the General Declarations section of a module. Public variables can be seen and modified by any function or procedure in any module in the database.
A module is also made up of user-defined subroutines and functions. Figure 3 shows a subroutine called SayHello. Notice the drop-down list in the upper-left portion of the window titled Chap8Ex - basHello (Code). This is referred to as the Object drop-down list. Subroutines and functions are sometimes associated with a specific object, such as a form or a control within a form. This is the place such an association is noted. In this case, the subroutine named SayHello is not associated with any object, so the Object drop-down list contains (General).
Option Explicit is a statement that you can include in the General Declarations section of any module, including the Class module of a form, or report. When you use Option Explicit, you must declare all variables in that module before you use them; otherwise, an error saying that a variable is undefined will occur when you compile the module. If Access encounters an undeclared variable when it compiles a module without Option Explicit, VBA will simply treat it as a new variable and continue without warning. At first glance, you might think that, because Option Explicit can cause compiler errors that would otherwise not occur, it might be better to avoid the use of this option. However, just the opposite is true. You should use Option Explicit in every module, without exception. For example, look at the following code:
intAmount = 2
intTotal = intAmont * 2
Clearly, the intent of this code is to multiply the value contained in the variable intAmount, in this case 2, by 2. Notice, however, that the variable name is misspelled on the second line. If you have not set Option Explicit, VBA views intAmont as a new variable and simply continues its processing. The variable intTotal will be set to 0 instead of 4, and no error indication is given at all. You can totally avoid this kind of result by using Option Explicit.
In earlier versions of Access you had the option of globally instructing Access to insert the Option Explicit statement in all new modules. In Access 2007 and above, the default setting is to insert the Option Explicit statement in all new modules. To review this setting in Access 2010, with the VBE active, choose Tools, Options. Under the Editor tab, click Require Variable Declaration (see Figure 4). It’s important that you place the Option Explicit statement in all your modules, so make sure this option is set to True. Option Explicit will save you hours of debugging and prevent your cell phone from ringing after you distribute your application to your users.
In addition to a General Declarations section and user-defined procedures, forms, and reports, Class modules also contain event procedures that are associated with a particular object on a form. Notice in Figure 5 that the Object drop-down list says cmdHello. This is the name of the object whose event routines you are viewing. The drop-down list on the right shows all the events that you can code for a command button; each of these events creates a separate event routine. You will have the opportunity to write many event routines as you develop your own applications.
Creating Event Procedures
Access automatically creates event procedures when you write event code for an object. For example, Access automatically creates the routine Private Sub cmdHello_Click when you place code in the Click event of the cmdHello command button, shown in Figure 5. To get to the event code of an object, follow these steps:
Click on the object in Design view and click the Property Sheet button on the toolbar, or right-click on the object and choose Properties from the context-sensitive menu.
Click on the Event properties tab.
Select the event for which you want to write code for (for example, the On Click event).
Select [Event Procedure] from the drop-down list.
Click on the ellipsis button, which places you in the VBE within the event code for that object.
You are now ready to write code that will execute when that event occurs for the selected object.
As discussed at the beginning of the article, the VBE opens in a separate window. It provides a programming environment consistent with that of all the other Microsoft Office applications. Modules added in the VBE will not appear in the database container until you save them within the VBE.
Creating Functions and Subroutines
You can also create your own procedures that aren’t tied to a particular object or event. Depending on how and where you declare them, you can call them from anywhere in your application or from a particular Code module, Form module, or Report module.
Creating a User-Defined Routine in a Code Module
Whereas event routines are tied to a specific event that occurs for an object, user-defined routines are not associated with a particular event or a particular object. Here are the steps that you can take to create a user-defined routine:
Click to select the Create tab.
Click to select Module (see Figure 6). The VBE appears, and Access places you in a new module.
Select Procedure from the Insert menu. The Add Procedure dialog box shown in Figure 7 appears.
Type the name of the procedure.
Select Sub, Function, or Property as the Type of procedure.
To make the procedure available to your entire application, select Public as the Scope (Scope is covered later in this article in the section “Scope and Lifetime of Procedures”); to make the procedure private to this module, select Private.
Finally, indicate whether you want all the variables in the procedure to be static. (Static variables are discussed in Part 2 of this article under “Scope and Lifetime of Variables: Exposing Your Variables as Little as Possible.”) Then click OK.
Access creates a user-defined routine. Your cursor is placed within the routine, and you can now write the code that encompasses the body of the routine.
Creating a User-Defined Routine in a Form or Report Class Module
Just as you can create a user-defined routine in a Code module, you can also create a user-defined routine in a Form or Report Class module. Here’s the process:
While in Design view of a form or report, click to select the Design tab. Select the View Code button in the Tools group. Access places you in the VBE.
Choose Procedure from the Insert menu to open the Insert Procedure dialog box.
Type the name of the procedure.
Select Sub, Function, or Property as the Type of procedure.
To make the procedure available to your entire application, select Public as the Scope; to make the procedure private to this module, select Private.
Finally, indicate whether you want all the variables in the procedure to be static. When you’re finished, click OK.
Access places a user-defined procedure within your Form or Report Class module. You are now ready to write the code that executes when another procedure calls the user-defined procedure.
Whether you’re creating a procedure in a Standard module or a Class module, you’re now ready to enter the code for your procedure. A great shortcut for creating a procedure is to type directly in the Code window the name of the new procedure, preceded by its designation as either a Sub or a Function. Example: Sub Whatever or Function Whatever. This creates a new subroutine or function as soon as you press Enter.
Calling Event and User-Defined Procedures
Event procedures are automatically called when an event occurs for an object. For example, when a user clicks a command button, the Click event code for that command button executes.
The standard method for calling user-defined procedures is to use the Call keyword - Call SayHello, for example. You can also call the same procedure without using the Call keyword: SayHello.
The Call keyword works only with subroutines, not with functions.
Although not required, using the Call keyword makes the statement self-documenting and easier to read. You can call a user-defined procedure from an event routine or from another user-defined procedure or function.
Scope and Lifetime of Procedures
You can declare the scope of a procedure as public or private. A procedure’s scope determines how widely you can call it from other procedures. In addition to a procedure’s scope, the placement of a procedure can noticeably affect your application’s functionality and performance.
Another attribute of a procedure has to do with the lifetime of any variables that you declare within the procedure. By default, the variables you declare within a procedure have a lifetime; that is, they have value and meaning only while the procedure is executing. When the procedure completes execution, the variables that it declared are destroyed. You can alter this default lifetime by using the Static keyword.
You can call a public procedure placed in a code module from anywhere in the application. Procedures declared in a module are automatically public. This means that, unless you specify otherwise, you can call procedures you place in any code module from anywhere within your application.
You might think that two public procedures can’t have the same name. They actually can. If two public procedures share a name, the procedure that calls them must explicitly state which of the two routines it’s calling. This is illustrated by the following code snippet.
Private Sub cmdSayGoodBye_Click()
You can find the SayGoodBye routine in two Access code modules; however, the prefix basUtils indicates that the routine you want to execute is in the Standard module named basUtils.
Procedures declared in Form or Report Class modules are also automatically public, so you can call them from anywhere within the application. The procedure called cbfIAmPublic, shown in Figure 8, is found in the form called frmHello. The only requirement for this procedure to be called from outside the form is that the form containing the procedure must be open in Form view. You can call the cbfIAmPublic procedure from anywhere within the application by using the following syntax (found in the Standard module basHello):
Although all procedures (except event procedures) are by default public, you should use the Public keyword to show that the procedure is visible to any subroutine or function in the database.
As mentioned, all user-defined procedures are automatically public. If you want a procedure declared in a module to have the scope of that module only, meaning that you can call it only from another routine within the module, you must explicitly declare it as private (see Figure 9).
The procedure shown in Figure 9, called IAmPrivate, is private. You can call it only from other procedures in the Standard basUtils module.
Private procedures always take precedence over public procedures. If a private procedure in one module has the same name as a public procedure declared in another module, the private procedure’s code is executed if it’s called by any routine in the module where it was declared. Naming conflicts don’t occur between public and private procedures (unless you declare a public and private variable with the same name in the same module).
Developers often wonder where to place code: in Form or Report Class modules, or in Standard modules? There are pros and cons to each method. Placing code in Standard modules means that you can easily call the code from anywhere in your application, without loading a specific form or report. Public routines placed in Standard modules can also be called from other databases. For this reason, Standard modules are a great place to put generic routines that you want readily available as part of a library.
Access loads modules on a demand-only basis, which means that procedures do not take up memory unless they’re being used. When Access loads the code, an advantage of placing code behind forms and reports (rather than within modules) is that the form or report is self-contained and, therefore, portable. You can import the form or report into any other database, and it still operates as expected. This object-oriented approach means that the form requires nothing from the outside world.
As you can see, there are pluses and minuses to each method. As a general rule, if a routine is specific to a particular form or report, place that routine in the form or report; if it’s widely used, place it in a module.
If a procedure is declared as static, all the variables declared in the procedure maintain their values between calls to the procedure. This is an alternative to explicitly declaring each variable in the procedure as static. Here’s an example of a static procedure:
Static Sub IncrementThem()
Dim intCounter1 As Integer
Dim intCounter2 As Integer
Dim intCounter3 As Integer
intCounter1 = intCounter1 + 1
intCounter2 = intCounter2 + 1
intCounter3 = intCounter3 + 1
MsgBox intCounter1 & “ - “ & intCounter2 & “ - “ & intCounter3
Ordinarily, each variable in this procedure would be reinitialized to zero each time the procedure is run. This means that all 1s would appear in the message box each time you run the procedure. Because the procedure is declared as static, the variables in it retain their values from call to call. That means that each time you run the procedure, the values in the message box increase. This behavior should become much clearer after the discussion of variables covered in Part 2 of this series.
A strong knowledge of the VBA language is imperative for the Access developer. This article covered many the basics of the VBA language. You have learned the differences between Code, Form, and Report modules and how to effectively use each. You have also learned the difference between event procedures and user-defined subroutines and functions. Finally you learned about the scope and lifetime of subroutines and functions.
Note: This article was excerpted from Alison Balter's Mastering Access 2007 Development with permission by SAMS Publishing.