Как создать функцию в VBA в Excel

Обновлено: 04.07.2024

Excel предлагает большое количество встроенных функций, которые можно использовать для простого и быстрого выполнения множества различных задач. Но хотя бы раз вы могли почувствовать, что встроенных функций, предоставляемых Excel, недостаточно для выполнения какой-то сложной задачи. В таких случаях вам придется писать собственные функции и делается это с помощью VBA. VBA означает Visual Basic для приложений. Кроме того, всякий раз, когда вам приходится повторно использовать блок кода, рекомендуется создать функцию, чтобы вы могли легко использовать код повторно.

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

Создать функцию VBA

Шаг 1. Откройте Excel и сохраните файл как vbafunction.xlsm. Убедитесь, что вы сохранили файл как рабочий лист Microsoft Excel с поддержкой макросов. В противном случае функция, которую вы собираетесь создать, не будет работать.

Шаг 2. Введите "Форма" в A1, "Сторона1" в B1, "Сторона2" в C1, "Сторона3" в D1, "Площадь" в E1, "Треугольник" в A2, "Прямоугольник" в A3, " Квадрат" формата А4 и "Круг" формата А5. Вы можете отформатировать ячейки A1, B1, C1, D1 и E1 и выделить их жирным шрифтом. Теперь ваш экран будет выглядеть так:

Шаг 3. Нажмите ALT + F11, чтобы открыть редактор Visual Basic.

Шаг 4. Перейдите к Вставка --> Модуль.

Теперь вы получите такой экран:

Это область, в которой вам нужно ввести свои функции.

Введите код функции

Шаг 5. Введите следующий код в отведенное место:

Прямоугольная область функции (высота как удвоенная, ширина как удвоенная) как удвоенная

ПлощадьПрямоугольника = Ширина * Высота

Функциональная областьТреугольник (сторона 1 как двойная, сторона 2 как двойная, сторона 3 как двойная) как двойная

Тусклый p как двойной

p = (Сторона1 + Сторона2 + Сторона3) / 2

ПлощадьТреугольника = Sqr(p * (p - Сторона1) * (p - Сторона2) * (p - Сторона3))

Функция AreaSquare(Side As Double) As Double

ПлощадьКвадрат = Сторона * Сторона

Функция AreaCircle(Радиус как двойной) Как двойной

Площадьокружности = 3,14159 * радиус * радиус

Теперь ваш экран будет выглядеть так:

Шаг 6. Сохраните эти функции, щелкнув значок "Сохранить" или выбрав "Файл" --> "Сохранить vbafunction.xlsm" или нажав CTRL + S.

Шаг 7. Нажмите ALT + F11, чтобы вернуться на лист Excel.

Шаг 8. Введите 5, 6 и 7 в ячейки B2, C2 и D2. Введите 10 и 8 в ячейки B3 и D3. Введите 10 в ячейку B4. Введите 5 в ячейку B5. Теперь ваш экран будет выглядеть так:

Шаг 9. Щелкните ячейку E2, перейдите в строку формул и введите =ar. Когда вы вводите первые несколько строк имени вашей функции, вы можете обнаружить, что ваша функция указана вместе с другими встроенными функциями Excel, например:

Выберите свою функцию из списка или введите полное имя функции как =areaTriangle(B2,C2,D2) и нажмите клавишу Enter. Здесь B2, C2 и D2 — ячейки, содержащие три стороны треугольника. Теперь ячейка E2 будет содержать значение 14,69694.

Шаг 10. Щелкните ячейку E3 и введите формулу =Прямоугольник(B3,C3). Ячейка E3 будет иметь значение 80.

Шаг 11. Щелкните ячейку E4 и введите формулу =площадьКвадрат(B4). Ячейка E4 будет иметь значение 100.

Шаг 12. Щелкните ячейку E5 и введите формулу =площадьокружности(B5). Ячейка E5 будет иметь значение 78,53975. Теперь ваш экран будет выглядеть так:

Здесь вы создали простые математические функции для вычисления площадей различной формы. Точно так же вы можете создавать более сложные функции. Вы даже можете использовать встроенные функции Excel в своих функциях и сделать код пригодным для повторного использования.

Ниже мы рассмотрим программу в Excel VBA, которая создает определяемую пользователем функцию. Excel имеет большой набор функций. В большинстве ситуаций этих функций достаточно для выполнения работы. Если нет, вы можете создать свою собственную функцию под названием «Определяемая пользователем функция» или пользовательскую функцию Excel. Вы можете получить доступ к определяемой пользователем функции точно так же, как и к любой другой функции Excel.

Мы хотим создать функцию SUMEVENNUMBERS, которая находит сумму четных чисел в случайно выбранном диапазоне.

Определяемые пользователем функции необходимо поместить в модуль.

<р>1. Откройте редактор Visual Basic и нажмите «Вставить», «Модуль».

<р>2. Добавьте следующую строку кода:

Наша функция называется SUMEVENNUMBERS. Часть в скобках означает, что мы даем Excel VBA диапазон в качестве входных данных. Мы называем наш диапазон rng, но вы можете использовать любое имя.

<р>3. Затем мы объявляем объект Range и называем его ячейкой.

<р>4. Мы хотим проверить каждую ячейку в случайно выбранном диапазоне (этот диапазон может быть любого размера).В Excel VBA для этого можно использовать цикл For Each Next. Добавьте следующие строки кода:

Для каждой ячейки в rng

Примечание: здесь ячейка выбирается случайным образом, вы можете использовать любое имя.

<р>5. Далее мы проверяем для каждого значения в этом диапазоне, является ли оно четным или нет. Для этого мы используем оператор Mod. Оператор Mod дает остаток от деления. Например, 7 по модулю 2 = 1, потому что 7 разделить на 2 равно 3 с остатком 1. Сказав это, легко проверить, является ли число четным или нет. Только если остаток от деления на 2 равен 0, число четное. Например, 8 mod 2 = 0, потому что 8, разделенное на 2, равно 4 с остатком 0. Добавьте следующий оператор If в цикл For Each Next.

Если ячейка.Value Mod 2 = 0, то

<р>6. Только если это утверждение верно, мы добавляем значение к SUMEVENNUMBERS. Добавьте следующую строку кода в оператор If.

<р>7. Не забудьте завершить функцию (вне цикла).

<р>8. Теперь вы можете использовать эту функцию, как и любую другую функцию Excel, для нахождения суммы четных чисел в случайно выбранном диапазоне.

Молодец! Вот насколько просты пользовательские функции в Excel VBA. Примечание. Эта функция доступна только в этой книге.

У нас также есть функции в VBA, которые полностью встроены в него. Почти все функции Excel можно использовать и в VBA с почти таким же синтаксисом. И если есть какие-либо изменения в синтаксисе, мы сможем увидеть это также, когда мы собираемся использовать функцию VBA. Чтобы объявить функцию в VBA, сначала откройте процедуру Private-End и выберите тип данных в соответствии с функциональной необходимостью.

Оценка, Hadoop, Excel, мобильные приложения, веб-разработка и многое другое.

Визуальный базовый

VBA означает Visual Basic для приложения. VBA обычно представляет собой язык программирования Excel, который встроен в отдельное приложение Microsoft, например Excel или Access, где мы можем автоматизировать несколько задач, которые также называются «МАКРО» в Excel. Приложение Visual Basic поставляется с несколькими командными кнопками и другими функциями, упрощающими автоматизацию для пользователя.

Определяемая пользователем функция VBA (UDF)

Определяемая пользователем функция — это просто функция, которую мы можем создать в VBA для целей автоматизации.

Эта определяемая пользователем функция представляет собой не что иное, как модуль кода, прикрепленный к рабочему листу, где пользователь фактически будет вводить код.

Как использовать функцию VBA в Excel?

В Excel мы можем найти функцию VBA на вкладке «РАЗРАБОТЧИК». По умолчанию в Excel нет этого меню. Поэтому, чтобы отобразить его, нам нужно добавить это меню «РАЗРАБОТЧИК» вручную, выполнив следующие шаги.

Перейдите в меню «Файл». Нажмите на меню «Параметры», которое выделено желтым цветом.

После того, как мы нажмем на эту опцию, мы получим диалоговое окно, показанное ниже.

Выберите параметр «Настроить ленту», и мы получим диалоговое окно, показанное ниже.

В опции настройки ленты мы можем добавить или удалить ленту вручную. Здесь мы видим, что в «Правой части» ленты настройки вкладка «РАЗРАБОТЧИК» не отмечена флажком. Отметьте вкладку «РАЗРАБОТЧИК» и нажмите «ОК».

После того, как вы отметите вкладку «РАЗРАБОТЧИК», она будет добавлена в меню Excel, которое отображается следующим образом.

Теперь мы видим, что вкладка «Разработчик» добавлена и имеет такие функции, как VISUAL BASIC, MACROS и т. д.

Добавление VBA в Excel

В Excel книгу VBA можно легко добавить, выполнив следующие действия.

Откройте книгу Excel.Используйте сочетание клавиш ALT+F11, чтобы открыть редактор Visual Basic, который также называется VBE в Excel, как показано на снимке экрана ниже.

Щелкните правой кнопкой мыши имя своей книги на панели «Проект VBAProject» (в верхнем левом углу окна редактора) и выберите «Вставить» -> «Модуль» в контекстном меню, как показано ниже.

Теперь мы видим, что новый модуль был добавлен как МОДУЛЬ 1 в левой части панели.

В правой части мы можем использовать функцию UDF с помощью VBA.

Функция Visual Basic

В Visual Basic мы будем использовать различные функции. Функция — это не что иное, как набор визуальных основных операторов, заключенных в начальную и конечную функции. Эта функция обычно выполняет задачу, а затем возвращает элементы управления вызывающему коду. Если он возвращает элемент управления, он также возвращает значение для определенного кода.

Функция VBA

Visual basic содержит большое количество встроенных функций, таких как функция сообщения, функция строки и текста, информационная функция, функция преобразования, дата и время, математические и триггерные функции, финансовая функция

Мы увидим наиболее часто используемую функцию VBA, которая указана ниже.

Поле ввода. Отображает диалоговое окно ввода.
Окно сообщения – отображает окно сообщения, т. е. окно предупреждения.
Формат. Применяет формат к определенной строке.
Len – возвращает длину строки.
Заменить – заменяет определенную строку другой строкой.
Is Array – проверяет, является ли предоставленное значение массивом.
Is Date – проверяет, является ли предоставленное выражение форматом даты.
Is Empty – проверяет, является ли конкретный вариант пустым или нет.
Is Null – проверяет, является ли предоставленное значение нулевым или нет.
Дата — возвращает текущую дату.
Месяц – возвращает номер месяца.
Сейчас — возвращает текущую дату и время.
Год – возвращает текущий год.

Примеры функции VBA в Excel

Функция VBA очень проста в использовании. Давайте теперь посмотрим, как использовать функцию VBA в Excel с помощью нескольких примеров.

Сложение двух чисел

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

Откройте книгу Excel. Используйте сочетание клавиш ALT+F11, чтобы открыть редактор VBE. Получаем редактор VBE следующим образом

Как только мы нажмем на модуль, новый модуль будет добавлен на рабочий лист, где мы напишем код функции VBA в правой части страницы «Общие».

Теперь, чтобы получить результат сложения двух чисел, мы должны использовать приведенную ниже кодировку следующим образом

Функция addtwo(arg1, arg2)
addtwo = arg1 + arg2
Конец функции

Здесь в приведенном выше коде мы назвали функцию «addtwo», и код будет отображаться как:

Теперь на листе Excel мы можем использовать функцию «addtwo», чтобы добавить новое число как:

Функция даты

В VBA функция даты — это встроенная функция DATE(), которая возвращает дату и время. Теперь мы будем использовать функцию даты, чтобы получить дату на конкретном листе. В VBA мы можем использовать UDF для получения результата.

Вставьте новый модуль, как мы обсуждали выше в примере 1.

Как только мы нажмем на модуль, новый модуль будет добавлен на рабочий лист, а в правой части страницы «Общие» мы напишем код функции VBA.

Sub todayDate()
Range("A1").Value = Date
End Sub

Здесь в приведенном выше коде мы назвали функцию «todayDate», и кодировка будет отображаться как:

Нажмите кнопку RUN или нажмите F5, чтобы выполнить код

Эта кодировка вернет только текущую дату в упомянутом имени ячейки A1, и после выполнения результат будет показан следующим образом:

Окно сообщений VBA

Visual Basic обычно имеет два окна сообщений

Метод окна сообщений.
Функция окна сообщений.

Метод MsgBox

Метод MSGBOX — это один из языков VBA, в котором он отображает окно msgbox для заданной строки. Обычно он имеет кнопку ok, чтобы пользователь мог увидеть MSGBOX и продолжить выполнение указанной задачи.

Функция MsgBox

Функция MSGBOX, которая обычно возвращает значение, эта функция MSGBOX будет иметь тип вопроса, например "Да" или "Нет" или "Прервать" или "Продолжить".

В приведенных ниже примерах мы увидим как поле MSG, так и следующее.

Метод Msg Box Пример:

Вставьте новый модуль, как мы обсуждали выше в примере 1.

Sub Firstmsg()
MsgBox "Hello"
End Sub

На приведенном ниже снимке экрана мы написали код для отображения поля MSG BOX как Hello.

Нажмите кнопку RUN или нажмите F5, чтобы выполнить код.

Мы получим следующий вывод.

Мы видим сообщение HELLO, которое приходит из окна MSG.

Пример функции окна сообщений:

В этом примере мы увидим, как использовать функцию MSG BOX с помощью vbYesNoCancel

Вставьте новый модуль, как мы обсуждали выше в примере 1.

Как только мы нажмем на модуль, новый модуль будет добавлен на рабочий лист и в правую часть страницы «Общие», где мы напишем код функции VBA.

Sub Firstmsg()
MsgBox "Добро пожаловать, пользователь или гость?", vbYesNoCancel, "Войти?"
Конец подписки

На приведенном ниже снимке экрана мы написали код для отображения поля MSG BOX. Здесь, в функции MSG BOX, мы указали вариант YES или NO, чтобы проверить, является ли конечный пользователь пользователем или гостем.

Нажмите кнопку RUN или нажмите F5, чтобы выполнить код

После выполнения мы получим следующий MSGBOX

Что следует помнить о функциях VBA

В VBA есть много встроенных функций, которые можно использовать в процессе автоматизации.
При применении нескольких функций MACROS убедитесь, что мы назначили конкретную клавишу для ее запуска.
Назовите каждый модуль в VBA, чтобы избежать путаницы при кодировании.

Почему вы должны создать пользовательскую функцию Excel

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

И ниже я перечислил некоторые причины или ситуации, в которых вам нужно использовать пользовательскую функцию.

1. Когда для этого нет функции

Это одна из распространенных причин создания пользовательской функции с помощью VBA, потому что иногда вам нужно что-то вычислить, а для этого нет специальной функции. Я могу привести вам пример подсчета слов в ячейке, и я обнаружил, что UDF может быть идеальным решением.

2. Замена сложной формулы

Если вы работаете с формулами, я уверен, вы знаете, что сложные формулы трудно читать, а иногда и труднее понять другим. Таким образом, пользовательская функция может решить эту проблему, потому что после создания пользовательской функции вам не нужно снова и снова писать эту сложную формулу.

3. Когда вы не хотите использовать подпрограмму SUB

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

Как создать свою первую определяемую пользователем функцию в Excel

Хорошо, смотри. Я разделил весь процесс на три этапа:

Объявление процедуры как функции
Определение его аргументов и их типа данных
Добавить код для расчета желаемого значения

Но позвольте мне дать вам:

Вам нужно создать функцию, которая может возвращать название дня из значения даты. Итак, у нас есть функция, которая возвращает номер дня недели, но не название. Вы поняли, что я говорю? Да?

Итак, давайте выполним следующие шаги, чтобы создать свою первую определяемую пользователем функцию:

Поздравляем! Вы только что создали свою первую определяемую пользователем функцию. Это момент настоящей Радости. Не так ли? Введите «Радость» в разделе комментариев.

Как эта функция работает и возвращает значение в ячейке

Ваша первая пользовательская функция здесь, но дело в том, что вам нужно понять, как она работает. Если говорить простыми словами, это код VBA, но вы использовали его как процедуру-функцию.

Давайте разделим его на три части:

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

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

Поскольку вы указали «InputDate» в качестве аргумента для функции, и когда вы вводите функцию в ячейку и указываете дату, VBA берет это значение даты и передает его текстовой функции, которую вы использовали в коде.

И в примере, который я упомянул выше, в ячейке A1 указана дата 01 января 2019 года.

После этого функция ТЕКСТ преобразует эту дату в день, используя код формата «dddddd», который вы уже упомянули в коде функции. И тот день, который возвращается функцией ТЕКСТ, назначается «myDayName».

Поэтому, если результатом функции ТЕКСТ является вторник, это значение будет присвоено «myDayName».

На этом работа функции заканчивается. «myDayName» — это имя функции, поэтому любое значение, присвоенное «myDayName», будет значением результата, и функция, которую вы вставили на лист, вернет его в ячейку.

Когда вы пишете код для пользовательской функции, вам нужно позаботиться о том, чтобы значение, возвращаемое этим кодом, было присвоено имени функции.

Читайте также:

Как создать функцию в VBA в Excel

Создать функцию VBA

Введите код функции

Как использовать функцию VBA в Excel?

Функция Visual Basic

Примеры функции VBA в Excel

Что следует помнить о функциях VBA

Рекомендуемые статьи

Почему вы должны создать пользовательскую функцию Excel

1. Когда для этого нет функции

2. Замена сложной формулы

3. Когда вы не хотите использовать подпрограмму SUB

Как создать свою первую определяемую пользователем функцию в Excel

Как эта функция работает и возвращает значение в ячейке