Excel for Microsoft 365 for Mac Excel 2021 for Mac Excel 2019 for Mac Excel 2016 for Mac More…Less To automate a repetitive task so that you can do the task again with a single click, you can use the Developer tab in Excel for Mac to record and run a macro. You can also create a macro by using the Visual Basic Editor in Microsoft Visual Basic for Applications (VBA) to write your own macro programming code. If you no longer use a macro, you can delete it.

Record a macro

When you record a macro, the macro recorder records all the steps required to complete the actions that you want your macro to perform. These steps can include typing text or numbers, clicking cells or commands on the ribbon or on menus, formatting, selecting cells, rows, or columns, and dragging your mouse to select cells on your spreadsheet. The commands for recording, creating, and deleting macros are available on the Developer tab on the Ribbon.

  1. If the Developer tab is not available, do the following to display it:
    1. Select Excel > Preferences > Ribbon & Toolbar.
    2. Under Customize the Ribbon, select Main Tabs and then check Developer.
    3. Click Save and then close Excel Preferences.

    Excel for Mac Developer tab Preference

  2. On the Developer tab, click Record Macro.Note: To create a macro from VBE, click Visual Basic. In the code window of the module, type the macro code that you want to use.Excel for Mac Record Macro
  3. In the Macro name box, enter a name for the macro.The first character of the macro name must be a letter. Subsequent characters can be letters, numbers, or underscore characters. Spaces cannot be used in a macro name; an underscore character works well as a word separator. If you use a macro name that is also a cell reference, you may get an error message that the macro name is not valid.
  4. In the Store macro in list, select This Workbook.
  5. In the Shortcut key box, type any lowercase letter or uppercase letter that you want to use.Excel for Mac Record Macros FormNote: The shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open.
  6. In the Description box, type a description of the macro and click OK.
  7. Complete recording your macro.
  8. On the Developer tab, click Stop Recording.

Top of Page

Delete a macro

  1. On the Developer tab, click Macros.
  2. In the list, click the macro that you want to delete, and click the delete button.Select a macro and click the minus sign to delete it

Top of Page

Need more help?

Using VBA Macros in Excel can be a huge time saver. You can automate a lot of repetitive tasks and create new functions and functionalities in Excel with simple VBA macro codes. But in some cases, you may want to remove all the macros from an Excel workbook (or delete specific macros only). This may be the case when you get a workbook from someone else and you want to make it macro-free, or when you’re sending a file with macros to someone and the receipt doesn’t need these in the workbook. In this tutorial, I will show you a couple of really simple ways to remove macros from a workbook in Microsoft Excel. So let’s get started!

  • Remove All Macros by Saving the File in XLSX format
  • Remove Specific Macros from the Macro dialog box
  • Remove the Module that has the Macro

Remove All Macros by Saving the File in XLSX format

If you want to get rid of all the macros at once, the easiest way to do this would be to save the existing workbook with the XLSX format. By design, you can not have any VBA macro code in the XLSX file format. In case you do, it would be removed automatically while saving the Excel file. With Excel, you can only have the macros in the .XLSM, .XLSB, and the older .XLS formats. When you save the workbook in any other format, the macros are immediately lost. Suppose you have a file called Example.xlsm (with macros), below are the steps to remove all the macros from this file:

  1. Click the File tab
  2. Click on ‘Save As’ option (it’s ‘Save a Copy’ in new Excel versions)
  3. Click on Browse. This will open the Save As dialog box.
  4. In the Save As dialogue box, enter the name of the file with which you want to save it. You can also keep the existing name if you want
  5. Click on the Save As type drop-down
  6. Select the Excel Workbook (*.xlsx) option
  7. Click on Save
  8. In the prompt that appears, click on Yes. It’s just informing you that the VB Code will be lost if you save this file in the .XLSX format.

That’s it! Your file is now macro-free. This method is great as it removes all the macros from the current Excel workbook in one go. However, if you want to remove some macros and delete some, this method will not work for you (see the one using the Macro dialog box for this). Another good thing about this method is that you still have a copy of the original file that has all the macros (in case you need it in the future).

Remove Specific Macros from the Macro dialog box

