Update A Recorded Macro

You can update macro code at any time by adding or removing VBA code. Of course, after you record a macro, you can record over the top of it to replace it, but you cannot modify it directly within Excel. The only method you can use to actually modify the macro code is to change the corresponding subroutine within the Visual Basic Editor. If you do not know how to read and write VBA code required for the step you want to add to the macro, this can become quite an undertaking.

Typically, modifying a macro, even one you create with the Macro Recorder, requires manually specifying the new VBA code you want to add to the macro. A quick and dirty method for updating a macro involves recording another macro containing the steps you want to add to the first one, and then using Copy and Paste buttons within the Visual Basic Editor to add the new steps to the old macro.

For example, if you create a macro to sum a column of values but forget to change the formatting of the column to Currency, you can record a second macro in Excel that formats the column and then add that source to the first macro. After you do this, you open the Visual Basic Editor and copy the formatting code of the second macro and paste it into the subroutine for the first macro. Keep in mind, however, that when you copy the code, you only want to copy the portion of the subroutine between the Sub and the End Sub statements.

When you copy the code from the new macro into the old macro, you should delete the new macro. You can find out more about deleting macros in Chapter 1.



—D On the Macro dialog box, click to the macro that contains the source you want to add to the original macro.

■ To open the Macro dialog box click Tools O Macro O Macros.

L-H Click Edit.

■ The Visual Basic Editor displays the code for the selected macro.

-0 Press the Shift key and click the start of the source you want to copy.

□ Continue pressing the Shift key and click the end of the source to copy.

■ Excel highlights the code between the Sub and End Sub statements.

0 0

Post a comment