Excel Dashboards: How to Record Your First Macro

If you’re a beginner to dashboard automation in Excel, it’s unlikely that you’ll be able to write the VBA (Visual Basic for Applications) code by hand to create macros. 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 the desired action, and then run the macro each time you want that action performed.

To begin, you first need to unhide the Developer tab. You can find the full macro toolset in Excel 2013 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. To open the Excel Options dialog box, click the Options button.

  3. Click the Customize Ribbon button.

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

  4. Select the Developer tab, as shown in this figure.

    image0.jpg
  5. Click OK.

Now that you have the Developer tab, select it and click the Record Macro command. This opens the Record Macro dialog box, as shown in this figure.

image1.jpg

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 press, 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 your macro to run. You need not enter a shortcut key to run your 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, provided the macro security is properly set by your user.

  • 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, as shown in this figure. Press OK.

image2.jpg

Excel is now recording your actions. While Excel is recording, you can perform any actions you’d like. 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, go to the Format Style drop-down menu and select Data Bar.

  4. Another dialog box appears. Here, select the Show Bar Only check box.

  5. Press 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 can 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 from the Store Macro In drop-down menu, as shown in this figure. Press OK.

    image3.jpg
  9. Highlight cells C1:C21.

  10. Go to the Home tab and select Conditional Formatting___Clear Rules___Clear Rules from Selected Cells.

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

blog comments powered by Disqus
Advertisement

Inside Dummies.com