Spot the 10 differences

Hi everyone!

Yesterday I experienced the tedious task of figuring out what the differences are between the VBA projects of two different versions of a customer project I built a long time ago. Luckily there are tools to compare differences in text files, such as ExamDiff.
But the VB editor doesn’t give you an easy way to export all of your code into a single textfile. So I figured I’d roll my own. At first I simply let the routine run through all VBComponents of the VBA project.

To my surprise, the order of the components in the generated textfiles wasn’t the same for both workbooks, even with the exact same components in there.
So I decided I’d better first make a list of all VBComponents, sort that list and then export the content to a textfile.

Download ExportVBAProject here.


Jan Karel Pieterse

Posted in Uncategorized

12 thoughts on “Spot the 10 differences

  1. I frequently use WinMerge to compare exported VBA. It has an option to ignore line numbers which I use for error line reporting.
    To export, I simply use Code Cleaner, and I added similar export code to my XLTest addin.

  2. I have to say, your method of exporting all code into a single file for an external compare feels like a better approach. I’ve always used WinMerge and I’d want to continue using it.

    Pretty sure the VBA Code Compare tool is free. Other offers on that website require payment though.

  3. VBA Code Compare is freeware because it is still in Beta Testing.
    It is important to note that it will also work on XLSM and XLSB workbooks if you add .XLS to the filename.

  4. @Patrick

    Based on your comments I have given WinMerge a try – it looks very useful, however I could not get the ignore line numbers to work automatically – and the thought of painfully applying it for every module is making me wonder if you found an automatic way of applying that predefine. Thanks in advance

  5. Charlie,
    I don’t see how to do it automatically.
    As you say, you can apply a Prediffer filter when comparing any two files.

    You can select all the files in the Folder view, then right-click, choose Plugin settings, Prediffer settings and select Auto. But that only applies suggested plugins. For the Ignoreleadingnumbers.dll to be a suggested one, the file type has to be *.nc (see the Help) I don’t see a way of specifying that for *.bas files. Maybe the plugin can be recompiled or the dll hacked?

  6. Patrick,

    Thanks – bummer – hoping you would have figured it out for me. I did, though, use JK’s ExportVBA to create a single file, and saved it with the ‘.nc’ extension – then WinMerge worked perfectly. Much easier than enabling it module by module

  7. Any chance you or someone else could port this to Excel 2003 for those of us stuck about a decade behind everyone else, corporate IS-wise anyway.

    Jan, your automation code *always* rocks and has proved so helpful in expanding my understanding of topics too numerous to enumerate here.

    Many thanks,

    – Elise =)

  8. That code works in 2003; in fact he adds a command to the Tools menu in that case.
    Be sure to install the MS converter to be able to open xlsm files in 2003, then open and save as xls for future convenience.
    I’m going to use his idea, with a tweak to flag each header with a comment showing what module it came from.

  9. @Patrick: It’s the MS converter I was missing; I’d totally forgotten about it. Thanks for setting me straight. =) -e

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

Leave a Reply

Your email address will not be published.