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.
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. 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:
To use the VBA Code Cleaner after installing :
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.