Excel VBA - ArrayLists 的 ArrayList 到 Excel 工作表

Question

寻找更合适的方法。我有一个可行的解决方案，但似乎应该有一个内置的或更优雅的方法。

我正在比较不同工作簿中的两张纸，记录当前工作簿中一张纸上的差异。每次发现差异时，我都会生成一行输出数据。由于我不知道我会发现的差异总数，因此将输出数据行附加到 ArrayList。

我有一些工作代码，但有效的方法是:

1. 创建一行作为数组列表。
2. 将行转换为数组。
3. 将行添加到数组列表以进行输出
4. TWICE 在转换为数组时转置输出数组列表
5. 将数组输出到工作表。

有了使用 ArrayLists 的所有好处，似乎应该有一种直接的方法来输出 2D“ArrayLists 的 ArrayList”或类似的东西。

这是当前代码:

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

Answer 1

当您没有真正使用 ArrayList 的任何有用内容时，似乎这里有很多额外的工作。如您所知，不匹配计数不能超过起始元素的数量，并且最后的列为 4，您只需使用一个数组即可完成所有这些操作。预先确定数组的大小并在循环中填充它。

---

简化示例:

当您使用 Me 时，此代码将位于“Sheet1”中。

现在，如果您想将 ReDim 调整为实际的不匹配数量以避免重写某些内容，事情会变得更加复杂，但通常明智的做法是规划开发以避免此类风险。您需要双转置才能将行重新调整为列，然后返回到行。

对于您提到的范围，我认为转置限制不会成为问题，但在其他情况下这是一个问题，需要通过额外的循环来解决。

有效的方法是始终使用数组。将两个范围读入数组，循环一个并与另一个进行比较，写出对预先确定大小的数组的更改，将数组写入工作表

---

如果这只是关于在 ArrayList 中是否有更好的功能，没有。您所做的事情简短而有效，但会产生不必要的开销。

---

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

---

---

其他想法:

1. 如果添加引用不是问题，您可以提前绑定(bind):

发件人:https://www.snb-vba.eu/VBA_Arraylist_en.html

ThisWorkbook.VBProject.References.AddFromFile "C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\mscorlib.tlb"

或

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

1. 在循环中不断重新创建 missing_row ArrayList 是在浪费一个已经创建的对象。创建一次，在循环之前，就在您再次循环之前调用 .Clear 方法。