Keep It Clean

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 :

  • Open Excel
  • 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.

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel, Microsoft Office and tagged , , . Bookmark the permalink.

8 Responses to Keep It Clean

  1. Yes, it doesn’t work with my version of Windows 7 which is 64 bit. Says it’s not a valid Office add-in when I try to add it in manage COM add-ins. Is that because my Windows 7 is 64 bit?

    Like

  2. Colin Legg says:

    If you are not a local admin and you get this installed by someone else (who is an admin) then you will need to add the following registry entries in order for it to work for you:

    Run regedit.exe and browse to:
    HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\

    Right click on AddIns > New > Key
    Give it the name VBACodeCleaner.CodeCleanerConnect

    Right click on VBACodeCleaner.CodeCleanerConnect > New > DWORD
    Give it the name LoadBehavior and give it a value of 3

    Right click on VBACodeCleaner.CodeCleanerConnect > New > String Value
    Give it the name FriendlyName and give it a value of VBA Code Cleaner 5.0

    Right click on VBACodeCleaner.CodeCleanerConnect > New > String Value
    Give it the name Description and give it a value of VBA Code Cleaner 5.0

    Like

    • Charlie Hall says:

      I realize this is 17 months late but I just found this thread when searching for code cleaners for 64bit excel. I am wondering what the comment by Colin Legg is intended to fix – does it actually allow the 32bit code cleaner addin to work on 64bit excel? If so, that would be fantastic

      Like

      • Colin Legg says:

        Hi Charlie,

        Sadly, no, 32-bit binaries won’t run in 64-bit Excel.There’s no escaping from that.

        The comment I posted on 24 Dec 2014 is the procedure you have to go through it was installed using a different user account on the machine (for example, at work if you do not have local admin rights and the helpdesk installed it for you using their admin account).

        Colin

        Like

      • Charlie Hall says:

        Thanks for the clarification
        Btw – have you tried any 64-bit cleaning tools and if so, any observations you can share – I found two – one called RibbonCommander which was expensive and then wanted payment every year. The other was VBA CODE DECOMPILER AND COMPACTOR.
        –Charlie

        Like

      • Colin Legg says:

        Hi Charlie,

        No I haven’t tried any 64-bit cleaning tools. I have 64-bit Office but the vast majority of the development work I do for clients is in 32-bit Office with future 64-bit compatibility considered for.

        If you experiment with some products then I’d be very interested to hear how you get on with them.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s