VBA Code Cleaner is a free and indispensable utility created by Rob Bovey which can be downloaded at his website.
According to the information on Rob’s site, when you’re writing VBA code, a lot of unwanted junk accumulates behind the scenes and can cause problems. I’ve certainly experienced such problems from time to time – strange compile error messages stating that something should be a constant when it already is – and in these situations VBA Code Cleaner has saved my bacon. I always make a habit of cleaning my projects before deploying them.
VBA Code Cleaner isn’t a one trick pony though and some extras can be found on the Options tab. For example, there isn’t a tool in the VBA IDE to mass export all the code modules of a project in one go but, with VBA Code Cleaner, we can do just that!
If you’re a developer using source control for your VBA then this feature is absolutely great.
As a side note, I should mention that VBA Code Cleaner works fine in 32-bit Excel 2010. Once you’ve downloaded and installed it, you need to :
File > Options > Add-Ins > Manage COM Add-ins > GO
Tick VBA Code Cleaner 5.0 (you may need to browse for it) > OK.
Once that’s done, in the VBA IDE you should see a Tools > Clean Project option. If not, go back into the Add-Ins management area and check that the add-in isn’t disabled. You’ll need to speak with Rob if you’re running 64-bit Office though – I don’t know if and when he may be planning to release a 64-bit compatible version.