Removing All Macros
Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Removing All Macros.
Gerald asked if there was a way to get rid of all the macros in an Excel workbook, without the need to individually delete them. There are two ways you can accomplish this task. The first approach is used if you don't want to mess with the macros at all. Just follow these steps:
- Unhide any worksheets that may be hidden.
- Select all the worksheets in the workbook. (Click on the first worksheet tab, then hold down Shift as you click on the last worksheet tab.)
- Right click on one of the worksheet tabs. Excel displays a Context menu.
- Choose Move or Copy from the Context menu. Excel displays the Move or Copy dialog box. (See Figure 1.)
Figure 1. The Move or Copy dialog box.
- Using the To Book drop-down list, choose (new book).
- Make sure the Create Copy check box is not selected.
- Click on OK.
- Rehide any worksheets you unhid in step 1.
Your worksheets have now been moved to a new workbook—one that does not have any macros attached to it.
The second approach is to simply work with the existing workbook, and is a viable choice if you feel comfortable with macros in the first place. Follow these steps:
- Press Alt+F11 to display the VBA Editor.
- In the Project Explorer (upper-left corner of the Editor), right-click on a module that you want to delete. (Remember that macros are stored in modules, and that you should only right-click on a module that is associated with the workbook that you want to cleanse.) Excel displays a Context menu.
- Choose the Remove option from the Context menu. The actual wording of the option will include the name of the module you want to remove, such as Remove Module1.
- When asked if you want to export the module before removing it, click on No.
- Repeat steps 2 through 4 for any other modules you want to remove.
- Close the VBA Editor.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2713) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Removing All Macros.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Leave your own comment:
Comments for this tip:
email@example.com 29 Oct 2016, 09:53
Petros is correct, but he is providing an answer for newer versions of Excel. Remember that *this tip* (above) is for older versions of Excel (2003 and before) where you CANNOT save in a "macro-free format." For the older versions, you must follow the steps listed above.
If you are using a newer version of Excel (2007 and later), then follow the link at both the beginning and end of the tip, above, to find the proper instructions you need.
Petros 29 Oct 2016, 09:48
The easiest way to remove all VBA code from a macro-enabled Office file is to save the file in a macro-free format. For example, save a .xlsb workbook as .xlsx
However you should not open any workbook that you suspect that it has malicious macros or code with bugs that may damage your data or PC. If you disable macros, then you are left with no choice other than to delete macros manually or open and save each file in a macro-free format.
However, the Macro Mover add-in allows you to mitigate security risks and work more productively:
Macros are removed from closed files only! There is no need to disable all macros from the Trust center. Your files will not be opened in Excel, so VBA code cannot be executed.
Code is deleted from closed files, so you don't have to wait while opening/saving large files.
VBA code can be removed from any macro-enabled Office file (including add-ins), even from locked VBA projects.
Macros can be removed without changing the extension of the file. You can still take advantage of the high compression ratio offered by the Excel binary file format (.xlsb), while erasing macros fast.
Macros can be removed from multiple Office files (Excel, PowerPoint, Word) in a single operation.