用于比较两个 Excel 文件的所有单元格的 VBA 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5387929/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Macro to compare all cells of two Excel files
提问by Jungle Hunter
I'm trying to compare two Excel files and store what's only there in the new file in one sheet and store what is only there in the old one in another sheet. (Basically new - old = sheet1and old - new = sheet2.) One SO answerssuggest to loop through all cells and do a simple comparison. I'm (very) new to VBA and Macros so I don't know how to do this. How is it done (or where can I learn this)?
我正在尝试比较两个 Excel 文件，并将新文件中仅存在的内容存储在一张工作表中，并将旧文件中仅存在的内容存储在另一张工作表中。（基本上new - old = sheet1和old - new = sheet2。）一个SO答案建议遍历所有单元格并进行简单比较。我（非常）不熟悉 VBA 和宏，所以我不知道该怎么做。它是如何完成的（或者我可以在哪里学习）？
回答by Jean-Fran?ois Corbett
Do NOTloop through all cells!! There is a lot of overhead in communications between worksheets and VBA, for both reading and writing. Looping through all cells will be agonizingly slow. I'm talking hours.
难道不是遍历所有的细胞！工作表和 VBA 之间的通信有很多开销，无论是读取还是写入。循环遍历所有单元格将非常缓慢。我说几个小时。
Instead, load an entire sheet at once into a Variant array. In Excel 2003, this takes about 2 seconds (and 250 MB of RAM). Then you can loop through it in no time at all.
相反，一次将整个工作表加载到 Variant 数组中。在 Excel 2003 中，这大约需要 2 秒（和 250 MB 的 RAM）。然后，您可以立即遍历它。
In Excel 2007 and later, sheets are about 1000 times larger (1048576 rows × 16384 columns = 17 billioncells, compared to 65536 rows × 256 columns = 17 millionin Excel 2003). You will run into an "Out of memory" error if you try to load the whole sheet into a Variant; on my machine I can only load 32 million cells at once. So you have to limit yourself to the range you know has actual data in it, or load the sheet bit by bit, e.g. 30 columns at a time.
在 Excel 2007 及更高版本中，工作表大约大 1000 倍（1048576 行 × 16384 列 = 170亿个单元格，而Excel 2003 中的65536 行 × 256 列 = 1700万个单元格）。如果您尝试将整个工作表加载到 Variant 中，您将遇到“内存不足”错误；在我的机器上，我一次只能加载 3200 万个单元格。因此，您必须将自己限制在您知道其中包含实际数据的范围内，或者一点一点地加载工作表，例如一次 30 列。
Option Explicit
Sub test()
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long
strRangeToCheck = "A1:IV65536"
' If you know the data will only be in a smaller range, reduce the size of the ranges above.
Debug.Print Now
varSheetA = Worksheets("Sheet1").Range(strRangeToCheck)
varSheetB = Worksheets("Sheet2").Range(strRangeToCheck) ' or whatever your other sheet is.
Debug.Print Now
For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
' Cells are identical.
' Do nothing.
Else
' Cells are different.
' Code goes here for whatever it is you want to do.
End If
Next iCol
Next iRow
End Sub
To compare to a sheet in a different workbook, open that workbook and get the sheet as follows:
要与不同工作簿中的工作表进行比较，请打开该工作簿并按如下方式获取工作表：
Set wbkA = Workbooks.Open(filename:="C:\MyBook.xls")
Set varSheetA = wbkA.Worksheets("Sheet1") ' or whatever sheet you need
回答by stema
A very simple check you can do with Cell formulas:
您可以使用单元格公式进行非常简单的检查：
Sheet 1 (new - old)
表 1（新 - 旧）
=(if(AND(Ref_New<>"";Ref_Old="");Ref_New;"")
Sheet 2 (old - new)
表 2（旧 - 新）
=(if(AND(Ref_Old<>"";Ref_New="");Ref_Old;"")
This formulas should work for an ENGLISH Excel. For other languages they need to be translated. (For German i can assist)
这个公式应该适用于英文 Excel。对于其他语言，它们需要翻译。（对于德语，我可以提供帮助）
You need to open all three Excel Documents, then copy the first formula into A1 of your sheet 1 and the second into A1 of sheet 2. Now click in A1 of the first cell and mark "Ref_New", now you can select your reference, go to the new file and click in the A1, go back to sheet1 and do the same for "Ref_Old" with the old file. Replace also the other "Ref_New".
您需要打开所有三个 Excel 文档，然后将第一个公式复制到工作表 1 的 A1 中，将第二个公式复制到工作表 2 的 A1 中。现在单击第一个单元格的 A1 并标记“Ref_New”，现在您可以选择您的引用，转到新文件并单击 A1，返回 sheet1 并对旧文件的“Ref_Old”执行相同操作。也替换另一个“Ref_New”。
Doe the same for Sheet two.
对第二张纸做同样的事情。
Now copy the formaula form A1 over the complete range where zour data is in the old and the new file.
现在将公式表 A1 复制到 zour 数据在旧文件和新文件中的完整范围内。
But two cases are not covered here:
但这里不包括两种情况：
- In the compared cell of New and Old is the same data (Resulting Cell will be empty)
- In the compared cell of New and Old is diffe data (Resulting Cell will be empty)
- 在新旧的比较单元格中是相同的数据（结果单元格将为空）
- 在新旧的比较单元格中是不同的数据（结果单元格将为空）
To cover this two cases also, you should create your own function, means learn VBA. A very useful Excel page is cpearson.com
为了也涵盖这两种情况，您应该创建自己的函数，这意味着学习 VBA。一个非常有用的 Excel 页面是cpearson.com
