Chapter 2: Make a Macro Do Complex Tasks

Chapter at a Glance

In this chapter, you will learn to:

• Break a complex project into manageable pieces.

• Watch a macro run one statement at a time.

• Enter values into a macro while it’s running.

• Record movements relative to the active cell.

• Create a macro that runs other macros.

Rube Goldberg was famous for inventing intricate contraptions with hundreds of parts that made what should have been a simple device appear wildly complex. For example, his cat “alarm clock” drops a ball into a bucket and the weight of the bucket lifts a lever that releases a spring that wakes up a cat. The Milton Bradley game company has had years of success with the Mousetrap game, which is based on a Rube Goldberg concept. Rube Goldberg contraptions are fun to look at. Two massive, perpetually working Rube Goldberg contraptions in the lobby of Boston’s Logan International Airport have entertained travelers for hours.

Entertainment is one thing. Getting your job done is another. For example, the steps you might go through to get out a monthly report can be as complicated as a Rube Goldberg invention. First you import the monthly order file and add new columns to it. Then you sort it and print it and sort it a different way and print it again. Then you paste it at the end of the cumulative order-history file, and so forth. Each step has to be completed just right before the next one is started. Pretty soon, you start scheduling your vacations around the project because you don’t want to train someone else to do it. Right?

One good use for macros is making cumbersome Rube Goldberg tasks simple. By putting the numerous steps involved in creating that Rube Goldberg monthly report in a macro, you can accomplish the task with one mouse click.

The secret to creating a macro capable of handling a long, intricate project is to break the project into small pieces, create a macro for each piece, and then glue the pieces together. If you just turn on the recorder, carry out 400 steps, and cross your fingers hoping for the best, you have about a 1-in-400 chance of having your macro work properly. Let’s look at a hypothetical example.

As the bookkeeper at Lucerne Publishing, you have an elaborate month-end project that you’d like to automate so that you can delegate it to subordinates when you go on vacation. You get a monthly summary report of orders for the previous month from the order-processing system.

The report shows sales information for each combination of state, channel, price range, and category. The order-processing system still exports the report as a text file. You prepare the file and add the new month’s orders to a cumulative order-history database.

This chapter shows you how to record the separate tasks that make up this large, complex project as small, testable macros. Subsequently, you combine these small macros into one comprehensive macro. Along the way, you might learn some useful techniques for completing everyday tasks as well.

On The CD-Important 

Before you complete this chapter, you need to install the practice files from the book’s companion CD to their default locations. See “Using the Book’s CD” on page xv for more information.

USE a new blank, macro-enabled workbook and the Orders.xlsx and Nov2007.txt _files. These practice files are located in the Documents\MSP\ExcelVBA07SBS folder.

BE SURE TO save the new workbook as Chapter02.xlsx in the trusted Work folder that you created in Chapter 1. (If you didn’t create a trusted folder, you will have to enable the macros each time you close and re-open the workbook. For details about macro-enabled workbooks and creating a trusted folder, see “Save the Macro Workbook” in Chapter 1, “Make a Macro Do Simple Tasks.”)

OPEN the Chapter02.xlsx workbook.