Any recommended method to compare VBA modules?

NigelTufnel

Board Regular
Joined
Apr 3, 2008
Messages
53
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
Upvote 0
Thanks for the tip, Gary...I forgot that Microsoft Word had that capability.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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).

ξ
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top