Using the Macro Recorder to Build an Expression

The macro recorder is probably the best way to learn how to build expressions that return objects. The recorder is especially useful when you work with the complex object hierarchies of charts, charts embedded on worksheets, and PivotTables®. Use the macro recorder to build an expression that contains the properties and methods you need, and then modify the expression if necessary.

For example, suppose that you need help building an expression that changes the font style and font size for the title of a chart. You don't remember the object model, so you use the recorder to produce the following code.

Sub Macro1()
    ActiveChart.ChartTitle.Select
    With Selection.Font
        .Name = "Times New Roman"
        .FontStyle = "Bold"
        .Size = 24
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
End Sub

The recorder navigated the object model for you, recording the accessors for the Chart, ChartTitle, and Font objects. You can now modify the recorded code to:

• Change the ActiveChart property to the Charts method and use a chart name as the argument. This allows you to run the macro from any sheet in the workbook, not just from the chart sheet.
• Remove the selection-based code. You don't need the Select method or the Selection property. You also need to move the With keyword after you make this change.
• Remove the properties of the Font object that the macro should not change (it should change only the FontStyle property and the Size property; it should not change any other property).
• Change the procedure name.

The following example shows the code after modification.

Sub FormatChartTitle()
    With Charts("Chart1").ChartTitle.Font
        .FontStyle = "Bold"
        .Size = 24
    End With
End Sub

When you record actions on cells and ranges, be sure to record using relative references (on the Tools menu, point to Record Macro, and then click Use Relative References). This ensures that the recorded code contains offsets to cells instead of absolute references to cells, which makes the code more general and more easily adaptable to your specific requirements.