Removing Macros

Q: I have an Excel file and for some reason, every time I open it, it asks me if it’s OK to run macros … but there aren’t any! How do I stop this annoying message from appearing?

By Jeff Robson
This message means that somewhere in your file there must be some macros lurking.  Possibly, a macro was recorded at some point, or a macro was created then deleted … but it’s in there somewhere … you’ve just got to find out where!  Find it if you can!  Ha ha ha.

Unfortunately Excel doesn’t give you a lot of help, and some of the places that macros live are downright obscure!

Excel allows macros to lurk in a variety of places such as behind UserForms, in Modules, in Class Modules, behind worksheets, and behind the workbook object itself.

To remove all macro code from a file you can follow these steps:

  1. Backup your file.
  2. Press ALT-F11 to bring up the Visual Basic Editor (“VBE”).
  3. In the Project Explorer on the left-hand side of the screen, find the name of your workbook.
  4. Look for a Modules folder below this.  If it exists, open the folder and right-click on each Module.  Choose Remove ModuleName to delete each one and choose No when asked if you want to export first.
  5. Locate and open the Microsoft Excel Objects folder.
  6. Double-click each Sheet as well as This Workbook, to show the macros behind these.  Press Ctrl-A to select any code that may exist and press Delete.
  7. Look for a Class Modules folder and open this if it exists.  Remove any Class Modules that may exist.
  8. Look for a Forms folder and open this if it exists.  Remove any UserForms that may exist.
  9. Save the file.

Your file should now be macro-free and should open without the annoying message.  Yay!  Just make sure you haven’t accidentally broken anything in the process!