Using Subroutines in Macros

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-or in an entirely different macro. In our CompanyAddress macro, for example, it might be convenient if we could quickly and easily apply the font formats of the company name to other items in a worksheet.

With VBA, you can 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. 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 subroutine in another macro, you call the subroutine by using its name in the other macro.

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

1. Click Macros in the Code group on the Developer tab. In the Macro dialog box, select CompanyAddress, and click Edit. Then select the statements that format the font of the company's name:

    With Selection. Font     .Name = "Cambria"     .FontStyle = "Bold Italic"     .Size = 14     .Strikethrough = False     .Superscript = False     .Subscript = False     .OutlineFont = False     .Shadow = False     .Underline = xlUnderlineStyleNone     .ThemeColor = xlThemeColorLightl     .TintAndShade = 0     .ThemeFont = xlThemeFontMajor End With 

2. Click Edit, Cut (or press Ctrl+X).

3. Click after the End Sub statement at the end of the CompanyAddress macro, and type Sub CompanyFont().

4. The Visual Basic Editor types an End Sub statement for you. In the blank line between the Sub and End Sub statements, click Edit, Paste (or press Ctrl+V) to insert the font-formatting code.

You've created a new CompanyFont macro by moving the formatting codes from the CompanyAddress macro to the new CompanyFont 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 CompanyAddress macro so it uses the CompanyFont macro, follow these steps:

1. Click at the end of this statement, and then press Enter to insert a new line:

    ActiveCell.FormulaR1C1 = "Coho Winery" 

2. Type CompanyFont.

When you've finished, the two macros should look like the ones in the following listing:

 Sub CompanyAddress()     CompanyFont     Range("A6").Select     ActiveCell.FormulaR1C1 = "Coho Winery"     Range("A7").Select     ActiveCell. FormulaR1C1 = "3012 West Beaujolais St."     Range("A8").Select     ActiveCell.FormulaR1C1 = "Walla Walla, Wa 98765"     Range("A9").Select End Sub Sub CompanyFont()     With Selection.Font         .Name = "Cambria"         .FontStyle = "Bold Italic"         .Size = 14         .Strikethrough = False         .Superscript = False         .Subscript = False         .OutlineFont = False         .Shadow = False         .Underline = xlUnderlineStyleNone         .ThemeColor = xlThemeColorLightl         .TintAndShade = 0         .ThemeFont = xlThemeFontMajor     End With End Sub 

When you activate the CompanyAddress macro by pressing Ctrl+Shift+A, Excel runs the first statement in the macro. Because that first statement calls the CompanyFont macro, it switches to the first line of CompanyFont. When Excel reaches the End Sub statement at the end of CompanyFont, it returns to the statement in CompanyAddress immediately after the one that called CompanyFont and continues until it reaches the End Sub statement at the end of CompanyAddress.