Add-ins.com home page
Questions? E-mail us or call us 302-584-1771, 8AM to 10PM U.S. East Coast Time 7 days a week
     

Quantity
Discounts

Money back
guarantee

How to fix Visual Basic macros that stops working
using the VBA Code Cleaner for Microsoft Excel

The more you edit and modify visual basic macros, the more likely the macros are to become unstable and inexplicable errors occur when you run the macros.  This is not very frequent, but does happen.  The best way to say it is that perfectly good code simply stops working.

The first thing to try is to close Excel and re-open.  This normally solves problems if you have been editing heavily for several hours, and do a lot of macro runs.  However, it does not always solve.  One solution that has been know to help is to export all of your userforms and modules, delete the ones you have, and re-import them.  Exporting means that you create a files (.BAS and .MDL file types) that are just the userform or module code without extra junk or baggage that the VB editor leaves in the file.

Although this sounds simple, it is a lot of work to do manually as you must export each userform and module and then delete them all before importing back.  And if you forget to export a module or user form and delete it, the only way to recover is from a backup copy of your file.  To automate this task and help eliminate errors, Rob Bovey developed a special add-in called the VBA Code Cleaner.   To install, you must run as Admin.  Download and right click on the file and select 'Run as Administrator".  Also, it only 32 bit compatible.

Product: VBA Code Cleaner     Customers who looked at this product also looked at:
For: Excel 97 to Excel 2013, 32 bit only  

Macros Made Easy - A downloadable book (Windows help file format) on how to write macros for Microsoft Excel

Macro Examples - Get over 1200 macro examples for Microsoft Excel in a downloadable book (Windows help file format). These examples are new and not a repeat of the ones in Macros Made Easy.

Price: Free
Company: Application Professionals
Location: U.S.
Email support: Yes
Phone support: No

Memory usage:  The VBA Code Cleaner is a COM add-in versus a VBA add-in.  It fully opens all the time. COM add-ins typically use a noticable amount of Excel memory.  We have found that having several COM add-ins open at one time makes Excel unstable (all of our add-ins are VBA add-ins).  We recommend opening COM add-ins on demand only by using the Add-In Information Lister, which provides easy access to the COM add-in control panel.  And un-installing COM add-ins as soon as they are not needed.

When you install the VBA Code Cleaner (link to download site below), the following dialog will appear each time you open Excel:

There are two cures to this:

  • Do what the above dialog says, or
  • Do the above, but immediately un-install the Code Cleaner when done and reset security to have the "Trust access to Visual Basic Project" unchecked.  You should never leave this option unchecked, as doing so represents a strong security threat on your PC.
  • For excel 200-2003, select Tools, Macro, Security.  the following dialog appears:

  • If you are using Excel 2007, select Office Button,  Excel Options, Trust Center, Trust Center Settings, Macros.  The following dialog appears:

To use the VBA Code Cleaner after installing :

  • Make a backup copy of your project.  This insures you can recover back to it in case use the VBA Code Cleaner causes a problem
  • Go to the first module in your project.
  • In the VBA editor, select Tools, Clean Project.  The following dialog appears:

  • Select your project.  Confirm the defaults in options and File Locations are acceptable. Click OK to clean your project. 

Then save your project, close Excel and then reopen Excel and your project. 

When done uncheck the box to trust VBA access to your code.  This will unfortunately display the warning dialog show at the top each time you open Excel. The only way to eliminate it is to un-install for the VBA Code Cleaner.  This is easiest done by using the Add-In Information Lister, which allows easy access to COM add-ins for removal or re-installation.

What to do if the above doesn't work

If you are still having errors in your code, you may try creating a new module, copying the code in the problem module, pasting it into Notepad. Recopying the code in Notepad, and then pasting into the new module.  We have used this technique with success when even the code cleaner doesn't work.

You should keep the number of rows in your visual basic modules to under 1000 lines, and we strongly prefer to keep ours under 600 lines. It seems that very large modules become "unstable" and perfectly good code stops working.

If all else fails, then accept that there may truly be an an error in the code.  Try re-writing it in a completely different fashion to see if that solves.

 

 


Copyright 2017 Add-ins.com LLC, all rights reserved.  Spreadsheet Assistant is a registered trademark of Add-ins.com LLC.