Excel VBA Code Cleaner Version 5.0

Since we’re on the topic of Excel utilities lately, I figured I’d jump in with my announcement of a new major version of the Excel VBA Code Cleaner. Here’s a brief summary of what’s new:

  • The code cleaner is now implemented as a COM add-in. This has significantly improved its speed and stability.
  • I addressed the most commonly requested improvement by adding two new features: code module export-only and code module import-only. While not technically related to code cleaning, I’ve received numerous requests from users who want the code cleaner to assist them with Excel source code control.
  • I addressed my second most common feature request by exposing virtually every function used within the code cleaner to COM automation. You can now use the code cleaner DLL to roll your own code cleaning and code management solutions.
  • The code cleaner desperately needed a high-quality help file, and I finally wrote one for this version. The help file includes complete documentation of the COM interface, including examples.

Like previous versions, the code cleaner is free and source code is included. With this version, however, you’ll need a copy of VB6 to take advantage of the source code because that’s what I used to write it. The source code will be located in the installation folder in a file cryptically named VB6_SourceCode.zip.

You can download the new code cleaner from:

http://www.appspro.com/Utilities/CodeCleaner.htm

Please uninstall any previous versions of the code cleaner prior to installing this version. Comments and bug reports welcome.

Posted in Uncategorized

38 thoughts on “Excel VBA Code Cleaner Version 5.0

  1. Running the risk of showing my ignorance: why would one wish to ‘remove code comments’; ‘remove blanks lines and whitespace’ after working so hard to make the code readable and understandable? Most of the lines of code I write is 25% – 30% comments.

    Maybe I just don’t write clear code… ;-)

  2. Very nice, Rob. The automation piece is huge, I may build it into my semi-automatic pre-deployment routine.

    Suggestion: Use the listbox_doubleclick event to run the code cleaner on the selected project.

  3. doco: The purpose of the stripper is mostly to obfuscate code (make it harder to read). People who don’t want to share the code of the programs they release often like to do this. It will also reduce the size of an add-in dramatically, although that has become less important over time as computers have become more powerful.

    Jon: Good suggestion. I’ll stream this into the next build.

  4. Rob: thanks for the clarification – makes perfect sense now. I have created a lot of widgets over the last 10 years or so but never anything that would be distributed beyond my own realm. so, purposefully muddying the waters, so to speak, hadn’t occurred to me. I do know that even the things I make, if I don’t make copious notes, I soon forget; AKA life beyond fifty ;-)

  5. Rob,

    Thanks. As I stated in a previous post, “Code Cleaner” is sometimes my most important utility.
    I really think you should have added a “donation option” but “to each, his own”.

    The Export/Import feature might come in real handy for some individual workbooks that I update fairly regularly (updating one and then overwriting the code in 6 others).
    Not that I have any right to make a suggestion for something that was given freely but is there any way to add an option to delete the original code once it’s exported …or… is there any other way to do a blanket removal of all code from a workbook?

    I’ve tried, in vain, a few times to get Chip’s code to move the VBA from one workbook to another to work correctly. Regardless, his code doesn’t include the sheet code which you so nicely included in Code Cleaner.

    Reason I’m asking (and I’m sure others have similar scenarios) is that I have half a dozen timesheet workbooks in my office. Sometimes it’s necessary to update or enhance the coding in all of them. What I do now is update one, export all of it, painstakingly delete the code in the other 5 and then import the new code.

  6. Hi John,

    Shame I hadn’t thought about adding a delete all modules function to the public interface. Seems like a reasonable feature, but I don’t want to do it right now because it would force me to break binary compatibility with the DLL I just released. I’ll add it to the list for next time.

    As for the specific example you mention, I think you can already accomplish this. If you have a master timesheet workbook where you make code modifications that just need to be propagated to other timesheet workbooks this is no problem, as long as corresponding modules have the same name in all workbooks.

    Export the modules from the master timesheet workbook and then import them into each of the other timesheet workbooks. If a code module being imported has the same name as an existing code module, the existing code module will be replaced. This includes code behind document object. This entire process can easily be automated using the new COM interface.

  7. Rob,

    Tried your suggestion and it worked like a charm.

    When I did a manual import on a module that was already there, it created a new one with a “1? concatenated on the end of it.
    Doing the import with Code Cleaner just overwrote any modules or forms that were already there (exactly as you said it would).

    Thanks again.

    P.S. I searched far and wide for that “donation screen” but I can’t find it anywhere??

    Per my (and others) posts in that other topic, you “guys” should really, at the very least, provide a method for users of your utilities to reward you for your efforts (even if it’s only on the download page of your website).

  8. Rob:

    I agree with John about the donation aspect. A simple “PayPal Donations Welcome” button would be appropriate – I see this all the time with the (showing my age now…) younger crowd when they offer up some method or product that helps with life – most of the younger people I know add a donation button simply to pay for the bandwidth charges – fair enough. John W does it for bandwidth costs for the free upgrade in the PUP world, and I for one would be happy to donate (ala the shareware model) to keep excellent Excel developers going.

    Please at least entertain the thought. Just my $0.02

  9. Hi Guys,

    I appreciate the sentiments, and I have no problem at all with other people asking for donations or charging for their stuff. Me, I’m just giving something back to the world that lets me play with computers all day in my home office and actually get paid for it. Life’s been good to me already. :-)

  10. Hi John,

    “Per my (and others) posts in that other topic, you “guys” should really, at the very least, provide a method for users of your utilities to reward you for your efforts (even if it’s only on the download page of your website).”

    I do have a donate button on a couple of pages. My experience is, that it is harldy ever used (I think it has been used twice on two years’ time…).

    My new version of Autosafe will show a polite messagebox, asking for a donation after every third time it recovers a file for you. It keeps doing that until you agree to, but recoveries are rare, so this point will not be reached quickly.

  11. code cleaner is an excellent tool. I used earlier versions and now I have updated to newversion
    5.0. But I think I am doing something wrong.
    When I choose a projet and go the “options” and UNCHECK “remove code comments” and check only “remove only bblank lines and whitespeaces” still I found both the comments and blank lines are removed.
    where I am doing the wrong. The comments are sometimes useful when I later revise the code.

    build no. 5.0.3

  12. “Per my (and others) posts in that other topic, you “guys” should really, at the very least, provide a method for users of your utilities to reward you for your efforts (even if it’s only on the download page of your website).”

    Like Rob, I’m just happy to be able to give back some of what I took in my early days. If anyone wants to send a gift or cheque of appreciation, my postal address is on my web site! Over the years, I’ve received bottles of scotch (that I don’t drink), cigars (that I don’t smoke), free software (that I haven’t installed), books (that I rarely read) and a genuine NYPD baseball cap (hung on my monitor!), but a simple email of thanks is reward enough.

    By far the best way of showing your appreciation, though, is to follow our example and give back to the community whatever you can. As soon as you understand the answer to a question (however simple), you’re in a position to help other people who might ask the same question. Rob and I have been MVPs for about 12 years now (Rob for one year more than me) and in that time we’ve seen many people grow from complete newbie to join us as MVPs – including people like Chip Pearson who I’m sure most people here would rank amongst the best of the best.

  13. Rob,
    A nasty bug: Your routine to determine Trust Access gives false positives and prevents addin from functioning. On my machine it occurs in both in xlXP and xl2003. Probably because I have XPDeveloper installed. By avoiding the VBProjects collection and checking on VBE itself problems were solved.

    Public Function ProjectProtectionEnabled(ByRef xlApp As Excel.Application) As Boolean
        Dim objVBE As Object
        If Val(xlApp.Version) >= 10 Then
            On Error Resume Next
                Set objVBE = xlApp.VBE
            On Error GoTo 0
            ProjectProtectionEnabled = (objVBE Is Nothing)
        End If
    End Function
  14. For everyone following along, keepITcool discovered that making a dummy Application.Run call in the OnConnection event like so:

    On Error Resume Next
        ”’ Force the VBE to initialize.
       gxlApp.Run “xhYe1lsGtd3soe2t4ns”
    On Error GoTo ErrorHandler

    forced the VBE to initialize and cured the false positive errors in the ProjectProtectionEnabled method. The latest build that includes this and several other minor fixes is posted on my web site. Thanks for the help!

  15. Rob,
    Good work! Just a couple of suggestions:
    Firstly, the requirement to set Trust Access To Visual Basic Project is annoying and a potential security hole. If you converted this to a VBE COM addin rather than an Excel one the Trust Access… setting would become irrelevant, wouldn’t it?
    Secondly (very minor gripe), if you’re using the SSTab control, set the Style to PropertyPage to stop it looking so ugly.

  16. Hi Rob,

    Thanks! The reason I made it an Excel add-in rather than a VBE add-in was so it wouldn’t load into any other Office apps besides Excel. I also wanted it to be able to run from an Excel UI menu, and it’s a bit tricky working backward from the VBE to the Excel Application object.

    Your other suggestion about the tab style was an excellent one and was also made by keepITcool. It’s been implemented in the latest version posted on my web site, along with the latest set of minor fixes, mostly to the help file.

  17. Hi Rob,

    A small remark: The textbox that should show the backup folder wraps the path, but I’m unable to click in the textbox to view it. Dunno what the best property would be to set, but maybe increasing the height so you have a couple more lines would be helpful? There seems enough room for this (for both textboxes) on that third tab.

  18. Hi Jan Karel,

    Good idea. I’ve resized them to accomodate three line wraps, or about 150 characters in folder name length, plus or minus depending on where the breaks occur.

  19. Hi Rob!

    I wrote an add-in based on your code-cleaner tool to be able to use general source code versioning (other than VSS, which I dislike because several constraints imposed). The versioning tool currently implemented is Subversion and it’s cool windows client TortoiseSVN. The idea was to save the modules as source code files and put these under version control.

    On the other side, you can then import back the source files into excel (at least class and standard modules).

    There’s an additional goodie, you can simply compare Excel VBA code between files using a diff util like WinMerge.

    A nice enhancement of this tool would be to generalize it to all VBA enabled applications using a COM add-in. I’ve seen that the codecleaner COM-Addin is still restricted to Excel, do you think it would be extremely hard to generalize it? At least to the Office applications?

    Many thanks for this great tool, it’s (together with the VBA indenter) one of my favourites.

    -regards,
    Roland

  20. Hi Roland,

    Because different people have spent a lot of time porting the Code Cleaner to Word in the past I tried to design it to make life easier for anyone who wants to port this version to other Office apps. I don’t need it enough outside of Excel to do it myself, but if you or anyone else wants to port it I’ll be more than happy to give credit and host it on my web site.

  21. Rob,

    I would like to second Roland’s comment on using versioning with subversion. This makes it easy. I now have several branches from the main development version. I was using clearcase for entire .xla file. This was almost no help in maintaining the source code between branches. With the new Code Cleaner I just export to the local checked out folder and then use TortoiseSVN to check it in to the remote svn server. I can pull a certain version to a local directory, import the directory to a fresh copy of Excel, add a few references I use then run the addin. Awesome!!!!

  22. Rob,

    Fnatstic utililty and is certainly on the check list of processes (along with smart indenter) to run perform before releasing any code.

    I am curious to know how you handled the escape key being pressed. I have been trying to port a vba routine to vb6 (more for learning than anything else) and I’m have great differculty in letting the user stop the process. It may be because I’ve chosen to port a routine hat finds excel files on a drive using the recursive method.

  23. @Rob Bovey
    Just wanted to say “THANKS FOR THE GIFT”. I’ve used a previous version for years and the “new” version works better than ever (including Excel 2010)!

    MORE OVER, many thanks for endless commitment to “giving back” So many people talk the talk but it is refreshing to see those who actually live it.

    The excel community seems blessed with an abundance of benevolent giants who continually give back to all that follow behind them (like your, Stephen Bullen, Ron de Bruin, Dick Kusleika, Jon Peltier, Chip Pearson, Jan Karel Pieterse, Andy Pope, and SO MANY OTHERS. SO, very publicly, THANK YOU ALL FOR GIVING BACK SO MUCH.

    @Everyone. Rob & Stephen have authored some GREAT books and AMAZON has a working PayPal button. It is certainly an easy way to give a little back in the other direction and, at the same time, gain so much more from your investment.

  24. Hi,
    i downloaded the code cleaner but i don’t see it on my VBE.
    I’m using excel 2010 64 bit – there is a problem with that version?

    My excel file is getting bigger and bigger and I’m pretty sure the code cleaner will to the work

    Thanks
    R

  25. I also use CodeCleaner & SmartIdenter many years, so thanks a lot for the Autors. Is there any way to combine yours efforts and make just 1 routine to summarise all valuable features of CC & SI ?

  26. Pingback: Anonymous
  27. Yes, I have the same problem. I can’t add it in the manage-COMS add in place. Says it’s not a valid Office add-in. I’m using excel 2010 and Windows 7, 64 bit. Is there a workaround for this because I’d love to be able to use it.

  28. Please tolerate my stupidity, but ….

    I’ve read the intro burb on the CodeCleaner, but if you run it “out of the box”, what does it ACTUALLY do. For example, does it determine that specific subs are never used and delete them ? Does it determine that various dimm’ed variables aren’t used and delete them ?

    Sorry to be so thick, but I’m missing that piece of info.

  29. Michael: Code Cleaner exports all the modules in your project and re-imports them. This round-tripping through text files cleans out some of the gunk that the get embedded in the code through frequent editing. If you need to find unused variables, consider MZ-Tools.

  30. Hi. Any plans for an x64 version? I use this tool a lot to clean other peoples modules before remediating and fixing them, but work recently moved to x64 Office and no longer works. :(
    Cheers

  31. @Scott: Same for me, switching to Win64 and Office64 …

    @Rob: Any chance for a 64bit version?


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.