Все, кто работает с Excel, сталкивались со встроенными функциями, например ВПР, ЕСЛИ и т.д. Из этих функций в Excel строятся различные формулы позволяющие, посчитать, обработать или принять решение. Эти функции находятся в мастере функций и разделены по группам. Мастер же позволяет упростить ввод аргументов функции. Набор функций в Excel достаточно обширный и для большинства задач можно найти нужную функцию или составить формулу из нескольких вложенных функций. Но что, если для решения задачи требуются особые вычисления!? В этом нам поможет встроенный язык VB, который позволяет написать собственные процедуры и функции обработки данных, при этом функции могут быть добавлены в мастер функций и использоваться как обычные встроенные функции (пользовательские функции).Итак, что такое функция в Excel и VBA?Функция — это набор команд, которые обрабатывают данные заданным образом и возвращают результат. Функция имеет входные данные, используемые при расчетах (аргументы функции). По сути, функция это та же процедура, с которыми мы уже сталкивались неоднократно при написании макросов, только функция еще и возвращает результат. Функции могут использоваться в следующих ситуациях:
Область видимости функций аналогична области действия переменных т.е. Public, Private, Static. Описание функции начинается с ключевого слова Function и заканчивается End Function.Требования к именам функций такие же, как и к именам переменных в VBA.Рассмотрим простейший пример функции:Function Test (a as integer, b as integer) as integer Test = a*bend functionМы создали функцию Test, которая имеет два аргумента a и b целого типа. Функция Test возвращает только целый результат. Наша функция находит произведение a и b. Обратите внимание, что результат мы присваиваем в Test т.е. это завершающая стадия работы функции. Если этого не сделать, то функция не вернет никакого результата. end function — указывает, что вычисления функции закончились.Эту функцию мы можем использовать в других процедурах и функциях vba как вспомогательную или в формулах листа Excel. Хочу обратить ваше внимание, если Вы пишите функции для использования в формулах, то описание функции должно располагаться в модуле (Module) и начинаться только со слова Function. Описание типа Private сделает функцию невидимой в мастере. Поэтому, если использование функции не планируется в формулах, то Вы можете ограничить ее область видимости: Private Function Test (a as integer, b as integer) as integer …..Остановимся теперь более подробно на создании пользовательских функций, которые будут использоваться в формулах для вычислений.Мы уже создали функцию Test. Добавьте ее в созданный Module.
Переходим теперь на лист и вводим в ячейки A1 и B1 целые числа, выделяем ячейку C1 и жмем вставка функции. В мастере функций необходимо выбрать категорию «Определенные пользователем» и в списке функций найдите «Test»:
Далее, вставляйте как обычную функцию, указав аргументы.Есть один момент, пользовательские функции, в отличие от встроенных, работают только при включенных макросах. Как включить поддержку макросов описано в этой статье.
По умолчанию функции находятся в категории «Определенные пользователем». Каким образом можно пользовательской функции переназначить категорию!?С помощью команды: Application.MacroOptions Macro:=”Test”, Category:=10где Macro:=”Test” это название нашего макроса, Category:=10 номер категории, куда разместить функцию. Всего категорий в Excel — 15. Ниже приведен список:
Sub InstallFunc() Application.MacroOptions Macro:=”Test”, Category:=10End Sub
Как Вы заметили, наша функция не имеет никакого описания, для пользователей это будет неудобно, а те, кто впервые увидят эту функцию, вообще не поймут для чего она. Поэтому добавим некоторое описание нашей функции. По аналогии с определением категории, нам необходимо один раз выполнить команду Application.MacroOptions. Расширим наш InstallFunc:
Собственно описание находится в Description. Выполните процедуру InstallFunc. Готово. Смотрим результат:
Для того чтобы функции были видны постоянно для всех книг, можно создать набор функций, сделать Install для размещения функций по категориям, добавить к ним описание и сохранить книгу как файл Надстройки xla с последующим подключением в Excel.
Прикрепленный файл: Видеопример создания функции в Excel.zip
