Excel Macros - Editing

You have learned how to write macro code in VBA editor in the previous chapter. You can edit the macro code, rename a macro and delete a macro.

If you master Excel VBA, writing code or modifying code for a macro is a trivial task. You can edit the macro code however you want. If you want to make only few simple changes in the macro code, you can even copy macro code from one place to another.

Copying a Macro Code

You have created two macros – MyFirstMacro and Button1_Click in the macro enabled workbook MyFirstMacro.xlsm. You have created the first macro by recording the steps and the second macro by writing code. You can copy code from the first macro into the second macro.

• Open the workbook MyFirstMacro.xlsm.

• Click the Developer tab on the Ribbon.

• Click Visual Basic. The Visual Basic editor opens.

• Open the code for Module1 (MyFirstMacro macro code) and Module2 (Button1_Click () macro code).

• Click the Window tab on the Ribbon.

• Select Tile Horizontally from the dropdown list.

You can view the code of the two macros in the tiled windows.

• Copy the MsgBox line in the Module2 code.

• Paste it above that line.

• Modify the string as −

  MsgBox “Hello World!”

• Copy the following code from Module1.

Paste it in the Module2 code in between the two MsgBox lines of code.

• Click the Save icon to save the code.

• Click the Button in the Excel sheet. A Message box appears with the message - Hello World! Click OK.

The table data appears (according to the code that you copied) and message box appears with message - Best Wishes to You!

You can modify the code in just a few steps. This is the easiest task for a beginner.

Renaming a Macro

Suppose you want to run the edited macro from any worksheet other than the one that has the command button. You can do it irrespective of button click by renaming the macro.

• Click the VIEW tab on the Ribbon.
• Click Macros.
• Select View Macros from the dropdown list.

The Macro dialog box appears.

• Click the macro name – Button1_Click.
• Click the Edit button.

The macro code appears in the VBA editor.

Change the name that appears in the Sub line from Button1_Click to RenamedMacro. Leave Sub and parenthesis as they are.

Open the Macro dialog box. The macro name appears as you renamed.

• Click RenamedMacro.
• Click the Run button. The macro runs. Now a button click is not necessary.

Deleting a Macro

You can delete a macro that you have recorded or coded.

• Open the Macros dialog box.
• Click the macro name.
• Click the Delete button.

The Delete confirmation message appears.

Click Yes if you are sure to delete the macro. Otherwise, click No.