В этом руководстве объясняется, как создавать пользовательские функции в VBA.
VBA состоит из подпроцедур и функциональных процедур. Функциональные процедуры возвращают значение и могут быть вызваны подпроцедурами или могут использоваться в листе Excel, где значение, создаваемое функцией, возвращается в лист Excel. В Excel, конечно же, есть ряд встроенных функций, таких как функция суммы или функция If. Функциональные процедуры, которые вы пишете на VBA, используются аналогично встроенным функциям и известны как пользовательские функции (UDF).
Зачем создавать UDF?
Требуемая функция отсутствует
Одна из основных причин создания UDF в Excel заключается в том, что не существует встроенной функции, которая бы выполняла эту задачу за вас. Написание собственной функции на VBA обычно является наиболее эффективным способом решения проблемы. Функция ниже преобразует значение из килограммов в фунты, где переменный параметр (dblKilo) используется для получения значения в килограммах для выполнения расчета.
Замена подпрограммы (макрос)
Вы можете написать подпроцедуру (макрос) для решения задачи за вас, но подпроцедуры не возвращают значение и не являются динамическими - другими словами, если значения в вашем листе изменяются, вам нужно будет повторно запустить макрос, чтобы вычисления в макросе обновляли ваши данные. Подпроцедура ниже также преобразует наши килограммы в фунты, но каждый раз, когда данные изменяются в A1, вам нужно будет повторно запускать макрос, чтобы обновить результат.
Заменить формулу
У вас может быть действительно сложная формула в Excel, которую вам нужно использовать многократно - включение формулы в код VBA упрощает чтение и понимание - а также, возможно, устраняет место для ошибки пользователя при вводе формулы.
Создание UDF
Чтобы создать UDF, сначала добавьте модуль либо в свою книгу Excel, либо, если у вас есть книга личных макросов, вы можете либо использовать существующий модуль, либо добавить новый. Для этого вам необходимо находиться в редакторе Visual Basic (VBE). Чтобы попасть в VBE, нажмите ALT + F11 или щелкните параметр Visual Basic на вкладке «Разработчик» на ленте.
КОНЧИК: Если на вашей ленте не включена вкладка Разработчик, перейдите в Файл, Параметры и нажмите на Настроить ленту. Убедитесь, что установлен флажок Разработчик, и нажмите ОК.
Чтобы вставить новый модуль, выберите проект VBA, в который вы хотите вставить модуль (либо проект VBA для текущей книги, с которой вы работаете, либо личную книгу макросов), щелкните значок Вставлять Меню и щелкните Модуль
После того, как вы создали свой модуль, вы можете приступить к созданию своей UDF.
Все UDF начинаются с функции, а затем имени UDF. Функции могут быть частными или общедоступными, но обычно вы хотите, чтобы UDF была общедоступной, чтобы они отображались в диалоговом окне «Вставить функцию» в Excel (см. Использование функции из листа Excel далее в этой статье). Если вы не поставите ключевое слово Private перед функцией, функция автоматически станет общедоступной.
| 123 | Функция TestFunction1 (intA как целое число) как целое числоTestFunction1 = intA * 7Конечная функция |
Вышеупомянутая функция имеет единственный аргумент (intA). Вы можете создать функцию с несколькими аргументами
| 123 | Функция TestFunction2 (intA как целое число, intB как целое число, intC как целое число) как целое числоTestFunction2 = (intA * intB) + intCКонечная функция |
Вы также можете создать функцию с необязательными аргументами. Если аргумент опущен, вы можете установить значение по умолчанию для аргумента в функции.
| 123 | Функция TestFunction3 (intA как целое число, intB как целое число, необязательно intC как целое число = 10) как целое числоTestFunction3 = (intA * intB) + intCКонечная функция |
Использование функции из листа Excel
Созданные вами функции по умолчанию появятся в вашем списке функций в разделе «Пользовательские» списка функций.
Нажать на FX для отображения диалогового окна "Вставить функцию".
Выбирать Определяемые пользователем из списка категорий
Выберите нужную функцию из доступных Пользовательские функции.
В качестве альтернативы, когда вы начнете писать свою функцию в Excel, функция должна появиться в раскрывающемся списке функций.
Сохранение функций в файле Excel
Поскольку функции написаны в коде VBA, очевидно, что код должен быть доступен для рабочей книги, чтобы его можно было использовать на листе Excel. Вы можете сохранить свои функции в рабочей книге, в которой вы их используете, или можете сохранить их в своей личной книге макросов. Ваша личная книга макросов - это скрытый файл, который доступен всякий раз, когда открыт Excel, и поэтому доступен для использования в любой книге в Excel. Обычно он создается, когда вы записываете макрос и выбираете вариант сохранения макроса в личной книге макросов.
Если вы хотите сохранить свои функции в рабочей книге, над которой вы работаете, вам необходимо убедиться, что при сохранении книги она сохраняется как «Книга с поддержкой макросов‘Или xlsm файл.
