RSS

Пользовательские функции в Excel на VBA (+ видео)

Все, кто работает с Excel, сталкивались со встроенными функциями, например ВПР, ЕСЛИ и т.д. Из этих функций в Excel строятся различные формулы позволяющие, посчитать, обработать или принять решение. Эти функции находятся в мастере функций и разделены по группам. Мастер же позволяет упростить ввод аргументов функции. Набор функций в Excel достаточно обширный и для большинства задач можно найти нужную функцию или составить формулу из нескольких вложенных функций. Но что, если для решения задачи требуются особые вычисления!? В этом нам поможет встроенный язык VB, который позволяет написать собственные процедуры и функции обработки данных, при этом функции могут быть добавлены в мастер функций и использоваться как обычные встроенные функции (пользовательские функции).

Итак, что такое функция в Excel и VBA?
Функция
— это набор команд, которые обрабатывают данные заданным образом и возвращают результат. Функция имеет входные данные, используемые при расчетах (аргументы функции). По сути, функция это та же процедура, с которыми мы уже сталкивались неоднократно при написании макросов, только функция еще и возвращает результат. Функции могут использоваться в следующих ситуациях:

  • Как часть в процедуре VBA;
  • В формулах, создаваемых на листах Excel.

Область видимости функций аналогична области действия переменных т.е. Public, Private, Static. Описание функции начинается с ключевого слова Function и заканчивается End Function.Требования к именам функций такие же, как и к именам переменных в VBA.
Рассмотрим простейший пример функции:

Function Test (a as integer, b as integer) as integer
   Test = a*b
end 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. Ниже приведен список:

коды категорий пользовательских функций в Excel
Данную команду достаточно выполнить всего один раз и в дальнейшем при открытии книги функция будет находится в определенной категории. Поэтому, напишем процедуру InstallFunc, которая определит категорию для нашей функции.

Sub InstallFunc()
   Application.MacroOptions Macro:=»Test», Category:=10
End Sub
 

Описание пользовательской функции

Как Вы заметили, наша функция не имеет никакого описания, для пользователей это будет неудобно, а те, кто впервые увидят эту функцию, вообще не поймут для чего она. Поэтому добавим некоторое описание нашей функции. По аналогии с определением категории, нам необходимо один раз выполнить команду Application.MacroOptions. Расширим наш InstallFunc:

Sub InstallFunc()
   Application.MacroOptions Macro:=»Test», Category:=10, _
         Description:=»Находит произведение аргументов A и B»
End Sub

Собственно описание находится в Description. Выполните процедуру InstallFunc. Готово. Смотрим результат:

Для того чтобы функции были видны постоянно для всех книг, можно создать набор функций, сделать Install для размещения функций по категориям, добавить к ним описание и сохранить книгу как файл Надстройки xla с последующим подключением в Excel.

На этом пока все. Видео по созданию пользовательской функции и готовый пример Вы можете скачать ниже.

Прикрепленный файл: Видеопример создания функции в Excel.zip