Assembling the Pieces

You have all the subordinate task macros ready for carrying out your complex monthly project:

• ImportFile opens and parses the text file.

• FillLabels makes the file look like a database.

• AddDates distinguishes one month from another in the database.

• AppendData adds the new rows to the bottom of the saved database.

• DeleteSheet cleans up the temporary worksheet.

Each piece is prepared and tested. Now you get to put them all together.

Record a Macro That Runs Other Macros

The easiest way to glue macros together is to record a macro that runs other macros.

1. Click the Record Macro button, type MonthlyProject as the macro name, and then click OK.

2. In the View tab of the Ribbon, click the View Macros button, click ImportFile, and then click Run.

3. Select the text file you want to import, and then click Open.

4. Click the View Macros button, click FillLabels, and then click Run.

5. Click the View Macros button, click AddDates, and then click Run.

   

6. Type an appropriate date, and click OK.

7. Click the View Macros button, click AppendData, and then click Run.

8. Click the View Macros button, click DeleteSheet, and then click Run.

9. Click the Stop Recording button.

10. Now you can look at what you created. Click the View Macros button, select the MonthlyProject macro, and click Edit.

    After deleting the standard comments, here’s what the macro to run other macros looks like:

    Sub MonthlyProject()   Application.Run "Chapter02.xlsm!ImportFile"   Application.Run "Chapter02.xlsm!FillLabels"   Application.Run "Chapter02.xlsm!AddDates"   Application.Run "Chapter02.xlsm!AppendData"   Application.Run "Chapter02.xlsm!DeleteSheet"  End Sub 

The MonthlyProject macro runs each of the subordinate macros in turn. If you think of the MonthlyProject macro as a routine, then it makes sense to think of each of the subordinate macros as a subroutine. (And this is, in fact, the archaic historical reason each macro begins with the word Sub: because it can act as a subroutine.)

Simplify the Subroutine Statements

The statement that the macro recorder creates for running a subroutine works, but it is somewhat unwieldy. You can simplify the statement, making it easier to read and faster to run (although you are unlikely to see any difference in speed).

1. Delete everything from each recorded subroutine statement except the name of the macro itself.

   Here’s what the macro should look like when you’re done:

   Sub MonthlyProject()   ImportFile   FillLabels   AddDates   AppendData   DeleteSheet  End Sub 

2. Save the Chapter02 workbook.

3. Press F5 to test the MonthlyProject macro. (You might also want to try pressing F8 to step through the main macro and each of the subroutines.)

You now have an automated process for importing each new month’s data. You’ve worked hard and deserve a rest. Take the rest of the day off.

CLOSE the Chapter02.xlsx Workbook.