Excel VBA：如何比较二维数组的列/行且避免全列循环

一、适配(x,y)格式二维数组的列比较 #

你原来的代码针对的是锯齿数组（(x)(y)格式），这类数组的每个索引x对应一个一维数组（即整列数据），因此可以直接用Join拼接比较。但标准二维数组（(x,y)格式，行优先存储）的列并非独立一维数组，需要先将目标列提取为一维数组，再用Join完成比较，无需在外层遍历列内的每个元素。

实现步骤： #

1. 编写列提取辅助函数，将二维数组的指定列转为一维数组：

Function ExtractColumn(arr As Variant, colIndex As Long) As Variant
    Dim rowCount As Long
    Dim result() As Variant
    rowCount = UBound(arr, 1) - LBound(arr, 1) + 1
    ReDim result(1 To rowCount)
    
    Dim i As Long
    For i = LBound(arr, 1) To UBound(arr, 1)
        result(i - LBound(arr, 1) + 1) = arr(i, colIndex)
    Next i
    
    ExtractColumn = result
End Function

2. 使用辅助函数完成列比较：

Dim intColumn As Long
For intColumn = 2 To UBound(arrArrayToCheck, 2)
    ' 提取当前列与上一列的一维数组
    Dim colCurrent As Variant, colPrevious As Variant
    colCurrent = ExtractColumn(arrArrayToCheck, intColumn)
    colPrevious = ExtractColumn(arrArrayToCheck, intColumn - 1)
    
    ' 用Join拼接后比较
    If Join(colCurrent, ":-^") = Join(colPrevious, ":-^") Then
        ' 此处写入你的业务逻辑
    End If
Next intColumn

二、行比较与代码复用 #

要避免重复编写行/列提取的代码，可以封装一个通用的维度提取函数，同时支持提取行或列：

Function ExtractDimension(arr As Variant, dimType As Long, index As Long) As Variant
    Dim count As Long
    Dim result() As Variant
    Dim i As Long
    
    Select Case dimType
        Case 1 ' 提取行（dimType=1代表行维度）
            count = UBound(arr, 2) - LBound(arr, 2) + 1
            ReDim result(1 To count)
            For i = LBound(arr, 2) To UBound(arr, 2)
                result(i - LBound(arr, 2) + 1) = arr(index, i)
            Next i
        Case 2 ' 提取列（dimType=2代表列维度）
            count = UBound(arr, 1) - LBound(arr, 1) + 1
            ReDim result(1 To count)
            For i = LBound(arr, 1) To UBound(arr, 1)
                result(i - LBound(arr, 1) + 1) = arr(i, index)
            Next i
    End Select
    
    ExtractDimension = result
End Function

行比较示例： #

Dim intRow As Long
For intRow = 2 To UBound(arrArrayToCheck, 1)
    Dim rowCurrent As Variant, rowPrevious As Variant
    rowCurrent = ExtractDimension(arrArrayToCheck, 1, intRow)
    rowPrevious = ExtractDimension(arrArrayToCheck, 1, intRow - 1)
    
    If Join(rowCurrent, ":-^") = Join(rowPrevious, ":-^") Then
        ' 此处写入你的业务逻辑
    End If
Next intRow

关于转置报错的说明 #

你尝试用转置数组后Join报错，原因是Excel的WorksheetFunction.Transpose存在限制：一是对数组大小有上限（旧版Excel仅支持65536个元素），二是若数组包含混合数据类型（如文本+数字），转置可能失败。自己封装提取函数可以避开这些问题，适配所有合规的二维数组。

内容的提问来源于stack exchange，提问作者Hallvard Skrede