PDA

View Full Version : [SOLVED] Compare VBA projects in different workbooks



offthelip
06-08-2017, 03:57 AM
I have just been asked to update a workbook which I developed 4 years ago. In the last four years it has been changed in minor ways by somebody else. Does anybody know of an easy way to compare the code in the VBA project I issued 4 years ago with workbook which has just been returned to me. There are 15 code modules some about 50 lines of code others nearer 1000 lines of code.

GTO
06-08-2017, 04:02 AM
Not particularly effective if much code has changed, but you can try copying both versions to separate WORD documents and compare those.

Hope that helps,

Mark

offthelip
06-08-2017, 04:08 AM
Yes, I had thought about that, but with 15 modules it isn't that easy just to get them into word.

mdmackillop
06-08-2017, 04:29 AM
Why not copy and paste into Excel worksheet. Use Dave Brett's Duplicate Master (http://computernewsreport.com/wp-content/uploads/2010/05/Duplicate-Master-V2.16.zip) to compare modules

offthelip
06-09-2017, 01:54 AM
Thanks for your suggestions, I have solved this problem, I thought I would share the solution because it is probably a common problem.
With help from Chip Pearson's excellent webiste (http://www.cpearson.com/Excel/MainPage.aspx) I wrote the following code to write all the code from my code modules in a workbook to "sheet1".
I then cut and pasted it into word, changed the table to text and saved the document. I then did the same with the original document and used Word Compare on the two document. Word compare works really well for code. Writing the code took a while but doing the compare took less than 5 minutes. Working out what to do about the changes will take me longer!!

Note: I have not generalised this, the code only looks at code modules, i:e no classes or other objects, it writesthe results to "sheet1" .

Sub ListModules()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim WS As Worksheet
Dim Rng As Range
Dim CodeMod As VBIDE.CodeModule
Dim ProcKind As VBIDE.vbext_ProcKind


Set VBProj = ActiveWorkbook.VBProject
Set WS = ActiveWorkbook.Worksheets("Sheet1")
Set Rng = WS.Range("A1")

For Each VBComp In VBProj.VBComponents

If VBComp.Type = vbext_ct_StdModule Then
Rng(1, 1).Value = VBComp.Name

Set Rng = Rng(2, 2)
Set CodeMod = VBComp.CodeModule
With CodeMod
ProcName = .ProcOfLine(1, ProcKind)
Rng.Value = ProcName
Set Rng = Rng(2, 3)


For LineNum = 1 To .CountOfLines
ProcName = .ProcOfLine(LineNum, ProcKind)
pbody = .Lines(LineNum, 1)

Rng(1, 1).Value = pbody
Set Rng = Rng(2, 1)
Next LineNum
End With




End If
rowno = Rng.Row
Set Rng = WS.Range("A" & rowno)


Next VBComp
End Sub

offthelip
06-09-2017, 02:38 AM
I forgot to say you need to set a reference to:
Microsoft Visual Basic for Applications Extensibility 5.3
to get the code to work

snb
06-09-2017, 04:15 AM
Avoid clumsy coding:


Sub M_snb()
For Each it In ThisWorkbook.VBProject.vbcomponents
If it.codemodule.countoflines > 0 Then c00 = c00 & String(3, vbCr) & it.codemodule.Lines(1, it.codemodule.countoflines)
Next

with createobject("word.document")
.content= c00
.windows(1).visible=true
end with
End Sub

offthelip
06-09-2017, 06:28 AM
I wish I could avoid clumsy coding, but I have no chance of doing that because I lack the superb knowledge of Excel VBA that you have. So I develop mickey mouse simple code that may be clumsy but at least it is easy to understand, particularly for coders that come from a different environment than EXCEL vBA.

snb
06-09-2017, 08:57 AM
The code I wrote is totally application independent (Word, Outlook, Access, Excel, Powerpoint, etc.)

SamT
06-09-2017, 11:47 AM
snb, I snagged your version for My Personal.

offthlip, I snagged yours so I would know where his creative inspiration came from.

snb
06-09-2017, 01:38 PM
To explore: http://www.snb-vba.eu/VBA_Excel_VBproject_en.html

offthelip
06-09-2017, 04:00 PM
Thanks snb, that is a great description of how to modify the VBa project. I wish I had seen it before I embarked on my bit of development.