如何在Excel VBA中将WorksheetFunction.Transpose的数组基数设为0？

问题说明 #

在VBA代码中使用WorksheetFunction.Transpose函数时，发现即便Application.Option Base默认设为0，该函数返回的数组仍以1作为索引基数。在Excel工作表或Range对象中使用该函数不会有问题，但作用于普通数组时会超出预期。目前只能通过将值重新索引到新数组来兼容程序中的其他0基数组，想知道是否存在参数或设置，能让WorksheetFunction.Transpose返回的数组索引基数设为0，或匹配Application.Option Base的设置。

测试代码 #

Sub arrtra()
    Dim arr(5, 2)
    Dim res As Variant
    For i = 0 To 5
        For j = 0 To 2
            arr(i, j) = Rnd()
        Next j
    Next i
    res = arr
    tra = tes(res)
    Debug.Print "Ubound="; UBound(arr, 1), "Ubound="; UBound(tra, 2), arr(0, 0), tra(1, 1)

End Sub

Function tes(vmi As Variant) As Variant

    tes = WorksheetFunction.Transpose(vmi)

End Function

测试结果 #

Ubound= 5     Ubound= 6      0,7055475     0,705547511577606 

解答 #

• 不存在直接的参数或设置可以修改WorksheetFunction.Transpose返回数组的索引基数。该函数的行为是固定的，无论Option Base设置为0还是1，它返回的数组始终是1基的，这是Excel工作表函数的固有特性（因为Excel单元格区域本身就是1基的）。
• 若需要0基数组，只能手动将转置后的1基数组转换为0基数组。可以写一个辅助函数来完成转换，示例如下：

Function TransposeToZeroBased(arr As Variant) As Variant
    Dim originalRows As Long, originalCols As Long
    Dim newArr As Variant
    Dim i As Long, j As Long
    
    originalRows = UBound(arr, 1) - LBound(arr, 1) + 1
    originalCols = UBound(arr, 2) - LBound(arr, 2) + 1
    
    ' 创建0基的转置数组
    ReDim newArr(0 To originalCols - 1, 0 To originalRows - 1)
    
    For i = LBound(arr, 1) To UBound(arr, 1)
        For j = LBound(arr, 2) To UBound(arr, 2)
            newArr(j - LBound(arr, 2), i - LBound(arr, 1)) = arr(i, j)
        Next j
    Next i
    
    TransposeToZeroBased = newArr
End Function

• 使用这个辅助函数替代WorksheetFunction.Transpose，就能得到0基的转置数组，兼容程序中的其他0基数组。

内容的提问来源于stack exchange，提问作者Black cat