Hack 6. Organize and Enhance Your Macros

Optimize and reduce the number of macros using the optional name and condition columns.

Macros are often used for small automationsusually for tasks that aren't too complex or sophisticated because VBA is available to handle the heavy processing. Let's think that way no more. Actually, macros can handle a decent amount of intelligent processing and, in fact, have a condition-testing ability similar to the If…Then structure in VBA. This hack shows you how to transform a single macro into a multipurpose workhorse.

1.7.1. Conditional Macro Actions

Macros have but a single mandatory column: the Action column. A macro can have one or more actions. However, macros also have an optional Condition column, in which a little entry can go a long way toward adding some punch to the process. When you're designing a macro, use the View menu to display the Condition column.

A condition can test a field value, evaluate the result returned by a function, and even use the returned value from a message box. Conditions also can use Boolean logic, incorporating and/or-type logic in the condition testing.

Figure 1-13 shows a macro in which a series of actions occur when the macro is run. A few of the actions run only when their condition is met. For instance, the End of Month function and the End of Month report are included in the processing only when it is the first day of the month (presumably tallying up figures about the month that just ended). Using the Day and Now functions takes care of testing for the first day of the month.

The Employee Bonus report runs only when a condition tested with a DLookup function is TRue.

The unconditional actions in the macro always run. Even when the actions with unmet conditions are passed over, the macro continues to run and doesn't stop prematurely.

1.7.2. Creating Macro Groups

Macros can also be organized into groups, known as macro groups. By creating macro groups, you can reduce the number of overall macros and keep similar macro actions together in one place. The key difference between a macro and a macro group is the use of the optional Macro Name column.

When you're designing macros, use the View menu to display the Macro Name column. Figure 1-14 shows a macro group named RunReport. The macro group handles the task of opening a number of individual reports. An important point, though, is that these reports won't open at the same time. Each macro name exists as a separate macro within the larger group.

Figure 1-13. Using conditions in a macro

Figure 1-14. Using the Macro Name column

When a particular action needs to be initiated, you use the name of the macro group, a dot qualifier, and the name in the Macro Name column, like this:

	DoCmd.RunMacro "RunReport.Inventory Status"

The point where the action starts is the row with the macro name. Successive actions will run until another macro name is encountered. Not all rows require a value in the Macro Name column. This is the beauty of macro groups. One cohesive design houses any number of smaller action sets. The benefit is a cleaner and easier-to-manage macro implementation.