excel - 将 2D 数组从工作表传递到 VBA/UDF 函数

Question

我使用的是 Excel 2010。

当 VBA 用户定义函数(例如 make2DArray)输出表示 2D 数组的变体时，该 2D 数组可以用作另一个 VBA 函数(例如 someFunction)的输入。从 VBA 代码中调用函数时。效果很好。

但是，当执行相同操作时，从工作表内(通过单元格中的公式)将 make2DArray 输出作为参数传递给 someFunction，那么它似乎仅适用于具有 **2 行或更多行** 的 2D 数组。生成 1 行的 2D 数组将会失败。似乎 1 行 2D 数组会自动转换为等效的 1D 数组。

一个小例子:

Option Explicit

'returns last (top-most, right-most) element
Function someFunction(v As Variant) As Integer
    On Error Resume Next
        Debug.Print "Dim-1 size", UBound(v, 1) - LBound(v, 1) + 1
        Debug.Print "Dim-2 size", UBound(v, 2) - LBound(v, 2) + 1
    On Error GoTo 0

    someFunction = v(UBound(v, 1), UBound(v, 2))
End Function

Function make2DArray(h As Integer, w As Integer) As Variant
    Dim i As Integer, j  As Integer
    Dim v2d As Variant

    ReDim v2d(1 To h, 1 To w)
    For i = 1 To h
        For j = 1 To w
            v2d(i, j) = i * j
        Next j
    Next i

    make2DArray = v2d
End Function

Sub test()
    'also works when called from sheet
    Debug.Print someFunction(make2DArray(2, 3)) 'returns 6
    'doesn't work when called from sheet
    Debug.Print someFunction(make2DArray(1, 3)) 'returns 3
End Sub

测试函数在 VBA 中可以正常工作。同样， =someFunction(make2DArray(2, 3)) 或任何 =someFunction(make2DArray(i, j)) 单元格公式对于 i>1 都适用，但是 =someFunction(make2DArray(1, 3)) 或任何 =someFunction(make2DArray) (1, j)) 只会产生#VALUE!结果在工作表中。

我的问题:这种行为是否记录在某处？有没有办法避免 1 行 2D 数组从 2D 数组“转换”到 1D 数组？

Answer 1

来 self 的评论:

_{I've never seen that before. It is like it is using transpose to convert a 2-D (one row) array into a 1-D array. I consider this a bug in that the VBA overhead is looking at a 2-D variant array with one 'row' (e.g. 1 as ubound first rank) and converting it to a 1-D array with the original ubound second rank as the ubound of the sole 1-D rank. For universality, the only suggestion I can come up with would be error control; I'll post a suggestion below.}

这里有一些错误控制来克服恶意的 2-D 到 1-D 数组转换。

'returns bottom-right element of 2D-array
Function someFunction(v As Variant) 'As Integer
    On Error Resume Next
        Debug.Print "Dim-1 size", UBound(v, 1) - LBound(v, 1) + 1
        Debug.Print "Dim-2 size", UBound(v, 2) - LBound(v, 2) + 1
    On Error GoTo 0

    'Debug.Print IsArray(v)
    'Debug.Print UBound(v, 1) & ":" & UBound(v, 2)

    On Error GoTo err_1D_array
    someFunction = v(UBound(v, 1), UBound(v, 2))
    Exit Function

err_1D_array:
    someFunction = v(UBound(v))

End Function