Creating a macro

A simple way to demonstrate how to create macros is to build one that displays a message box that says “Hello World!” To create a new stand-alone macro, click the Macro button on the Macros & Code group on the Create tab of the Ribbon (shown in Figure 22.1).

Clicking the Macro button opens the macro design window (shown in Figure 22.2). Initially, the macro design window is almost featureless. The only thing in the Macro window is a drop-down list of macro actions.

To the right of the Macro window you may see the Action Catalog. There are dozens of different macro actions, and knowing which action to use for a particular task can be an issue. The Action Catalog provides a tree view of all available macro actions and helps you know which action is needed to perform a particular task.

Select MessageBox from the drop-down list in the macro window. The macro window changes to display an area where you input the arguments (Message, Beep, Type, and Title) associated with the MessageBox action.

Set the arguments as follows:

• Message: Hello World!
• Beep: No
• Type: None
• Title: A Simple Macro

Your screen should look similar to Figure 22.3. The Message argument defines the text that appears in the message box and is the only argument that is required and has no default. The Beep argument determines whether a beep is heard when the message box appears. The Type argument sets which icon appears in the message box: None, Critical, Warning?, Warning!, or Information. The Title argument defines the text that appears in the message box's title bar.

To run the macro, click the Run button in the Tools group of the Design tab of the Ribbon. (The Run button looks like a big red exclamation point at the far left of the Ribbon.) When you create a new macro or change an existing macro, you'll be prompted to save the macro. In fact, you must save the macro before Access runs it for you. When prompted, click Yes to save it, provide a name (such as “mcrHelloWorld”), and click OK. The macro runs and displays a message box with the arguments you specified (shown in Figure 22.4).

You can also run the macro from the Navigation pane. Close the macro design window and display the Macros group in the Navigation pane. Double-click the mcrHelloWorld macro to run it. You'll see the same message box that displayed when you ran the macro from the design window.

Notice that the message box always appears right in the middle of the screen and blocks you from working with Access until you click OK. These are built-in behaviors of the message box object and are identical in every regard to a message box displayed from VBA code.

When you're satisfied with the Hello World! macro, click the close button in the upper-right corner of the macro window to return to the main Access window.

Assigning a macro to an event

When you're creating macros, you probably don't want end users using the Navigation pane to run them—or worse, running them from the macro design window. Macros are intended for you to automate your application without writing VBA code. In order to make an application easy to use, assign your macros to an object's event.

The most common event to which you might assign a macro is a button's Click event. Follow these steps to create a simple form with a button that runs mcrHelloWorld:

1. Select the Create tab on the Ribbon, and then click the Form Design button in the Forms group.
2. On the Form Design Tools Design tab of the Ribbon, deselect the Use Control Wizards option in the Controls group. For this example, you don't want to use a wizard to decide what this button does.
3. Click the Button control and draw a button on the form.
4. Set the button's Name property to cmdHelloWorld. Press F4 to open the button's Property Sheet if it isn't visible on the screen.
5. Set the button's Caption property to Hello World!.
6. Click the drop-down list in the button's On Click event property, and select mcrHelloWorld from the list (shown in Figure 22.5).

   

That's all there is to creating and running a macro. Just select the action, set the action arguments, and assign the macro to an event property.

Enabling sandbox mode

Sandbox mode allows Access to block any of the commands in the unsafe list it encounters when running forms, reports, queries, macros, data access pages, and Visual Basic code. Here's how to enable it:

1. Open Access, click the File button, and select Options. The Access Options dialog box appears.
2. Select the Trust Center tab, and then click Trust Center Settings. The Trust Center dialog box appears.
3. Select the Macro Settings tab (shown in Figure 22.6).

   

4. Select either Disable All Macros without Notification or Disable All Macros with Notification.
5. Restart Access to apply the security change.

   The Macro Settings tab provides four levels of macro security:

   • Disable All Macros without Notification: All macros and VBA code are disabled and the user isn't prompted to enable them.
   • Disable All Macros with Notification: All macros and VBA code are disabled and the user is prompted to enable them.
   • Disable All Macros Except Digitally Signed Macros: The status of the macro's digital signature is validated for digitally signed macros. For unsigned macros, a prompt displays advising the user to enable the macro or to cancel opening the database.
   • Enable All Macros (Not Recommended; Potentially Dangerous Code Can Be Run): Macros and VBA code are not checked for digital signatures and no warning displays for unsigned macros.

