Macros

Macros are not part of VBA, but they do offer an easy way to automate your Access applications. One big attraction of macros is that you do not have to remember their syntax ; Access makes it easy to select a macro action from a drop-down list box. On the other hand, with macros the debugging techniques are not nearly as rich as with VBA. Another disadvantage is that macros are stored in their own macro container, not behind forms. Over time, this can lead to maintenance problems. Also, Access macros are different from the macros in Excel, Word, and PowerPoint. In fact, macros are becoming an obsolete technology.

If you have been using an earlier version of Access, you have probably had some hands-on experience with macros. In earlier releases of Access, macros were the only way to create special startup options and custom menus. You can now manage menus using the VBA CommandBars collection object. In addition, you can use a Startup dialog box for controlling various aspects of starting an Access application, such as displaying a form when the startup process concludes, specifying whether users can make changes to menus and toolbars, and specifying whether to display the Database window.

Designing Macros

To work with macros, you have to know how to use the macros user interface and you need a basic familiarity with macro actions. Figure 1-28 shows the Macro window with the Northwind database loaded.

Each Access macro consists of one or more rows in the Macro window. Each row specifies an action (such as moving the focus to a control), an optional condition for the action, and an optional comment. The Action Arguments area at the bottom of the window (visible when you select an action) allows you to specify the options for each action and displays help for the selected item. In addition, the first row of each macro specifies the macro's name. (Choose Macro Names from the Access View menu if you can't see the macro names.)

Many macro actions have options that you can select with a simple click. For example, the second row of the Preview macro in Figure 1-28 (the row with the right-pointing triangle in the first column) displays a message box. In the Action Arguments area of the window, you can select the arguments for a message box from drop-down list boxes. For example, you can use the Type drop-down list box to select the icon that is displayed in the message box.

In the early days, this point-and-click selection feature gave macros a definite usability advantage over VBA code. Now, VBA has IntelliSense. Macros are still fairly easy to use—you still enjoy the ease of selecting arguments—but you do not have a flexible interface in which you can simultaneously view the code (including arguments) for multiple macro lines.

The most important column in the Macro window is the Action column. You do not have to type actions into this column; you can simply click any row in the column for a full menu of macro actions. In the Condition column just to the left of the Action column, you can specify criteria that conditionally control the execution of the action on a line.

Figure 1-28. The main user interface elements of the Macro window.

You can store multiple macros in a single macro object. This is an efficient way to store multiple macros that share some common function. Use the Macro Name column to assign names to the individual macros within a macro object. When you assign a macro to an action for a control on a form or a report, you specify the macro using a two-part name. The first part is the name of the macro object; the second part is the macro's name, as shown in the Macro Name column. (Use a period to separate the parts of the name, as in MyMacros.DisplayHelp.)

Access features a built-in conversion procedure for translating macros to VBA code. You invoke the conversion features slightly differently depending on whether your application calls its macros from a form or from the Database window. From a form or report, choose Macro from the Tools menu and then choose Convert Form's Macros To Visual Basic or Convert Report's Macros To Visual Basic. To convert a macro from the Database window, select it on the Macro page. Choose File-Save As, and in the Save As dialog box, select Module from the As drop-down list box.

VBA vs. Macros

VBA keeps getting dramatically better with each new release, while macros have remained basically unchanged. This is the major reason for switching to VBA from macros, but there are several other reasons as well:

• VBA's built-in error trapping and other flexible debugging can dramatically reduce the life cycle cost of ownership by reducing maintenance costs.
• VBA offers many advanced processing features, such as looping options, great Internet functionality, and OLE DB links to external data sources.
• VBA custom form and report classes simplify code reuse.
• VBA enables you to view concurrently the arguments for all lines—not just one line.
• You can tap into the Windows API from VBA.