Excel / VBA development tools ?

[Luke]

We have a number of Excel / VBA applications at our
department. Does anyone know of any tools to help with
the maintenance ? E.g. to compare two versions of a
workbook, and give a difference report of the embedded
VBA source code and Excel functions (or to just export
all VBA code, and Excel functions in plain text format to
be able to do a diff with a separate tool).

Thanks,
Luke.

[Bill Manville]

In article <97oaqc$ubg$1...@tasmania.dev.ict.nl>, Luke wrote:
> Does anyone know of any tools to help with
> the maintenance ? E.g. to compare two versions of a
> workbook, and give a difference report of the embedded
> VBA source code and Excel functions (or to just export
> all VBA code, and Excel functions in plain text format to
> be able to do a diff with a separate tool).
>

To compare worksheet contents, download COMPARE.XLA from Chip Pearson's web
site: http://www.cprearson.com

Microsoft Office Developer has some useful tools including multi-sheet import /
export and Visual SourceSafe which contains facilities for comparing VBA
modules.

I recently had to compare the VBA code in 2 workbooks and wrote the following.
Some day I might improve it, but you are welcome to try it as a starting
point. Paste it into a module and run macro CodeCompare

Dim R As Range

Sub AddRow(What As String, One As String, Two As String, Optional High As
Boolean)
R = What
R.Offset(, 1) = One
R.Offset(, 2) = Two
If High Then R.EntireRow.Font.ColorIndex = 3
Set R = R.Offset(1)
If R.Row Mod 20 = 0 Then
Application.Goto R, True
DoEvents
End If
End Sub

Function IsIn(oCollection As Object, stName As String) As Boolean
Dim O As Object
On Error GoTo NotIn
Set O = oCollection(stName)
IsIn = True
NotIn:
End Function

Sub CodeCompare()
Dim VP1 As Object
Dim VP2 As Object
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim VBC As Object

Dim V

V = Application.GetOpenFilename("All files (*.*),*.*", , "Select the first
file to compare")
If TypeName(V) = "Boolean" Then Exit Sub
If IsIn(Workbooks, Dir(V)) Then
Set WB1 = Workbooks(Dir(V))
Else
Set WB1 = Workbooks.Open(V)
End If
Set VP1 = WB1.VBProject

V = Application.GetOpenFilename("All files (*.*),*.*", , "Select the first
file to compare")
If TypeName(V) = "Boolean" Then Exit Sub
If IsIn(Workbooks, Dir(V)) Then
Set WB2 = Workbooks(Dir(V))
Else
Set WB2 = Workbooks.Open(V)
End If
Set VP2 = WB2.VBProject

Workbooks.Add xlWorksheet
Set R = Range("A1")
AddRow "Name", WB1.Name, WB2.Name
AddRow "Path", WB1.Path, WB2.Path

For Each VBC In VP1.VBComponents
If Not IsIn(VP2.VBComponents, VBC.Name) Then
AddRow "Name", VBC.Name, ">>absent", True
Else
AddRow "Name", VBC.Name, ""
CompareModules VBC.CodeModule, VP2.VBComponents(VBC.Name).CodeModule
End If
Next
End Sub

Sub CompareModules(CM1 As CodeModule, CM2 As CodeModule)
Dim L1 As Long, L2 As Long
Dim L As Long
Dim stProc As String
AddRow "DeclareLines", CM1.CountOfDeclarationLines,
CM2.CountOfDeclarationLines, CM1.CountOfDeclarationLines <>
CM2.CountOfDeclarationLines
For L1 = 1 To CM1.CountOfDeclarationLines
If CM1.Lines(L1, 1) <> CM2.Lines(L1, 1) Then
AddRow "Dec Line " & L1, CM1.Lines(L1, 1), IIf(L1 >
CM2.CountOfDeclarationLines, "", CM2.Lines(L1, 1)), True
End If
Next
L2 = L1
For L2 = L1 To CM2.CountOfDeclarationLines
If CM1.Lines(L2, 1) <> CM2.Lines(L2, 1) Then
AddRow "Dec Line " & L2, "", CM2.Lines(L2, 1), True
End If
Next
Do Until L1 > CM1.CountOfLines
Do
stProc = CM1.ProcOfLine(L1, 0)
If stProc <> CM2.ProcOfLine(L2, 0) Then
For L = 1 To CM2.CountOfLines
If CM2.ProcOfLine(L, 0) = stProc Then
L2 = L
End If
Next
If stProc <> CM2.ProcOfLine(L2, 0) Then
AddRow "Proc ", stProc, ">>Missing", True
L1 = L1 + CM1.ProcCountLines(stProc, 0)
Else
End If
End If
Loop Until L1 > CM1.CountOfLines Or stProc = CM2.ProcOfLine(L2, 0)
If stProc = CM2.ProcOfLine(L2, 0) Then
AddRow "Proc", stProc, ""
If CM1.ProcCountLines(stProc, 0) <> CM2.ProcCountLines(stProc, 0) Then
AddRow "Proc Lines", CM1.ProcCountLines(stProc, 0),
CM2.ProcCountLines(stProc, 0), True
End If
For L = 1 To CM1.ProcCountLines(stProc, 0)
If CM1.Lines(L1 + L - 1, 1) <> CM2.Lines(L2 + L - 1, 1) Then
AddRow "Line " & L, CM1.Lines(L1 + L - 1, 1), IIf(L >
CM2.ProcCountLines(stProc, 0), "", CM2.Lines(L2 + L - 1, 1)), True
End If
Next
L1 = L1 + CM1.ProcCountLines(stProc, 0)
L2 = L2 + CM2.ProcCountLines(stProc, 0)
End If
Loop
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

[Chip Pearson]

> site: http://www.cprearson.com

Who's "prearson"?

Try www.cpearson.com

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Bill Manville" <Bill-M...@msn.com> wrote in message
news:VA.0000090...@msn.com...

[Bill Manville]

In article <OpdsTALqAHA.1948@tkmsftngp04>, Chip Pearson wrote:
> Who's "prearson"?
>
Sorry Chip, I must have been dunk <g>.

[Chip Pearson]

> Sorry Chip, I must have been dunk <g>.

I've been called far worse.....

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Bill Manville" <Bill-M...@msn.com> wrote in message

news:VA.0000091...@msn.com...

[Dave Peterson]

I think Bill just used that extra "r" so that he could be "dunk" without anyone
knowing it.

--

Dave Peterson
ea3...@msn.com