PDA

View Full Version : Compare 2 VBA Projects (code)



theta
05-15-2012, 02:46 AM
I have 2 VBA projects that have subtle coding differences (but this is creating a real impact on the end result).

Visual Source Safe is in use but both versions were updated offline without using checkout and so I now need a macro to compare both .xls files and show the differences in the code on a line by line basis - so that I can stitch together the relevant changes from each into a single file.

Any help appreciated as it is going to be a mammoth task if completed manually...

Bob Phillips
05-15-2012, 02:59 AM
Export the modules as text files, and then use a text file comparison application to compare them.

Bob Phillips
05-15-2012, 03:00 AM
BTW, what is the point of having SourceSafe if you allow modifying offline?

theta
05-15-2012, 03:04 AM
I am not in control of this. I just inherited the issue.

What text comparison software would you suggest?

Bob Phillips
05-15-2012, 03:59 AM
I use TextPad. It is a text editor (a very good one), but it has a compare function as well. It is shareware, but you can download it free and do what you need.

theta
05-15-2012, 04:06 AM
Found a freebie - ~545kb and has a nice simple interface

http://www.mediafire.com/?csss3yal9caxlep

TestDiff

Thanks again - just a shame there is no VBA module / userform for this so I could run it as an addin in Excel (and save back to modules without the text export step).

When I become a VBA genuis I'll put one together...

Aussiebear
05-15-2012, 04:12 AM
Have a look at WinMerge, a free utility for comparing vba code. Or failing that its possible to copy the code into Word and run text compare from there

Jan Karel Pieterse
05-15-2012, 05:41 AM
I am not in control of this. I just inherited the issue.

What text comparison software would you suggest?

Suggestion:
www.jkp-ads.com/download.asp#exportvbaproject (http://www.jkp-ads.com/download.asp#exportvbaproject)

macropod
05-15-2012, 05:49 AM
You could also save both projects as separate Word files, then use Word's Compare/Combine tools for the comparison.

Jan Karel Pieterse
05-15-2012, 07:02 AM
macropod: you'd still have to "save" each code module manually, or do you know a fast way to export everything in one go?

theta
05-15-2012, 07:21 AM
macropod: you'd still have to "save" each code module manually, or do you know a fast way to export everything in one go?

If you look at the functionality of the file I uploaded - it would be amazing to have that functionality as an addin + userform. Then you could open up "Original.xls" and "Adjusted.xls" and compare the two projects in that way.

I would happily work on a project to make this a reality...

macropod
05-15-2012, 02:29 PM
macropod: you'd still have to "save" each code module manually, or do you know a fast way to export everything in one go?Well, one could export each module using vba, but for a one-off project that hardly seems worth the effort.

Bob Phillips
05-15-2012, 02:41 PM
Who says it is a one-off? If they allow their source control to be ignored, they probably do this a lot :whistle:

macropod
05-15-2012, 03:05 PM
Maybe, or maybe they'll learn to use SourceSafe the way it's meant to be used. Otherwise they may as well ditch it.

Paul_Hossler
05-15-2012, 03:12 PM
macropod: you'd still have to "save" each code module manually, or do you know a fast way to export everything in one go?


http://www.appspro.com/Utilities/CodeCleaner.htm

Rob Bovey's Code Cleaner has the option to export all modules (UF, Cls, Std) with one click

Also does other amazing things

Paul

Jan Karel Pieterse
05-15-2012, 10:09 PM
Paul, macropod: the tool I referred to exports the entire VBA project into one single textfile, making comparing a breeze.

macropod
05-15-2012, 10:19 PM
Hi JKP,

