Chapter 26. Writing Visual Basic for Applications Code

Most Access applications you create require you to write little or no Access Visual Basic for Applications (VBA) code. A few commercial Access applications rely primarily on macros rather than Access VBA code for automating applications. Starting with Access 95, however, Microsoft recommended that Access developers use VBA code instead of macros, with the clear implication that macros may not be supported in future versions of Access. (Access 97 does support macro operations, but the Microsoft documentation states that it does so primarily for backward compatibility.) Short VBA procedures using the DoCmd object usually are sufficient to provide the methods needed by simple applications to run queries, display forms, and print reports. The built-in functions of Access allow you to perform complex calculations in queries. You may want or need to use Access VBA code for any of the following reasons:

• To create user-defined functions (UDFs) that substitute for complex expressions you use repeatedly to validate data, compute values for text boxes, and perform other duties. Creating a UDF that you refer to by a short name minimizes potential typing errors and allows you to document the way your expression works.

• To write expressions that include more complex decision structures than allowed by the standard IIf() function (in an If…Then… Else…End If structure, for example), or to write expressions that need loops for repetitive operations.

• To perform transaction processing actions with the Access VBA equivalents of SQL COMMIT and ROLLBACK statements.

• To manipulate other applications' objects with Automation code. Using Automation is the primary subject of Chapter 31, "Exchanging Data with Automation and ActiveX Controls."

• To open more than one database in an application where attaching a table or using the SQL IN statement is not sufficient for your application.

• To provide hard-copy documentation for your application. If you include actions in Access VBA code, you can print the Access VBA code to improve the documentation for your application.

• To provide graceful error-handling if something goes wrong in your application. With Access VBA code, you can closely control how your application responds to errors such as missing forms, missing data, incorrect values entered by a user, and other problems.

This chapter describes Access VBA, introduces you to Access VBA modules and procedures, shows you how to use the Module window to enter and test Access VBA code, and helps you start writing user-defined functions. The chapter also includes examples of Access VBA programs.