Excel VBA传递数组至函数时被压缩的原因与规避方法

问题代码 #

Function udf() As Variant
    udf = udfWithArg(getArray())
End Function

Function udfWithArg(arg) As Variant
    udfWithArg = arg
End Function

Function getArray() As Variant
    'getArray = Array(Array(1), Array(2))
    getArray = Array(Array(1, 2))
End Function

问题现象 #

在Excel中调用=udf()时，可正常返回二维数组结果；但直接调用=udfWithArg(getArray())时，VBA会将原本的单行二维数组压缩为一维数组，且该压缩仅发生在单行数组场景（单列数组无此问题）。

补充说明 #

针对@Ike的评论，我认为Array(Array(1, 2))属于二维数组，且它与udfWithArg中接收的arg确实存在差异。

原因解析 #

这是Excel公式引擎处理UDF参数时的自动降维行为：当传入的嵌套数组仅包含一行（外层数组只有1个元素，且该元素是横向一维数组），Excel会默认将其扁平化处理为一维数组；而单列嵌套数组（外层数组元素为纵向一维数组）不会触发该逻辑，因为Excel对纵向数组的处理更贴合二维数组的预期。

解决方法 #

方法1：在UDF中手动恢复二维数组格式 #

修改udfWithArg函数，对传入参数进行检查并转换：

Function udfWithArg(arg As Variant) As Variant
    Dim tempArr() As Variant
    Dim i As Long
    
    ' 判断是否是被压缩后的一维数组
    If IsArray(arg) And Not IsArray(arg(0)) Then
        ' 重构为1行N列的二维数组
        ReDim tempArr(1 To 1, 1 To UBound(arg) + 1)
        For i = LBound(arg) To UBound(arg)
            tempArr(1, i + 1) = arg(i)
        Next i
        udfWithArg = tempArr
    Else
        udfWithArg = arg
    End If
End Function

方法2：直接返回Excel兼容的二维数组 #

修改getArray函数，创建明确的二维数组而非嵌套一维数组：

Function getArray() As Variant
    Dim resultArr() As Variant
    ReDim resultArr(1 To 1, 1 To 2) ' 定义1行2列的二维数组
    resultArr(1, 1) = 1
    resultArr(1, 2) = 2
    getArray = resultArr
End Function

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