Any recommended method to compare VBA modules?

[NigelTufnel]

Do any Excel pros out there have a preferred method of comparing the code in two VBA modules to determine the differences (e.g., code from two separate Excel files, such as two versions of the same workbook)?

I got in a position where I have two files containing nearly-identical VBA code, and I need to determine (and aggregate) the differences. I know that you can do it by hand, by opening both files and putting the module windows side-by-side in the VBE, but it's tedious.

Are there any VB tools or VBE extensions that will do it automatically?

I've seen a piece of old software called VBA Code Compare, but development on this product apparently ended in 2006 with Excel 2003. Has anyone used it?

Barring this tool, I can export the code to a text file and compare that way. Does anyone recommend a text-file comparison utility that would be suitable?

If I use the text file method, then I (apparently) have to export each module separately. Does anyone know of a utility that allows you to export the entire set of VBA code from a workbook into a single text file? That way I could do one large comparison rather than many smaller comparisons.

I realize I've got a lot of questions here. Thanks in advance for any help.

 

[GaryWMn]

In the past, I've just copied and pasted into two different Word files and done a text comparison that way. There are other utilities that might be more elaborate, but if you need something quick and dirty....

Gary

 

[NigelTufnel]

Thanks for the tip, Gary...I forgot that Microsoft Word had that capability.

 

[xenou]

Barring this tool, I can export the code to a text file and compare that way. Does anyone recommend a text-file comparison utility that would be suitable?

Notepad++ is great little text editor I use frequently. I believe it's free software (free as in beer and free as in speech). The standard install includes a diff utility - just open the two files and run compare.

 

[NigelTufnel]

Thanks for the tip, xenou. That's a good solution as well.

 

[NigelTufnel]

For reference of future posters/searchers, here is a method I used for comparing Visual Basic code that exist in separate Excel workbooks.
The process is roughly as follows: (Note: I have not tested these directions are perfect, but they are pretty close)

• Download and install WinMerge, a free utility for comparing text files

• If you don’t have it installed already, download and install the VBA Code Cleaner Excel add-in. (How do you know if it is already installed? Go to the Visual Basic Editor, Look under the Tools menu and check for a “Clean Project…” command)

• Create two new, empty directories, one for each Excel File

• Open your first project, go to the Visual Basic Editor and select a module. Select the “Clean Project…” command from the Tools menu and export the Visual Basic Code to one of your new directories. Note that you can export the VBA code without having to actually “clean” the project (specify this on the second tab of the Clean Project dialog box). The third tab of the dialog lets you choose where to export the VBA text files. Note that each module (and possibly sheet, if it contains code) will be exported into a separate file.

• Repeat the prior step for the other Excel project

• Open the WinMerge program and select “Open…” from the file menu. You can now select one of your new directories as “Right”, and the other as “Left”. Click OK.

• WinMerge will now tell you, for each filename that appears in both directories, whether the files are identical. If not, you can double-click on the filename and it will show you exactly where the files are different.

 

[xenou]

Sounds like a winner. If you have exported your modules to be compared as text files notepad++ will work the same way (I don't doubt that winmerge is fine, and it looks great, but I'm very fond of np++ so I just wanted to mention that it also works quite well when comparing two text files of your source code).

ξ

 

[Chris Spicer]

Hi Nigel, I built VbaDiff to solve this exact problem. It will give you an 'at a glance' report on what's changed, as well as allow you drill down and see the exact code changes using your favourite diff tool (e.g. WinMerge).

The problem with home-grown solutions is that they often require a lot of manual steps, especially if the VBA is protected. VbaDiff keeps things quick.

You can find out more at VBADiff

Regards,

Chris

 

[jgm27]

Old thread but ...
The fastest method I have found is to export my modules en masse for both projects to 2 separate folders and compare the folders with something like "Beyond Compare" (although there are many free utilities to do the same) . Code to export your modules can be found at https://www.rondebruin.nl/win/s9/win002.htm and this only takes a little tweaking for this purpose.

Best regards,
John