In the previous blogs, we learned about recording a macro and writing a macro using the VBA editor. Editing a Macro implies renaming a macro, assigning a shortcut key to run an existing macro, and copying the code of one macro into another.
So let us begin learning.
Example of Macro in Excel
Here we have two recorded macros in our workbook. You can download this practice workbook.
- Open the excel workbook. Go to the Developer tab->Code Group->Visual Basic button. Alternatively, press the alt F11 keys to open the VBA editor.
- From the Project Explorer window, open module 1 and module 2. Modules would open in the code window as two separate modules.
- Go to the Windows tab and click on Tile Horizontally.
- Module 1 contains the code of the button named button 1. It is button1_click(). Module 2 has a macro named macro1(). These two module windows would now be arranged horizontally in the code window.
Renaming a Macro in Excel
Now to rename any of these two macros, follow these steps:-
- The text string after Sub tells the name of the macro.
- Button1_click() is the default name given to the macro which is executed when the command button named button1 is clicked. If you change this name, then you would need to reassign the renamed macro to button1.
- We would now change the name of “Macro1” in Module2 to “Renamed_Macro”.
This would update the macro name. You can check using these steps.
- Close the VBA editor and go to the Developer tab. Click on the Macros button in the Code Group. Alternatively,, press Alt F8 keys.
- This opens the list of macros. Click on This Workbook in the Macros In the field. There are two macros. Button1_click and Renamed_Macro.
Assigning/Changing Shortcut Key of Existing Macro.
We have already opened the list of macros in This Workbook (Shortcut – Alt F8). Now to assign a shortcut key to a macro named “Renamed_Macro”, follow these steps.
- Select the “Renamed_Macro from the list of macros.
- Click on the Options button.
- This opens the Macro Options dialog box. Assign the desired shortcut key to the Renamed_Macro. We are taking the Ctrl Shift G keys.
- Click on the OK button and then when you press the ctrl shift G keys, the Renamed_Maco would run.
Copying Code from one Macro to Another
In the example, we have two macros in two different modules. Let us say we want to add the code of button1_click() to Macro1. Follow these steps to do this.
- (optional step) Open the VBA editor. Right-click on the module that you wish to open in the code window and click on View Code in the shortcut menu. Open both module1 and module2 and tile horizontally from the windows tab.
- Select the code between Sub Button1_Click() and End Sub in Module1. Press the Ctrl C keys and paste it in between Sub Renamed_Macro() and End Sub in Macro2.
- Close the VBA editor, and now when you press the Ctrl Shift G keys ( shortcut assigned to run Renamed_Macro, you would see two message boxes one after another.
It brings us to the end of learning about editing existing excel macros.
Thank you for reading.