Examining the macro

The macro was recorded in a new module named Module1. To view the code in this module, you must activate Visual Basic (VB) Editor. You can activate VB Editor in either of two ways:

• Press Alt+F11.
• Choose Developer ⇒ Code ⇒ Visual Basic.

In VB Editor, the project window displays a list of all open workbooks and add-ins. This list is displayed as a tree diagram, which you can expand or collapse. The code that you recorded previously is stored in Module1 in the current workbook. When you double-click Module1, the code in the module appears in the Code window.

The macro should look something like this:

Sub MyName()
'
' MyName Macro
'
' Keyboard Shortcut: Ctrl+Shift+N
'
    ActiveCell.FormulaR1C1 = "Michael Alexander"

End Sub

The recorded macro is a Sub procedure named MyName. The statements tell Excel what to do when the macro is executed.

At the top of the procedure, note that Excel inserted some comments, which consist of information from the Record Macro dialog box. These comment lines (which begin with an apostrophe) aren’t necessary, and deleting them has no effect on how the macro runs. If you ignore the comments, you'll see that this procedure has only one VBA statement:

ActiveCell.FormulaR1C1 = "Michael Alexander"

This single statement inserts in the active cell the name you typed while recording.

Placing a single apostrophe in front of any text of creates a comment and is called commenting a line. Commented lines will turn green and Excel will skip these lines when running the macro. Comments allow you to add your own notes in the code, giving you a chance to document what the code is doing, any business rules you’ve applied, or any other information you feel would help when reading the code.

Testing the macro

Before you recorded this macro, you set an option that assigned the macro to the Ctrl+Shift+N shortcut key combination. To test the macro, return to Excel by using either of the following methods:

• Press Alt+F11.
• Click the View Microsoft Excel button on the VB Editor toolbar.

When Excel is active, activate a worksheet. (It can be in the workbook that contains the VBA module or in any other workbook.) Select a cell and press Ctrl+Shift+N. The macro immediately enters your name into the cell.

In the preceding example, note that you selected the cell to be formatted before you started recording your macro. This step is important. If you select a cell while the macro recorder is turned on, that cell will be recorded into the macro. In such a case, the macro would always format that particular cell, and it would not be a general-purpose macro.

Editing the macro

After you record a macro, you can make changes to it (although you must know what you’re doing). For example, assume that you want your name to be bold. You could re-record the macro, but editing the code is more efficient because this modification is simple. Press Alt+F11 to activate the VB Editor window. Then double-click Module1 and insert the following statement before the End Sub statement:

ActiveCell.Font.Bold = True

The edited macro appears as follows:

Sub MyName()
'
' MyName Macro
'
' Keyboard Shortcut: Ctrl+Shift+N
'
    ActiveCell.Font.Bold = True

    ActiveCell.FormulaR1C1 = "Michael Alexander"

End Sub

Test this new macro, and you see that it performs as it should.

Recording macros with absolute references

Excel’s default recording mode is absolute reference. When a cell reference in a formula is an absolute reference, it does not automatically adjust when the formula is pasted to a new location.

The best way to understand how this concept applies to macros is to try it out. Open the Chapter 1 Sample File.xlsx file and record a macro that counts the rows in the Branchlist worksheet. (See Figure 1-2.)

You can find the sample data set used in this chapter on this book’s companion website at www.dummies.com/extras/excelmacros. See this book's Introduction for more on the companion website.

Follow these steps to record the macro:

1. Make sure cell A1 is selected.
2. On the Developer tab, select Record Macro.
3. Name the macro AddTotal.
4. Choose This Workbook for the save location.

5. Click OK to start recording.

   At this point, Excel is recording your actions.

6. While Excel is recording, select cell A16 and type Total in the cell.

7. Select the first empty cell in Column D (D16) and type = COUNTA(D2:D15).

   This formula gives a count of branch numbers at the bottom of column D. You use the COUNTA function because the branch numbers are stored as text.

8. Click Stop Recording on the Developer tab to stop recording the macro.

   The formatted worksheet should look like something like the one in Figure 1-3.

To see your macro in action, delete the Total row you just added and play back your macro by following these steps:

1. On the Developer tab, select Macros.
2. Find and select the AddTotal macro you just recorded.
3. Click the Run button.

If all goes well, the macro plays back your actions to a T and gives your table a total. Now here’s the thing. No matter how hard you try, you can’t make the AddTotal macro work on the second table. Why? Because you recorded it as an absolute macro.

