Excel VBA - ArrayList of ArrayLists to Excel Sheet

Question

Looking for a more appropriate approach. I have a working solution, but it seems there should be a built-in or more elegant method.

I am comparing two sheets from separate workbooks, documenting the differences on a sheet in current workbook. Every time a difference is found, I'm generating a row of output data. As I'm unaware of the total number of differences I will find, the row of output data is appended to an ArrayList.

I have a working bit of code, but the effective method is:

1. Create a row as an arraylist.
2. Convert the row to an array.
3. Add the row to an arraylist for output
4. TWICE Transpose the output arraylist while converting to an array
5. Output the array to worksheet.

With all the benefit of using ArrayLists, it seems that there should be a direct method for outputting a 2D "ArrayList of ArrayLists" or something along those lines.

Here is the current code:

Sub findUnmatchingCells()

    Dim oWB_v1 As Workbook, oWB_v2 As Workbook, oRange_v1 As Range, oRange_v2 As Range
    
    On Error GoTo endofsub
    
    With Me
    
        .Cells.Clear
        .Cells(1, 1) = "Row"
        .Cells(1, 2) = "Column"
        .Cells(1, 3) = "v1"
        .Cells(1, 4) = "v2"
        
    End With
    Dim missing_items As Object
    Dim output_row(), output(), missing_row As Object
    
    Set oWB_v1 = Workbooks("foo.xls")
    Set oWB_v2 = Workbooks("bar.xls")

    Set oRange_v1 = oWB_v1.Sheets(1).Range("A1:AD102")
    Set oRange_v2 = oWB_v2.Sheets(1).Range("A1:AD102")
    
    Set missing_items = CreateObject("System.Collections.ArrayList")
    
    For rRow = 1 To oRange_v1.Rows.Count
        For cCol = 1 To oRange_v1.Columns.Count
            
            If oRange_v1.Cells(rRow, cCol) <> oRange_v2.Cells(rRow, cCol) Then
                
                Set missing_row = CreateObject("System.Collections.ArrayList")
                
                missing_row.Add rRow
                missing_row.Add cCol
                missing_row.Add oRange_v1.Cells(rRow, cCol).Value2
                missing_row.Add oRange_v2.Cells(rRow, cCol).Value2
                
                output_row = missing_row.toarray
                
                missing_items.Add output_row
                
            End If
        
        Next cCol
    Next rRow
    
    output = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(missing_items.toarray))
    
    'my own output routine
    If Not outputArrayToRange(output, Me.Range("A2")) Then Stop
    
    Exit Sub
    
endofsub:
    Debug.Print rRow, cCol, missing_items.Count, missing_row.Count, Error
    Stop

End Sub

question from:https://stackoverflow.com/questions/66054862/excel-vba-arraylist-of-arraylists-to-excel-sheet

Answer 1

Seems like a lot of extra work here with ArrayList when you are not really using anything useful from them. As you know the mismatch count cannot be more than the number of start elements, and the columns will be 4 at end, you can do all of this just with a single array. Pre-size the array and in your loop populate it.

---

Simplified example:

As you are using Me this code would be in "Sheet1".

Now it would get more complicated if you wanted to ReDim to actual number of mismatches to avoid over-writing something, but generally it is wise to plan developments to avoid such risks. You would need the double transpose to be able to ReDim the rows as columns then back to rows.

With the ranges you mention I don't think the Transpose limit would be an issue, but that is a concern in other cases which needs to be resolved with additional looping.

The efficient way is to use arrays the whole time. Read the two ranges into arrays, loop one and compare against the other, write out changes to pre-sized array, write array to sheet

---

If this is just about is there nicer functionality for this within ArrayLists, no. What you have done is short and effective but incurs more overhead than is necessary.

---

Option Explicit

Public Sub findUnmatchingCells()

    Dim oWB As ThisWorkbook, oRange_v1 As Range, oRange_v2 As Range

    With Me
    
        .Cells.Clear
        .Cells(1, 1) = "Row"
        .Cells(1, 2) = "Column"
        .Cells(1, 3) = "v1"
        .Cells(1, 4) = "v2"
        
    End With
    
    Dim rRow As Long, cCol As Long
    
    Set oWB = ThisWorkbook

    Set oRange_v1 = oWB.Worksheets("Sheet2").Range("A1:D3") 'would be faster to read this into array and later loop that
    Set oRange_v2 = oWB.Worksheets("Sheet3").Range("A1:D3") 'would be faster to read this into array and later loop that
    
    Dim totalElements As Long, output()
    
    totalElements = oRange_v1.Rows.Count * oRange_v1.Rows.Count
    
    ReDim output(1 To totalElements, 1 To 4)

    For rRow = 1 To oRange_v1.Rows.Count 'would be faster to loop arrays than sheet
        For cCol = 1 To oRange_v1.Columns.Count
            If oRange_v1.Cells(rRow, cCol) <> oRange_v2.Cells(rRow, cCol) Then
                output(rRow, 1) = rRow
                output(rRow, 2) = cCol
                output(rRow, 3) = oRange_v1.Cells(rRow, cCol).Value2
                output(rRow, 4) = oRange_v2.Cells(rRow, cCol).Value2
            End If
        Next cCol
    Next rRow
        
    oWB.Worksheets("Sheet1").Cells(2, 1).Resize(UBound(output, 1), UBound(output, 2)) = output

End Sub

---

---

Other thoughts:

1. You can have early bound if adding references is not a concern:

From: https://www.snb-vba.eu/VBA_Arraylist_en.html

ThisWorkbook.VBProject.References.AddFromFile "C:WINDOWSMicrosoft.NETFrameworkv4.0.30319mscorlib.tlb"

or

ThisWorkbook.VBProject.References.AddFromguid "{BED7F4EA-1A96-11D2-8F08-00A0C9A6186D}", 2, 4

2. You are wasting an already created object by continually re-creating your missing_row ArrayList within loop. Create it once, before the loop, and just before you loop round again call the .Clear method.