What are Macros?
Macros can be defined as a tool in Excel used to record routine steps that may be replayed over and over.
This replay can be done automatically with a single click of a button.
Technically, creating macros means you are recording your mouse clicks and keystrokes of your steps which may be edited or adjusted to your preference.
Using Macros help save time and energy which would have been expended on repetitive routines.
However, irrespective of its identified advantages, there are instances where we may need to remove Macros from our Excel workbook.
This write up explains steps that can be undertaken should such scenarios come up
Removing Macros in an Excel Workbook
If your source data came with Macros already recorded or you have already recorded some Macros that you won’t be needing or probably not interested in them again in your workbook, you have the option of deleting them from your workbook.
Macros can be deleted in the entire workbook or in part of the module in which they are located.
Deleting some Macros that are not needed makes it easier to view useful ones in the code window usually located in Excel Visual Editor.
There are several methods available for deleting or removing Macros from your workbook.
In this tutorial, we will look at the following methods in deleting Macros from an Excel workbook.
The methods are as follows:
- Selecting and removing Macros from the Dialog Box in Excel
- Using the shortcut key to delete Macros from the Macro Dialog Box in Excel.
- Selecting and easing Macros from the modules in Excel.
- Removing all the macros by saving the file as an XLSX file in Excel.
We will look at the above-mentioned methods one after the other.
Selecting and Removing Macros from the Macro Dialog Box.
Macros can be removed or deleted from your workbook by selecting them from the Dialog box called Macro.
To carry out this procedure, take the following steps.
Step 1: Navigate to the Macros tool under the code section from the developer tab in the Excel toolbar
Step 2: Click on Macros.
Step 3: A dialog box called Macros will open, as shown below. Select the Macros you want to delete and then select delete from the right pane of the dialog box displayed.
Depending on the number of Macros in the Excel workbook, you can delete as many as you want. You can as well keep the Macros you think may be useful in the future.
Using the Shortcut Key to Delete Macros from the Macros Diagram Box in Excel
Shortcuts in Excel make performing a task easier and faster. This shortcut key method (ALT+F8 ) removes Macros from the Macros dialog box.
To use this method, you should follow the following steps.
Step 1: Press the ALT+F8 key on your keyboard to open the Macros dialog box.
When this is done, a Macros dialog box will appear.
Step 2: As the Macros Dialog Box appears, select the Macros you want to delete from the available Macros options. You will notice that the selected Macro is highlighted.
Step 3: Click on delete from the right pane of the dialog box.
You will discover that your Macros of choice are deleted from your workbook.
This method is very useful when you want to remove a part of Macros you don’t need.
This step also requires you to remain in your worksheet while pressing the shortcut key otherwise it will not work.
Selecting and Erasing Macros from Excel-Modules
Macros can be created by using several methods. If Macros were created by inserting modules in your workbook, this method of deleting Macros will come in handy.
Excel modules are of different types and perform different functions.
To select and erase Macros from Excel modules, the following steps should be taken.
Step 1: Navigate to the Visual Basic Tool (VBA) under the code section from the developer tab in your Excel toolbar.
Step 2: Click on the Visual Basic tool icon.
Alternatively, Press the ALT+F11 Key on your keyboard. The VBA window will open immediately.
Step 3: As the VBA window opens, select the module that contains the Macros you want to delete from the right scroll bar under the module section.
Step 4: Once the module has been selected, right-click on your mouse.
A dialog box will appear with options to either ‘Insert’, ‘Import file’ ‘Export file’ ‘Remove module’, and so on.
Step 5: Click on remove module from the options available.
Step 6: After selecting the Remove Module option, a warning box pop-ups asking if you want to export the module before removing it or not.
Step 7: Click on No. This means you don’t intend to save the module elsewhere before deleting it from that workbook.
You will discover that the module has been removed from the workbook.
It is very important to note that when you use this method, the selected module will be deleted permanently.
If you intend to keep the module, click on Yes instead, from the warning dialog box that appeared when you wanted to delete it.
Deleting all Macros by saving them as an XLSX File in Excel
There are instances when you may not be interested in any Macros tool that may have accompanied the data you want to work with or you decide to dispose of any form of Macros.
If you are experienced in using Macro, you will discover that the only Excel files that can hold macros are those of the XLSM and XLSB formats this can also be the older XLS format.
In that instance, saving your Excel workbook in XLSX or any other format will result in your Macros being deleted or lost.
One of the easy ways to remove all the Macros from an XLSM or XLSB format workbook is to save it as XLSX and then delete the original file if need be.
You can also decide to keep it as a backup if needed.
Most people generally prefer this method because you can still hold on to the original document containing the Macros, peradventure you need them later, you can always retrieve it.
The following steps will come in handy to achieve your purpose.
Step 1: Navigate to the File tab on the top left-hand corner of your Excel toolbar.
Step 2: Click on Save As Option
Save As menu options will pop out indicating the possible places you may want to save your file.
Step 3: Click on Browse
Step 4: Locate where you want to save the Excel file. You can decide to keep the existing file if you want. In this case, we want to save it on the desktop.
Step 5: Type in the name you want to give to your file and make sure you through the file if it is being saved to the destination of your choice.
Step 6: click on the ‘Save as type’
Step 7: Click on the dropdown arrow in the field next to Save as. The Save as dialog box will be opened with several options of Excel saving formats.
Step 8: Select Excel Workbook and click on Save.
Step 9: A prompt will appear. The prompt informs you that the VB code will be lost if you proceed with saving this file in XLSX format.
Step 10: Click on Ok.
The workbook will now be saved with the name you saved it within the XLSX format.
You will discover that when you select the Macros button from the Developer tab, you will find an empty Macros list in the Macros dialog box.
What this means is that there are no more Macros in the workbook.
Saving the new workbook in an XLSX format gets rid of all the Macros that may have been present in the old file.
You can also decide to rename the new file as the original, this does not affect any of the files as the extensions of the new file you just saved will be different from the original one.
How About Hidden Macros?
There are some instances where the Macros may be hidden in the workbook.
In these instances, deleting Macros may not be possible because you cannot find them to delete.
To delete hidden Macros in your Personal Macro workbook, the following steps should be followed.
Step 1: Navigate to the view tab under the window group at the top of the Excel toolbar.
Step 2: Select ‘Unhide’
Step 3: The unhide dialog box will open. Select your personal Macro workbook from the list of hidden workbooks.
Step 4: Click on Ok. This automatically Unhide your Personal Macro workbook.
Step 5: You can now proceed with deleting any Macros of your choice using any of the outlined methods.
We have been able to demonstrate how you can remove Macros in an Excel file either totally or partially using different suitable methods.
The choice of what method to employ when faced with a task will depend on whether you want to permanently or partially delete the macros.
In addition, your preferred method may also impact your choice of method used.