I take it you're referring to ExamDiff. While not for a moment suggesting Word is a better tool, I was simply pointing the OP to software they probably already have. Word, of course, won't compare userform differences, so the OP may need something more adept than either Word or ExamDiff (the web page doesn't seem to suggest any capabilities in that regard).

Bob Phillips
05-16-2012, 12:25 AM
No, he is referring to the stage before that comparison, and is suggesting his ExportVBAProject tool www.jkp-ads.com/download.asp#exportvbaproject, which creates a single text file which mitigates the comparison process whichever tool is used for that comparison.

macropod
05-16-2012, 12:46 AM
OK, but as I see it the crux of the issue is how to do the comparison, not how to export the code. Whether having it all in a single file makes the comparison any easier is debatable. After all, it's pretty easy to confirm that pairs of files have the same content. In a large project with multiple modules, a module-by-module comparison would, arguably, make it easier to find which modules had been changed, and where, than trying to compare the lot as a single file.

Bob Phillips
05-16-2012, 12:52 AM
Beg to differ on both points.

It is a hollow victory if the comparison works easily but it takes a lot of effort to get the data to the compare stage, and vice versa. When you have a process, each step is important and should be optimised.

I would rather compare 1 file that 35 any day of the week, even in a semi-automated process. Any comparison tool worth anything will re-align after it finds differences. Jan

theta
05-16-2012, 01:56 AM
I was thinking a mixture of all of these ideas :

It would be good to open up 2 files, and compare them within excel - using a VBA userform. Then all changes can be saved in the existing workbooks. The functionality of WinMerge, without the need to export to text file (so removing this step) and saving everything for immediate testing.

Would be a great migration / comparison tool if it could be achieved...and very useful for those who do not have access to Source Safe but inherit lots of files :

> Macro addin NEW.xls
> Macro addin 2007.xlsm
> Macro addin Other.xlam
> Macro addin latest working.xlsm
> Macro addin latest latest.xls

Who hasn't found themselves in this position at least once...

Bob Phillips
05-16-2012, 02:32 AM
Theta,

Keeping this functionality all within VBA has one very large problem. Obviously you need to write some code to identify the projects in question, some code to process each module within each project (catering for situations where the modules may have different names, the projects may have less/more modules, and so on).

But you would also need to write some code that does the comparison. This is not trivial, for instance
- is Public Sub XYZ and Sub XYZ the same or a difference
- Private Sub XYZ is definitely different to Sub XYZ
- is Function ABC the same as Sub ABC or different (if it returns a value it is different, if it doesn't it makes no odds)
- what if one project breaks up a line the other doesn't
- how far ahead do you look before deciding that they are completely different
- how to re-align
- etc. etc.

Whilst a VBA solution can be tailored exactly to VBA (for instance, I doubt any of the utilities cater for broken line), it is a lot of work. This is exactly the problem(s) that these utilities have solved.

Personally, I need such functionality insufficiently often to persuade me to build a VBA tool for myself, and I cannot see a market for such. Using Jan Karel's utility and a file comparison tool works for me.

theta
05-16-2012, 02:38 AM
So what would be wrong with the WinMerge approach :

Select module 1 to be compared
Select module 2 to be compared
Compare modules

No dependence on filenames etc - freedom given to the user then a text compare can occur. No need to over complicate things

Jan Karel Pieterse
05-16-2012, 03:54 AM
What I don't like about the winmerge aproach is that I have to go through all modules to be 100% sure I caught all differences.

And I see no fun in selecting 20 pairs of modules if my project has 20 modules.

In my experience, I mostly have to compare two versions of a project and don't recall which modules I changed. I therefore need the entire project.

ExamDiff does a great job detecting those differences, but with some limitations e.g. line continuation isn't ignored.

snb
05-16-2012, 08:47 AM
I downloaded http://www.jkp-ads.com/download.asp#exportvbaproject.

To me its seems to be a lot of code to perform a simple task.

Bob Phillips
05-16-2012, 10:32 AM
I downloaded http://www.jkp-ads.com/download.asp#exportvbaproject.

To me its seems to be a lot of code to perform a simple task.

That is a bit of a pointless post isn't it? It does what it says on the tin, it is free, what more do you want. Just like everyone else, you are perfectly at liberty to not download it, never use it.

Jan Karel Pieterse
05-18-2012, 03:01 AM
@snb: I know you like concise code.
I know I like code I can read and understand next year too.
I know you don't prefer to declare variables. I know most professional programmers prefer the opposite.
NB: Your version exports immediately when the user clicks, mine at least waits for an OK.
Also, your code does not sort the code objects before exporting, I have found that sometimes the order in which the VBE runs through the code modules is not the same, even if the object names are identical. Hence my more verbose code.

Bob Phillips
05-18-2012, 07:46 AM
@snb: I know you like concise code.

I think it is more a matter of liking obtuse code, makes him feel smart.

ukyank
06-20-2013, 02:31 AM
I'm not sure if you were able to help the OP. But you certainly helped me with this. Learning VBA is easier when you can copy all your code review it and see how you've moved forward and where you need to make corrections. THANK YOU SO MUCH!