Excel VBA - ArrayList of ArrayLists to Excel Sheet

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  

https://stackoverflow.com/questions/66054862/excel-vba-arraylist-of-arraylists-to-excel-sheet February 05, 2021 at 06:34AM