Page 170 - Excel Data Analysis

10 537547 Ch09.qxd 3/4/03 12:13 PM Page 156

EXCEL DA T A ANAL YSIS
EXCEL DATA ANALYSIS

AN INTRODUCTION TO MACROS

macro is a set of instructions that you use to You can create a macro to perform a task as simple as
automate a task. Using macros enables you to repeat adding two numbers or as complex as creating a whole user
A tasks much more efficiently than tediously interface within Excel. To do so, you can employ one, or a
performing each step over and over. When performing combination, of two different methods: You can use the
repeated data analysis steps, creating macros allows you to Macro Recorder, or you can manually write a macro using
quickly perform the same steps in multiple worksheets. For the Visual Basic Editor (VBE). Although many macro users
example, if you want to take each column of numbers in a rarely venture past the Macro Recorder, you can harness the
worksheet, convert them to currency, and then add them power of Visual Basic for Applications, or VBA, to create
together, you can create a simple macro to perform this more complex macros. No matter how simple or complex
task. In other worksheets, you simply apply the macro, and the macros, they are all written in VBA.
Excel performs the corresponding steps. As an Excel user Macro is a common term in the spreadsheet world. All
who analyzes data, you probably have a series of tasks that spreadsheet packages enable you to create macros to
you perform frequently. By creating a macro to perform automate tasks, and Excel is no exception. Although you can
complex or repetitive tasks, you can save time by pressing a create macros with all Microsoft Office products, macros
simple keystroke each time you want to perform the tasks.
are often best suited for Microsoft Excel.

MACRO RECORDER

Using the Macro Recorder is a great method for The Macro Recorder does work well in conjunction with
creating a macro without writing VBA code directly. The the Visual Basic Editor: You can modify all macros that
Macro Recorder holds true to its name. When you turn you create with the Macro Recorder in the VBE. For
it on, like a tape recorder, it records all the events that example, if you want to create a macro that sums each
occur within Excel. Excel takes the recorded events and column of data in your worksheet, you record the macro
creates the VBA code necessary to re-create the events. that sums a column. You then edit the macro in the
The Macro Recorder works well for creating simple Visual Basic Editor, modifying it to run the macro until
macros, such as a macro that adds a column of numbers Excel processes all columns. Combining the use of the
or changes the layout of the page. But because the Macro Recorder and the Visual Basic Editor simplifies the
Macro Recorder creates a macro by recording your macro creation because Excel codes part of it for you.
actions, it cannot create a complex macro, such as one See the section "Record a Macro" for more information
that repeats a process until meeting a specific condition on recording a macro in Excel.
or one that displays a custom dialog box. More complex
Excel macros require the use of VBA.

156
156