In This Chapter

Introducing macros

Recording macros

Setting up trusted locations for your macros

Adding macros to your dashboards and reports

A macro is essentially a set of instructions or code that you create to tell Excel to execute any number of actions. In Excel, macros can be written or recorded. The key word here is recorded.

Recording a macro is like programming a phone number into your cellphone. You first manually dial and save a number. Then when you want, you can redial those numbers with the touch of a button. Just as on a cellphone, you can record your actions in Excel while you perform them. While you record, Excel gets busy in the background, translating your keystrokes and mouse clicks to written code, also known as Visual Basic for Applications (VBA). After you record a macro, you can play back those actions anytime you want.

In this chapter, you explore macros and see how you can use them to automate recurring processes to simplify your life.

Why Use a Macro?

The first step in using macros is admitting you have a problem. Actually, you may have several problems:

• Repetitive tasks. As each new month rolls around, you have to make the doughnuts — that is, crank out those reports. You have to import that data. You have to update those pivot tables. You have to delete those columns, and so on. Wouldn’t it be nice if you could fire up a macro and have those more redundant parts of your dashboard processes done automatically?
• You’re making mistakes. When you enter into hand-to-hand combat with Excel, you’re bound to make mistakes. When you’re repeatedly applying formulas, sorting, and moving things around manually, you always run the risk of catastrophe. Add to that the looming deadlines and endless change requests, and your error rate goes up. Why not calmly record a macro, ensure that everything is running correctly, and then forget it? The macro is sure to perform every action the same way every time you run it, reducing the chance of errors.
• Awkward navigation. Remember that you’re creating these dashboards and reports for an audience that probably has a limited knowledge of Excel. If your reports are a bit too difficult to use and navigate, you’ll find that you slowly lose support for your cause. It’s always helpful to make your dashboard more user-friendly.

Here are some ideas for macros that make things easier for everyone:

• A macro that formats and prints a worksheet or range of worksheets at the touch of a button
• Macros that navigate a multisheet worksheet with a navigation page or with a go-to button for each sheet in a workbook
• A macro that saves the open document in a specified location and then closes the application at the touch of a button

Obviously, you can perform each of these examples in Excel without the aid of a macro. However, your audience will appreciate these little touches that help make perusal of your dashboard a bit more pleasant.

Recording Your First Macro

If you’re a beginner to dashboard automation, you’re unlikely to be able to write the VBA code by hand. Without full knowledge of Excel’s object model and syntax, writing the needed code would be impossible for most beginning users. This is where recording a macro comes in handy. The idea is that you record an action and then run the macro every time you want that action performed.

To get started in creating your first macro, open the Chapter 10 Samples file found on this book’s companion website. After the file is open, select the Recording Your First Macro tab.

To begin, you first need to unhide the Developer tab. You can find the full macro toolset in Excel on the Developer tab, which is initially hidden. You have to explicitly tell Excel to make it visible. To enable the Developer tab, follow these steps:

1. Go to the Ribbon and click the File button.
2. Open the Excel Options dialog box by clicking the Options button.

3. Click the Customize Ribbon button.

   In the list box on the right, you see all available tabs.

4. Select the Developer tab, as shown in Figure 10-1.
5. Click OK.

Now that you have the Developer tab visible on the Ribbon, select it and click the Record Macro command. This opens the Record Macro dialog box, as shown in Figure 10-2.

Here are the four fields in the Record Macro dialog box:

• Macro Name: Excel gives a default name to your macro, such as Macro1, but it’s best practice to give your macro a name more descriptive of what it actually does. For example, you might name a macro that formats a generic table as AddDataBars.
• Shortcut Key: This field is optional. Every macro needs an event, or something to happen, for it to run. This event can be a button click, a workbook opening, or in this case, a keystroke combination. When you assign a shortcut key to your macro, entering that combination of keys triggers the macro to run. You need not enter a shortcut key to run the macro.
• Store Macro In: This Workbook is the default option. Storing your macro in This Workbook simply means that the macro is stored along with the active Excel file. The next time you open that particular workbook, the macro will be available to run. Similarly, if you send the workbook to another user, that user can run the macro as well, as long as the macro security is properly set by your user — but more on that later.
• Description: This field is optional, but it’s useful if you have numerous macros in a spreadsheet or if you need to give a user a more detailed description about what the macro does.

In this first example, enter AddDataBars into the Macro Name field and select This Workbook from the Store Macro In drop-down menu; see Figure 10-3. Click OK.

Excel is now recording your actions. While Excel is recording, you can perform any actions you want. In this scenario, you record a macro to add Data Bars to a column of numbers.

Follow along using these steps:

1. Highlight cells C1:C21.
2. Go to the Home tab and select Conditional Formatting ⇒ New Rule.
3. In the New Formatting Rule dialog box, select Data Bar from the Format Style drop-down menu.
4. In the new dialog box that appears, select the Show Bar Only check box.
5. Click OK to apply your change.

