Как создать функцию в vba в excel
Обновлено: 05.07.2024
Начнем с функций, которые не имеют аргументов. В качестве примера, можете вспомнить такие стандартные функции как СЕГОДНЯ() или СЛЧИС(). Эти функции не зависят от каких либо входных параметров. Давайте тоже создадим аналогичную функцию. Например функцию, которая будет возвращать нам название текущего листа (нам ведь не нужны никакие параметры в данном случае. Код функции приведен ниже:
Как видите скобки мы оставили пустые, указали наименование функции НазваниеЛиста и задали тип возвращаемых значение As String (Текстовое значение).
Единственный нюанс данной функции состоит в том, что она автоматически не изменит значение, если Вы переименуете лист. К сожалению Excel запускает процесс пересчета когда меняется какой либо аргумент, но тут их нет и функций "не знает" когда пересчитывать значение. Принудительно можно запустить пересчет книги с помощью сочетания клавиш Ctrl + Alt + F9. Чтобы заставить формулу пересчитываться всякий раз, когда происходит изменение на листе, нужно указать еще одно строку кода.
Теперь, если вы измените имя листа, функция будет пересчитываться. Функция также будет пересчитываться при любом редактировании листа.
Создание функции с одним аргументом
В предыдущей статье Создаем свою первую функцию в Excel мы достаточно подробно рассказали о создании функции с одним аргументом. Поэтому приведем еще один пример простой но полезной функции и перейдем к следующему пункту.
Наверняка вы сталкивались с тем, что необходимо написать огромную формулу, проверяющую несколько условий с помощью функций ЕСЛИ. Получалось что-то вроде нескольких вложенных функций ЕСЛИ. Давайте попробуем решить эту задачу путем создания пользовательской функции. Например нам необходимо рассчитать значение премии в зависти от процента выполненного плана. Если план выполнен менее чем 50% нам ничего не платят. Если от 50 до 75 процентов, то дают премию 5 000 рублей. Если от 75 до 90% - 15 000 рублей. От 90 до 100% - 25 000 рублей. И если более 100%, то 50 000 тысяч. Давайте писать код:
Смысл кода думаю ясен, конструкция Select Case проверяет условие и выдает результат если условие истинно.
А теперь применим функцию и посмотрим как из сложной функции мы сделали короткую, а самое главное понятную для других пользователей функцию.
Создание функции с несколькими аргументами
Сейчас научимся создавать функции с несколькими переменными и заодно создадим достаточно полезную для работы функцию. Наверняка у всех была похожая задача. Нужно из ячейки, которая содержит ФИО извлечь, к примеру только имя или отчество. Можно обойтись набором стандартных функций, но сложновато. Подобно функциям листа, вы можете создавать функции в VBA, которые принимают несколько аргументов.
В приведенном ниже коде будет создана функция, которая будет извлекать текст перед указанным разделителем. Он принимает два аргумента - ссылку на ячейку с текстовой строкой и разделитель.
Когда вам необходимо использовать более одного аргумента в функции, то нужно их все перечислить в скобках через запятую. Обратите внимание, что для каждого аргумента вы можете указать тип данных. В приведенном выше примере переменная Текст была объявлена As String (как текст), а переменная Номер объявлена как Long (целое число). Если вы не укажете какой-либо тип данных, VBA считает, что тип данных Variant - этот тип переменных сам изменяет свой тип в зависимости от значения, но потребляет больше ресурсов.
При использовании функций на листе вы можете в качестве аргументов указывать как статичное значение, так и делать ссылки на ячейки.
На картинке ниже, которая демонстрирует работу нашей функции можно увидеть, что в качестве первого параметра (Текст) мы берем ссылку на ячейку, а в качестве второго аргумента вводим число вручную, хотя также могли бы ссылаться на ячейку.
Создание функции с необязательными аргументами
В Excel есть много функций, в которых некоторые аргументы являются необязательными.
Например, всем известная функция ВПР. У нее 3 обязательных аргумента и один необязательный. Он обозначается в квадратных скобках - [интервальный_просмотр].
Если вы не укажете один из обязательных аргументов, ваша функция выдаст ошибку, а если не указать необязательный аргумент функция продолжит работу.
Но это не означает, что необязательные аргументы бесполезны. Они призваны облегчить функцию и использовать значения по умолчанию.
Чтобы создать функцию с необязательными аргументами, необходимо перед названием аргумента указать инструкцию Optional. Существует правило - все необязательные переменные должны идти после обязательных. Нельзя создать функцию, где сначала будет необязательный аргумент, а после обязательные.
Приведем пример функции с необязательными аргументами
Тут хочу обратить внимание на первую строку, а именно на:
Optional обозначает, что переменная необязательная, далее идет наименование аргумента - Прописные, далее при необходимости указывается тип данных. В нашем случае это логическая переменная Boolean. А далее? после знака равно, указывается значение по умолчанию. Если при использовании функции второй аргумент не будет указан, то переменной присвоется значение по умолчанию, в нашем случае False.
Далее в коде вы увидите строку:
Она как раз и проверяет значение необязательного аргумента. Если в качестве аргумента указано значение ИСТИНА (или 1), то функция вернет текст прописными буквами.
Все функции вы можете скачать в файле к этой статье. В следующих статьях поговорим о функциях, которые возвращают массив значений.
Создание функций с бесконечным количеством аргументов
В предыдущих примерах мы создавали функции, в которых в качестве аргументов выступало одно значение. Но есть функции, у которых в качестве переменной выступает массив значений. Примерами таких функций встроенных в Excel служат функции СУММ, ВПР, МАКС, СРЗНАЧ и так далее. Давайте теперь создадим свою похожую функцию.
Далее приведен код функции, которая суммирует все четные значения.
В первой строке данной функции можно увидеть, что переменной Диапазон мы присвоили тип Range (Диапазон As Range). Таким образом, в качестве аргумента функции мы можем использовать диапазон ячеек. Применим эту функцию на рабочем листе Excel.
Как видно из рисунка, мы не перечисляли каждое значение, а использовали диапазон ячеек A2:A11.
Теперь немного разберем код. Мы использовали цикл For Each - Next. Его задача пройтись по каждой ячейке нашего диапазона. Предварительно мы объявили еще одну переменную r типа Range. Она будет хранить значение каждой ячейки.
Далее идет проверка значения ячейки и если оно кратно 2, то мы суммируем результат во временную переменную S.
В конце, после цикла, мы присваиваем функции СуммаЧетных значение просуммированных элементов S.
Создание функций с неопределенным количеством аргументов
Предыдущая функция имеет недостаток - если вы попытаетесь использовать в качестве переменной несмежный диапазон ячеек, то получите ошибку. Иногда требуется более универсальная функция, так как на старте мы можем не знать количество аргументов, которые будут использоваться. Например, всем известная функция СУММ может принимать неограниченное количество аргументов =СУММ( A1 ; B1:B5 ;1;2;3), функция все рано вернет верный результат.
Вы можете создать свою аналогичную функцию в VBA, указав к последнему (или единственному) аргументу ключевое слово ParamArray.
ParamArray - данный модификатор применяется только к последнему аргументу. Аргумент с данным модификатором всегда должен иметь тип данных Variant и всегда является необязательным, ключевое слов Optional не указывается дополнительно
Теперь давайте создадим функцию, которая может принимать произвольное количество аргументов/ А в качестве результата возвращать всю туже сумму нечетных значений.
Данная функция может принимать любое количество аргументов, однако аргументы не могут быть многомерные, это означает, что мы не можем указывать диапазоны, а должны перечислить все значения или все ячейки. Как это показано на рисунке далее.
Что же делать, если необходимо использовать различные диапазоны ячеек, а не перечислять каждый элемент отдельно. Рассмотрим следующий код функции, который реализует эту возможность.
У нас добавился еще один цикл For Each - Next. Т.е. сначала мы проходим по всем аргументам (диапазонам ячеек), а после проходим по всем ячейкам этих диапазонов.
Этот код тоже далек от идеала: если мы будем использовать вместо диапазонов значения, то получим ошибку. Поэтому если необходимо создать идеальную функцию, например как СУММ (она может обработать любые типы аргументов), то придется потрудится и дополнительно проверять тип аргументов. Но об это уже поговорим в другой раз. Перейдем к созданию еще одного типа функций.
Создание функции, возвращающих массив значений
Надеюсь вы знаете, как использовать формулы массивов в Excel. Эти функции вводятся на рабочий лист Excel с помощью сочетания клавиш Ctrl + Shift + Enter. Сейчас мы создадим функцию, которая возвратит массив значений. Например, выведет нам список всех месяцев.
Если мы введем функцию в одну ячейку, то увидим, что результатом будет только Январь. Чтобы понять как работает функция необходимо выделить 12 ячеек по горизонтали, ввести =СписокМесяцев() и нажать сочетание клавиш Ctrl + Shift + Enter.
Если необходимо вывести список месяцев по вертикали, то можно дополнительно использовать функцию =ТРАНСП(СписокМесяцев()).
Возможно покажется, что функции, возвращающие массив достаточно бесполезны. Однако это не так, да они реже используются на рабочем листе Excel, но я надеюсь что они будут незаменимой вещаю в ваших будущих программах на VBA.
Функция написанная на VBA - это код, который выполняет вычисления и возвращает значение (или массив значений). Создав функцию вы можете использовать ее тремя способами:
- В качестве формулы на листе, где она может принимать аргументы и возвращать значения.
- Как часть вашей подпрограммы VBA. В процедуре Sub или внутри других функций.
- В правилах условного форматирования.
Хотя Excel уже содержит более 450 встроенных функций, но их тоже периодически не хватает. Иногда встроенные функции не могут выполнить то, что вы хотите сделать. Иногда для достижения результата необходимо создать огромную и сложную формулу, которая не понятна окружающим. В этом случае вы можете создать пользовательскую функцию, которую легко читать и использовать.
Встроенными функциями можете пользоваться не только вы, но и ваши коллеги. Написанные вами функции будут появляться наряду с другими в диалоговом окне Мастер функций. Возможно вас пугает процесс создания функций, но спешу вас уверить, что это достаточно просто.Обратите внимание, что пользовательские функции, созданные с помощью VBA, как правило значительно медленнее, чем встроенные функции. Следовательно, они лучше всего подходят для ситуаций, когда вы не можете получить результат, используя встроенные функции или вычислений не много и снижение производительности не критично.
В чем отличие процедуры (Sub) от функции (Function)?
Основное отличие в том, что процедура (sub) используется для выполнения набора команд, и не призвана в отличие от функции (function) возвращать значение (или массив значений).
Для демонстрации приведем пример. Например есть ряд чисел от 1 до 100 и необходимо отделить четные от нечетных.
С помощью процедуры (sub) вы можете, к примеру, пройтись по ячейкам и выделить нечетные с помощью заливки. А функцию можно использовать в соседнем столбце и она вернет ИСТИНА или ЛОЖЬ в зависимости от того четное значение или нет. Т.е. вы не сможете изменить цвет заливки с помощью функции на листе.
Создание простой пользовательской функции в VBA
Давайте создадим простую пользовательскую функцию на VBA и посмотрим как там все работает.
Ниже представлен код функции, которая из текста оставляет только цифры, отбрасывая буквенные значения.
Чтобы у вас все заработало, необходимо вставить данный код в модуль книги. Если вы не знаете как это сделать, то начните со статьи Как записать макрос в Excel.
Теперь посмотрим как функция работает, попробуем использовать ее на листе:
Прежде чем разбирать саму функцию отметим 2 приятных момента, которые появились после создания:
- Она стала доступна, как и любая другая встроенная функция (как создать скрытую функцию, расскажем далее).
- Когда вы ввели знак "=" и начинаете вводить имя функции, то Excel выводит все совпадения и показывает не только встроенные функции, но и пользовательские.
Разбираем функцию пошагово
Теперь давайте глубоко погрузимся и посмотрим, как эта функция создавалась. Начинается функция со строки
Слово Function говорит о начале функции, далее идет ее название, в нашем случае Цифры.
- Имя функции не может содержать пробелов. Кроме того, вы не можете назвать функцию, если она сталкивается с именем ссылки на ячейку. Например, вы не можете назвать функцию ABC123, так как это имя также относится к ячейке в листе Excel.
- Вы не должны указывать на свою функцию то же имя, что и у существующей функции. Если вы это сделаете, Excel будет отдавать предпочтение встроенной функции.
- Вы можете использовать символ подчеркивания, если хотите разделить слова. Например, Сумма_Прописью является допустимым именем.
После названия в круглых скобках описываются аргументы функции. По аналогии со встроенными функциями Excel. В нашем случае используется единственный аргумент Текст. После названия аргумента мы указали As String, это означает, что наш аргумент - текстовое значение или ссылка на ячейку, содержащее текстовое значение. Если вы не укажете тип данных, VBA рассмотрит его как Variant (что означает, что вы можете использовать любой тип данных, VBA его определит самостоятельно).
Последняя часть первой строки As Long задает тип данных, которая возвращает функция. В нашем случае функция будет возвращать целые значения. Это также не обязательно.
Вторая и третья строка функции объявляет дополнительные внутренние переменные, которые мы будем использовать.
Переменную i мы буем использовать для перебора символов. А переменную result для хранения промежуточного результата функции.
Задача функции - пройти по всем символам переменной Текст и сохранить только те, что являются цифрами. Поэтому начнем цикл с 1 и до последнего символа.
Len - функция, которая определяет количество символов.
Основная строка функции - это проверка является ли очередной символ текста цифрой и если да - то сохранение его в переменной result
Для этого нам потребуется функция IsNumeric - она возвращает True если текст - число и False в противном случае.
Функция Mid берет из аргумента Текст i-ый символ (значение 1, указывает что функция Mid берет только 1 символ)/
Функция Next - закрывает цикл For тут все понятно.
Этой строкой мы преобразовываем текстовую переменную result, которая содержит все цифры аргумента Текст, в числовое значение. И говорим какой результат должна вывести наша функция Цифры.
Последняя строка кода - End Function. Это обязательная строка кода, которая сообщает VBA, что код функции заканчивается здесь.
В приведенном выше коде описаны различные части типичной пользовательской функции, созданной в VBA. В следующих статьях мы более подробно разберем эти элементы, а также рассмотрим различные способы выполнения функции VBA в Excel.
Функция (Function) отличается от подпрограммы (Sub) тем, что она всегда возвращает какое-либо значение. Если функция размещается в ячейке рабочего листа, то в этой ячейке мы видим значение, которое функция возвратила.
В качестве примера мы создадим функцию, вычисляющую объем цилиндра по формуле: V=π*D²/4*H, где
- V – объем цилиндра;
- π – число Pi;
- D – диаметр основания цилиндра;
- H – высота цилиндра.
Первая функция на VBA Excel
Создайте или откройте файл Excel с расширением .xlsm (Книга Excel с поддержкой макросов). В версиях Excel по 2003 год – с расширением .xls.
Перейдите в редактор VBA, нажав сочетание клавиш «Левая_клавиша_Alt+F11».
Если вы не создавали ранее в этом проекте VBA стандартный программный модуль, нажмите кнопку «Module» во вкладке «Insert» главного меню. То же подменю откроется при нажатии на вторую кнопку после значка Excel на панели инструментов.
Ссылка на модуль появится в проводнике слева. Если модуль создан ранее, дважды кликните по его ссылке в проводнике, и он откроется справа для редактирования.
В открывшемся окне добавления шаблона процедуры выберите «Function», вставьте в поле «Name» название функции «ObyemTsilindra» и нажмите «OK».
Шаблон функции добавится на лист модуля.
Вставьте в скобки через запятую аргументы функции (D, H), а внутрь шаблона следующую строку:
В этой строке, функции (слева) присваивается значение, вычисленное в выражении справа. Выражение повторяет нашу первоначальную формулу вычисления объема цилиндра, где «WorksheetFunction.Pi» – это функция рабочего листа, возвращающая число Pi.
Функция, вычисляющая объем цилиндра по диаметру основания и высоте готова.
Вставка функции в ячейку
Вставить пользовательскую функцию в ячейку можно как вручную: =ObyemTsilindra(Ячейка1;Ячейка2) , так и с помощью мастера функций.
Выберите ячейку, в которую вы хотите вставить формулу, и вызовите матер функций.
В открывшемся окне «Вставка функции» выберите категорию «Определенные пользователем», а в открывшемся списке – функцию «ObyemTsilindra» и нажмите «OK».
В окне «Аргументы функции» в полях аргументов выберите соответствующие ячейки, кликнув по ним на рабочем листе, и нажмите «OK».
Обращаться с пользовательской функцией на рабочем листе можно так же, как с любой другой формулой, например, копировать протягиванием.
Читайте также: