Special Edition Using Microsoft Office Access 2003

Historically, productivity applications such as the members of Microsoft Office have used macros (short for macroinstructions) to automate repetitive operations. Microsoft Word and Excel, for instance, let you capture a sequence of menu choices, mouse clicks, and keyboard operations. You save the captured sequence as a macro that you subsequently execute from a menu choice or a shortcut-key combination. The macros in recent versions of Word and Excel consist of Visual Basic for Applications (VBA) code, but you don't need to understand VBA programming to create and execute Word and Excel macros. Unfortunately, the keyboard and mouse actions you use with Access applications don't translate to a usable macro. For better or worse, automation of Access applications requires programming.

Simple Access applications require you to write little or no VBA code. Most users of early versions of Access wrote Access macros, rather than various flavors of Access Basic to automate their applications. Access macros define actions, such as opening a form, that you assign to events, such as clicking a command button. Starting with Access 95, Microsoft recommended that you use VBA code instead of macros, with the clear implication that macros might not be supported in future versions of Access. (Access 2003 does support macro operations, but the Microsoft documentation states that it does so primarily for backward compatibility.)

This chapter describes VBA, introduces you to VBA modules and procedures that replace Access macros, shows you how to use the VBA editor to write and test VBA code, and helps you start writing user-defined functions. The chapter also includes examples of simple VBA programs.

There are no significant changes to VBA 6.0 in the upgrade from Access 2000 or 2002 to Access 2003. If you're migrating from Access 97 to 2003, following are the most important new VBA-related features added by Access 2000+ and the transition from VBA 5.0 to 6.0:

• VBA editor, which replaces the dedicated Access code editor of Access 97 and earlier, now is a common component that serves all Office 2000+ members.

• Decimal data type improves the accuracy of operations on numbers with decimal fractions.

• Array functions Filter, Split, and Join provide array search, string-to-array, and array-to-string capabilities, respectively.

• String functions Replace, StrReverse, and InstrRev enhance VBA's character manipulation features.

• Round rounds numbers with decimal fractions to a specified number of decimal places.

• MonthName and WeekDayName return the localized name of the month and day from a Date argument.

• FileSystemObject provides an object model for disk drives.

• Assert, when set to False, causes execution of code to halt and enter break mode.

• Event and RaiseEvent let you declare and fire events in class modules.

• Friend members of class modules have Public scope within an Access project (application), but aren't accessible from other projects.

• Implements lets class modules share property and method declarations.

• AddressOf supports callbacks in Windows API functions by providing the address of the calling VBA function or subprocedure.

• Enum lets you define custom enumerations for, as an example, sets of named constants that supply property values.

The items in the preceding list aren't of great significance to beginning VBA programmers. Advanced VBA coders, however, appreciate the incremental improvements to the language that occur with every upgrade.

Note

Version 6.0 is Microsoft's last upgrade to VBA. Visual Basic .NET isn't an upgrade it's a complete makeover of the venerable Visual Basic language that's required for compatibility with the .NET Framework's Common Language Runtime (CLR) environment.

For more commentary on the VBA versus Visual Basic .NET issue as it relates to Access, see "In the Real World Macro Schizophrenia," p. 1188.