Chapter 10

Macro-Charged Dashboarding

In This Chapter

arrow Introducing macros

arrow Recording macros

arrow Setting up trusted locations for your macros

arrow 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.

remember 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.
image

Figure 10-1: Enabling the Developer tab.

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.

image

Figure 10-2: The Record Macro dialog box.

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.

image

Figure 10-3: Start recording a new macro called AddDataBars.

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.

image

Figure 10-4: Start recording a new Macro called RemoveDataBars.

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.

image

Figure 10-5: Use the Macro dialog box to select a macro and run it.

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

image

Figure 10-6: Your macro applies Data Bars automatically!

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

image

Figure 10-7: The Remove-DataBars macro removes the applied Data Bars.

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.
image

Figure 10-8: You can find the Form Controls menu on the Developer tab.

image

Figure 10-9: Assign a macro to the newly added button.

tip 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.

remember 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.

Understanding macro-enabled file extensions

It’s important to note that Microsoft has created a separate file extension for workbooks that contain macros.

Workbooks created in Excel 2010 and later versions have the default file extension .xlsx. Files with the .xlsx extension cannot contain macros. If your workbook contains macros and you then save that workbook as an .xlsx file, your macros are removed automatically. Of course, Excel warns you that macro content will be disabled when saving a workbook with macros as an .xlsx file.

If you want to retain the macros, you must save your file as an Excel macro-enabled workbook. This gives your file an .xlsm extension. All workbooks with an .xlsx file extension are automatically known to be safe, whereas you can recognize .xlsm files as a potential threat.

Enabling macro content

When you open a workbook that contains macros in Excel, you get a message in the form of a yellow bar under the Ribbon stating that macros (active content) have in effect been disabled.

If you click Enable Content, it automatically becomes a trusted document. You are then no longer prompted to enable the content as long as you open that file on your computer. If you told Excel that you trust a particular workbook by enabling macros, it’s highly likely that you will enable macros every time you open it. Thus, Excel remembers that you’ve enabled macros before and inhibits any further messages about macros for that workbook.

This is great news for you and your clients. After enabling your macros just one time, they won’t be annoyed at the constant messages about macros, and you won’t have to worry that your macro-enabled dashboard will fall flat because macros have been disabled.

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.

image

Figure 10-10: The Trusted Locations menu allows you to add directories that are considered trusted.

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.

remember 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.

image

Figure 10-11: 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.

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

tip 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.

image

Figure 10-12: This report allows users to choose their perspective.

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.

image

Figure 10-13: The macros behind these buttons rearrange the data fields in a pivot table.

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.

image

Figure 10-14: Macros can offer your users prerecorded views.

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.

image

Figure 10-15: You can give your audience a choice in how they view data.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.117.107.40