Передача многомерного массива в Excel UDF в VBA

1 Christopher Tso [2010-08-07 04:36:00]

Как передать многомерные массивы или вложенные массивы в Excel UDF, которые позволяют мне ссылаться на другие диапазоны?

У меня есть UDF, определенный как "ARY", который делает то, что делает Array() в VBA, но в функции рабочего листа.

Это позволяет мне иметь формулу рабочего листа, например

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

Тем не менее, я получаю Error 2015 при выполнении TEST1 в качестве функции листа. Если я выполняю TEST1 из VBA, он отлично работает и возвращает "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

Оригинал вопроса (на английском)

2 Решение jtolle [2010-08-07 17:55:00]

В общем, вы не можете этого сделать. VBA отлично вложенных массивов в качестве значений, но Excel просто нет. Это имеет смысл, потому что Excel в конечном счете должен обрабатывать любые значения, которые он получает от ваших UDF, как вещи, которые могут попасть в таблицу рабочих таблиц.

Было бы неплохо, если бы промежуточные результаты не должны были подчиняться этому ограничению. В приведенном выше примере вы не пытаетесь помещать зубчатый массив в любые ячейки; вы просто хотите создать его и передать его в "TEST1". К сожалению, Excel просто не работает. Он оценивает каждое выражение в формуле в соответствии с юридическим значением Excel и #VALUE! является "ловушкой" для "не юридической ценности". (Существуют и другие ограничения для возвращаемых массивов, кроме зубьев. Например, массивы с определенным размером также приводят к ошибке #VALUE!.

Обратите внимание, что вложенные массивы, которые имеют одинаковую длину, могут быть возвращены из UDF:

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

Это может быть полезно, когда вы создаете список списков, которые вы на самом деле хотите получить в двумерном массиве.

Итак, вызов вашей функции ARY выше, как это должно работать очень хорошо:

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

Однако ваша функция TEST1 не сработает, так как вызов

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

приведет к передаче 2-D массива в TEST1, а не в 1-мерном массиве из 1-D массивов.

Вы также можете найти этот вопрос, и мой ответ на него будет полезен:

Возвращает определенный пользователем тип данных в ячейке Excel

ПОСЛЕДНЕЕ ИЗМЕНЕНИЕ:

Кстати, ваша функция "ARY" может быть намного короче. Это не имеет никакого отношения к вашему первоначальному вопросу, но стоит упомянуть:

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

В этом ответе есть пример использования:

Excel Select Case?

Оригинал ответа (на английском)

Передача многомерного массива в Excel UDF в VBA

1 ответ