While the previous method would delete all the macros. this one allows you to choose the ones that you want to be removed. And in case you want to delete all the macros, you can do that as well. Suppose you have a file called Example.xlsm that has some macros. Below are the steps to delete a macro from this workbook:

  1. Click the Developer tab (in case you don’t see the Developer tab, see the note in yellow after the steps)
  2. Click on the Macros button. This will open the Macro dialogue box where you can see all the macros in the workbook
  3. In the ‘Macros in’ drop-down, make sure ‘This Workbook’ is selected.
  4. Select the macro name that you want to delete from the macro list
  5. Click on the Delete button. This will delete that selected macro

If you want to remove multiple (or all) macros, repeat steps 4 and 5. Note: In case you don’t see the developer tab, click here to read on how to get the developer tab to show up in the ribbon in Excel. Alternatively, you can also use the keyboard shortcut – ALT + 8 to open the Macro dialog box. Alternatively, you can also click on the Views tab, click on the Macros drop-down and then click on View Macros option. This will also open the Macros dialog box. While this method works great, it would only allow you to remove macros that are stored in a module in the Visual Basic Editor. In case you have event macros (in specific worksheets or ThisWorkbook) or macros in the personal macro workbook, those can not be removed with this method.

Remove the Module that has the Macro

Another way to remove macros is to go to the Visual Basic Editor and remove macros from there. This method gives you the most control as you can access all the macros (be it in the module or objects or personal macro workbook). Below are the steps to delete a macro from the Visual Basic Editor:

  1. Click on the Developer tab in the ribbon
  2. Click on Visual Basic option (or use the keyboard shortcut – ALT + F11)
  3. In the VB Editor, you will have all the workbook objects in the Project Explorer. If you don’t see the Project Explorer, click on the View option in the menu and then click on Project Explorer
  4. In the Project Explorer, double click on the object that has the macro code. This could be a module, a worksheet object, or ThisWorkbook.
  5. In the code window that opens, delete the macros you want to remove. If you want to remove all, just select everything and hit the delete key.

In case you have a module that has the code that you want to remove, you can right-click on the module object and then click on Remove module option. So these are three ways you can use to remove macros from a Microsoft Excel workbook. I hope you found this tutorial useful! Other Excel tutorials you may like:

  • How to Assign a Macro to a Button in Excel
  • How to Record a Macro in Excel
  • Useful Excel Macro Examples for VBA Beginners (Ready-to-use)
  • How to Run a Macro in Excel
  • How to Enable Macros in Excel?

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. Written by Allen Wyatt (last updated February 2, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003 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:

    1. Unhide any worksheets that may be hidden.
    2. 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.)
    3. Right click on one of the worksheet tabs. Excel displays a Context menu.
    4. 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.

  1. Using the To Book drop-down list, choose (new book).
  2. Make sure the Create Copy check box is not selected.
  3. Click on OK.
  4. 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:

  1. Press Alt+F11 to display the VBA Editor.
  2. 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.
  3. 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.
  4. When asked if you want to export the module before removing it, click on No.
  5. Repeat steps 2 through 4 for any other modules you want to remove.
  6. Close the VBA Editor.

Note: If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I’ve prepared a special page that includes helpful information. Click here to open that special page in a new browser tab. 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.

Author Bio

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. Learn more about Allen…

MORE FROM ALLEN

Adjusting Chart Size If you don’t have Excel installed on your system, Microsoft Graph is a handy way to create simple charts for your … Discover More Saving a Workbook with a Preview When you save your workbooks, Excel can also save a preview image (thumbnail) that can be displayed in the Open dialog … Discover More Using the System Information Tool Need to lift the hood and look into the inner workings of Windows? One place you can do this easily is through the System … Discover More 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!

More ExcelTips (menu)

Finding Workbooks Containing Macros Workbooks can contain macros, or not. It is entirely up to you whether they do or not, but at some future time you might … Discover More Automatically Opening Macro Workbooks when Using a Shortcut Key Click a button on a toolbar and Excel will go so far as to open a another workbook in order to run a macro associated … Discover More Working while a Macro is Running If you have a macro that takes a long time to process a workbook, you might want to continue working in Excel while the … Discover More

Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click «Subscribe.» View most recent newsletter.

