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.
4. Click Run.
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 RecordMacro command.
2. Name the macro FormatTable and click OK. (You cannot use spaces or special characters
in your macro names.)
CHAPTER 1 n THE MACRO RECORDER AND CODE MODULES 16
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").
Pages:
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42