To understand what this means, examine the underlying code by selecting Macros on the Developer tab. The Macro dialog box appears, as shown in Figure 1-4.

Select the AddTotal macro and click the Edit button. Visual Basic Editor opens and displays the code that was written when you recorded your macro:

Sub AddTotal()

  Range("A16").Select

  ActiveCell.FormulaR1C1 = "Total"

  Range("D16").Select

  ActiveCell.FormulaR1C1 = "=COUNTA(R[-14]C:R[-1]C)"

End Sub

Pay particular attention to the two lines of code that select range A16 and range D16. Because the macro was recorded in absolute reference mode, Excel interpreted your range selection as absolute cell references. In other words, no matter where your cursor is in your workbook, when you run the recorded macro, Excel will select cell A16 and then cell D16. In the next section, you take a look at what the same macro looks like when recorded in relative reference mode.

Recording macros with relative references

A relative reference means relative to the currently active cell. So use caution with your active cell choice — both when you record the relative reference macro and when you run it.

First, make sure the Chapter 1 Sample File.xlsx file is open. (This file is available on this book’s companion website at www.dummies.com/extras/excelmacros.) Then, use the following steps to record a relative reference macro:

1. On the Developer tab, select the Use Relative References option, as shown in Figure 1-5.
2. Make sure cell A1 is selected.
3. On the Developer tab, select Record Macro.
4. Name the macro AddTotalRelative.
5. Choose This Workbook for the save location.
6. Click OK to start recording.
7. Select cell A16 and type Total in the cell.
8. Select the first empty cell in Column D (D16) and type = COUNTA(D2:D15).
9. On the Developer tab, click Stop Recording to stop recording the macro.

At this point, you've recorded two macros. Take a moment to examine the code for your newly created macro by selecting Macros on the Developer tab to open the Macro dialog box. Choose the AddTotalRelative macro and click Edit.

Again, Visual Basic Editor opens and shows you the code that was written when you recorded your macro. This time, your code looks something like the following:

Sub AddTotalRelative()

  ActiveCell.Offset(15, 0).Range("A1").Select

  ActiveCell.FormulaR1C1 = "Total"

  ActiveCell.Offset(0, 3).Range("A1").Select

  ActiveCell.FormulaR1C1 = "=COUNTA(R[-14]C:R[-1]C)"

End Sub

First note that the code does not contain references to specific cell ranges (other than the starting point, A1). Note that in this macro, Excel uses the Offset property of the active cell. This property tells the cursor to move a certain number of cells up or down and a certain number of cells left or right.

In this case, the Offset property code tells Excel to move 15 rows down and 0 columns across from the active cell (A1). Because the macro was recorded using relative reference, Excel will not explicitly select a particular cell as it did when recording an absolute reference macro.

To see this macro in action, delete the Total row for both tables and do the following:

1. Select cell A1.
2. On the Developer tab, select Macros.
3. Find and select the AddTotalRelative macro.
4. Click the Run button.
5. Select cell F1.
6. On the Developer tab, select Macros.
7. Find and select the AddTotalRelative macro.
8. Click the Run button.

Note that this macro, unlike your previous macro, works on both sets of data. Because the macro applies the totals relative to the currently active cell, the totals are applied correctly.

For this macro to work, you simply need to ensure that

• You’ve selected the correct starting cell before running the macro.
• The block of data has the same number of rows and columns as the data on which you recorded the macro.

I hope this simple example has given you a firm grasp of macro recording with both absolute and relative references.

Macro-enabled file extensions

Beginning with Excel 2007, Excel workbooks were given the standard 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. 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. The idea is that all workbooks with an .xlsx file extension are automatically known to be safe, whereas you can recognize .xlsm files as a potential threat.

Macro security in Excel 2010

With the release of Office 2010, 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 to be safe.

If you open a workbook that contains macros in Excel 2010 or later, you see a yellow bar message under the ribbon stating that macros (active content) have been disabled.

If you click Enable, the workbook automatically becomes a trusted document. This means you no longer are prompted to enable the content as long as you open that file on your computer. The basic idea is that if you tell Excel that you trust a particular workbook by enabling macros, it is highly likely that you will enable macros each time you open the workbook. Thus, Excel remembers that you’ve enabled macros before and inhibits any further messages about macros for that workbook.

This feature is great news for you and your clients. After enabling your macros 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.