Newest Tips

Links and Sharing
  1. Home
  2. Resources
  3. Excel
  4. Editing Macros

Editing and Deleting Macros in Microsoft Excel

Editing a Macro

If you need to make simple changes to a macro, such as inserting text or deleting a command, such as a specific format applied to a cell, you can edit the macro. You edit a macro in the Visual Basic Editor, shown in Figure 2-6. The elements of the Visual Basic Editor are described in the table below. The Project Explorer, Properties window, and Code window all appear when you open the Visual Basic Editor. Since you won’t need the Properties window while performing simple editing, you can close the Properties window, and then expand the Project Explorer to view more of its window. Each open workbook in Excel has a project associated with it in the Project Explorer. Navigating the Project Explorer is similar to navigating Windows Explorer, in that they both have hierarchical structures. The code for a macro is stored in a module, which is simply a holding place for the code, just as a worksheet is a holding place for data in cells. Double-clicking a module in Project Explorer displays the module’s code in the Code window. Editing Visual Basic code is similar to editing text in a word processing program. For more details of Macro and VBA Excel training in Los Angeles call us on 888.815.0604. Our classes are hands-on and instructor-led. Beginner, Intermediate and Advanced Excel classes are also available. Figure 2-6: The Visual Basic Editor Figure 2-6: The Visual Basic Editor

Element Description
Project Explorer Contains projects that store the Visual Basic code for each open workbook. Each project can contain folders for objects (such as the worksheets in the workbook), forms, references, and modules. You select a module to view its code, copy modules to other open workbooks, and delete modules.
Code window Displays the Visual Basic code for the selected module in a project.
Properties window Displays specific characteristics of an object, such as the name of the object, or the standard width of the columns.
Standard (macro) toolbar Displays the basic tools needed to use the Visual Basic Editor.
Object box Contains a drop-down list from which to select the desired object whose code you want to view in the Code window. If General appears in the Object box, all the code for the macros associated with the selected module appears in the Code window.
Procedure box Contains a drop-down list from which to select a macro to display the macro’s code in the Code window.

Steps to Edit a Macro

To display the Visual Basic Editor:
  1. In the Code group on the Developer tab, click the Visual Basic button.
To edit a macro:
  1. Display the Visual Basic Editor.
  2. From the Tools menu, choose Macros.
  3. In the Macros dialog box, from the Macros In drop-down list, select the project containing the macro you want to edit.
  4. In the Macro Name list box, select the desired macro.
  5. Choose Edit.
  6. In the Code window, make the desired edits.
  7. Close the Macros dialog box.
To close the Visual Basic Editor:
  1. From the File menu, choose Close and Return to Microsoft Excel.

Also see how to record a Macro.

Deleting a Macro

If you no longer need a macro, you can delete it. Deleting unwanted macros makes it easier to view macros in the Code window in the Visual Basic Editor as well as view macros in the Macro dialog box. You can delete a macro in an open workbook using the Macro dialog box or the Visual Basic Editor. If you want to delete a macro in the Personal Macro Workbook using the Macro dialog box, you must first unhide the Personal Macro Workbook. A benefit of using the Visual Basic Editor is that you can delete any macro in any open workbook or the Personal Macro Workbook, without unhiding it.

Steps to Delete a Macro

Macro dialog box method:
  1. Locate the Code group in the Developer tab on the Ribbon
  2. In the Code group on the Developer tab, click the Macros button.
  3. In the Macro dialog box, in the Macro Name list box, select the macro you want to delete.
  4. Choose Delete.
  5. In the message box that appears, choose Yes.
Visual Basic Editor method:
  1. Locate the Code group in the Developer tab on the Ribbon.
  2. In the Code group on the Developer tab, click the Visual Basic button.
  3. From the Tools menu, choose Macros.
  4. In the Macros dialog box, from the Macros In drop-down list, select the project containing the macro you want to delete.
  5. In the Macro Name list box, select the desired macro.
  6. Choose Delete.
  7. From the File menu, choose Close and Return to Microsoft Excel.


Leave a comment

Your email address will not be published. Required fields are marked *