6. Go to the Developer tab and click the Stop Recording command.

   At this point, Excel stops recording. You now have a macro that replaces the data in C1:C21 with Data Bars. Now you record a new macro to remove the Data Bars.

7. Go to the Developer tab and click the Record Macro command.
8. Enter RemoveDataBars into the Macro Name field and select the This Workbook option from the Store Macro In drop-down menu; see Figure 10-4.
9. Click OK.
10. Highlight cells C1:C21.
11. Go to the Home tab and select Conditional Formatting ⇒ Clear Rules ⇒ Clear Rules from Selected Cells.

12. Go to the Developer tab and click the Stop Recording command.

    Again, Excel stops recording. You now have a new macro that removes conditional formatting rules from cells C1:C21.

Running Your Macros

To see your macros in action, select the Macros command from the Developer tab. The dialog box in Figure 10-5 appears, allowing you to select the macro you want to run. Select the AddDataBars macro and click the Run button.

If all goes well, the macro plays back your actions to a T and applies the Data Bars as designed; see Figure 10-6.

You can now call up the Macro dialog box again and test the RemoveDataBars macro shown in Figure 10-7.

When you create macros, you want to give your audience a clear and easy way to run each macro. A button, used directly on the dashboard or report, can provide a simple but effective user interface.

Excel Form controls enable you to create user interfaces directly on your worksheets, simplifying work for your users. Form controls range from buttons (the most commonly used control) to scroll bars and check boxes.

For a macro, you can place a Form control in a worksheet and then assign that macro to it — that is, a macro you’ve already recorded. When a macro is assigned to the control, that macro is executed, or played, every time the control is clicked.

Take a moment to create buttons for the two macros (AddDataBars and RemoveDataBars) that you create earlier in this chapter. Here’s how:

1. Click the Insert drop-down list under the Developer tab.
2. Select the Button Form control, as shown in Figure 10-8.

3. Click the location where you want to place your button.

   When you drop the Button control into the worksheet, the Assign Macro dialog box, as shown in Figure 10-9, opens and asks you to assign a macro to this button.

4. Select the macro that you want to assign.

   In this case, select the AddDataBars macro and click OK.

5. Repeat Steps 1 through 4 for the RemoveDataBars macro.

The buttons you create come with a default name, such as Button3. To rename a button, right-click the button and then click the existing name. Then you can delete the existing name and replace it with a name of your choosing.

Keep in mind that all controls on the Form Controls menu work in the same way as the command button, in that you assign a macro to run when the control is selected.

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

Enabling and Trusting Macros

With the release of Office 2007, Microsoft introduced significant changes to its Office security model. One of the most significant changes is the concept of trusted documents. Without getting into the technical minutia, a trusted document is essentially a workbook you have deemed safe by enabling macros.

Setting up trusted locations

If the thought of any macro message coming up (even one time) unnerves you, you can set up a trusted location for your files. A trusted location is a directory deemed a safe zone where only trusted workbooks are placed. A trusted location allows you and your clients to run a macro-enabled workbook with no security restrictions as long as the workbook is in that location.

To set up a trusted location, follow these steps:

1. Select the Macro Security button on the Developer tab.

2. Click the Trusted Locations button.

   This step opens the Trusted Locations menu shown in Figure 10-10. There, you see all the directories that Excel considers trusted.

3. Click the Add New Location button.

4. Click Browse to find and specify the directory that will be considered a trusted location.

   After you specify a trusted location, any Excel file that’s opened from this location will have macros automatically enabled. Have your clients specify a trusted location and use your Excel files from there.

Examining Some Macro Examples

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 you can implement macros in your dashboards and reports.

Open the Chapter 10 Samples.xslm file found on this book’s companion website to follow along in the next section.

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 10-11. When users click the Example 1 button, they’re taken to the Example 1 sheet.

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.

   If you need help assigning a macro to a button, check out the “Running Your Macros” section, earlier in this chapter.

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 use text to let users know where they’ll go when they click the link.

Dynamically rearranging pivot table data

In the example illustrated in Figure 10-12, macros allow a user to change the perspective of the chart simply by selecting any one of the buttons shown.

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

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

1. Create the pivot table and then add a pivot chart by clicking inside the pivot table and selecting Insert ⇒ Charts ⇒ Bar.
2. Start recording a macro.
3. While recording, move a pivot field from one area of the pivot table 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 the pivot field dynamically move back and forth.

Offering one-touch reporting options

The two earlier macro 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 10-14, notice that you can filter the pivot table for the top or bottom 20 customers. Because the steps to filter a pivot table for the top and bottom 20 have been recorded, anyone can get the benefit of this functionality without knowing how to do it themselves. Also, recording specific actions 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.

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

Figure 10-15 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. Rather than take up real estate, just record a macro that changes the chart type. Your clients can switch views to their hearts’ content.