1

I am writing some VBA macros in Excel 2010. Source code is managed with TortoiseHG. However I cannot find a smooth way of comparing 2 different commits, because the TortoiseHG only shows the complete excelfile and not the different VBA files I'm writing my code in. If anyone have done this in a nice and smooth way I'd be very happy to get some ideas. Also using an external tool like BeyondCompare is possible.

Thank you!

Community
  • 1
  • 1
tallner
  • 13
  • 1
  • 4

3 Answers3

1

For the VBA Code:

For code comparison purposes I'd suggest exporting the VBA module(s) and checking in the .bas (regular module) and .cls (class module) files and checking those in next to the .xlsm. Those exported VBA modules actually give you plain text versions of the code. Code in Worksheet / Workbook modules can also be exported as .cls files. This will allow TortoiseHG to compare the actual code instead of the Excel file itself.

For the Excel file comparison on itself:

For comparing the actual XLSM, you can consider saving the file, changing the extension to .zip and extracting the zips contents. An Excel file for version 2007 and up is actually an archive containing XMLs that define the whole workbook and a .bin file for the VBA Project. You might want to pull said xmls through XMLLint or a similar tool, since they're not pretty printed by default.

Side note:

You're asking for a nice and smooth way - I'd say there is none within Excel itself. You can use VBA to do these Module Exports on Workbook_BeforeClose() event, but you'll run into some security issues - By default you're not allowed to access the VBProject from within. Of course there are some 3rd party tools for comparing VBA code without having to extract the modules, however "what to use" will be severely opinion based - BeyondCompare is indeed one option.

Hope this helps.

Rik Sportel
  • 2,661
  • 1
  • 14
  • 24
0

Beyond Compare has an add-on file format to compare VBA code in Excel files. Download the Microsoft Excel Workbooks VBA file format from the Additional File Formats for BC4 page.

Chris Kennedy
  • 2,639
  • 12
  • 11
0

You can export each module, and compare using Windows native fc command.

fc /N Module1 Module2 > Result.txt

Help:

fc /?
Compares two files or sets of files and displays the differences between
them


FC [/A] [/C] [/L] [/LBn] [/N] [/OFF[LINE]] [/T] [/U] [/W] [/nnnn]
   [drive1:][path1]filename1 [drive2:][path2]filename2
FC /B [drive1:][path1]filename1 [drive2:][path2]filename2

  /A         Displays only first and last lines for each set of differences.
  /B         Performs a binary comparison.
  /C         Disregards the case of letters.
  /L         Compares files as ASCII text.
  /LBn       Sets the maximum consecutive mismatches to the specified
             number of lines.
  /N         Displays the line numbers on an ASCII comparison.
  /OFF[LINE] Do not skip files with offline attribute set.
  /T         Does not expand tabs to spaces.
  /U         Compare files as UNICODE text files.
  /W         Compresses white space (tabs and spaces) for comparison.
  /nnnn      Specifies the number of consecutive lines that must match
             after a mismatch.
  [drive1:][path1]filename1
             Specifies the first file or set of files to compare.
  [drive2:][path2]filename2
             Specifies the second file or set of files to compare.

https://learn.microsoft.com/en-us/windows-server/administration/windows-commands/fc

johny why
  • 2,047
  • 7
  • 27
  • 52