Remove VBA from Excel

Sometimes when you receive an Excel workbook that contains VBA code you may want to remove the code to make the workbook macro-free. In other instances, you may be required to make your Excel workbook macro-free before sending it out to colleagues. This tutorial shows how we can remove some or all macros from Excel workbooks.

5 Methods to Remove VBA Code from Excel Workbook

In this tutorial, we will explore 5 methods that we can use to remove macros or VBA code from an Excel workbook.

Method 1: Save the file in .xlsx format to remove all macros

The easiest method to expunge all macros from a macro-enabled Excel workbook is to save it in .xlsx format.

Excel does not allow VBA code in an Excel file that is in .xlsx format. VBA code is only allowed in files that are in .xlsm, .xlsb, and the older .xls formats.

If we save a file that has VBA code in a .xlsx format all the macros are removed.

We use a file named Excel File with VBA.xlsm to show how this method works.

We use the following steps:

1. Click File.

2. Click Save As on the sidebar (It is Save a Copy in newer versions of Excel).

3. Click Browse on the right to open the Save As dialog box.

4. In the Save As dialog box type the new name to which you want to save the file in the File name drop-down list. You can retain the old name if you so wish.

5. Click the down arrow in the Save as type drop-down list.

6. Click Excel Workbook (*.xlsx) in the list that pops up.

7. Click the Save button.

8. Click the Yes button on the message box that appears to save the workbook as a macro-free workbook.

The file is saved as a macro-free workbook.

Note: One advantage of this method is that we can retain a copy of the original file that contains macros and can revert to it should we need to in the future.

Method 2: Remove specific macros via the macro dialog box

This method allows you to select from the macro dialog box the macros you want to be removed

We use a file named Excel File with VBA.xlsm to demonstrate how this method works.

We use the following steps:

1. Click Developer >> Code >> Macros to open the Macro dialog box.

We can also open the Macro dialog box by pressing Alt + F8 from the active worksheet or clicking View >> Macros >> Macros >> View Macros:

2. In the Macro dialog box that appears, select This Workbook in the Macros in the drop-down list:

We can see a list of all the macros in the workbook:

3. Select the macro you want to delete and click the Delete button:

4. Repeat step 3 if you want to delete more macros.

Note: This method only removes macros that are stored in standard modules in the Visual Basic Editor. It cannot remove the VBA code that is stored in the Personal Macro Workbook or worksheets and Thisworkbook object modules.

Method 3: Remove specific macros via the Visual Basic Editor (VBE)

In this method, we open the VBE and remove the modules that contain macros one by one. We use the workbook Excel File with VBA.xlsm to show how this method works.

We use the steps below:

1. Press Alt + F11 to open the VBE. Alternatively, click Developer >> Code >> Visual Basic:

Our Excel Workbook project is listed in the Project Explorer window as displayed below:

2. Right-click the module you want to remove and click Remove (name of the module) on the shortcut menu.

3. Click the No button on the message box that appears to remove the module without exporting it. You can click the Yes button to save the module to a .bas file before removing it.

4. Repeat step 3 to remove other modules that you want to remove.
5. Close the Visual Basic Editor.

Method 4: Delete procedures from modules in VBE

In this method, we open modules in the VBE and delete the procedures in them. We can access all modules in our project using this method.

We use the following steps:

1. Press Alt + F11 to open the Visual Basic Editor. Alternatively, click Developer >> Code >> Visual Basic.

Our Excel File with the VBA.xlsm project is listed in the Project Explorer window:

If you do not see the Project Explorer window click View >> Project Explorer to open it:

2. In the Project Explorer window double-click the object that has the macros you want to delete. This object can be a Worksheet, ThisWorkbook, or Module.
3. In the code window that opens select the procedure or procedures that you want to delete and press the Delete key. In the example below, we have selected the code in Module 1 for deletion.

This method is especially useful in removing VBA code from the worksheets and ThisWorkbook modules.

4. Close the Visual Basic Editor.

Method 5: Export worksheets to a new workbook

In this method, we export worksheets from the workbook that has macros to a workbook that has no macros.

We use the workbook Excel File with VBA.xlsm to show how this method works.

We use the steps below:

1. Create a new workbook.
2. Open the workbook the Excel File with VBA.xlsm.
3. Select all the worksheets in the workbook: click on the first worksheet tab, then hold down the Shift key as you click the last worksheet tab. A green line appears at the bottom of the selected worksheet tabs.

4. Right-click one of the worksheet tabs and click Move or Copy on the shortcut menu:

5. In the Move or Copy dialog box select the new workbook you created in the To book drop-down list.

6. Select Sheet1 in the Before sheet list box.

7. Ensure the Create a copy check box is not checked and click the OK button.

The worksheets are moved to a new worksheet that is macro-free.

Conclusion

Sometimes when you receive an Excel workbook that contains VBA code you may want to remove the code to make the workbook macro-free. In other instances, you may be required to make your Excel workbook macro-free before sending it out to colleagues.

In this tutorial, we looked at 5 methods that we can use to remove VBA from Excel.