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

Создаем свою первую функцию в Excel

Что такое функция в VBA?

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

  1. В качестве формулы на листе, где она может принимать аргументы и возвращать значения.
  2. Как часть вашей подпрограммы VBA. В процедуре Sub или внутри других функций.
  3. В правилах условного форматирования.

Хотя 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.

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

2015-04-23
2263

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

В MS Excel макросы и пользовательские функции создаются и сохраняются в модулях, а потому не зависят от конкретного листа или ячейки и могут быть использованы в разных рабочих листах. Применять можно только те пользовательские функции, которые находятся в открытых книгах. Если книга с пользовательской функцией будет закрыта, то формула в открытом листе, использующая эту функцию, возвратит ошибочное значение #ССЫЛКА.

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

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

· на вкладке Разработчик кнопкой Редактор Visual Basicоткрывается окно VBA;

· выполняется команда Вставка/Модуль,задается имя модуля;

· с помощью команды Вид/Программа отображается окно для ввода инструкций; вводится инструкция VB для пользовательской функции;

· выполняется команда Отладка/Компилировать;

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

Ниже приведены основные характеристики пользовательских функций.

1. Пользовательские функции начинаются инструкцией Functionи заканчиваются инструкцией End Function.

2. После инструкции Function следует ввести имя функции и далее, в круглых скобках, через запятую аргументы функции (разрешается использовать до 25 аргументов).

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

4. Инструкция присваивания обозначается знаком равенства.

5. Комментарий в VBA начинается с апострофа или ключевого слова Rem.

6. ПоследовательностьIf…Else…End if является управляющей конструкцией, которая служит для проверки условий и позволяет изменить поведение процедуры в зависимости от результатов проверки. Ключевые слова управляющей конструкции должны стоять в начале строки.

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

Ниже приведен пример простой пользовательской функции Скидка для вычисления торговой скидки 10%, если количество заказов на некоторый товар больше 500. С помощью встроенной функции Round результат расчета скидки округляется с точностью до 2 десятичных знаков.

Function Скидка (количество, цена)

If количество >= 500 Then

Ссылка на основную публикацию