5. Editing and Deleting Macros
Editing and deleting Macros can be tricky, particularly if you save a Macro to the Personal.xlsb file. In this lesson, I'll show you how to accomplish these tasks.
- To edit a Macro's keyboard shortcut or description, go to 'Options' in the Macro dialogue box
- To change the Macro code itself, you'll need to open the Visual Basic Editor
- Macros are found in the 'Module files' in the VBA project window
- Press 'Delete' in the Macros dialogue box to remove the selected Macro
- However, if the Macro is stored in Personal.xlsb, the process is more difficult
1 Open up the VBA editor and find the Module where the Macro exists
2 Then delete all of the code
ALT + L , P, M: Open Macro dialogue box
ALT + F11: Open Visual Basic Editor
ALT + Q: Close Visual Basic Editor
You'll often record a macro and then realize that you've made an error and you want to edit or delete this macro. If you've saved the macro to the workbook, this process is quite straightforward. In this example I have a macro called Highlighter saved in the workbook and this macro simply changes the background color of a cell to yellow by pressing control shift H. Say that I now want to edit this macro. Let's go to the Developer tab with alt L and then P M to open the macro's dialog box and here I can find my macro name.
If I'd like to change the keyboard shortcut or the description, I can go to the Options button. So, I'll press this and change the shortcut key to say control shift B.
If I'd like to change the code itself, we'll need to go to the Edit button.
And this opens the Visual Basic Editor and I can see all of the code written for this particular macro.
In the project window, I have a VBA project with the name of my Excel sheet included. Within the VBA project, I have Microsoft Excel Objects and Modules and in this file Module1 is where my macros are stored.
To delete the macro, I'll exit out of the Visual Basic Editor with alt Q, I'll open up the macros menu again with alt L P M and then I'll simply press the Delete button.
And this removes the macro.
If I open up the Visual Editor again with alt F11, and go to my Module1 as before, I'll see that the code is missing. So, deleting a macro simply removes the code from Module1. This is important to remember for when we delete macros stored in the PERSONAL.XLSB file.
Let's return to the Macros window by pressing alt Q and this time I'm going to create another macro off camera called CreateBorder which will be stored in the XLSB file.
Let's open up the Macros dialog box with alt L and then P M where we find our CreateBorder macro. If I try to delete this using this button in the dialog box, I'll get an error because it tells me I can't edit a macro on a hidden workbook.
So, instead we'll need to go to the VBA code, so I'll hit Exit and then alt F11 to open up the Visual Basic Editor.
We now need to go to the modules within the PERSONAL.XLSB file and I should find my macro in here Let's now select all of the code and delete and then I'll exit by pressing alt Q and if I now go back to my macros list I'll find that the macro has been deleted. Exactly what I want.
Deleting and editing macros can be a bit tricky at the start and you may need to re-watch this video a couple of times to understand how I've completed this task. However, if you can remember that macros are just blocks of code stored in the modules of the desired file, then you should find it quite easy to edit and delete these objects.