Optimize and reduce the number of macros using the optional name and condition columns.
Macros are often used for small automationsâusually 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.
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.
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.
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.
Get Access Hacks now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.