VBA Macro to compare all cells of two Excel files

Question

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)?

Answer 1

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.

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.

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

Answer 2

A very simple check you can do with Cell formulas:

Sheet 1 (new - old)

=(if(AND(Ref_New<>"";Ref_Old="");Ref_New;"")

Sheet 2 (old - new)

=(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)

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".

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.

But two cases are not covered here:

1. In the compared cell of New and Old is the same data (Resulting Cell will be empty)
2. 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