将多维数组传递到VBA中的Excel UDF中 [英] Pass multidimensional array into Excel UDF in VBA

问题描述

如何将多维数组或嵌套数组传递到允许我引用其他范围的Excel UDF中?

How do I pass multidimensional arrays or nested arrays into an Excel UDF which allow me to reference other ranges?

我有一个定义为"ARY"的UDF，它可以执行Array()在VBA中但在工作表函数中的功能.

I have a UDF defined as "ARY" which does what Array() does in VBA but in a worksheet function.

这使我有一个像这样的工作表公式

This allows me to have a worksheet formula like

= TEST1(ARY(ARY("A"，"B")，"C"))
或
= TEST1(ARY(ARY(A1，B1)，C1)

=TEST1(ARY(ARY("A", "B"), "C"))
or
=TEST1(ARY(ARY(A1, B1), C1)

但是，当将TEST1作为工作表函数执行时，出现错误2015.如果我从VBA执行TEST1，它将正常工作并返回"A".

However, I get Error 2015 when executing TEST1 as a worksheet function. If I execute TEST1 from VBA, it works fine and returns "A".

Public Function TEST1(Params As Variant) As Variant
    TEST1 = Params(0)(0)
End Function

'Returns 1D ARRAY
Public Function ARY(ParamArray Params() As Variant)
    ReDim result(0 To UBound(Params)) As Variant
    Dim nextIndex As Integer
    Dim p As Variant

    nextIndex = 0

    For Each p In Params
        result(nextIndex) = p
        nextIndex = nextIndex + 1
    Next

    ARY = result
End Function

推荐答案

通常，您不能这样做. VBA 可以使用嵌套数组作为值，但Excel则不行.这是有道理的，因为Excel最终必须将它从UDF中获得的任何值都视为可以进入工作表网格的内容.

In general, you can't do this. VBA is fine with nested arrays as values, but Excel just isn't. This makes sense, because Excel ultimately has to treat any values it gets from your UDFs as things that can go into the worksheet grid.

如果中级结果不必遵守此限制，那就太好了.在上面的示例中，您并没有试图将锯齿状的数组放入任何单元格中.您只想创建它并将其传递给"TEST1".不幸的是，Excel不能那样工作.它将公式中的每个表达式求值为合法的Excel值和#VALUE！是非法律价值"的统称.(除了参差不齐，返回数组还有其他限制.例如，超过一定大小的数组也会导致#VALUE！错误.)

It would be nice if intermediate results didn't have to obey this restriction. In your example above, you're not trying to put a jagged array into any cells; you just want to create it and pass it to 'TEST1'. Unfortunately, Excel just doesn't work that way. It evaluates each expression in a formula to a legal Excel value, and #VALUE! is the catch-all for "not a legal value". (There are other limitations to returned arrays besides jaggedness. For example, arrays over a certain size also result in a #VALUE! error.)

请注意，所有长度相同的嵌套数组 可以从UDF中传回:

Note that nested arrays that are all the same length can be passed back from UDFs:

Public Function thisKindOfNestingWorks()
    thisKindOfNestingWorks = Array(Array(1, 2), Array(3, 4))
End Function

当您构建一些实际要强制转换为二维数组的列表时，此功能很有用.

This can be useful when you're building up some list-of-lists that you actually want coerced to a 2-D array.

因此，像上面这样调用您的ARY函数应该可以正常工作:

So, calling your ARY function above like this should work just fine:

=ARY(ARY(A1, B1), ARY(C1, D1))

但是，自调用以来，您的TEST1函数将失败

However, your TEST1 function would fail, since calling

=TEST1(ARY(ARY(A1, B1), ARY(C1, D1)))

将导致将2-D数组传递给TEST1，而不是将1-D数组传递给1-D数组.

would result in a 2-D array being passed to TEST1, not a 1-D array of 1-D arrays.

您可能还会发现此问题及其答案对您有帮助:

You might also find this question and my answer to it to be helpful:

在Excel中返回用户定义的数据类型单元格

以后进行的

顺便说一句，您的"ARY"功能可能会短很多.这与您最初的问题无关，但值得一提:

Incidentally, your 'ARY' function could be a lot shorter. This has nothing to do with your original question, but it's worth mentioning:

Public Function arr(ParamArray args())
     arr = args
End Function

在此答案中有一个使用它的示例:

There is an example of using it in this answer:

Excel Select Case?

这篇关于将多维数组传递到VBA中的Excel UDF中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！