Excel VBA如何将单行Range对象的单元格值转换为数组

问题根源 #

你当前代码中row_.Value返回的是二维数组：Excel中包含2个及以上单元格的Range对象，Value属性默认返回二维数组，第一维对应行索引、第二维对应列索引，单行Range的返回值结构为(1 To 1, 1 To 总列数)，直接调用UBound(arr)默认取第一维的上限1，和你预期的行内单元格数量不符。

实现方案 #

修改Row_To_Array函数，将二维单行数组转为一维数组即可，兼容单个单元格、多列单行的所有场景：

Private Function Row_To_Array(row_ As Range) As Variant
    Dim sourceArr As Variant
    sourceArr = row_.Value
    ' 处理单个单元格的特殊场景
    If row_.Cells.Count = 1 Then
        Dim resArr(1 To 1) As Variant
        resArr(1) = sourceArr
        Row_To_Array = resArr
        Exit Function
    End If
    ' 二维数组转一维数组
    ReDim resArr(1 To UBound(sourceArr, 2)) As Variant
    Dim i As Long
    For i = 1 To UBound(sourceArr, 2)
        resArr(i) = sourceArr(1, i)
    Next
    Row_To_Array = resArr
End Function

如果使用的Excel版本没有兼容性限制，也可以用Transpose方法简写，代码更精简：

Private Function Row_To_Array(row_ As Range) As Variant
    If row_.Cells.Count = 1 Then
        Dim res(1 To 1) As Variant
        res(1) = row_.Value
        Row_To_Array = res
        Exit Function
    End If
    ' 两次转置将单行二维数组转为一维数组
    Row_To_Array = WorksheetFunction.Transpose(WorksheetFunction.Transpose(row_.Value))
End Function

修改后你的主程序无需调整，Debug.Print输出的结果就是每行的单元格总数，后续需要拼接行内容也可以直接遍历返回的一维数组操作。

内容的提问来源于stack exchange，提问作者Joseph Gagnon