В предыдущих примерах мы создавали функции, в которых в качестве аргументов выступало одно значение. Но есть функции, у которых в качестве переменной выступает массив значений. Примерами таких функций встроенных в Excel служат функции СУММ, ВПР, МАКС, СРЗНАЧ и так далее. Давайте теперь создадим свою похожую функцию.
Далее приведен код функции, которая суммирует все четные значения.
Function СуммаЧетных(Диапазон As Range) As Double Dim r As Range Dim S As Double For Each r In Диапазон If r.Value Mod 2 = 0 Then S = S + r.Value Next СуммаЧетных = S End Function
В первой строке данной функции можно увидеть, что переменной Диапазон мы присвоили тип Range (Диапазон As Range). Таким образом, в качестве аргумента функции мы можем использовать диапазон ячеек. Применим эту функцию на рабочем листе Excel.
Как видно из рисунка, мы не перечисляли каждое значение, а использовали диапазон ячеек A2:A11.
Теперь немного разберем код. Мы использовали цикл For Each - Next. Его задача пройтись по каждой ячейке нашего диапазона. Предварительно мы объявили еще одну переменную r типа Range. Она будет хранить значение каждой ячейки.
For Each r In Диапазон
Далее идет проверка значения ячейки и если оно кратно 2, то мы суммируем результат во временную переменную S.
If r.Value Mod 2 = 0 Then S = S + r.Value
В конце, после цикла, мы присваиваем функции СуммаЧетных значение просуммированных элементов S.
Предыдущая функция имеет недостаток - если вы попытаетесь использовать в качестве переменной несмежный диапазон ячеек, то получите ошибку. Иногда требуется более универсальная функция, так как на старте мы можем не знать количество аргументов, которые будут использоваться. Например, всем известная функция СУММ может принимать неограниченное количество аргументов =СУММ(A1;B1:B5;1;2;3), функция все рано вернет верный результат.
Вы можете создать свою аналогичную функцию в VBA, указав к последнему (или единственному) аргументу ключевое слово ParamArray.
ParamArray - данный модификатор применяется только к последнему аргументу. Аргумент с данным модификатором всегда должен иметь тип данных Variant и всегда является необязательным, ключевое слов Optional не указывается дополнительно
Теперь давайте создадим функцию, которая может принимать произвольное количество аргументов/ А в качестве результата возвращать всю туже сумму нечетных значений.
Function СуммаЧетных2(ParamArray Значения() As Variant) As Double Dim v As Variant Dim S As Double For Each v In Значения If v Mod 2 = 0 Then S = S + v Next СуммаЧетных2 = S End Function
Данная функция может принимать любое количество аргументов, однако аргументы не могут быть многомерные, это означает, что мы не можем указывать диапазоны, а должны перечислить все значения или все ячейки. Как это показано на рисунке далее.
Что же делать, если необходимо использовать различные диапазоны ячеек, а не перечислять каждый элемент отдельно. Рассмотрим следующий код функции, который реализует эту возможность.
Function СуммаЧетных3(ParamArray Диапазоны() As Variant) As Double Dim v As Variant Dim r As Variant Dim S As Double For Each v In Диапазоны For Each r In v If r Mod 2 = 0 Then S = S + r Next Next СуммаЧетных3 = S End Function
У нас добавился еще один цикл For Each - Next. Т.е. сначала мы проходим по всем аргументам (диапазонам ячеек), а после проходим по всем ячейкам этих диапазонов.
Этот код тоже далек от идеала: если мы будем использовать вместо диапазонов значения, то получим ошибку. Поэтому если необходимо создать идеальную функцию, например как СУММ (она может обработать любые типы аргументов), то придется потрудится и дополнительно проверять тип аргументов. Но об это уже поговорим в другой раз. Перейдем к созданию еще одного типа функций.
Надеюсь вы знаете, как использовать формулы массивов в Excel. Эти функции вводятся на рабочий лист Excel с помощью сочетания клавиш Ctrl + Shift + Enter. Сейчас мы создадим функцию, которая возвратит массив значений. Например, выведет нам список всех месяцев.
Function СписокМесяцев() СписокМесяцев = Array("Январь", "Февраль", "Март", "Апрель", _ "Май", "Июнь", "Июль", "Август", _ "Сентябрь", "Октябрь", "Ноябрь", "Декабрь") End Function
Если мы введем функцию в одну ячейку, то увидим, что результатом будет только Январь. Чтобы понять как работает функция необходимо выделить 12 ячеек по горизонтали, ввести =СписокМесяцев() и нажать сочетание клавиш Ctrl + Shift + Enter.
Если необходимо вывести список месяцев по вертикали, то можно дополнительно использовать функцию =ТРАНСП(СписокМесяцев()).
Возможно покажется, что функции, возвращающие массив достаточно бесполезны. Однако это не так, да они реже используются на рабочем листе Excel, но я надеюсь что они будут незаменимой вещаю в ваших будущих программах на VBA.