ADDING CODE TO AN EXISTING MACRO

Suppose you’ve recorded a macro that enters a series of labels, sets their font, and then draws a border around them. Then you discover that you forgot a step or that you recorded a step incorrectly—you chose the wrong border format, for example. What do you do?

To add code to an existing macro, you can record actions in a temporary macro and then transfer the code into the macro you want to change.

For example, to add to the MyPolyAddress macro a step that sets font options for my name, follow these steps:

  1. Switch to the worksheet that contains the address you entered earlier and select cell A2, which contains my name.
  2. Choose Tools, Macro, Record New Macro. Excel presents the Record Macro dialog box. In the Macro Name box, enter MacroTemp and click OK. Excel displays the Stop Recording toolbar.
  3. Choose Format, Cells, and click the Font tab. Select Arial (or any other installed font), 14-point, and Bold Italic. Then click OK to apply the formats.
  4. Click the Stop Recording button on the Stop Recording toolbar.
  5. Choose Tools, Macro, Macros. In the Macro dialog box, select MacroTemp and click Edit.
  6. A window appears that contains the original macro you recorded plus the MacroTemp macro.
  7. Select all the code inside the macro—from the line beginning With through the line beginning End With—and then choose Edit, Copy.
  8. Scroll up to display the MyPolyAddress macro.
  9. Click at the beginning of the line that contains this statement: Range("A2").Select Press Enter to create a blank line. Then position the insertion point at the beginning of the blank line.
  10. Choose Edit, Paste.
  11. Scroll back down and delete the entire MacroTemp macro, from the Sub statement to the End Sub statement.
The MyPolyAddress macro should now look like this (with the comment lines excluded and assuming you’ve made the name 14-point Arial Bold Italic):
Sub MyPolyAddress()
Range("A2").Select
ActiveCell.FormulaR1C1 = "Prof. Ming Leung"
With Selection.Font
    .Name = "Arial"
    .FontStyle = "Bold Italic"
    .Size = 14
    .Strikethrough = False
    .Superscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
End With
Range("A3").Select
ActiveCell.FormulaR1C1 = "Polytechnic University"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Six Metrotech Center"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Brooklyn, NY 11201"
Range("A6").Select
End Sub
To test the macro, return to Excel (press Alt+F11 or select the Excel button on the taskbar). Clear the names and address that you entered earlier when you recorded the macro. Then press Ctrl+Shift+A.

The With and End With statements that the macro recorder created when you recorded MacroTemp (the statements that you subsequently copied into MyPolyAddress) specify a group of properties belonging to an object—in this case, the font of the current selection.

The With … End With construct provides a kind of shorthand for a series of VBA statements that would otherwise look like this:
Selection.Font.Name = "Arial"
Selection.Font.FontStyle = "Bold Italic"
Selection.Font.Size = 14
and so on. In the MyPolyAddress macro, the ActiveCell object and the Selection object both refer to the same range on the worksheet, cell A6. Because you can apply a series of font formatting options to an entire range, Excel records the action with Selection, rather than with ActiveCell. Enclosing the property assignments within the With … End With structure simplifies the code and also makes it run faster.