Использование формул массива в VBA

Question

Функции массива Excel в таблицах полезны. Например, для вычисления среднего значения sin в массиве A1:A5 я ввожу следующее, что хорошо работает:

{=AVERAGE(SIN(A1:A5))}

Теперь я хотел бы создать функцию в VBA, которая выполняет эту работу, что-то вроде:

Function MyFunction(r As Variant) As Variant     ' r is the range, e.g. A1:A5 as above  
    MyFunction = WorksheetFunction.Average(Sin(r))  
End Function  

Эта функция должна приводить к одному значению, но она не работает (независимо от того, вводю ли я ее как нормальную или массивную формулу в ячейку Excel). Я думал, что проблема может быть в том, что функция sin VBA отличается от функции sin рабочего листа, но заменяет sin на WorksheetFunction.Грех тоже не работает.

Конечно, можно узнать количество значений, написать цикл, который хранит значения sin(r[i=1..n]) во временной переменной массива, а затем вычислить среднее значение, но я ищу более гибкий и эффективный решение.

Answer 1

Проблема здесь в том, что функция Sin ожидает одно значение в качестве параметра, а не массив

Обратите внимание, что функция Sin VBA не совпадает с функцией Sin рабочего листа (и Sin не является членом объекта WorkSheetFunction , поэтому WorksheetFunction.Average(WorksheetFunction.Sin(r)) тоже не сработает)

Если вы измените свой UDF на

Function MyFunction(r As Variant) As Variant     ' r is the range, e.g. A1:A5 as above  
    MyFunction = WorksheetFunction.Average(r)  
End Function  

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

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