Page 186 - Excel Data Analysis

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

EXCEL DATA ANALYSIS

UPDATE A RECORDED MACRO

s your data analysis needs change, you may find that containing the steps that you want to add to the first one
a macro needs updating because you want it to and then using the Copy and Paste buttons in the Visual
A perform additional or different steps. You can update Basic Editor to add the new steps to the old macro. For
a macro at any time by adding or removing VBA code. Of example, if you create a macro to sum up a column of
course, after you record a macro, you can record over the values but forget to change the formatting of the column to
top of it to replace it, but you cannot modify it directly Currency, you can record a second macro in Excel that
within Excel. The only way to actually modify the macro formats the column. After you do this, you open the Visual
code is to change the corresponding subroutine in the Basic Editor and copy the formatting code of the second
Visual Basic Editor. If you do not know how to read and macro and paste it into the subroutine for the first macro.
write the VBA code required for the step that you want to Keep in mind when you copy the code that you want to
add to the macro, this can become quite an undertaking. copy only the portion of the subroutine between the Sub
and the End Sub statements.
Typically, modifying a macro, even one that you create with
the Macro Recorder, requires manually specifying the new After you copy the code from the new macro into the old
VBA code that you want to add. A quick-and-dirty method macro, you should delete the new macro from the Macro
for updating a macro involves recording another macro dialog box.

UPDATE A RECORDED MACRO

Format_Currency
Format_Currency

⁄ In the Macro dialog box, ¤ Click Edit. ‹ Click the start of the ■ Excel highlights the code
click the macro that contains source code that you want to that you selected.
the source code that you ■ The Visual Basic Editor copy.
want to add to the original displays the code for the ˇ Click the Copy button.
macro. selected macro. › Press the Shift key and
click the end of the source
Note: To open the Macro dialog box, code to copy.
see the section "Run a Macro."
172