Suppose you’re creating a complex macro and you discover that, among other things, you want the macro to perform a task you’ve already recorded under a different name. Or suppose you discover that a task you’ve recorded as part of a macro is something you’d like to use by itself.

In our MyPolyAddress macro, for example, it might be nice if we could quickly and easily apply the font formats of my name to other items in a worksheet. With VBA, you can conveniently divide large macros into a series of smaller macros, and you can easily string together a series of small macros to create one large macro.

A macro procedure that is used by another macro is called a subroutine.

Macro subroutines can simplify your macros because you have to write only one set of instructions rather than repeat the instructions over and over.

To use a macro subroutine in another macro, you call the subroutine by using its name in the other macro.

To demonstrate, let’s split the MyPolyAddressRel macro into two parts by following these steps:

1. Choose Tools, Macro, Macros. Select MyPolyAddressRel and click Edit. Then select the statements that format the font of my name:
   

   With Selection.Font
   .Name = "Arial"
       .FontStyle = "Bold Italic"
       .Size = 12
       .Strikethrough = False
       .Superscript = False
       .Subscript = False
       .OutlineFont = False
       .Shadow = False
       .Underline = xlUnderlineStyleNone
       .ColorIndex = xlAutomatic
   End With
   

2. Choose Edit, Cut.
3. Click below the End Sub statement at the end of the MyPolyAddressRel macro, and type Sub MyPolyFont().
4. The VBE types an End Sub statement for you. In the blank line between the Sub and End Sub statements, choose Edit, Paste to insert the font formatting code.
   

1. Click at the end of this statement:
   

   ActiveCell.FormulaR1C1 = "Prof. Ming Leung"
   

   Press Enter to insert a new line.
2. Type MyPolyFont.

Sub MyPolyAddressRel()
    MyPolyFont
    ActiveCell.FormulaR1C1 = "Prof. Ming Leung"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Polytechnic University"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Six Metrotech Center"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Brooklyn, NY 11201"
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Sub MyPolyFont()
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold Italic"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
End Sub