Comparing Workbooks


How to Compare 2 workbooks ?

This procedure creates a new workbook which lists the comparison results for each worksheet in the workbooks.
Open the 2 workbooks you would like to compare.
In this examples the files have the names "BetterSolutions_1.xlsm" and "BetterSolutions_2.xlsm".

Public Sub CompareTwoWorkbooks() 
  Dim WS As Worksheet
  Workbooks.Add
  For Each WS In Workbooks("BetterSolutions_1.xlsm").Worksheets
    Call CompareWorksheets(WS, Workbooks("BetterSolutions_2.xlsm").Worksheets(WS.Name))
  Next
End Sub

Public Sub CompareWorksheets(ByVal WS1 As Worksheet, _
                             ByVal WS2 As Worksheet)
Dim iRow As Integer
Dim iCol As Integer
Dim R1 As Range
Dim R2 As Range

' add the corresponding worksheet for the results
  Worksheets.Add.Name = WS1.Name
  Range("A1:D1").Value = Array("Address", "Difference", WS1.Parent.Name, WS2.Parent.Name)
  Range("A2").Select
  For iRow = 1 To Application.Max(WS1.Range("A1").SpecialCells(xlLastCell).Row, _
                      WS2.Range("A1").SpecialCells(xlLastCell).Row)
    For iCol = 1 To Application.Max(WS1.Range("A1").SpecialCells(xlLastCell).Column, _
                      WS2.Range("A1").SpecialCells(xlLastCell).Column)
      
      Set R1 = WS1.Cells(iRow, iCol)
      Set R2 = WS2.Cells(iRow, iCol)
      
' compare the types to avoid getting VBA type mismatch errors.
      If (TypeName(R1.Value) <> TypeName(R2.Value)) Then
        Call LogDifference(R1.Address, "Type", R1.Value, R2.Value)
      ElseIf (R1.Value <> R2.Value) Then
        If (TypeName(R1.Value) = "Double") Then
          If (Abs(R1.Value - R2.Value) > R1.Value * 10 ^ (-12)) Then
            Call LogDifference(R1.Address, "Double", R1.Value, R2.Value)
          End If
        Else
            Call LogDifference(R1.Address, "Value", R1.Value, R2.Value)
        End If
      End If
      
' record formulae without leading "=" to avoid them being evaluated
      If (R1.HasFormula = True) Then
        If (R2.HasFormula = True) Then
          If (R1.Formula <> R2.Formula) Then
            Call LogDifference(R1.Address, "Formula", Mid(R1.Formula, 2), Mid(R2.Formula, 2))
          End If
        Else
          Call LogDifference(R1.Address, "Formula", Mid(R1.Formula, 2), "**no formula**")
        End If
      Else
        If (R2.HasFormula = True) Then
          Call LogDifference(R1.Address, "Formula", "**no formula**", Mid(R2.Formula, 2))
        End If
      End If
      If (R1.NumberFormat <> R2.NumberFormat) Then
        Call LogDifference(R1.Address, "NumberFormat", R1.NumberFormat, R2.NumberFormat)
      End If
    Next iCol
  Next iRow
  With ActiveSheet.UsedRange.Columns
    .AutoFit
    .HorizontalAlignment = xlLeft
  End With
End Sub

Public Sub LogDifference(ByVal Address As String, _
                         ByVal What As String, _
                         ByVal V1 As Variant, _
                         ByVal V2 As Variant)
  ActiveCell.Resize(1, 4).Value = Array(Address, What, V1, V2)
  ActiveCell.Offset(1, 0).Select
  If ActiveCell.Row = Rows.Count Then
    Call MsgBox("Too many differences", vbExclamation)
    End
  End If
End Sub

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext