Recording a Macro

In an Excel workbook, open the Developer ribbon and choose the Record Macro command to display the Record Macro dialog box, shown in Figure 1-18. The Record Macro dialog will display. The dialog box shows the default macro name, allows you to assign a shortcut key, lets you choose where to store the macro, and provides a text field where you can enter text describing the macro's function.

By default, Excel 2007 stores macros in the current workbook. If you want your macros to be available to any workbook, you can choose Personal Macro Workbook from the "Store macro in" drop-down list.

Figure 1-18. The Record Macro dialog box

1. In the Macro name text box, enter a name for your macro: MyMacro.

2. Add a shortcut key if you like.

3. From the "Store macro in" drop-down, choose This Workbook.

4. Add descriptive text if you like.

6. Enter the data shown in Figure 1-19.

Figure 1-19. Recording data entry

7. Choose the Stop Recording command from the Developer ribbon.

Let's take a look at the code Excel 2007 created for us. To open the Visual Basic Editor (VBE), choose the Visual Basic command from the Developer ribbon or use the Alt+F11 shortcut key combination.

A new standard code module named Modulel has been inserted in your project. Open Modulel by double-clicking the Modules folder, and then click Modulel to view the Macro Recorder-generated code. Listing 1-1 shows the code the Macro Recorder generated for us.

Listing 1-1. Macro Recorder-Generated Code Sub MyMacro()

' MyMacro Macro ' Enter test data

Range("A1").Select ActiveCell.FormulaR1C1 = "Item" Range("B1").Select ActiveCell.FormulaR1C1 = "Color" Range("C1").Select ActiveCell.FormulaR1C1 = "Quantity" Range("D1").Select ActiveCell.FormulaR1C1 = "Price" Range("E1").Select

ActiveCell.FormulaR1C1 = "Line total" Range("A2").Select ActiveCell.FormulaR1C1 = "Shirt" Range("B2").Select ActiveCell.FormulaR1C1 = "Red" Range("C2").Select ActiveCell.FormulaR1C1 = "5" Range("D2").Select ActiveCell.FormulaR1C1 = "6"

Range( "A3").Select ActiveCell.FormulaR1C1 Range("B3").Select ActiveCell.FormulaR1C1 Range("C3").Select ActiveCell.FormulaR1C1 Range("D3").Select ActiveCell.FormulaR1C1 Range( "A4").Select ActiveCell.FormulaR1C1 Range("B4").Select ActiveCell.FormulaR1C1 Range("C4").Select ActiveCell.FormulaR1C1 Range("D4").Select ActiveCell.FormulaR1C1 Range( "A6").Select ActiveCell.FormulaR1C1 Range( "A7").Select End Sub

Excel 2007 has created a subroutine for us, and we can see each cell we selected and the data we entered into each. One interesting thing to notice is Excel's choice of the FormulaR1C1 property to assign the data to the Range object (cell A1 in the second line of code generated), ActiveCell.FormulaR1C1 = "Item". We did not enter any formulas, and yet Excel uses a property used to reference a formula. As you're coding, you'll most likely assign a value to a cell or range by using the Range object's Value property, and use the FormulaR1C1 property to insert formulas.

1. Change the line ActiveCell.FormulaR1C1 = "Item" to ActiveCell.Value = "Item", and then delete all of the data from the worksheet. Run the MyMacro macro.

2. Click the Macros command from the Developer ribbon.

3. Choose MyMacro from the Macro dialog box.

Cell A1 contains the word Item as its value as it did in the previous example. The Value property is a bit more intuitive to use when typing code.

Let's create two more quick macros, one to format our data table and one to add formulas, to get a look at the code Excel creates.

Formatting the Table

1. Select the Record Macro command.

2. Name the macro FormatTable and click OK. (You cannot use spaces or special characters in your macro names.)

= "Hat" = "Black" = "10" = "8" = "Total"

3. Select cells A1:E1 using the mouse, and apply bold formatting to them.

4. Select cell A6 and apply bold formatting.

5. Choose the Stop Recording command from the Developer ribbon. The code Excel generates is very straightforward:

Sub FormatTable()

' FormatTable Macro ' Formats the table

Range("A1:E1").Select Selection.Font.Bold = True Range( "A6").Select Selection.Font.Bold = True End Sub

We select the range containing our data. Each Selection object's Font property has a Bold property that is set to True.

Adding Totals

1. Select the Record Macro command.

2. Name the macro AddTotals and click OK.

3. Select cell C6, choose the AutoSum command, and then press Enter (AutoSum can be found on the Home ribbon or the Formulas ribbon, as shown in Figure 1-20).

g*3 Insert * Delete -ffi Format ' Cells

Sort & Find & <2.w Filter" Select" Editing

