用于比较两个 Excel 文件的所有单元格的 VBA 宏 [英] VBA Macro to compare all cells of two Excel files

问题描述

我正在尝试比较两个 Excel 文件，并将新文件中仅存在的内容存储在一张工作表中，并将旧文件中仅存在的内容存储在另一张工作表中.(基本上是 new - old = sheet1 和 old - new = sheet2.)一个 SO答案建议遍历所有单元格并进行简单比较.我(非常)不熟悉 VBA 和宏，所以我不知道该怎么做.它是如何完成的(或者我可以在哪里学习)?

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 = sheet1 and old - new = sheet2.) One SO answers suggest 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)?

推荐答案

不要遍历所有单元格！！工作表和 VBA 之间的通信有很多开销，无论是读取还是写入.循环遍历所有单元格将非常缓慢.我说几个小时.

Do NOT loop 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.

相反，一次将整个工作表加载到 Variant 数组中.在 Excel 2003 中，这大约需要 2 秒(和 250 MB 的 RAM).然后，您可以立即遍历它.

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.

在 Excel 2007 及更高版本中，工作表大约大 1000 倍(1048576 行 × 16384 列 = 17 十亿 个单元格，而 65536 行 × 256 列 = 17 百万在 Excel 2003 中).如果您尝试将整个工作表加载到 Variant 中，您将遇到内存不足"错误；在我的机器上，我一次只能加载 3200 万个单元格.因此，您必须将自己限制在您知道其中包含实际数据的范围内，或者一点一点地加载工作表，例如一次 30 列.

In Excel 2007 and later, sheets are about 1000 times larger (1048576 rows × 16384 columns = 17 billion cells, compared to 65536 rows × 256 columns = 17 million in 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.

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

这篇关于用于比较两个 Excel 文件的所有单元格的 VBA 宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！