FIGURE 2.4 The Excel Macro dialog box
Select the AddTotal macro and click the Edit button. This opens the Visual Basic Editor to show you 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 line 2 and line 4 of the macro. When you asked Excel to select cell range A16 and then D16, those cells are exactly what it selected. Because the macro was recorded in absolute reference mode, Excel interpreted your range selection as absolute. In other words, if you select cell A16, that cell is what Excel gives you. In the next section, you will examine what the same macro looks like when recorded in relative reference mode.
Recording macros with relative references
In the context of Excel macros, relative means relative to the currently active cell. Thus, you should use caution with your active cell choice—both when you record the relative reference macro and when you run it.
First, make sure that the Chapter 2 Sample.xlsm file is open. (This file is available on this book's companion website.) Then use the following steps to record a relative-reference macro:
1 Select the Use Relative References toggle button from the Developer tab, as shown in Figure 2.5.FIGURE 2.5 Recording a macro with relative references
2 Before recording, make sure that cell A1 is selected.
3 Select Record Macro from the Developer tab.
4 Name the macro AddTotalRelative.
5 Choose This Workbook in the Store Macro In drop-down.
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), type = COUNTA(D2:D15), and then press Enter.
9 Click Stop Recording on the Developer tab to stop recording the macro.
At this point, you have recorded two macros. Take a moment to examine the code for your newly created macro.
Select Macros from the Developer tab to open the Macro dialog box. Here, choose the AddTotalRelative macro and click Edit.
Again, this opens the Visual Basic Editor to show 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
Notice that there are no references to any specific cell ranges at all (other than the starting point "A1"). Let's take a moment to look at what the relevant parts of this VBA code really mean.
Notice that in line 2, 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.
The Offset property code tells Excel to move 15 rows down and 0 columns across from the active cell (in this case, A1). There's no need for Excel to select a cell explicitly, as it did when recording an absolute reference macro.
To see this macro in action, delete the total row and do the following:
1 Select cell A1.
2 Select Macros from the Developer tab.
3 Find and select the AddTotalRelative macro.
4 Click the Run button.
5 Now select cell F1.
6 Select Macros from the Developer tab.
7 Find and select the AddTotalRelative macro.
8 Click the Run button.
Notice 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.
Ideally, this simple example has given you a firm grasp of macro recording of both absolute and relative references.
Other macro recording concepts
At this point, you should feel comfortable recording your own Excel macros. Next are some of the other important concepts you'll need to keep in mind when writing or recording macros.
By default, Excel workbooks are given the standard file extension .xlsx. Be aware that files with the .xlsx extension cannot contain macros. If your workbook contains macros and then you save that workbook as an .xlsx file, all VBA code is removed automatically. Luckily, Excel will warn you that your macro content will be removed 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.
Alternatively, you can save your workbook as an Excel 97-2003 Workbook (with the .xls extension). The .xls file type can contain macros, but it doesn't support some of the modern features of Excel such as conditional formatting icons and pivot table slicers. You would typically use this file type only if there is a specific reason, such as that you need to have your workbook interact with an add-in that works only with .xls files.
Macro security in Excel
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 minutiae, a trusted document is essentially a workbook that you have deemed safe by enabling macros.
If you open a workbook that contains macros, you will see a yellow bar message under the Ribbon stating that macros (active content) have, in effect, been disabled.
If you click Enable, it automatically becomes a trusted document. This means that 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 told Excel that you “trust” a particular workbook by enabling macros, it is highly likely that you will enable macros each 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 by 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.
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