MACRO SUBROUTINES

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.
You’ve created a new MyPolyFont macro by moving the font formatting codes from the MyPolyAddress macro into the new MyPolyFont macro. As mentioned, to run one macro from within another, you must use the name of the second macro in the first. To update the MyPolyAddress macro so it uses the MyPolyFont macro, follow these steps:
  1. Click at the end of this statement:
    ActiveCell.FormulaR1C1 = "Prof. Ming Leung"
    
    Press Enter to insert a new line.
  2. Type MyPolyFont.
When you’ve finished, the two macros should look like the ones in the following listing:
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
When you activate the MyPolyAddressRel macro by pressing Ctrl+A, Excel runs the first statement in the macro. Since that first statement calls the MyPolyFont macro, it switches to the first line of MyPolyFont. When Excel reaches the End Sub statement at the end of MyPolyFont, it returns to the statement in MyPolyAddress immediately after the one that called MyPolyFont and continues until it reaches the End Sub statement at the end of MyPolyAddress.