A digital signature (contained within a digital certificate) is an encrypted secure file that accompanies a macro or document. It confirms that the author is a trusted source for the macro or document. Digital signatures are generally implemented within large organizations that are willing to fund the expense of purchasing and maintaining digital signatures. You, or your organization's IT department, can obtain a digital certificate through a commercial certification authority, like VeriSign, Inc., or Thawte. Search http://msdn.microsoft.com for “Microsoft Root Certificate Program Members” to obtain information on how to obtain a digital certificate.

The default, and generally the best choice, is Disable All Macros with Notification. During the development and maintenance cycles, you'll want all the code and macros in the application to execute without interrupting you with permissions dialog boxes. The next section describes Trusted Locations. You can put your development database in a trusted location to avoid having to enable unsigned code, but still be protected from other databases you may open.

If you or your organization has acquired a digital certificate, you can use it to sign your Access projects:

1. Open the Access database to digitally sign; then access any module to open the Visual Basic Editor.
2. Choose Tools Digital Signature from the Visual Basic Editor menu. The Digital Signature dialog box opens, as shown in Figure 22.7.

   

3. Click Choose to display the Select Certificate dialog box and select a certificate from the list.
4. Select the certificate to add to the Access project.
5. Click OK to close the Select Certificate dialog box, and click OK again to close the Digital Signature dialog box and save the security setting.

The Trust Center

The Trust Center is where you can find security and privacy settings for Access. To display the Trust Center, click the File button and click Options to open the Access Options dialog box. Select the Trust Center tab, and then click Trust Center Settings.

Here's a description of each section and what it controls:

• Trusted Publishers: Displays a list of trusted publishers—publishers where you clicked Trust All Documents from This Publisher when encountering a potentially unsafe macro—for Office. To remove a publisher from this list, select the publisher and click Remove. Trusted publishers must have a valid digital signature that hasn't expired.
• Trusted Locations: Displays the list of trusted locations on your computer or network. From this section, you can add, remove, or modify folders on your computer that will always contain trusted files. Any file in a trusted location can be opened without being checked by the Trust Center. You can also choose not to allow network locations and to disable all Trusted Locations and accept signed files.
• Trusted Documents: When Trusted Documents is selected, the name of the Access application is added to a special area in the system registry on the user's computer. Then, each time the application is used, it is recognized as a trusted document, and all the macros, code, and other elements of the application are enabled without interrupting the user's workflow.
• Trusted Add-In Catalogs: Displays the list of trusted Office Web App catalogs. Web Apps are embedded web applications that extend the features of Access with content directly from the web or SharePoint websites. Beginning in Access 2016, Microsoft uses the term Add-ins to refer to Web Apps, though they are not related to the traditional Add-ins created with VBA. The Trusted Add-In Catalogs section allows you to add, remove, or modify the SharePoint catalogs you trust.
• Add-ins: Lets you to set up how Access handles add-ins. You can choose whether add-ins need to be digitally signed from a trusted source and whether to display a notification for unsigned add-ins. You can also choose to disable all add-ins, which may impair functionality.
• ActiveX Settings: Lets you set the security level for ActiveX controls.
• Macro Settings: Lets you set the security setting for macros not in a trusted location. (For more information on Macro Settings, see the previous section on sandbox mode.)
• Message Bar: Lets you set whether to display the message bar that warns you about blocked content, or to never show information about blocked content.
• Privacy Options: Lets you choose how Microsoft Office Online communicates with your computer. You can set options to use Microsoft Office Online for help, show featured links at startup, download files to determine system problems, and sign up for the Customer Experience Improvement Program.

Opening reports using conditions

To demonstrate conditions and the If macro action, frmReportMenu (shown in Figure 22.11), contains three buttons and a frame control (fraView) with two option buttons: Print and Print Preview. Clicking Print sets the frame's value to 1; clicking Print Preview sets the frame's value to 2.

The macro that opens the reports uses submacros, as well as the If macro action. Table 22.3 shows the submacro names, conditions, actions, and action arguments for mcrReportMenu (a portion of which is shown in Figure 22.12), which opens one of three reports. The Filter Name and Where Condition arguments are blank for each OpenReport action.

To implement this macro, set the On Click event properties of the buttons (cmdContacts, cmdProducts, and cmdSales) on frmReportMenu as follows:

The If macro action in mcrReportMenu has two expressions that look at fraView on frmReportMenu to determine whether Print or Print Preview is selected:

• [Forms]![frmReportMenu]![fraView]=1: Print view selected
• [Forms]![frmReportMenu]![fraView]=2: Print Preview view selected

If Print is selected on frmReportMenu, the OpenReport action with the View arguments set to Print executes. If Print Preview is selected on frmReportMenu, the OpenReport action with the View arguments set to Print Preview executes. This structure is set up for each submacro in mcrReportMenu.

Multiple actions in conditions

If you want to run multiple actions based on a condition, add multiple actions within the If and End If actions. Figure 22.13 illustrates this concept.

The If macro action lets you selectively run actions based on other values in your application. Use the If macro action to reference controls on forms or reports and other objects and determine which actions to execute.

Enhancing a macro you've already created

A simple way to demonstrate how to use variables in macros is to enhance the Hello World! example created earlier in this chapter (see “Creating a macro”). Table 22.4 shows the macro actions and action arguments for mcrHelloWorldEnhanced (shown in Figure 22.14).

The SetTempVar action has two arguments: Name and Expression. The Name argument (MyName in this example) is simply the name of the temporary variable. The Expression argument is what you want the value of the variable to be. In this example, the InputBox() function prompts the user for his name.

The MessageBox action's Message argument contains the following expression:

="Hello " & [TempVars]![MyName] & "."

This expression concatenates the word Hello with the temporary variable MyName, created in the SetTempVar action of the macro. When referring to a temporary variable created with the SetTempVar action, use the following syntax:

[TempVars]![VariableName]

The RemoveTempVar action removes a single temporary variable from memory—in this example, MyName. You can have only 255 temporary variables defined at one time. These variables stay in memory until you close the database, unless you remove them with RemoveTempVar or RemoveAllTempVars. It's a good practice to remove temporary variables when you're done using them.

Temporary variables are global. Once you create a temporary variable, you can use it in VBA procedures, queries, macros, or object properties. For example, if you remove the RemoveTempVar action from mcrHelloWorldEnhanced, you can create a text box on a form and set its Control Source property as follows to display the name the user entered:

=[TempVars]![MyName]

Using temporary variables to simplify macros

Using temporary variables, you can sometimes eliminate steps from a macro. You can get the form or report name from another control on a form. With a temporary variable, you eliminate the need for creating a structure of multiple OpenForm or OpenReport actions. You can also use more than one variable in a macro.

For this example, use frmReportMenuEnhanced (shown in Figure 22.15), which contains the same fraView shown in Figure 22.12, but adds a combo box (cboReport), which contains a list of reports to run. The Run Command button executes mcrReportMenuEnhanced, which doesn't use submacros to decide which report to open.

Table 22.5 shows the conditions, actions, and action arguments for mcrReportMenuEnhanced (shown in Figure 22.16), which opens one of three reports.

The first two SetTempVar actions in mcrReportMenuEnhanced set the values of the temporary variables—ReportName and ReportView—from cboReport and fraView on frmReportMenuEnhanced. The OpenReport actions use the temporary variables in the Condition column and for the ReportName argument. When using temporary variables as a setting for an argument, you must use an equal (=) sign in front of the expression:

=[TempVars]![ReportName]

There are still two OpenReport actions in this macro. Certain arguments—such as View—don't allow the use of temporary variables in expressions. Because one of your variables is a setting for the report's view, you still have to use the Condition column to decide which view to open the report in.

The last two RemoveTempVar lines remove the temporary variables—ReportName and ReportView—from memory. Because these variables probably won't be used later on in the application, it's important to remove them.

Using temporary variables in macros gives you far more flexibility in Access 2016 than in versions prior to Access 2007. You can use these variables to store values to use later on in the macro, or anywhere in the application. Just remember that you have only 255 temporary variables to use, so don't forget to clean up after yourself by removing them from memory once you're finished using them.

Using temporary variables in VBA

You may start out using macros to automate your application, but over time, you may begin using VBA code to automate and add functionality to other areas. What do you do with the temporary variables you've already implemented with macros? Well, you don't have to abandon them; instead, you can use them directly in your VBA code.

To access a temporary variable in VBA, use the same syntax used in macros:

X = [TempVars]![VariableName]

If you don't use spaces in your variable names, you can omit the brackets:

X = TempVars!VariableName

Use the previous syntax to assign a new value to an existing temporary variable. The only difference is to put the temporary variable on the left side of the equation:

TempVars!VariableName = NewValue

Use the TempVars object to create and remove temporary variables in VBA. The TempVars object contains three methods: Add, Remove, and RemoveAll. To create a new temporary variable and set its value, use the Add method of the TempVars object as follows:

TempVars.Add "VariableName", Value

Use the Remove method of the TempVars object to remove a single temporary variable from memory:

TempVars.Remove "VariableName"

To remove all the temporary variables from memory, use the RemoveAll method of the TempVars object as follows:

TempVars.RemoveAll

Any VBA variables you create are available to use in your macros, and vice versa. Any variables you remove in VBA are no longer available to use in your macros, and vice versa. Using temporary variables, your macros and VBA code no longer have to be independent from each other.

The OnError action

The OnError action lets you decide what happens when an error occurs in your macro. This action has two arguments: Go to and Macro Name. The Go to argument has three settings and the Macro Name argument is used only with one of these settings, described as follows:

• Next: This setting records the details of the error in the MacroError object but does not stop the macro. The macro continues with the next action.
• Macro Name: This setting stops the current macro and runs the macro in the Macro Name argument of the OnError action.
• Fail: This setting stops the current macro and displays an error message. This is the same as not having error handling in the macro.

The VBA equivalents of these settings are as follows:

On Error Resume Next       'Next
On Error GoTo LABELNAME    'Macro Name
On Error GoTo 0          'Fail

The simplest way to add error handling to a macro is to make OnError the first action and set the Go to argument to Next. This will cause your macro to run without stopping, but you won't have any clue which actions ran and which ones didn't.

Instead, create an error-handling structure. Table 22.6 shows the macro names, actions, and action arguments for mcrDivisionErrorHandling (shown in Figure 22.19).

The first OnError action in the macro lets Access know to move to the submacro ErrorHandler when an error occurs. If an error occurs (by entering 0 as the denominator), the macro stops and moves to the ErrorHandler submacro. The ErrorHandler submacro displays a message box—using the MacroError object (described in the next section) to display the error's description in the Message and the error's number in the Title, using the following expressions:

[MacroError].[Description]
[MacroError].[Number]

After the error handler's message box, the ClearMacroError action clears the MacroError object. The RunMacro action moves execution to the macro's Cleanup submacro. The Cleanup section of the macro removes the temporary variables.

The RunMacro action also appears after the MessageBox action in the main section of the macro. Because you're using submacros, the macro stops after it reaches the ErrorHandler submacro. In order to force the cleanup of the temporary variables, use the RunMacro action to run the Cleanup submacro. Otherwise, you'd have to put the RemoveTempVar actions in the main section and in the ErrorHandler section of the macro.

The MacroError object

The MacroError object contains information about the last macro error that occurred. It retains this information until a new error occurs or you clear it with the ClearMacroError action. This object contains a number of read-only properties you can access from the macro itself or from VBA. These properties are as follows:

• ActionName: This is the name of the macro action that was running when the error occurred.
• Arguments: The arguments for the macro action that was running when the error occurred.
• Condition: This property contains the condition for the macro action that was running when the error occurred.
• Description: The text representing the current error message—for example, Divide by Zero or Type Mismatch.
• MacroName: Contains the name of the macro that was running when the error occurred.
• Number: This property contains the current error number—for example, 11 or 13.

Use the MacroError object as a debugging tool or to display messages to the user, who can then relay that information to you. You can even write these properties to a table to track the errors that occur in your macros. Use this object within an If action to customize what actions execute based on the error that occurs. When used in combination with the OnError action, it gives you additional functionality by handling errors, displaying useful messages, and providing information to you and the user.

Debugging macros

Trying to figure out what's going on in a macro can be difficult. The OnError action and MacroError object make debugging Access macros easier than in previous versions. There are other tools and techniques that are useful when debugging macros. Use the following list as a guideline for troubleshooting macros.

• Single Step: Click the Single Step button in the Tools group on the Macro Tools Design tab of the Ribbon to turn on Single Step mode. The Macro Single Step dialog box (shown in Figure 22.20) lets you see the macro name, condition, action name, arguments, and error number of a macro action before the action executes. From this dialog box, click Step to execute the action, Stop All Macros to stop the macro from running, or Continue to finish the macro with Single Step mode turned off.

  

• MessageBox: Use the MessageBox macro action to display values of variables, error messages, control settings, or whatever else you want to see while the macro is running. To see the value of a combo box on a form, set the Message argument as follows:

  [Forms]![frmReportMenuEnhanced]![cboReport]