Any workbook you create from scratch will automatically be considered to be trusted. That is, Excel will not require you to enable macros in the workbooks you create.

Trusted locations

If the thought of any macro message coming up (even one time) unnerves you, set up a trusted location for your files. A trusted location is a directory that is 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. On the Developer tab, select the Macro Security button.

   This activates the Trust Center dialog box.

2. On the left, click Trusted Locations.

   The Trusted Locations menu appears (see Figure 1-6), displaying all the directories that are considered trusted.

3. Click the Add New Location button.
4. Click Browse, and find and select the directory that will be considered a trusted location.

After you specify a trusted location, any Excel file that is opened from this location will have macros automatically enabled.

Storing macros in your personal macro workbook

Most user-created macros are designed for use in a specific workbook, but you may want to use some macros in all your work. You can store these general-purpose macros in the personal macro workbook so that they’re always available to you. The personal macro workbook is loaded whenever you start Excel. This file, named personal.xlsb, doesn't exist until you record a macro using the personal macro workbook as the destination.

To record the macro in your personal macro workbook, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down list (refer to Figure 1-1).

If you store macros in the personal macro workbook, you don’t have to remember to open the personal macro workbook when you load a workbook that uses macros. When you want to exit, Excel asks whether you want to save changes to the personal macro workbook.

The personal macro workbook normally is in a hidden window to keep it out of the way.

Assigning a macro to a button and other form controls

When you create macros, you may want to have a clear and easy way to run each one. A basic button can provide a simple but effective user interface.

As luck would have it, Excel offers a set of form controls for creating user interfaces directly on spreadsheets. Several types of form controls are available, from buttons (the most commonly used control) to scrollbars.

The idea behind using a form control is simple. You place a form control on a spreadsheet and then assign a macro to it — that is, a macro you’ve already recorded. When the control is clicked, the macro is executed, or played.

Take a moment to create a button for the AddTotalRelative macro you created earlier. Here’s how:

1. On the Developer tab, click the Insert command, shown in Figure 1-7.
2. In the drop-down list that appears, select the button form control.

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

   When you drop the button control on your spreadsheet, the Assign Macro dialog box appears, as shown in Figure 1-8.

4. Select the macro you want to assign to the button and then click OK.

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

Placing a macro on the Quick Access toolbar

You can assign a macro not only to a form control on a spreadsheet but also 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 will run your macro by following these steps:

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

   The dialog box illustrated in Figure 1-9 appears.

2. On the left of the dialog box, click Quick Access Toolbar.
3. In the Choose Commands From drop-down list, select Macros.
4. Select the macro you want to add and then click the Add button.
5. Change the icon by clicking the Modify button.

Building navigation buttons

The most common use of macros is in navigation. Workbooks that have many worksheets or tabs can be frustrating to navigate. To help your audience, you can create a switchboard, like the one shown in Figure 1-10. When users click the Example 1 button, for example, 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 starting point.
2. Start recording a macro.
3. While recording, click the destination sheet (the sheet this macro will navigate to).
4. Stop recording.
5. Add a button form control on your starting point and Assign the macro to a button by selecting your newly recorded macro in the Assign Macro dialog box.

Excel has a built-in hyperlink feature, which enables you to convert the contents of a cell to a hyperlink that links to another location. That location can be a separate Excel workbook, a website, or another tab in the current workbook. Although creating a hyperlink may be easier than setting up a macro, you can’t apply a hyperlink to a form control (such as a button). 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 1-11, macros allow a user to change the perspective of the chart simply by selecting one of the buttons shown.

Figure 1-12 reveals that the chart is actually a pivot chart tied to a pivot table. The recorded macros assigned to the buttons do nothing more than rearrange 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 your pivot table and a pivot chart.
2. Start recording a macro.
3. Move a pivot field from one area of the pivot table to the other, and then stop recording the macro.
4. Record another macro to move the data field back to its original position.
5. Assign each macro 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, note that users can filter the pivot table for the top or bottom 20 customers. Because the steps for this filter have been recorded, users save time and effort and can benefit from this functionality without having to know the steps involved. Also, recording a specific action enables you to manage risk because your users will interact with your reports in a method that you've developed and tested.

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 apparent uselessness of this example. It’s not uncommon to be asked to see the same data different ways.

Instead of taking up real estate with multiple charts, just record a macro that changes the chart type. Your clients will be able to switch views to their heart’s content.