Функция-процедура - это особый вид процедуры VBA, возвращающей результат. Пользовательские функции-процедуры, как и встроенные функции VBA, могут иметь необязательные и именованные аргументы. Для записи функции-процедуры нельзя использовать макрорекордер, хотя можно редактировать записанный рекордером макрос и превращать его в функцию-процедуру.
Основное различие между функцией-процедурой и другими процедурами, помимо того, что функции возвращают значение, а процедуры - нет, состоит в том, что в функции-процедуре используются ключевые слова Function и End Function .
Синтаксис:
Function Name()
"VBA Statements
End Function
Function - ключевое слово, объявляющее начало функции.
Name - имя функции. Имена функций следуют тем же правилам, что и имена других идентификаторов VBA.
Arglist - список аргументов данной функции, необязательный элемент.
Type - любой тип возвращаемого значения функции. Если тип не определен, результат, который возвращает функция-процедура, имеет тип Variant.
Name = expression - присваивание функции, которое указывает VBA, какое значение должна возвращать функция, необязательный элемент. Тем не менее, всегда следует включать оператор присваивания в функции-процедуры.
End Function - ключевые слова, заканчивающие функцию.
Даже если функция не имеет аргументов (например, Now, Date) в объявлении функции необходимо использовать круглые скобки.
Обычно функция предназначается для выполнения вычисления и для возвращения результата. При объявлении функции-процедуры указывается имя каждого аргумента, передаваемого функции. Имена аргументов в списке отделяются друг от друга запятой и должны следовать правилам, применяемым к любому идентификатору VBA.
Ниже представлен листинг использования простейшей функции-процедуры, которая убирает начальные и конечные пробелы и подсчитывает количество символов в выражении.
Без использования функции листинг бы выглядел так:
Из этого простого примера, думаю, понятна основная идея использования функций-процедур - улучшение читабельности программного кода и его сокращение (другими словами, функция-процедура пишется когда в программном коде более 2-3 раз встречается один и тот же "кусок" кода). Действительно, если бы наша функция-процедура состояла не из одной строки, а, скажем, из 10 строк; и программный код использовал бы эту функцию-процедуру 5 раз, то общий листинг программы был бы меньше на 38 строк.
Как уже указывалось ранее, VBA передает все аргументы в функцию-процедуру как типы Variant. Можно объявлять определенные типы данных для каждого аргумента в списке аргументов.
Функция написанная на VBA - это код, который выполняет вычисления и возвращает значение (или массив значений). Создав функцию вы можете использовать ее тремя способами:
- В качестве формулы на листе, где она может принимать аргументы и возвращать значения.
- Как часть вашей подпрограммы VBA. В процедуре Sub или внутри других функций.
- В правилах условного форматирования.
Хотя Excel уже содержит более 450 встроенных функций, но их тоже периодически не хватает. Иногда встроенные функции не могут выполнить то, что вы хотите сделать. Иногда для достижения результата необходимо создать огромную и сложную формулу, которая не понятна окружающим. В этом случае вы можете создать пользовательскую функцию, которую легко читать и использовать.
Обратите внимание, что пользовательские функции, созданные с помощью VBA, как правило значительно медленнее, чем встроенные функции. Следовательно, они лучше всего подходят для ситуаций, когда вы не можете получить результат, используя встроенные функции или вычислений не много и снижение производительности не критично.
Встроенными функциями можете пользоваться не только вы, но и ваши коллеги. Написанные вами функции будут появляться наряду с другими в диалоговом окне Мастер функций. Возможно вас пугает процесс создания функций, но спешу вас уверить, что это достаточно просто.
В чем отличие процедуры (Sub) от функции (Function)?
Основное отличие в том, что процедура (sub) используется для выполнения набора команд, и не призвана в отличие от функции (function) возвращать значение (или массив значений).
Для демонстрации приведем пример. Например есть ряд чисел от 1 до 100 и необходимо отделить четные от нечетных.
С помощью процедуры (sub) вы можете, к примеру, пройтись по ячейкам и выделить нечетные с помощью заливки. А функцию можно использовать в соседнем столбце и она вернет ИСТИНА или ЛОЖЬ в зависимости от того четное значение или нет. Т.е. вы не сможете изменить цвет заливки с помощью функции на листе.
Создание простой пользовательской функции в VBA
Давайте создадим простую пользовательскую функцию на VBA и посмотрим как там все работает.
Ниже представлен код функции, которая из текста оставляет только цифры, отбрасывая буквенные значения.
Function Цифры(Текст As String) As Long Dim i As Long Dim result As String For i = 1 To Len(Текст) If IsNumeric(Mid(Текст, i, 1)) Then result = result & Mid(Текст, i, 1) Next Цифры = CLng(result) End Function
Чтобы у вас все заработало, необходимо вставить данный код в модуль книги. Если вы не знаете как это сделать, то начните со статьи Как записать макрос в Excel .
Теперь посмотрим как функция работает, попробуем использовать ее на листе:
Прежде чем разбирать саму функцию отметим 2 приятных момента, которые появились после создания:
- Она стала доступна, как и любая другая встроенная функция (как создать скрытую функцию, расскажем далее).
- Когда вы ввели знак "=" и начинаете вводить имя функции, то Excel выводит все совпадения и показывает не только встроенные функции, но и пользовательские.
Разбираем функцию пошагово
Теперь давайте глубоко погрузимся и посмотрим, как эта функция создавалась. Начинается функция со строки
Function Цифры(Текст As String) As Long
Слово Function говорит о начале функции, далее идет ее название, в нашем случае Цифры .
- Имя функции не может содержать пробелов. Кроме того, вы не можете назвать функцию, если она сталкивается с именем ссылки на ячейку. Например, вы не можете назвать функцию ABC123, так как это имя также относится к ячейке в листе Excel.
- Вы не должны указывать на свою функцию то же имя, что и у существующей функции. Если вы это сделаете, Excel будет отдавать предпочтение встроенной функции.
- Вы можете использовать символ подчеркивания, если хотите разделить слова. Например, Сумма_Прописью является допустимым именем.
После названия в круглых скобках описываются аргументы функции. По аналогии со встроенными функциями Excel. В нашем случае используется единственный аргумент Текст . После названия аргумента мы указали As String , это означает, что наш аргумент - текстовое значение или ссылка на ячейку, содержащее текстовое значение. Если вы не укажете тип данных, VBA рассмотрит его как Variant (что означает, что вы можете использовать любой тип данных, VBA его определит самостоятельно).
Последняя часть первой строки As Long задает тип данных, которая возвращает функция. В нашем случае функция будет возвращать целые значения. Это также не обязательно.
Вторая и третья строка функции объявляет дополнительные внутренние переменные, которые мы будем использовать.
Dim i As Long Dim result As String
Переменную i мы буем использовать для перебора символов. А переменную result для хранения промежуточного результата функции.
Задача функции - пройти по всем символам переменной Текст и сохранить только те, что являются цифрами. Поэтому начнем цикл с 1 и до последнего символа.
For i = 1 To Len(Текст)
Len - функция, которая определяет количество символов.
Основная строка функции - это проверка является ли очередной символ текста цифрой и если да - то сохранение его в переменной result
If IsNumeric(Mid(Текст, i, 1)) Then result = result & Mid(Текст, i, 1)
Для этого нам потребуется функция IsNumeric - она возвращает True если текст - число и False в противном случае.
Функция Mid берет из аргумента Текст i -ый символ (значение 1 , указывает что функция Mid берет только 1 символ)/
Функция Next - закрывает цикл For тут все понятно.
Цифры = CLng(result)
Этой строкой мы преобразовываем текстовую переменную result , которая содержит все цифры аргумента Текст , в числовое значение. И говорим какой результат должна вывести наша функция Цифры .
Последняя строка кода - End Function . Это обязательная строка кода, которая сообщает VBA, что код функции заканчивается здесь.
В приведенном выше коде описаны различные части типичной пользовательской функции, созданной в VBA. В следующих статьях мы более подробно разберем эти элементы, а также рассмотрим различные способы выполнения функции VBA в Excel.
При написании кода VBA в Excel набор встроенных операторов используют на каждом шагу. Эти операторы делятся на математические, строковые, операторы сравнения и логические. Далее мы подробно рассмотрим каждую группу операторов.
Математические операторы
Основные математические операторы VBA перечислены в таблице ниже.
В правом столбце таблицы указан приоритет операторов, принятый по умолчанию при отсутствии скобок. Добавляя в выражение скобки, можно изменять порядок выполнения операторов VBA по своему желанию.
Строковые операторы
Основной строковый оператор в Excel VBA – это оператор конкатенации & (слияние):
Операторы сравнения
Операторы сравнения используются для сравнения двух чисел или строк и возвращают логическое значение типа Boolean (True или False). Основные операторы сравнения Excel VBA перечислены в этой таблице:
Логические операторы
Логические операторы, как и операторы сравнения, возвращают логическое значение типа Boolean (True или False). Основные логические операторы Excel VBA перечислены в таблице ниже:
В приведённой выше таблице перечислены не все логические операторы, доступные в VBA. Полный список логических операторов можно найти на сайте Visual Basic Developer Center .
Встроенные функции
В VBA доступно множество встроенных функций, которые могут быть использованы при написании кода. Ниже перечислены некоторые из наиболее часто используемых:
| Функция | Действие |
|---|---|
| Abs | Возвращает абсолютную величину заданного числа.
|
| Chr | Возвращает символ ANSI, соответствующий числовому значению параметра.
|
| Date | Возвращает текущую системную дату. |
| DateAdd | Добавляет определённый временной интервал к заданной дате. Синтаксис функции:
DateAdd(интервал , число , дата ) Где аргумент интервал определяет тип временного интервала, добавляемого к заданной дате в количестве, указанном в аргументе число . Аргумент интервал может принимать одно из следующих значений:
|
| DateDiff | Вычисляет количество определённых временных интервалов между двумя заданными датами.
|
| Day | Возвращает целое число, соответствующее дню месяца в заданной дате.
Пример: Day(“29/01/2015”) возвращает число 29. |
| Hour | Возвращает целое число, соответствующее количеству часов в заданном времени.
Пример: Hour(“22:45:00”) возвращает число 22. |
| InStr | Принимает в качестве аргументов целое число и две строки. Возвращает позицию вхождения второй строки внутри первой, начиная поиск с позиции, заданной целым числом.
Примечание: Аргумент-число может быть не задан, в таком случае поиск начинается с первого символа строки, заданной во втором аргументе функции. |
| Int | Возвращает целую часть заданного числа.
Пример: Int(5.79) возвращает результат 5. |
| Isdate | Возвращает True
, если заданное значение является датой, или False
– если датой не является.
|
| IsError | Возвращает True , если заданное значение является ошибкой, или False – если ошибкой не является. |
| IsMissing | В качестве аргумента функции передаётся имя необязательного аргумента процедуры. IsMissing возвращает True , если для рассматриваемого аргумента процедуры не передано значение. |
| IsNumeric | Возвращает True , если заданное значение может быть рассмотрено как число, в противном случае возвращает False . |
| Left | Возвращает заданное количество символов от начала переданной строки. Синтаксис функции вот такой:
Left(строка , длина ) где строка – это исходная строка, а длина – количество возвращаемых символов, считая от начала строки.
|
| Len | Возвращает количество символов в строке.
Пример: Len(“абвгдеж”) возвращает число 7. |
| Month | Возвращает целое число, соответствующее месяцу в заданной дате.
Пример: Month(“29/01/2015”) возвращает значение 1. |
| Mid | Возвращает заданное количество символов из середины переданной строки. Синтаксис функции:
Mid(строка , начало , длина ) где строка – это исходная строка, начало – позиция начала извлекаемой строки, длина – количество символов, которые требуется извлечь.
|
| Minute | Возвращает целое число, соответствующее количеству минут в заданном времени.Пример: Minute(“22:45:15”) возвращает значение 45. |
| Now | Возвращает текущую системную дату и время. |
| Right | Возвращает заданное количество символов от конца переданной строки. Синтаксис функции:
Right(строка , длина ) Где строка – это исходная строка, а длина – это количество символов, которые надо извлечь, считая от конца заданной строки.
|
| Second | Возвращает целое число, соответствующее количеству секунд в заданном времени.
Пример: Second(“22:45:15”) возвращает значение 15. |
| Sqr | Возвращает квадратный корень числовой величины, переданной в аргументе.
|
| Time | Возвращает текущее системное время. |
| Ubound | Возвращает верхний индекс измерения заданного массива.
Примечание: Для многомерных массивов в качестве необязательного аргумента может быть указано, индекс какого именно измерения нужо возвратить. Если не указано, то по умолчанию равно 1. |
Функция представляет собой группу повторно используемого кода, который может быть вызван в любом месте в вашей программе. Это устраняет необходимость повторного написания одного и того же кода. Это позволяет программистам разделить большую программу на множество небольших и управляемых функций.
Помимо встроенных функций, VBA также позволяет писать пользовательские функции. В этой статье вы узнаете, как писать свои собственные функции в VBA.
Определение функции
Функция VBA может иметь необязательный оператор return. Это необходимо, если вы хотите вернуть значение из функции.
Например, вы можете передать два числа в функции, а затем вы можете ожидать от функции возврата своего умножения в вашу вызывающую программу.
Примечание . Функция может возвращать несколько значений, разделенных запятой, как массив, назначенный самому имени функции.
Прежде чем использовать функцию, нам нужно определить эту конкретную функцию. Наиболее распространенным способом определения функции в VBA является использование ключевого слова Function , за которым следует уникальное имя функции, и оно может содержать или не содержать список параметров и оператор с ключевым словом End Function , который указывает конец функции. Ниже приведен базовый синтаксис.
Синтаксис
Добавьте кнопку и добавьте следующую функцию.
Function Functionname(parameter-list) statement 1 statement 2 statement 3 ....... statement n End Function
Пример
Добавьте следующую функцию, которая возвращает область. Обратите внимание, что значение / значения могут быть возвращены с именем самой функции.
Function find_area(Length As Double, Optional Width As Variant) If IsMissing(Width) Then find_area = Length * Length Else find_area = Length * Width End If End Function
Вызов функции
Чтобы вызвать функцию, вызовите функцию, используя имя функции, как показано на следующем снимке экрана.
Вывод области, как показано ниже, будет отображаться пользователю.
Создание пользовательской функции в VBA Excel, ее синтаксис и компоненты. Описание пользовательской функции и ее аргументов. Метод Application.MacroOptions.
Пользовательская функция - это процедура VBA, которая производит заданные вычисления и возвращает полученный результат. Используется для вставки в ячейки рабочего листа Excel или для вызова из других процедур.
Объявление пользовательской функции
Синтаксис функции
Function Имя ([СписокАргументов]) [Операторы] [Имя = выражение] [Операторы] [Имя = выражение] End FunctionКомпоненты функции
- Static - необязательное ключевое слово, указывающее на то, что значения переменных, объявленных в функции, сохраняются между ее вызовами.
- Имя - обязательный компонент, имя пользовательской функции.
- СписокАргументов - необязательный компонент, одна или более переменных, представляющих аргументы, которые передаются в функцию. Аргументы заключаются в скобки и разделяются между собой запятыми.
- Операторы - необязательный компонент, блок операторов (инструкций).
- Имя = выражение - необязательный* компонент, присвоение имени функции значения выражения или переменной. Обычно, значение присваивается функции непосредственно перед выходом из нее.
- Exit Function - необязательный компонент, принудительный выход из функции, если ей уже присвоено окончательное значение.
*Один из компонентов Имя = выражение следует считать обязательным, так как если не присвоить функции значения, смысл ее использования теряется.
Видимость функции
Видимость пользовательской функции определяется необязательными ключевыми словами Public и Private, которые могут быть указаны перед оператором Function (или Static, в случае его использования).
Ключевое слово Public указывает на то, что функция будет доступна для вызова из других процедур во всех модулях открытых книг Excel. Функция, объявленная как Public , отображается в диалоговом окне Мастера функций.
Ключевое слово Private указывает на то, что функция будет доступна для вызова из других процедур только в пределах программного модуля, в котором она находится. Функция, объявленная как Private , не отображается в диалоговом окне Мастера функций, но ее можно ввести в ячейку вручную.
Если ключевое слово Public или Private не указано, функция считается по умолчанию объявленной, как Public.
Чтобы пользовательская функция всегда была доступна во всех открытых книгах Excel, сохраните ее в без объявления видимости или как Public. Но если вы планируете передать рабочую книгу с пользовательской функцией на другой компьютер, код функции должен быть в программном модуле передаваемой книги.
Пример пользовательской функции
Для примера мы рассмотрим простейшую пользовательскую функцию, которой в следующем параграфе добавим описание. Называется функция «Деление», объявлена с типом данных Variant, так как ее возвращаемое значение может быть и числом, и текстом. Аргументы функции - Делимое и Делитель - тоже объявлены как Variant, так как в ячейках Excel могут быть числовые значения разных типов, и функция IsNumeric тоже проверяет разные типы данных и требует, чтобы ее аргументы были объявлены как Variant.
Function Деление(Делимое As Variant, Делитель As Variant) As Variant If IsNumeric(Делимое) = False Or IsNumeric(Делитель) = False Then Деление = "Ошибка: Делимое и Делитель должны быть числами!" Exit Function ElseIf Делитель = 0 Then Деление = "Ошибка: деление на ноль!" Exit Function Else Деление = Делимое / Делитель End If End Function
Эта функция выполняет деление значений двух ячеек рабочего листа Excel. Перед делением проверяются два блока условий:
- Если делимое или делитель не являются числом, функция возвращает значение: «Ошибка: Делимое и Делитель должны быть числами!», и производится принудительный выход из функции оператором Exit Function.
- Если делитель равен нулю, функция возвращает значение: «Ошибка: деление на ноль!», и производится принудительный выход из функции оператором Exit Function.
Если проверяемые условия не выполняются (возвращают значение False) производится деление чисел и функция возвращает частное (результат деления).
Вы можете скопировать к себе в стандартный модуль эту функцию и она станет доступна в разделе «Определенные пользователем» Мастера функций. Попробуйте вставить функцию «Деление» в ячейку рабочего листа с помощью Мастера и поэкспериментируйте с ней.
Практического смысла функция «Деление» не имеет, но она хорошо демонстрирует как объявляются, создаются и работают пользовательские функции в VBA Excel. А еще она поможет продемонстрировать, как добавлять к функциям и аргументам описания. С полноценной пользовательской функцией вы можете ознакомиться .
Добавление описания функции
В списке функций, выводимом Мастером, невозможно добавить или отредактировать их описание. Список макросов позволяет добавлять процедурам описание, но в нем нет функций. Проблема решается следующим образом:
- Запустите Мастер функций, посмотрите, как отображается имя нужной функции и закройте его.
- Откройте и в поле «Имя макроса» впишите имя пользовательской функции.
- Нажмите кнопку «Параметры» и в открывшемся окне добавьте или отредактируйте описание.
- Нажмите кнопку «OK», затем в окне списка макросов - «Отмена». Описание готово!
Добавление описания на примере функции «Деление»:
Описание функции «Деление» в диалоговом окне Мастера функций «Аргументы функции»:
С помощью окна «Список макросов» можно добавить описание самой функции, а ее аргументам нельзя. Но это можно сделать, используя метод Application.MacroOptions.
Метод Application.MacroOptions
Метод Application.MacroOptions позволяет добавить пользовательской функции описание, назначить сочетание клавиш, указать категорию, добавить описания аргументов и добавить или изменить другие параметры. Давайте рассмотрим возможности этого метода, используемые чаще всего.
Пример кода с методом Application.MacroOptions:
Sub ИмяПодпрограммы() Application.MacroOptions _ Macro:="ИмяФункции", _ Description:="Описание функции", _ Category:="Название категории", _ ArgumentDescriptions:=Array("Описание 1", "Описание 2", "Описание 3", ...) End Sub
- ИмяПодпрограммы - любое уникальное имя, подходящее для наименования процедур.
- ИмяФункции - имя функции, параметры которой добавляются или изменяются.
- Описание функции - описание функции, которое добавляется или изменяется.
- Название категории - название категории в которую будет помещена функция. Если параметр Category отсутствует, пользовательская функция будет записана в раздел по умолчанию - «Определенные пользователем». Если указанное Название категории соответствует одному из названий стандартного списка, функция будет записана в него. Если такого Названия категории нет в списке, будет создан новый раздел с этим названием и функция будет помещена в него.
- "Описание 1", "Описание 2", "Описание 3", ... - описания аргументов в том порядке, как они расположены в объявлении пользовательской функции.
Эта подпрограмма запускается один раз, после чего ее можно удалить или использовать как шаблон для корректировки параметров других пользовательских функций.
Сейчас с помощью метода Application.MacroOptions попробуем изменить описание пользовательской функции «Деление» и добавить описания аргументов.
Sub ИзменениеОписания() Application.MacroOptions _ Macro:="Деление", _ Description:="Описание функции Деление изменено методом Application.MacroOptions", _ ArgumentDescriptions:=Array("- любое числовое значение", "- числовое значение, кроме нуля") End Sub
После однократного запуска этой подпрограммы получаем следующий результат:
Метод Application.MacroOptions не работает в , но и здесь можно найти решение. Добавьте описания к пользовательским функциям и их аргументам в обычной книге Excel, затем экспортируйте модуль с функциями в любой каталог на жестком диске и оттуда импортируйте в Личную книгу макросов. Все описания сохранятся.
