Consulting

Results 1 to 12 of 12

Thread: Compare VBA projects in different workbooks

  1. #1
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location

    Compare VBA projects in different workbooks

    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.

  2. #2
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    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

  3. #3
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    Yes, I had thought about that, but with 15 modules it isn't that easy just to get them into word.

  4. #4
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Why not copy and paste into Excel worksheet. Use Dave Brett's Duplicate Master to compare modules
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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" .

    [VBA]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


    [/VBA]

  6. #6
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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

  7. #7
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,646
    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

  8. #8
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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.

  9. #9
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,646
    The code I wrote is totally application independent (Word, Outlook, Access, Excel, Powerpoint, etc.)
    Last edited by snb; 06-09-2017 at 01:36 PM.

  10. #10
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    snb, I snagged your version for My Personal.

    offthlip, I snagged yours so I would know where his creative inspiration came from.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  11. #11

  12. #12
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •