Создание функции vba: Создание пользовательских функций в Excel

Содержание

Создание пользовательских функций в Excel

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Еще…Меньше

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

Пользовательские функции (как и макросы) записываются на языке программирования Visual Basic для приложений (VBA). Они отличаются от макросов двумя вещами. Во-первых, в них используются процедуры Function, а не Sub. Это значит, что они начинаются с оператора Function, а не Sub, и заканчиваются оператором End Function, а не End Sub. Во-вторых, они выполняют различные вычисления, а не действия. Некоторые операторы (например, предназначенные для выбора и форматирования диапазонов) исключаются из пользовательских функций. Из этой статьи вы узнаете, как создавать и применять пользовательские функции. Для создания функций и макросов используется редактор Visual Basic (VBE), который открывается в отдельном окне.

Предположим, что ваша компания предоставляет скидку в размере 10 % клиентам, заказавшим более 100 единиц товара. Ниже мы объясним, как создать функцию для расчета такой скидки.

В примере ниже показана форма заказа, в которой перечислены товары, их количество и цена, скидка (если она предоставляется) и итоговая стоимость.

Чтобы создать пользовательскую функцию DISCOUNT в этой книге, сделайте следующее:

Нажмите клавиши ALT+F11 (или FN+ALT+F11 на Mac), чтобы открыть редактор Visual Basic, а затем щелкните Insert (Вставка) > Module (Модуль). В правой части редактора Visual Basic появится окно нового модуля.

Скопируйте указанный ниже код и вставьте его в новый модуль.


Function DISCOUNT(quantity, price)
   If quantity >=100 Then
     DISCOUNT = quantity * price * 0.1
   Else
     DISCOUNT = 0
   End If
 
 DISCOUNT = Application.Round(Discount, 2)
End Function

Примечание: Чтобы код было более удобно читать, можно добавлять отступы строк с помощью клавиши TAB. Отступы необязательны и не влияют на выполнение кода. Если добавить отступ, редактор Visual Basic автоматически вставит его и для следующей строки. Чтобы сдвинуть строку на один знак табуляции влево, нажмите SHIFT+TAB.

Теперь вы готовы использовать новую функцию DISCOUNT. Закройте редактор Visual Basic, выделите ячейку G7 и введите следующий код:

=DISCOUNT(D7;E7)

Excel вычислит 10%-ю скидку для 200 единиц по цене 47,50 ₽ и вернет 950,00 ₽.

В первой строке кода VBA функция DISCOUNT(quantity, price) указывает, что функции DISCOUNT требуется два аргумента: quantity (количество) и price (цена). При вызове функции в ячейке листа необходимо указать эти два аргумента. В формуле =DISCOUNT(D7;E7) аргумент quantity имеет значение D7, а аргумент price — значение E7. Если скопировать формулу в ячейки G8:G13, вы получите указанные ниже результаты.

Рассмотрим, как Excel обрабатывает эту функцию. При нажатии клавиши ВВОД Excel ищет имя DISCOUNT в текущей книге и определяет, что это пользовательская функция в модуле VBA. Имена аргументов, заключенные в скобки (quantity и price), представляют собой заполнители для значений, на основе которых вычисляется скидка.

Оператор If в следующем блоке кода проверяет аргумент quantity и сравнивает количество проданных товаров со значением 100:


If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Если количество проданных товаров не меньше 100, VBA выполняет следующую инструкцию, которая перемножает значения quantity и price, а затем умножает результат на 0,1:

Discount = quantity * price * 0.1

Результат хранится в виде переменной Discount. Оператор VBA, который хранит значение в переменной, называется оператором назначения, так как он вычисляет выражение справа от знака равенства и назначает результат имени переменной слева от него. Так как переменная Discount называется так же, как и процедура функции, значение, хранящееся в переменной, возвращается в формулу листа, из которой была вызвана функция DISCOUNT.

Если значение quantity меньше 100, VBA выполняет следующий оператор:

Discount = 0

Наконец, следующий оператор округляет значение, назначенное переменной Discount, до двух дробных разрядов:

Discount = Application. Round(Discount, 2)

В VBA нет функции округления, но она есть в Excel. Чтобы использовать округление в этом операторе, необходимо указать VBA, что метод (функцию) Round следует искать в объекте Application (Excel). Для этого добавьте слово Application перед словом Round. Используйте этот синтаксис каждый раз, когда нужно получить доступ к функции Excel из модуля VBA.

Пользовательские функции должны начинаться с оператора Function и заканчиваться оператором End Function. Помимо названия функции, оператор Function обычно включает один или несколько аргументов. Однако вы можете создать функцию без аргументов. В Excel доступно несколько встроенных функций (например, СЛЧИС и ТДАТА), в которых нет аргументов.

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

Количество ключевых слов VBA, которые можно использовать в пользовательских функциях, меньше числа, используемого в макросах. Настраиваемые функции не могут выполнять другие задачи, кроме возврата значения в формулу на этом или в выражение, используемом в другом макросе или функции VBA. Например, пользовательские функции не могут изменять размер окна, редактировать формулу в ячейке, а также изменять шрифт, цвет или узор текста в ячейке. Если в процедуру функции включить такой код действия, функция возвращает #VALUE! ошибку «#ВЫЧИС!».

Единственное действие, которое может выполнять процедура функции (кроме вычислений), — это отображение диалогового окна. Чтобы получить значение от пользователя, выполняющего функцию, можно использовать в ней оператор InputBox. Кроме того, с помощью оператора MsgBox можно выводить сведения для пользователей. Вы также можете использовать настраиваемые диалоговые окна (UserForms), но эта тема выходит за рамки данной статьи.

Даже простые макросы и пользовательские функции может быть сложно понять. Чтобы сделать эту задачу проще, добавьте комментарии с пояснениями. Для этого нужно ввести перед текстом апостроф. Например, ниже показана функция DISCOUNT с комментариями. Благодаря подобным комментариями и вам, и другим будет впоследствии проще работать с кодом VBA. Так, код будет легче понять, если потребуется внести в него изменения.

Апостроф указывает приложению Excel на то, что следует игнорировать всю строку справа от него, поэтому вы можете добавлять комментарии в отдельных строках или в правой части строк, содержащих код VBA. Советуем начинать длинный блок кода с комментария, в котором объясняется его назначение, а затем использовать встроенные комментарии для документирования отдельных операторов.

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

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

Для использования настраиваемой функции должна быть открыта книга, содержащая модуль, в котором она была создана. Если книга не открыта, вы получите #NAME? при попытке использования функции. Если вы ссылались на функцию в другой книге, ее имя должно предшествовать названию книги, в которой она находится. Например, при создании функции DISCOUNT в книге Personal.xlsb и вызове ее из другой книги необходимо ввести =personal.xlsb!discount(),а не просто =discount().

Чтобы вставить пользовательскую функцию быстрее (и избежать ошибок), ее можно выбрать в диалоговом окне «Вставка функции». Пользовательские функции доступны в категории «Определенные пользователем»:

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

Создав нужные функции, выберите Файл > Сохранить как.

В Excel 2007 нажмите кнопку Microsoft Office, а затем щелкните Сохранить как.

В диалоговом окне Сохранить как откройте раскрывающийся список Тип файла и выберите значение Надстройка Excel. Сохраните книгу с запоминающимся именем, таким как MyFunctions, в папке AddIns. Она будет автоматически предложена в диалоговом окне Сохранить как, поэтому вам потребуется только принять расположение, используемое по умолчанию.

Сохранив книгу, выберите Файл > Параметры Excel.

В Excel 2007 нажмите кнопку Microsoft Office и щелкните Параметры Excel.

В диалоговом окне Параметры Excel выберите категорию Надстройки.

