Excel Macros For Dummies. Alexander Michael

to the newly added button.

      At this point, you have a button that runs your macro when you click it! Keep in mind that all the controls in the Form Controls group (shown in Figure 1-7) work in the same way as the command button, in that you assign a macro to run when the control is selected.

      FORM CONTROLS VERSUS ActiveX CONTROLS

      Notice the form controls and ActiveX controls in Figure 1-7. Although they look similar, they’re quite different. Form controls are designed specifically for use on a spreadsheet, and ActiveX controls are typically used on Excel user forms. As a general rule, you should always use form controls when working on a spreadsheet. Why? Form controls need less overhead, so they perform better, and configuring form controls is far easier than configuring their ActiveX counterparts.

Placing a macro on the Quick Access Toolbar

      You can also assign a macro to a button in Excel’s Quick Access Toolbar. The Quick Access Toolbar sits either above or below the Ribbon. You can add a custom button that runs your macro by following these steps:

      1. Right-click your Quick Access Toolbar and select Customize Quick Access Toolbar.

This opens the dialog box shown in Figure 1-9.

      2. Click the Quick Access Toolbar button on the left of the Excel Options dialog box.

      3. Select Macros from the Choose Commands From drop-down list on the left.

      4. Select the macro you want to add and click the Add button.

      5. Change the icon by clicking the Modify button.

       FIGURE 1-9: Adding a macro to the Quick Access Toolbar.

      Examples of Macros in Action

      Covering the fundamentals of building and using macros is one thing. Coming up with good ways to incorporate them into your reporting processes is another. Take a moment to review a few examples of how macros automate simple reporting tasks.

      

Open the Chapter 1 Samples.xlsm file to follow along in the next section. To download the Chapter 1 Sample file, visit www.dummies.com/go/excelmacros.

Building navigation buttons

The most common use of macros is navigation. Workbooks that have many worksheets or tabs can be frustrating to navigate. To help your audience, you can create some sort of a switchboard, like the one shown in Figure 1-10. When a user clicks the Example 1 button, he’s taken to the Example 1 sheet.

       FIGURE 1-10: Use macros to build buttons that help users navigate your reports.

      Creating a macro to navigate to a sheet is quite simple.

      1. Start at the sheet that will become your switchboard or starting point.

      2. Start recording a macro.

      3. While recording, click the destination sheet (the sheet this macro will navigate to).

      4. After you click in the destination sheet, stop recording the macro.

      5. Assign the macro to a button.

      

It’s useful to know that Excel has a built-in hyperlink feature, allowing you to convert the contents of a cell into a hyperlink that links to another location. That location can be a separate Excel workbook, a website, or even another tab in the current workbook. Although using a hyperlink may be easier than setting up a macro, you can’t apply a hyperlink to form controls (like buttons). Instead of a button, you’d use text to let users know where they’ll go when they click the link.

Dynamically rearranging PivotTable data

Macros be used with any Excel object normally used in reporting. For instance, you can use a macro to give your audience a way to dynamically change a pivot table. In the example illustrated in Figure 1-11, macros allow a user to change the perspective of the chart simply by selecting any one of the buttons shown.

       FIGURE 1-11: This report allows users to choose their perspective.

Figure 1-12 reveals that the chart is actually a pivot chart tied to a PivotTable. The recorded macros assigned to each button are doing nothing more than rearranging the PivotTable to slice the data using various pivot fields.

       FIGURE 1-12: The macros behind these buttons rearrange the data fields in a PivotTable.

      Here are the high-level steps needed to create this type of setup:

      1. Create your PivotTable and a pivot chart.

      2. Start recording a macro.

      3. While recording, move a pivot field from one area of the PivotTable to the other. When you’re done, stop recording the macro.

      4. Record another macro to move the data field back to its original position.

      5. After both macros are set up, assign each one to a separate button.

      You can fire your new macros in turn to see your pivot field dynamically move back and forth.

Offering one-touch reporting options

      The last two examples demonstrate that you can record any action that you find of value. That is, if you think users would appreciate a certain feature being automated for them, why not record a macro to do so?

In Figure 1-13, notice that you can filter the PivotTable for the top or bottom 20 customers. Because the steps to filter a PivotTable for the top and bottom 20 have been recorded, users can get the benefit of this functionality without knowing how to do it themselves. Also, recording a specific action allows you to manage risk a bit. That is to say, you’ll know that your users will interact with your reports in a method that has been developed and tested by you.

       FIGURE 1-13: Offering prerecorded views not only saves time and effort, but it also allows users that don’t know how to use advanced features to benefit from them.

      This not only saves them time and effort, but it also allows users that don’t know how to take these actions to benefit from them.

Figure 1-14 demonstrates how you can give your audience a quick and easy way to see the same data on different charts. Don’t laugh too quickly at the uselessness of this example. It’s not uncommon to be asked to see the same data different ways. Instead of taking up real estate, just record a macro that changes the Chart Type of the chart. Your clients can switch views to their heart’s content.