Click here to display the result of a simple calculation, such as Average or Maximum Value, after the selected cells.

Figure 1-20. The AutoSum button on the Home ribbon

4. Select cell E2 and choose the AutoSum command. Press Enter.

5. Copy the contents of cell E2 to cells E3:E4. Press Enter.

6. Select cell E6 and choose the AutoSum command. Press Enter.

7. Choose the Stop Recording command from the Developer ribbon.

Taking a look at the code, notice that Excel uses the FormulaR1C1 property of the ActiveCell object, and this time it makes sense because we are entering formulas. One thing to note is that, depending on how you copy the formula from cell E2 to the rest of the column in step 5, Excel will create different lines of code.

Same Task, Different Code

If you use the fill handle and Ctrl-drag the contents into the range E3:E4, the code Excel generates might look like this:

Range("C6").Select

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" Range("E2").Select

ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])" Range("E2").Select

'Used fill handle to copy formula to E3:E4

Selection.AutoFill Destination:=Range("E2:E4"), Type:=xlFillDefault

Range("E2:E4").Select

Range("E6").Select

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" Range("E7").Select

If you select cell E2 and choose the Copy command, select the range E3:E4, and then choose the Paste command, Excel will generate this code:

Range("C6").Select

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" Range("E2").Select

ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])" Range("E2").Select

'Used Copy command to copy formula to E3:E4

Selection.Copy

Range("E3:E4").Select

ActiveSheet.Paste

Application.CutCopyMode = False

Range("E6").Select

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" Range("E7").Select

The code is identical up until the second Range("E2").Select command. In the first example, the fill method of copying was used, and we see Excel's AutoFill method invoked.

The AutoFill method takes two arguments, the range to fill (including the source range) and the type of fill to apply. The Type argument takes a value whose data type is xlAutoFillType enumeration. These correspond to the Series dialog and can contain the values listed in Table 1-2. These values can be combined by using the And operator (as in xlFillSeries And xlFillFormats).

Table 1-2. xlAutoFillType Enumerations

Name

Value Description xlFillCopy 1 Copies the values and formats from the source range to the target range xlFillDays 5 Extends the names of the days of the week in the source range into the target range xlFillDefault 0 Lets Excel determine the values and formats used to fill the target range xlFillFormats 3 Copies only the formats from the source range to the target range.

xlFillMonths 7 Extends the names of the months in the source range into the target range xlFillSeries 2 Extends the values in the source range into the target range as a series (e.g., "1, 2" will be extended as "3, 4, 5")

Copies only the values from the source range to the target range

Extends the names of the days of the workweek in the source range into the target range xlFillYears 8 Extends the years in the source range into the target range xlGrowthTrend 10 Extends the numeric values from the source range into the target range; assumes that each number is a result of multiplying the previous number by some value (e.g., "1, 2" will be extended as "4, 8, 16")

xlLinearTrend 9 Extends the numeric values from the source range into the target range, assuming that each number is a result of adding some value to the previous number (e.g., "1, 2" will be extended as "3, 4, 5")

xlFillValues 4 xlFillWeekdays 6

The copy-and-paste method is very straightforward:

1. Select the range to be copied: Range("E2").Select.

2. Choose the copy command: Selection.Copy.

3. Select the destination range: Range("E3:E4").Select.

4. Choose the Paste command: ActiveSheet.Paste.

Another interesting line of code is: ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)". The default cell or range reference behavior in the Macro Recorder is to use R1C1 notation. This provides you with row and column offsets from the active cell. It can be useful in situations where you must calculate cell addresses to be used in your formulas.

R1C1 notation uses the R value to show the row offset from the active cell and the C value to show the column offset from the active cell. The offset value is enclosed in brackets; it can be a negative number to show rows or columns with a lesser value than the active cell row or column, or a positive number to show rows or columns with a greater value than the active cell. If the reference is to the same row or column as the active cell, there is no value entered— only the letter R or C.

In the preceding example, the first call to the SUM function refers to the range R[-4]C:R[-1]C. This is interpreted as a range starting four rows above the active cell (C6) in the same column and ending in the cell one row above the active cell in the same column.

You may be used to seeing the SUM function used with direct cell references like =SUM(A1:A4), especially if you're entering formulas directly on a worksheet. If you are adding a total to cell A5, this is a direct way to get the total of that range. But what if you need to add a total value for a number of columns across a row under your data range through VBA code? Using R1C1 notation, the formula =SUM(R[-4]C:R[-1]C) will always refer to rows 1 through 4 in the same column as the active cell (where the active cell is located in row 5).

As you've seen, the VBE is where Excel's Macro Recorder stores the code it creates, and it's where you will create and save the code you use in your daily tasks as well as in this book's examples.

0 0

Post a comment