В раскрывающемся списке Управление выберите Надстройки Excel. Затем нажмите кнопку Перейти.

org/ListItem»>
В диалоговом окне Надстройки установите флажок рядом с именем книги, как показано ниже.

Создав нужные функции, выберите Файл > Сохранить как.

В диалоговом окне Сохранить как откройте раскрывающийся список Тип файла и выберите значение Надстройка Excel. Сохраните книгу с запоминающимся именем, таким как MyFunctions.

org/ListItem»>
Сохранив книгу, выберите Сервис > Надстройки Excel.

В диалоговом окне Надстройки нажмите кнопку «Обзор», найдите свою надстройку, нажмите кнопку Открыть, а затем установите флажок рядом с надстройкой в поле Доступные надстройки.

После этого пользовательские функции будут доступны при каждом запуске Excel. Если вы хотите добавить его в библиотеку функций, вернимся в Visual Basic редактора. Если вы заглянуть в Visual Basic редактора Project проводника под заголовком VBAProject, вы увидите модуль с именем файла надстройки. У надстройки будет расширение XLAM.

Дважды щелкните модуль в Project Explorer, чтобы вывести код функций. Чтобы добавить новую функцию, установите точку вставки после оператора End Function, который завершает последнюю функцию в окне кода, и начните ввод. Вы можете создать любое количество функций, и они будут всегда доступны в категории «Определенные пользователем» диалогового окна Вставка функции.

Эта статья основана на главе книги Microsoft Office Excel 2007 Inside Out, написанной Марком Доджем (Mark Dodge) и Крейгом Стинсоном (Craig Stinson). В нее были добавлены сведения, относящиеся к более поздним версиям Excel.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

В Excel более 450 функций, и некоторые из них весьма полезны в вашей повседневной работе.

Но… Excel дает вам возможность создавать пользовательские функции с помощью VBA.

Да — да, вы не ослышались. Пользовательская функция.

И я могу сказать со 100%-ой уверенностью, что каждый начинающий пользователь VBA хочет научиться создавать пользовательские функции. Не так ли? Кивните, если вы один из них.

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

… так что оставайтесь со мной, вы станете рок-звездой VBA в ближайшие пару минут.

Содержание

Зачем вам создавать пользовательскую функцию Excel?
Как создать свою первую пользовательскую функцию в Excel
Как эта функция работает и возвращает значение в ячейке
Как улучшить пользовательскую функцию?
Как использовать пользовательскую функцию VBA
Различные способы создания пользовательской функции VBA
Область действия определяемой пользователем функции
Ограничения пользовательской функции
Заключение

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

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

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

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

1. Когда нет подходящей функции

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

Например, подсчет слов в ячейке, в этом случае пользовательская функция — идеальное решение.

Function MyWordCount(rng As Range) As Integer
MyWordCount = UBound(Split(rng.Value, " "), 1) + 1
End Function

2. Заменить сложную формулу

Если вы работаете с формулами, то вы знаете, что сложные формулы бывает трудно читать самому, а другим пользователем еще сложнее.

Пользовательская функция может решить эту проблему.

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

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

Хотя вы можете использовать код VBA для выполнения расчетов, но коды VBA не динамические.

Вам нужно снова запустить этот код, если вы хотите обновить свой расчет.

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

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

Обычно для создания функции VBA необходимо выполнить следующие шаги:

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

Позвольте мне привести вам простой пример.

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

Да, у нас есть функция, которая возвращает номер дня недели, но не название.

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

Прежде всего, откройте редактор VB с помощью сочетания клавиш ALT + F11 или перейдите на вкладку «Разработчик» и нажмите кнопку «Visual Basic».

Вставка модуля: щелкните правой кнопкой мыши в окне проекта VBA, затем перейдите к Insert и после этого нажмите «Module».

Следующим шагом является определение имени для функции, здесь я использую «myDayName». Поэтому вы должны написать «Function mydayName».

