Передать значения вместо диапазона в функцию VBA

Question

Я создаю функции VBA в Excel 2007. У одного есть прототип:

Function ToLevelCode_Range(val As Double, ByRef R As Range)

Я могу назвать это так хорошо, и он получает правильный результат:

=ToLevelCode_Range(B2, Categories!D1:D4)

Есть экземпляр, где я хочу, чтобы вторым параметром был определенный набор значений:

=ToLevelCode_Range(B2, { 0.38, 0.78, 1.18, 1.58 })

Это приводит к # ЗНАЧЕНИЮ, и я даже не могу добраться до первой точки останова в моей функции.

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

Как я могу заставить это работать?

Answer 1

ByRef означает, что вы должны передать ссылку (адрес) существующего объекта в качестве аргумента, чтобы он мог работать с этим фактическим объектом; ByVal, что означает, что вы передадите значение, и это значение будет скопировано в принимающий аргумент.

Таким образом, вы не можете передать список значений в качестве ссылки, вам нужно создать объект Range, который содержит ваши значения, а затем передать (ссылку на этот) объект в качестве этого аргумента.

Проблема здесь заключается в том, что объект Range в Excel является представлением фактического набора ячеек, но я считаю, что класс NamedRange позволит вам программно создать «виртуальный» объект Range с виртуальными ячейками, которые содержат ваши значения, но я я никогда не использовал это лично. :)

Я считаю, что этот вопрос в StackOverflow (в основном) такой же, как у вас, и его стоит проверить:

Microsoft Excel: программно создать скрытый именованный диапазон

Answer 2

Если вы измените параметры функции на

Function ToLevelCode_Range(val As Double, R As Variant)

он будет принимать как диапазон, так и массив

Конечно, вам нужно будет обрабатывать различные типы данных в коде функции

Когда вызывается как = ToLevelCode_Range(B2, Категории!D1:D4) R будет иметь тип Variant/Object/Range

При вызове as =ToLevelCode_Range(B2, { 0.38, 0.78, 1.18, 1.58 }) R будет иметь тип Variant/Variant(1 to 4)