Создание пользовательской функции в VBA Excel, ее синтаксис и компоненты. Описание пользовательской функции и ее аргументов. Метод Application.MacroOptions.
Пользовательская функция – это процедура VBA, которая производит заданные вычисления и возвращает полученный результат. Используется для вставки в ячейки рабочего листа Excel или для вызова из других процедур.
Объявление пользовательской функции
Синтаксис функции
1 2 3 4 5 6 7 |
[Static] Function Имя ([СписокАргументов])[As ТипДанных] [Операторы] [Имя = выражение] [Exit Function] [Операторы] [Имя = выражение] End Function |
Компоненты функции
- Static — необязательное ключевое слово, указывающее на то, что значения переменных, объявленных в функции, сохраняются между ее вызовами.
- Имя — обязательный компонент, имя пользовательской функции.
- СписокАргументов — необязательный компонент, одна или более переменных, представляющих аргументы, которые передаются в функцию. Аргументы заключаются в скобки и разделяются между собой запятыми.
- Операторы — необязательный компонент, блок операторов (инструкций).
- Имя = выражение — необязательный* компонент, присвоение имени функции значения выражения или переменной. Обычно, значение присваивается функции непосредственно перед выходом из нее.
- Exit Function — необязательный компонент, принудительный выход из функции, если ей уже присвоено окончательное значение.
*Один из компонентов Имя = выражение следует считать обязательным, так как если не присвоить функции значения, смысл ее использования теряется.
Видимость функции
Видимость пользовательской функции определяется необязательными ключевыми словами Public и Private, которые могут быть указаны перед оператором Function (или Static, в случае его использования).
Ключевое слово Public указывает на то, что функция будет доступна для вызова из других процедур во всех модулях открытых книг Excel. Функция, объявленная как Public, отображается в диалоговом окне Мастера функций.
Ключевое слово Private указывает на то, что функция будет доступна для вызова из других процедур только в пределах программного модуля, в котором она находится. Функция, объявленная как Private, не отображается в диалоговом окне Мастера функций, но ее можно ввести в ячейку вручную.
Если ключевое слово Public или Private не указано, функция считается по умолчанию объявленной, как Public.
Чтобы пользовательская функция всегда была доступна во всех открытых книгах Excel, сохраните ее в Личной книге макросов без объявления видимости или как Public. Но если вы планируете передать рабочую книгу с пользовательской функцией на другой компьютер, код функции должен быть в программном модуле передаваемой книги.
Пример пользовательской функции
Для примера мы рассмотрим простейшую пользовательскую функцию, которой в следующем параграфе добавим описание. Называется функция «Деление», объявлена с типом данных Variant, так как ее возвращаемое значение может быть и числом, и текстом. Аргументы функции – Делимое и Делитель – тоже объявлены как Variant, так как в ячейках Excel могут быть числовые значения разных типов, и функция IsNumeric тоже проверяет разные типы данных и требует, чтобы ее аргументы были объявлены как Variant.
1 2 3 4 5 6 7 8 9 10 11 |
Function Деление(Делимое As Variant, Делитель As Variant) As Variant If IsNumeric(Делимое) = False Or IsNumeric(Делитель) = False Then Деление = "Ошибка: Делимое и Делитель должны быть числами!" Exit Function ElseIf Делитель = 0 Then Деление = "Ошибка: деление на ноль!" Exit Function Else Деление = Делимое / Делитель End If End Function |
Эта функция выполняет деление значений двух ячеек рабочего листа Excel. Перед делением проверяются два блока условий:
- Если делимое или делитель не являются числом, функция возвращает значение: «Ошибка: Делимое и Делитель должны быть числами!», и производится принудительный выход из функции оператором Exit Function.
- Если делитель равен нулю, функция возвращает значение: «Ошибка: деление на ноль!», и производится принудительный выход из функции оператором Exit Function.
Если проверяемые условия не выполняются (возвращают значение False) производится деление чисел и функция возвращает частное (результат деления).
Вы можете скопировать к себе в стандартный модуль эту функцию и она станет доступна в разделе «Определенные пользователем» Мастера функций. Попробуйте вставить функцию «Деление» в ячейку рабочего листа с помощью Мастера и поэкспериментируйте с ней.
Практического смысла функция «Деление» не имеет, но она хорошо демонстрирует как объявляются, создаются и работают пользовательские функции в VBA Excel. А еще она поможет продемонстрировать, как добавлять к функциям и аргументам описания. С полноценной пользовательской функцией вы можете ознакомиться здесь.
Добавление описания функции
В списке функций, выводимом Мастером, невозможно добавить или отредактировать их описание. Список макросов позволяет добавлять процедурам описание, но в нем нет функций. Проблема решается следующим образом:
- Запустите Мастер функций, посмотрите, как отображается имя нужной функции и закройте его.
- Откройте список макросов и в поле «Имя макроса» впишите имя пользовательской функции.
- Нажмите кнопку «Параметры» и в открывшемся окне добавьте или отредактируйте описание.
- Нажмите кнопку «OK», затем в окне списка макросов – «Отмена». Описание готово!
Добавление описания на примере функции «Деление»:
Описание функции «Деление» в диалоговом окне Мастера функций «Аргументы функции»:
С помощью окна «Список макросов» можно добавить описание самой функции, а ее аргументам нельзя. Но это можно сделать, используя метод Application.MacroOptions.
Метод Application.MacroOptions
Метод Application.MacroOptions позволяет добавить пользовательской функции описание, назначить сочетание клавиш, указать категорию, добавить описания аргументов и добавить или изменить другие параметры. Давайте рассмотрим возможности этого метода, используемые чаще всего.
Пример кода с методом Application.MacroOptions:
1 2 3 4 5 6 7 |
Sub ИмяПодпрограммы() Application.MacroOptions _ Macro:="ИмяФункции", _ Description:="Описание функции", _ Category:="Название категории", _ ArgumentDescriptions:=Array("Описание 1", "Описание 2", "Описание 3", ...) End Sub |
- ИмяПодпрограммы — любое уникальное имя, подходящее для наименования процедур.
- ИмяФункции — имя функции, параметры которой добавляются или изменяются.
- Описание функции — описание функции, которое добавляется или изменяется.
- Название категории — название категории в которую будет помещена функция. Если параметр Category отсутствует, пользовательская функция будет записана в раздел по умолчанию – «Определенные пользователем». Если указанное Название категории соответствует одному из названий стандартного списка, функция будет записана в него. Если такого Названия категории нет в списке, будет создан новый раздел с этим названием и функция будет помещена в него.
- “Описание 1”, “Описание 2”, “Описание 3”, … — описания аргументов в том порядке, как они расположены в объявлении пользовательской функции.
Эта подпрограмма запускается один раз, после чего ее можно удалить или использовать как шаблон для корректировки параметров других пользовательских функций.
Сейчас с помощью метода Application.MacroOptions попробуем изменить описание пользовательской функции «Деление» и добавить описания аргументов.
1 2 3 4 5 6 |
Sub ИзменениеОписания() Application.MacroOptions _ Macro:="Деление", _ Description:="Описание функции Деление изменено методом Application.MacroOptions", _ ArgumentDescriptions:=Array("- любое числовое значение", "- числовое значение, кроме нуля") End Sub |
После однократного запуска этой подпрограммы получаем следующий результат:
Метод Application.MacroOptions не работает в Личной книге макросов, но и здесь можно найти решение. Добавьте описания к пользовательским функциям и их аргументам в обычной книге Excel, затем экспортируйте модуль с функциями в любой каталог на жестком диске и оттуда импортируйте в Личную книгу макросов. Все описания сохранятся.
Отличная статья
Спасибо, Павел.
Полезная статья, но у Вас там ошибка в функции лишнее „As Variant“
Асхаб, в строке
Function Деление(Делимое As Variant, Делитель As Variant) As Variant
объявляются типы данных двух аргументов и возвращаемого значения функции.
Хотя, так тоже будет работать:
Function Деление(Делимое, Делитель)
типы данных Variant применятся по умолчанию (протестировано в Excel 2016).
Сделаю ремарку для тех, кто попал сразу на эту страницу, поиском: запись вида
Application.MacroOptions _
Macro:=”Деление”, _ и т.д. с символом подчеркивания означает перенос, а на самом деле вся запись делается в одну строку. Иначе возникает синтаксическая ошибка.
Или я чего то еще не знаю?! :-)))
Привет, Николай!
Скопируйте в стандартный модуль код функции «Деление» и код процедуры «ИзменениеОписания». Запустите однократно код «ИзменениеОписания». Вы увидите, что все работает, и мастер функций начнет открывать функцию «Деление» с описанием.
Ага, сам нашел: перенос кода –
https://vremya-ne-zhdet.ru/vba-excel/perenos-koda-i-teksta/
Как сделать в функции с неопределенным количеством аргументов, чтобы в окне где в поля ставятся значения эти аргументы именовались с порядковыми номерами? Как в функции СЦЕПИТЬ текст1 текст2 текст3 и т.д.
Привет, Роман!
Пожалуй, назначить пронумерованные имена аргументам из массива при объявлении функции с неопределенным количеством параметров
не получится. Но есть возможность, если максимальное количество аргументов не велико, объявить их все сразу как необязательные:
Правда, в отличие от применения массива, в мастере функций поля для заполнения перечисленных аргументов тоже отобразятся все сразу.
> Метод Application.MacroOptions не работает в Личной книге макросов, но и здесь можно найти решение.
Почему же не работает? А так?
Application.MacroOptions _
Macro:=”PERSONAL.XLSB!Деление”, _
> Добавьте описания к пользовательским функциям и их аргументам в обычной книге Excel, затем экспортируйте модуль с функциями в любой каталог на жестком диске и оттуда импортируйте в Личную книгу макросов. Все описания сохранятся.
Сохраняются, к сожалению, не все описания. По крайней мере пока не закрыл приложение.
Описания параметров по неизвестной причине видны только до момента выхода из приложения – при повторном открытии приложения сохраняется описание функции, описания параметров – пустые.