0

There are SO and SU answers for merging the contents of spreadsheets, but has anyone merged VBA macros to only keep distinct macros and/or highlight differences.

Community
  • 1
Mark Hurd
  • 409
  • 1
  • 6
  • 15

1 Answers1

1

One roundabout way would be to save all macros for each spreadsheet to corresponding text files and compare the same using diff/merge utilities such as WinMerge.

If you have a large number of spreadsheets then this VBA code or this VBScript will help reduce manual labour.

There's also a purpose-built VBA diff utility called VbaDiff you can try that "will mark modules that have code changes in red and modules that are not in both projects in blue":

1

Community
  • 1
Karan
  • 55,947
  • 20
  • 119
  • 191
  • Yeah, I was afraid of that. That was the approach I saw, but I've accumulated 16 of them :-( I do expect only two or three have different details, but which ones is still a bit hard to do manually. – Mark Hurd May 31 '15 at 03:27
  • 1
    Unless there's some sort of diff/merge utility that understands the XLSB format (and I don't think MS has ever described the binary format properly) and can compare the text directly, you might not have a choice. One way I can think of to reduce effort would be to write yet another macro or a stand-alone VBScript that iterates through all the files and does the work of extracting the macro code for you to diff/merge. Something like [this](http://stackoverflow.com/a/49773) or [this](http://www.pretentiousname.com/excel_extractvba/index.html) for example. – Karan May 31 '15 at 03:40
  • FYI I've used the second link from your comment, with WinMerge, if you want to add that to your answer. I just had to "Trust access to the VBA project object model" in Trust Center > Macro Settings (and perhaps "Enable all macros", which I did just in case). Of course, I have remembered to restore these settings afterwards :-) – Mark Hurd May 31 '15 at 09:17