• StopMacro: Use the StopMacro action to stop the macro from executing. Insert this action at any point in the macro to stop it at that point. Use this in conjunction with the debug window to check values.
• Debug window: Use the debug window to look at any values, temporary variables, or properties of the MacroError object after you stop the macro. Press Ctrl+G to display the code window after you stop the macro. Just type a question mark (?) and the name of the variable or expression you want to check the value of, and press Enter. Here are some examples of expressions to display in the Debug window:

  ? TempVars!MyNum
  ? MacroError!Description
  ? [Forms]![frmReportMenuEnhanced]![cboReport]

These techniques are similar to ones you'd use when debugging VBA code. You can step through sections of code, pause the code and look at values in the debug window, and display message boxes to display variables or errors that occur. Granted, you don't have all the tools available—such as watching variables and Debug.Print—but at least you have the new MacroError object to provide the information you need to figure out what's going wrong.

Choosing between macros and VBA

Although macros sometimes prove to be the solution of choice, VBA is the tool of choice at other times. You'll probably want to use VBA rather than macros when you want to:

• Create and use your own functions. In addition to using the built-in functions in Access, you can create and work with your own functions by using VBA code.
• Use automation to communicate with other Windows applications or to run system-level actions. You can write code to see whether a file exists before you take some action, or you can communicate with another Windows application (such as a spreadsheet), passing data back and forth.
• Use existing functions in external Windows Dynamic Link Libraries (DLLs). Macros don't enable you to call functions in other Windows DLLs.
• Work with records one at a time. If you need to step through records or move values from a record to variables for manipulation, code is the answer.
• Create or manipulate objects. In most cases, you'll find that creating and modifying an object is easiest in that object's Design view. In some situations, however, you may want to manipulate the definition of an object in code. With a few VBA statements, you can manipulate virtually any and all objects in a database, including the database itself.
• Display a progress meter on the status bar. If you need to display a progress meter to communicate progress to the user, VBA code is the answer.

Converting existing macros to VBA

After you become comfortable with writing VBA code, you may want to rewrite some of your application macros as VBA procedures. As you begin this process, you quickly realize how mentally challenging the effort can be as you review every macro in your various macro libraries. You can't merely cut the macro from the macro window and paste it into a module window. For each condition, action, and action argument for a macro, you must analyze the task it accomplishes and then write the equivalent statements of VBA code in your procedure.

Fortunately, Access provides a feature that converts macros to VBA code automatically. On the Tools group of the Design tab of the Ribbon, there is a Convert Macro to Visual Basic button. This option enables you to convert a macro to a module in seconds.

To try the conversion process, convert the mcrHelloWorldEnhanced macro used earlier in this chapter. Follow these steps to run the conversion process:

1. Click the Macros group in the Navigation pane.
2. Open mcrHelloWorldEnhanced in Design view.
3. Click the Convert Macros to Visual Basic button on the Design tab. The Convert Macro dialog box (shown in Figure 22.23) appears.

   

4. Select the options that include error handling and macro comments, and click Convert. When the conversion process completes, the Visual Basic Editor (VBE) is displayed and the Conversion Finished! message box appears.
5. Click OK to dismiss the message box.
6. In the VBE, open the Project Explorer from the View menu (Ctrl+R) and double-click the module named Converted Macro-mcrHelloWorldEnhanced. The code and Project Explorer are shown in Figure 22.24.

   

When you open the VBE for the new module, you can view the procedure created from the macro. Figure 22.24 shows the mcrHelloWorldEnhanced function that Access created from the mcrHelloWorldEnhanced macro.

At the top of the function, Access inserts four comment lines for the name of the function. The Function statement follows the comment lines. Access names the function, using the macro library's name (mcrHelloWorldEnhanced).

When you specify that you want Access to include error processing for the conversion, Access automatically inserts the On Error statement as the first command in the procedure. The On Error statement tells Access to branch to other statements that display an appropriate message and then exit the function.

The statement beginning with TempVars.Add is the actual code that Access created from the macro. Each line of the macro is converted into a line of VBA code, including the TempVars object, the Beep method, and the MsgBox function.

If you're new to VBA and want to learn code, a good starting point is converting your macros to modules. Just save your macros and modules, and then look at the VBA code to become familiar with the syntax. The macro features in Access 2016 make it harder to decide whether to use macros or VBA.