Почему «Function» перед именем?
Поскольку вы создаете функцию VBA, то используете слово «Function». Excel понимает, что этот код следует рассматривать как функцию

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

Здесь InputDate — имя аргумента, а date — его тип данных. Всегда лучше определить тип данных для аргумента.

Теперь закройте скобки и напишите «As String». Здесь вы определяете тип данных результата, возвращаемого функцией. Так как вы хотите, чтобы название дня недели было текстовым, его тип данных должен быть «String».

В конце нажмите ENTER.

На этом этапе имя вашей функции, ее аргумент, тип данных аргумента и тип данных функции определены, и у вы должны видеть что-то вроде этого в вашем модуле:

Теперь между «Function» и «End Function» вам нужно сделать расчет.

В Excel есть функция рабочего листа под названием «Text», давайте используем ее здесь. Для этого вам нужно написать следующий код:

myDayName = WorksheetFunction.Text (InputDate, "dddddd")

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

Теперь закройте редактор VB и вернитесь к рабочему листу.

В ячейке B2 введите «= myDayName (A2)», нажмите Enter, и у вы увидите название дня недели.

Поздравляю! Вы только что создали свою первую пользовательскую функцию!

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

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

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

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

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

Давайте чуть медленнее:

Есть код для функции, которую мы создали, и вы знаете, что в этом коде. Правильно?

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

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

Как улучшить пользовательскую функцию?

Вы уже знаете, как создать пользовательскую функцию VBA. Но…

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

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

Могут быть и другие проблемы, но я уверен, что вы поняли, о чем я.

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

Прежде всего, вам нужно изменить тип данных аргумента и использовать:

InputDate As Variant

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

Далее нам нужно использовать оператор VBA IF, чтобы проверить InputDate для некоторых условий. Первое условие — пустая ячейка или нет. И для этого вам нужно использовать следующий код:

If InputDate = "" Then
myDayName = ""

Функция ничего не будет возвращать, если указанная вами ячейка будет пустой. Одна проблема решена, давайте перейдем к следующей.

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

If IsDate (InputDate) = False Then
myDateName = ""

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

Таким образом, после добавления вышеуказанных условий код будет выглядеть так:

Функция myDayName (InputDate As Variant) в виде строки

Function myDayName(InputDate As Variant) As String
If InputDate = "" Then
myDayName = ""
Else
If IsDate(InputDate) = False Then
myDateName = ""
Else
myDayName = WorksheetFunction.Text(InputDate, "dddddd")
End If
End If
End Function

Я думаю, что вы можно внести еще некоторые изменения в эту функцию, но я уверен, что суть вы уловили.

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

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

Итак, давайте начнем.

1. На рабочем листе

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

Вы также можете ввести пользовательскую функцию из библиотеки функций.

Перейдите на вкладку «Формулы» ➜ «Вставить функцию» ➜ «Определенные пользователем».

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

2. Использование в других подпроцедурах и функциях

Вы также можете использовать функцию в других функциях или в процедуре «Sub».

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

Sub todayDay()
MsgBox "Сегодня " & myDayName(Date)
End Sub

3. Доступ к функциям из другой книги

Если у вас есть ПФF в одной рабочей книге и вы хотите использовать его в другой или во всех рабочих книгах, вы делаете это путем создания надстройки для нее. Выполните следующие простые шаги:

Прежде всего, вам нужно сохранить файл (в котором у вас есть код пользовательской функции) в виде надстройки.
Для этого перейдите на вкладку «Файл» ➜ «Сохранить как» ➜ «Надстройки Excel (.xalm)».

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

Теперь вы можете использовать все свои функции VBA в любой книге.

Различные способы создания пользовательской функции VBA

Когда мы используем встроенные функции, они имеют разные типы аргументов. В этом разделе руководства вы узнаете, как создать ПФ с аргументами другого типа.

без каких-либо аргументов
только с одним аргументом
с несколькими аргументами
использование массива в качестве аргумента

1. Без каких-либо аргументов

