比较两个Excel工作簿:VBA数组不相等

Question

我正在尝试比较两个 excel 工作簿并列出此工作簿中的差异。

我在以下行中收到错误"类型不匹配"：

If varSheetA <> varSheetB Then

法典：

Sub compareworkb()
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long
Set wbkA = Workbooks.Open(Filename:="C:Solution - Beginners template .xlsx")
Set wbkB = Workbooks.Open(Filename:="C:Template_Project Lead - Beginners.xlsx")
For i = 1 To wbkA.Sheets.Count    
Set varSheetA = wbkA.Worksheets(wbkA.Sheets(i).Name)
Set varSheetB = wbkB.Worksheets(wbkB.Sheets(i).Name)
strRangeToCheck = "A1:N100"
Debug.Print Now
varSheetA = varSheetA.Range(strRangeToCheck)
varSheetB = varSheetB.Range(strRangeToCheck)
Debug.Print Now
For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)    
If varSheetA <> varSheetB Then
wbkB.Sheets(ShName).Cells(iRow, iCol).Interior.Color = vbYellow
ThisWorkbook.Sheets(1).Cells(7 + sh, 2) = "Mismatch Found"
ThisWorkbook.Sheets(1).Cells(7 + sh, 2).Interior.Color = vbYellow
End If
Next
Next
Next i
End Sub

Answer 1

您正在遍历数组的每个元素，但每次都尝试比较整个数组，这是<>无法完成的。相反，只需添加您尝试比较的项目的索引

For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2) 
' Note this change vvvvv                   vvvvvv
If varSheetA(iRow,iCol) <> varSheetB(iRow,iCol) Then
wbkB.Sheets(ShName).Cells(iRow, iCol).Interior.Color = vbYellow
ThisWorkbook.Sheets(1).Cells(7 + sh, 2) = "Mismatch Found"
ThisWorkbook.Sheets(1).Cells(7 + sh, 2).Interior.Color = vbYellow
End If
Next iCol
Next iRow

---

编辑：

看起来您已经改变了对如何遍历工作表的想法，并且忘记更新变量名称。例如，当你没有定义ShName时，你试图引用工作表ShName，当你没有定义sh时，你试图写入第7+sh行！在使用它进行循环之前，您也没有将i声明为变量。

此外，当工作表中存在变量类型不匹配时，您会收到类型不匹配错误！例如，如果您在一个单元格中有一个错误，而在另一个工作表上的相应单元格中有一个整数，则无法使用<>比较这些内容。在下面的代码中，我包含一个变量类型检查，有关详细信息，请参阅注释。该代码还包含前面提到的更正：

Sub compareworkb()
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long
Dim i As Long
Dim mismatch As Boolean
Set wbkA = ThisWorkbook
Set wbkB = Workbooks("test2.xlsx")
strRangeToCheck = "A1:N100" ' Define this once outside the loop, as it doesn't change
For i = 1 To wbkA.Sheets.Count
varSheetA = wbkA.Worksheets(i).Range(strRangeToCheck)
varSheetB = wbkB.Worksheets(i).Range(strRangeToCheck)
For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
mismatch = False
' Check that cell values are the same variable type
If VarType(varSheetA(iRow, iCol)) = VarType(varSheetB(iRow, iCol)) Then
' If they are the same variable type, we can compare them!
If varSheetA(iRow, iCol) <> varSheetB(iRow, iCol) Then
mismatch = True
End If
Else ' If they are not the same variable type, then it must be a mismatch                        
mismatch = True
End If
' If mismatch found then note it / colour corresponding cell
If mismatch Then
wbkB.Sheets(i).Cells(iRow, iCol).Interior.Color = vbYellow
ThisWorkbook.Sheets(1).Cells(7 + i, 2) = "Mismatch Found"
ThisWorkbook.Sheets(1).Cells(7 + i, 2).Interior.Color = vbYellow
End If
Next iCol
Next iRow
Next i
End Sub