Помните есть такие функции, как СЕЙЧАС и СЕГОДНЯ, в которых вам не нужно вводить какие-либо аргументы? Вы можете создать пользовательскую функцию, в которой вам тоже не нужно вводить аргумент. Сделаем это на примере:

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

Вот код:

Function myPath() As String
Dim myLocation As String
Dim myName As String
myLocation = ActiveWorkbook.FullName
myName = ActiveWorkbook.Name
If myLocation = myName Then
myPath = "Файл еще не сохранен."
Else
myPath = myLocation
End If
End Function

Эта функция возвращает путь к месту, где хранится текущий файл, и, если рабочая книга нигде не сохранена, будет отображаться сообщение «Файл еще не сохранен».

Теперь, если вы обратите пристальное внимание на код этой функции, вы не увидите ни одного аргумента (в скобках). Вы определяете тип данных для результата функции. Основное правило создания функции без аргументов — это код, в который вам не нужно ничего вводить. Проще говоря, значение, которое вы хотите получить взамен функции, должно вычисляться автоматически.

Этот код «ActiveWorkbook.FullName» возвращает местоположение файла, а этот «ActiveWorkbook.Name» возвращает имя. Так что здесь вам не нужно ничего вводить.

2. Только с одним аргументом

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

Это функция нужна для извлечения URL-адреса из гиперссылки.

Function giveMeURL(rng As Range) As String
On Error Resume Next
giveMeURL = rng.Hyperlinks(1).Address
End Function

В этой функции у вас есть только один аргумент.

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

Теперь в этой функции основная работа выполняется:

rng.Hyperlinks (1) .Address

rng - это то, что вам нужно указать.

3. С несколькими аргументами

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

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

Итак, вот функция:

Function removeFirstC(rng As String, cnt As Long) As String
removeFirstC = Right(rng, Len(rng) - cnt)
End Function

Итак, посмотрим:

В этой функции у вас два аргумента:

rng: в этом аргументе вам нужно указать ячейку, из которой вы хотите удалить символ текста.
cnt: а в этом аргументе вам нужно указать количество символов для удаления (если вы хотите удалить более одного символа из текста).

Когда вы вводите ее в ячейку, она работает примерно так:

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

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

Но можно сделать этот аргумент необязательным, чтобы он принимал значение по умолчанию. Чтобы сделать аргумент необязательным, вам просто нужно добавить «Optional» перед ним.

4. Использование массива в качестве аргумента

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

Function addNumbers(CellRef As Range)
Dim Cell As Range
For Each Cell In CellRef
If IsNumeric(Cell.Value) = True Then
Result = Result + Cell.Value
End If
Next Cell
addNumbers = Result
End Function

В приведенном выше коде функции мы использовали весь диапазон A1: A10 вместо одного значения или ссылки на ячейку.

Используем цикл FOR EACH, он проверяет каждую ячейку диапазона и суммирует значение, если в ячейке есть число.

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

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

1. Public

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

Функция является Public по умолчанию, если вы не делаете ее Private. Во всех приведенных нами примерах все функции общедоступны.

2. Private

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

Допустим, если у вас есть функция в «Module1», вы можете использовать ее только в процедурах, которые вы используете в «Module1». И она не появится в списке функций на рабочем листе (когда вы используете знак = и пытаетесь ввести имя), но вы все равно можете использовать ее, введя ее название полностью и указав аргументы.

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

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

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

Есть ли разница между встроенной функцией и пользовательской функцией?

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

Медленнее, чем встроенная. Если вы сравните скорость встроенных функций и функции VBA, вы обнаружите, что первые быстрее. Причина в том, что встроенные функции написаны с использованием C ++ или FORTRAN.
Трудно обмениваться файлами. Мы часто обмениваемся файлами по электронной почте и в облаке, поэтому, если вы используете какую-либо из пользовательских функций, вам необходимо поделиться этим файлом в формате «xlam», чтобы другой человек также мог использовать вашу пользовательскую функцию.

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

Заключение

Создать пользовательскую функцию просто.

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

В конце добавьте код, чтобы вычислить значение, которое вы хотите получить от функции.

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

Создание пользовательских функций в Excel

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

Пользовательские функции, такие как макросы, используют язык программирования Visual Basic для приложений (VBA) . Они отличаются от макросов двумя важными способами. Во-первых, они используют процедуры Function вместо процедур Sub . То есть они начинаются с оператора Function вместо оператора Sub и заканчиваются End Function вместо End Sub . Во-вторых, они выполняют расчеты, а не действия. Определенные виды операторов, например операторы, которые выбирают и форматируют диапазоны, исключаются из пользовательских функций. В этой статье вы узнаете, как создавать и использовать пользовательские функции. Для создания функций и макросов вы работаете с Редактор Visual Basic (VBE) , который открывается в новом окне отдельно от Excel.

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

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

Чтобы создать пользовательскую функцию СКИДКИ в этой книге, выполните следующие действия:

Нажмите Alt+F11 , чтобы открыть редактор Visual Basic (на Mac нажмите FN+ALT+F11 ), а затем нажмите Вставить > Модуль . Окно нового модуля появится в правой части редактора Visual Basic.

Скопируйте и вставьте следующий код в новый модуль.

Функция СКИДКА(количество, цена)
   Если количество >=100 Тогда
     СКИДКА = количество * цена * 0,1
   Еще
     СКИДКА = 0
   Конец, если
 
 СКИДКА = Заявка. Раунд(Скидка, 2)
Конечная функция

Примечание. Чтобы сделать код более читаемым, вы можете использовать клавишу Tab для отступа строк. Отступы нужны только вам и не являются обязательными, так как код будет работать как с ними, так и без них. После ввода строки с отступом редактор Visual Basic предполагает, что следующая строка будет иметь такой же отступ. Чтобы сдвинуться (то есть влево) на один символ табуляции, нажмите Shift+Tab .

Теперь вы готовы использовать новую функцию СКИДКИ. Закройте редактор Visual Basic, выберите ячейку G7 и введите следующее:

=СКИДКА(D7,E7)

Excel вычисляет 10-процентную скидку на 200 единиц по цене 47,50 долларов США за единицу и возвращает 950,00 долларов США.

В первой строке вашего кода VBA, функция СКИДКА (количество, цена), вы указали, что функция СКИДКА требует двух аргументов: количество и цена . Когда вы вызываете функцию в ячейке рабочего листа, вы должны включить эти два аргумента. В формуле =СКИДКА(D7,E7) D7 — это количество аргумент, а E7 — цена аргумент. Теперь вы можете скопировать формулу СКИДКИ в G8:G13, чтобы получить результаты, показанные ниже.

Рассмотрим, как Excel интерпретирует эту процедуру функции. Когда вы нажимаете Enter , Excel ищет имя СКИДКА в текущей книге и обнаруживает, что это пользовательская функция в модуле VBA. Имена аргументов, заключенные в круглые скобки, количество и цена , являются заполнителями для значений, на которых основан расчет скидки.

Оператор If в следующем блоке кода проверяет аргумент количество и определяет, больше или равно ли количество проданных товаров 100:

Если количество >= 100 Тогда
 СКИДКА = количество * цена * 0,1
Еще
 СКИДКА = 0
Конец, если

Если количество проданных товаров больше или равно 100, VBA выполняет следующую инструкцию, которая умножает значение количество на цена значение, а затем умножает результат на 0,1:

Скидка = количество * цена * 0,1

Результат сохраняется как переменная Скидка . Оператор VBA, который сохраняет значение в переменной, называется оператором присваивания , потому что он оценивает выражение справа от знака равенства и присваивает результат имени переменной слева. Поскольку переменная Скидка имеет то же имя, что и процедура функции, значение, хранящееся в переменной, возвращается в формулу рабочего листа, вызвавшую функцию СКИДКА.

Если количество меньше 100, VBA выполняет следующую инструкцию:

Скидка = 0

Наконец, следующий оператор округляет значение, присвоенное переменной Discount , до двух знаков после запятой:

Скидка = Заявка.Раунд(Скидка, 2)

В VBA нет функции ОКРУГЛ, но в Excel она есть. Поэтому, чтобы использовать ROUND в этом операторе, вы указываете VBA искать метод (функцию) Round в объекте Application (Excel). Вы делаете это, добавляя слово Приложение перед словом Раунд. Используйте этот синтаксис всякий раз, когда вам нужно получить доступ к функции Excel из модуля VBA.

Пользовательская функция должна начинаться с оператора Function и заканчиваться оператором End Function. В дополнение к имени функции оператор Function обычно указывает один или несколько аргументов. Однако вы можете создать функцию без аргументов. В Excel есть несколько встроенных функций — например, СЛУЧАЙ и СЕЙЧАС, — которые не используют аргументы.

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

Количество ключевых слов VBA, которые вы можете использовать в пользовательских функциях, меньше, чем количество, которое вы можете использовать в макросах. Пользовательским функциям не разрешено делать ничего, кроме возврата значения в формулу на листе или в выражение, используемое в другом макросе или функции VBA. Например, пользовательские функции не могут изменять размер окон, редактировать формулу в ячейке или изменять параметры шрифта, цвета или узора для текста в ячейке. Если вы включаете код «действия» такого типа в процедуру функции, функция возвращает ошибку #ЗНАЧ! ошибка.

Единственное действие, которое может выполнять функциональная процедура (помимо выполнения вычислений), — это отображение диалогового окна. Вы можете использовать оператор InputBox в пользовательской функции как средство получения ввода от пользователя, выполняющего функцию. Вы можете использовать оператор MsgBox как средство передачи информации пользователю. Вы также можете использовать настраиваемые диалоговые окна или UserForms , но это выходит за рамки данного введения.

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

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

Еще один способ задокументировать макросы и пользовательские функции — дать им описательные имена. Например, вместо имени макроса Labels , вы можете назвать его MonthLabels , чтобы более конкретно описать цель, которой служит макрос. Использование описательных имен для макросов и пользовательских функций особенно полезно, когда вы создали много процедур, особенно если вы создаете процедуры, которые имеют схожие, но не идентичные цели.

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

Чтобы использовать пользовательскую функцию, должна быть открыта рабочая книга, содержащая модуль, в котором вы создали эту функцию. Если эта книга не открыта, вы получите #NAME? ошибка при попытке использовать функцию. Если вы ссылаетесь на функцию в другой рабочей книге, перед именем функции необходимо указать имя рабочей книги, в которой находится функция. Например, если вы создаете функцию с именем СКИДКА в книге с именем Personal.xlsb и вызываете эту функцию из другой книги, вы должны ввести =personal.xlsb!discount() , а не просто =discount() .

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

Более простой способ сделать ваши пользовательские функции доступными в любое время — сохранить их в отдельной книге, а затем сохранить эту книгу как надстройку. Затем вы можете сделать надстройку доступной при каждом запуске Excel. Вот как это сделать:

После создания необходимых функций нажмите Файл > Сохранить как .
В Excel 2007 нажмите кнопку Microsoft Office и выберите Сохранить как
В диалоговом окне Сохранить как откройте раскрывающийся список Сохранить как тип и выберите Надстройка Excel . Сохраните книгу под узнаваемым именем, например MyFunctions , в папке AddIns . В диалоговом окне «Сохранить как » будет предложена эта папка, поэтому все, что вам нужно сделать, это принять расположение по умолчанию.
После сохранения книги щелкните Файл > Параметры Excel .
В Excel 2007 щелкните значок Кнопка Microsoft Office и щелкните Параметры Excel .
В диалоговом окне Параметры Excel выберите категорию Надстройки .
В раскрывающемся списке Управление выберите Надстройки Excel . Затем нажмите кнопку Перейти .
В диалоговом окне Add-Ins установите флажок рядом с именем, которое вы использовали для сохранения книги, как показано ниже.

После создания необходимых функций нажмите Файл > Сохранить как .
В диалоговом окне Сохранить как откройте раскрывающийся список Сохранить как тип и выберите Надстройка Excel . Сохраните книгу под узнаваемым именем, например MyFunctions .
После сохранения книги щелкните Инструменты > Надстройки Excel .
В диалоговом окне Надстройки нажмите кнопку Обзор, чтобы найти надстройку, нажмите Открыть , затем установите флажок рядом с надстройкой в поле Доступные надстройки .

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

Двойной щелчок по этому модулю в Project Explorer приводит к тому, что редактор Visual Basic отображает код вашей функции. Чтобы добавить новую функцию, поместите точку ввода после оператора End Function, завершающего последнюю функцию в окне кода, и начните вводить текст. Таким образом вы можете создать столько функций, сколько вам нужно, и они всегда будут доступны в категории «Определяемые пользователем» в диалоговом окне «Вставить функцию».

Этот контент был первоначально создан Марком Доджем и Крейгом Стинсоном как часть их книги Microsoft Office Excel 2007 наизнанку . С тех пор он был обновлен, чтобы применяться и к более новым версиям Excel.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

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

Авторы: Ken Bluttman и

Обновлено: 07. 01.2022

Из книги: Формулы и функции Excel для чайников

Формулы и функции Excel для чайников

Изучить книгу Купить на Amazon

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

Написание кода VBA доступно не всем. Но тем не менее, вот короткий и милый пример. Если вы можете победить это, вы можете узнать больше о программировании VBA. Кто знает — может быть, однажды вы будете штамповать собственные сложные функции! Убедитесь, что вы работаете в рабочей книге с поддержкой макросов (один из типов файлов Excel).

Продолжайте создавать пользовательские функции:

Нажмите Alt + F11.
Вы попадете в редактор Visual Basic, где написан VBA.
Вы также можете нажать кнопку Visual Basic на вкладке «Разработчик» на ленте. Вкладка «Разработчик» отображается, только если установлен флажок «Разработчик» на вкладке «Настроить ленту» диалогового окна «Параметры Excel».
Выберите Вставка→Модуль в редакторе.
Перед вами лежит пустой кодовый модуль. Теперь пришло время создать свою собственную функцию!

Введите этот программный код, показанный на следующем рисунке:

Написание собственной функции

 Добавление общедоступной функции (число 1 как двойное, число 2 как двойное)
Добавить = число1 + число2
Завершить функцию

Сохраните функцию.
Макросы и программы VBA можно сохранять только в рабочей книге с поддержкой макросов.
После того, как вы наберете первую строку и нажмете Enter, автоматически появится последняя. В этом примере функция добавляет два числа, а слово Public перечисляет функцию в диалоговом окне «Вставить функцию». Возможно, вам придется найти книгу Excel на панели задач Windows, потому что редактор Visual Basic работает как отдельная программа. Или нажмите Alt + F11, чтобы вернуться к рабочей книге.
Вернуться в Excel.
Нажмите кнопку «Вставить функцию» на вкладке «Формулы», чтобы открыть диалоговое окно «Вставить функцию».
Поиск функции в категории «Определяется пользователем»
Нажмите OK.
Откроется диалоговое окно «Аргументы функции», готовое к приему аргументов. Разве это не невероятно? Это как если бы вы создавали расширение для Excel, и, по сути, так оно и есть.
Использование пользовательской функции добавления

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

Книги с поддержкой макросов имеют расширение .xlsm.

Эта статья из книги:

Формулы и функции Excel для чайников,

Об авторе книги:

Кен Блуттман — опытный программист и веб-разработчик, специализирующийся на Excel/VBA и базах данных.