Создаем свою первую функцию в Excel
Что такое функция в VBA?
Функция написанная на VBA — это код, который выполняет вычисления и возвращает значение (или массив значений). Создав функцию вы можете использовать ее тремя способами:
- В качестве формулы на листе, где она может принимать аргументы и возвращать значения.
- Как часть вашей подпрограммы VBA. В процедуре Sub или внутри других функций.
- В правилах условного форматирования.
Хотя Excel уже содержит более 450 встроенных функций, но их тоже периодически не хватает. Иногда встроенные функции не могут выполнить то, что вы хотите сделать. Иногда для достижения результата необходимо создать огромную и сложную формулу, которая не понятна окружающим. В этом случае вы можете создать пользовательскую функцию, которую легко читать и использовать.
Обратите внимание, что пользовательские функции, созданные с помощью VBA, как правило значительно медленнее, чем встроенные функции. Следовательно, они лучше всего подходят для ситуаций, когда вы не можете получить результат, используя встроенные функции или вычислений не много и снижение производительности не критично.
В чем отличие процедуры (Sub) от функции (Function)?
Основное отличие в том, что процедура (sub) используется для выполнения набора команд, и не призвана в отличие от функции (function) возвращать значение (или массив значений).
Для демонстрации приведем пример. Например есть ряд чисел от 1 до 100 и необходимо отделить четные от нечетных.
С помощью процедуры (sub) вы можете, к примеру, пройтись по ячейкам и выделить нечетные с помощью заливки. А функцию можно использовать в соседнем столбце и она вернет ИСТИНА или ЛОЖЬ в зависимости от того четное значение или нет. Т.е. вы не сможете изменить цвет заливки с помощью функции на листе.
Создание простой пользовательской функции в VBA
Давайте создадим простую пользовательскую функцию на VBA и посмотрим как там все работает.
Ниже представлен код функции, которая из текста оставляет только цифры, отбрасывая буквенные значения.
Чтобы у вас все заработало, необходимо вставить данный код в модуль книги. Если вы не знаете как это сделать, то начните со статьи Как записать макрос в Excel.
Теперь посмотрим как функция работает, попробуем использовать ее на листе:
Прежде чем разбирать саму функцию отметим 2 приятных момента, которые появились после создания:
- Она стала доступна, как и любая другая встроенная функция (как создать скрытую функцию, расскажем далее).
- Когда вы ввели знак «=» и начинаете вводить имя функции, то Excel выводит все совпадения и показывает не только встроенные функции, но и пользовательские.
Разбираем функцию пошагово
Теперь давайте глубоко погрузимся и посмотрим, как эта функция создавалась. Начинается функция со строки
Слово Function говорит о начале функции, далее идет ее название, в нашем случае Цифры.
- Имя функции не может содержать пробелов. Кроме того, вы не можете назвать функцию, если она сталкивается с именем ссылки на ячейку. Например, вы не можете назвать функцию ABC123, так как это имя также относится к ячейке в листе Excel.
- Вы не должны указывать на свою функцию то же имя, что и у существующей функции. Если вы это сделаете, Excel будет отдавать предпочтение встроенной функции.
- Вы можете использовать символ подчеркивания, если хотите разделить слова. Например, Сумма_Прописью является допустимым именем.
После названия в круглых скобках описываются аргументы функции. По аналогии со встроенными функциями Excel. В нашем случае используется единственный аргумент Текст. После названия аргумента мы указали As String, это означает, что наш аргумент — текстовое значение или ссылка на ячейку, содержащее текстовое значение. Если вы не укажете тип данных, VBA рассмотрит его как Variant (что означает, что вы можете использовать любой тип данных, VBA его определит самостоятельно).
Последняя часть первой строки As Long задает тип данных, которая возвращает функция. В нашем случае функция будет возвращать целые значения. Это также не обязательно.
Вторая и третья строка функции объявляет дополнительные внутренние переменные, которые мы будем использовать.
Переменную i мы буем использовать для перебора символов. А переменную result для хранения промежуточного результата функции.
Задача функции — пройти по всем символам переменной Текст и сохранить только те, что являются цифрами. Поэтому начнем цикл с 1 и до последнего символа.
Len — функция, которая определяет количество символов.
Основная строка функции — это проверка является ли очередной символ текста цифрой и если да — то сохранение его в переменной result
Для этого нам потребуется функция IsNumeric — она возвращает True если текст — число и False в противном случае.
Функция Mid берет из аргумента Текст i-ый символ (значение 1, указывает что функция Mid берет только 1 символ)/
Функция Next — закрывает цикл For тут все понятно.
Этой строкой мы преобразовываем текстовую переменную result, которая содержит все цифры аргумента Текст, в числовое значение. И говорим какой результат должна вывести наша функция Цифры.
Последняя строка кода — End Function. Это обязательная строка кода, которая сообщает VBA, что код функции заканчивается здесь.
В приведенном выше коде описаны различные части типичной пользовательской функции, созданной в VBA. В следующих статьях мы более подробно разберем эти элементы, а также рассмотрим различные способы выполнения функции VBA в Excel.
Источник статьи: http://micro-solution.ru/excel/vba/create-user-functions
VBA Excel. Пользовательская функция (синтаксис, компоненты)
Создание пользовательской функции в VBA Excel, ее синтаксис и компоненты. Описание пользовательской функции и ее аргументов. Метод Application.MacroOptions.
Объявление пользовательской функции
Синтаксис функции
Компоненты функции
- Static — необязательное ключевое слово, указывающее на то, что значения переменных, объявленных в функции, сохраняются между ее вызовами.
- Имя — обязательный компонент, имя пользовательской функции.
- СписокАргументов — необязательный компонент, одна или более переменных, представляющих аргументы, которые передаются в функцию. Аргументы заключаются в скобки и разделяются между собой запятыми.
- Операторы — необязательный компонент, блок операторов (инструкций).
- Имя = выражение — необязательный* компонент, присвоение имени функции значения выражения или переменной. Обычно, значение присваивается функции непосредственно перед выходом из нее.
- Exit Function — необязательный компонент, принудительный выход из функции, если ей уже присвоено окончательное значение.
*Один из компонентов Имя = выражение следует считать обязательным, так как если не присвоить функции значения, смысл ее использования теряется.
Видимость функции
Видимость пользовательской функции определяется необязательными ключевыми словами Public и Private, которые могут быть указаны перед оператором Function (или Static, в случае его использования).
Ключевое слово Public указывает на то, что функция будет доступна для вызова из других процедур во всех модулях открытых книг Excel. Функция, объявленная как Public, отображается в диалоговом окне Мастера функций.
Ключевое слово Private указывает на то, что функция будет доступна для вызова из других процедур только в пределах программного модуля, в котором она находится. Функция, объявленная как Private, не отображается в диалоговом окне Мастера функций, но ее можно ввести в ячейку вручную.
Если ключевое слово Public или Private не указано, функция считается по умолчанию объявленной, как Public.
Чтобы пользовательская функция всегда была доступна во всех открытых книгах Excel, сохраните ее в Личной книге макросов без объявления видимости или как Public. Но если вы планируете передать рабочую книгу с пользовательской функцией на другой компьютер, код функции должен быть в программном модуле передаваемой книги.
Пример пользовательской функции
Для примера мы рассмотрим простейшую пользовательскую функцию, которой в следующем параграфе добавим описание. Называется функция «Деление», объявлена с типом данных Variant, так как ее возвращаемое значение может быть и числом, и текстом. Аргументы функции – Делимое и Делитель – тоже объявлены как Variant, так как в ячейках Excel могут быть числовые значения разных типов, и функция IsNumeric тоже проверяет разные типы данных и требует, чтобы ее аргументы были объявлены как Variant.
Источник статьи: http://vremya-ne-zhdet.ru/vba-excel/polzovatelskaya-funktsiya/
Оператор Function Function statement
Объявляет имя, аргументыи код, которые формируют тело процедуры функции . Declares the name, arguments, and code that form the body of a Function procedure.
Синтаксис Syntax
[Общедоступный | частный | друг] [ Static ] Function Имя функции [( arglist ) ] [ тип ] [Public | Private | Friend] [ Static ] Function name [ ( arglist ) ] [ As type ][ Операторы ] [ statements ]
[ name = выражение имени] [ name = expression ]
[ Функция exit ] [ Exit Function ]
[ Операторы ] [ statements ]
[ name = выражение имени] [ name = expression ]
End Function End Function
Синтаксис оператора Function содержит такие части: The Function statement syntax has these parts:
| Часть Part | Описание Description |
|---|---|
| Public Public | Необязательно. Optional. Указывает на то, что процедура Function доступна для всех других процедур во всех модулях. Indicates that the Function procedure is accessible to all other procedures in all modules. При использовании в модуле, который содержит Option Private, процедура недоступна вне проекта. If used in a module that contains an Option Private, the procedure is not available outside the project. |
| Private Private | Необязательно. Optional. Указывает на то, что процедура Function доступна только для других процедур в том модуле, в котором была объявлена. Indicates that the Function procedure is accessible only to other procedures in the module where it is declared. |
| Friend Friend | Необязательно. Optional. Используется только в модуле класса. Used only in a class module. Указывает на то, что процедура Function видима повсюду в проекте, но невидима для контроллера экземпляра объекта. Indicates that the Function procedure is visible throughout the project, but not visible to a controller of an instance of an object. |
| Static Static | Необязательно. Optional. Указывает на то, что локальные переменные процедуры Function сохраняются между вызовами. Indicates that the Function procedure’s local variables are preserved between calls. Атрибут Static не влияет на переменные, которые были объявлены вне Function, даже если они используются в процедуре. The Static attribute doesn’t affect variables that are declared outside the Function, even if they are used in the procedure. |
| name name | Обязательный. Required. Название Function; соответствует стандарту соглашений об именовании переменных. Name of the Function; follows standard variable naming conventions. |
| arglist arglist | Необязательно. Optional. Список переменных, представляющих аргументы, которые передаются в процедуру Function при вызове. List of variables representing arguments that are passed to the Function procedure when it is called. В качестве разделителя переменных используется запятая. Multiple variables are separated by commas. |
| type type | Необязательно. Optional. Тип данных значения, возвращаемого процедурой Function ; Допустимые значения: Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (в настоящее время не поддерживается), Date, String (за исключением фиксированной длины), Object, Variantили какого -либо определяемого пользователем типа. Data type of the value returned by the Function procedure; may be Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not currently supported), Date, String (except fixed length), Object, Variant, or any user-defined type. |
| Операторы statements | Необязательно. Optional. Любая группа операторов, которая будет выполняться в процедуре Function. Any group of statements to be executed within the Function procedure. |
| выражение expression | Необязательно. Optional. Возвращаемое значение Function. Return value of the Function. |
Аргумент arglist имеет следующий синтаксис и элементы: The arglist argument has the following syntax and parts:
[ Необязательно ] [ ByVal | ByRef ] [ ParamArray ] имя_переменной [()] [ тип ] [ = DefaultValue ] [ Optional ] [ ByVal | ByRef ] [ ParamArray ] varname [ ( ) ] [ As type ] [ = defaultvalue ]
| Часть Part | Описание Description |
|---|---|
| Необязательное Optional | Необязательно. Optional. Указывает, что аргумент не является обязательным. Indicates that an argument is not required. При использовании все последующие аргументы в списке arglist также должны быть необязательными и объявлены с помощью ключевого слова Optional . If used, all subsequent arguments in arglist must also be optional and declared by using the Optional keyword. Optional не может использоваться для каких-либо аргументов, если используется ParamArray. Optional can’t be used for any argument if ParamArray is used. |
| ByVal ByVal | Необязательно. Optional. Указывает, что аргумент передается значением. Indicates that the argument is passed by value. |
| ByRef ByRef | Необязательно. Optional. Указывает, что аргумент передается по ссылке. Indicates that the argument is passed by reference. ByRef является значением по умолчанию в Visual Basic. ByRef is the default in Visual Basic. |
| ParamArray ParamArray | Необязательно. Optional. Используется как последний аргумент в arglist, чтобы указать, что последний аргумент является массивом Optional элементов Variant. Used only as the last argument in arglist to indicate that the final argument is an Optional array of Variant elements. Ключевое слово ParamArray позволяет предоставлять произвольное число аргументов. The ParamArray keyword allows you to provide an arbitrary number of arguments. Не может использоваться с аргументами ByVal, ByRef или Optional. It may not be used with ByVal, ByRef, or Optional. |
| varname varname | Обязательный. Required. Имя переменной, представляющее аргумент; соответствует стандарту соглашений об именовании переменных. Name of the variable representing the argument; follows standard variable naming conventions. |
| type type | Необязательно. Optional. Тип данных аргумента, передаваемого в процедуру; Допустимые значения: Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (в настоящее время не поддерживается), String (только переменная длина), Object, Variantили определенный тип объекта. Data type of the argument passed to the procedure; may be Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not currently supported) Date, String (variable length only), Object, Variant, or a specific object type. Если параметр объявлен без ключевого слова Optional, можно также указать тип, определяемый пользователем. If the parameter is not Optional, a user-defined type may also be specified. |
| значение defaultvalue | Необязательно. Optional. Любая константа или константное выражение. Any constant or constant expression. Действительно только для параметров Optional. Valid for Optional parameters only. Если типом является Object, явным значением по умолчанию может быть только Nothing. If the type is an Object, an explicit default value can only be Nothing. |
Примечания Remarks
Если он не указан явно с использованием общедоступных, частныхи дружественных, то по умолчанию процедуры функций являются общедоступными. If not explicitly specified by using Public, Private, or Friend, Function procedures are public by default.
Если функция static не используется, значение локальных переменных не сохраняется между вызовами. If Static isn’t used, the value of local variables is not preserved between calls.
Ключевое слово Friend может использоваться только в модулях классов. The Friend keyword can only be used in class modules. Однако доступ к процедурам Friend может осуществляться в любом модуле проекта. However, Friend procedures can be accessed by procedures in any module of a project. Дружественная процедура не отображается в библиотеке типов родительского класса, а также не может быть поздней привязкой. A Friend procedure does not appear in the type library of its parent class, nor can a Friend procedure be late bound.
Процедуры Function могут быть рекурсивными, то есть они могут вызывать сами себя для выполнения поставленной задачи. Function procedures can be recursive; that is, they can call themselves to perform a given task. Однако рекурсия может стать причиной переполнения стека. However, recursion can lead to stack overflow. Ключевое слово Static обычно не используется с рекурсивными процедурами Function. The Static keyword usually isn’t used with recursive Function procedures.
Весь выполняемый код должен быть в процедурах. All executable code must be in procedures. Невозможно определить процедуру Function внутри другой процедуры Function, Sub или Property . You can’t define a Function procedure inside another Function, Sub, or Property procedure.
Оператор Exit Function вызывает немедленный выход из процедуры Function . The Exit Function statement causes an immediate exit from a Function procedure. Выполнение программы продолжается с оператором, следующим за оператором, который вызвал процедуру Function. Program execution continues with the statement following the statement that called the Function procedure. В любом месте процедуры Function может появится любое количество операторов Exit Function. Any number of Exit Function statements can appear anywhere in a Function procedure.
Как и Sub, Function является отдельной процедурой, которая может содержать аргументы, выполнять наборы операторов и изменять значения их аргументов. Like a Sub procedure, a Function procedure is a separate procedure that can take arguments, perform a series of statements, and change the values of its arguments. Однако в отличие от Sub, процедуру Function можно использовать справа от выражения, как и при использовании любой встроенной функции, например Sqr, Cos или Chr, если необходимо использовать значение, возвращенное функцией. However, unlike a Sub procedure, you can use a Function procedure on the right side of an expression in the same way you use any intrinsic function, such as Sqr, Cos, or Chr, when you want to use the value returned by the function.
Вы вызываете процедуру Function с помощью имени функции, за которым следует список аргументов в круглых скобках, в выражении. You call a Function procedure by using the function name, followed by the argument list in parentheses, in an expression. В операторе Call представлены конкретные сведения о вызове процедур функций . See the Call statement for specific information about how to call Function procedures.
Чтобы вернуть значение функции, присвойте значение названию функции. To return a value from a function, assign the value to the function name. В любом месте процедуры может появится любое количество таких назначений. Any number of such assignments can appear anywhere within the procedure. Если параметру name не присвоено значение, процедура вернет значение по умолчанию: числовая функция вернет 0, строковая функция вернет строку нулевой длины («»), а функция Variant вернет значение Empty. If no value is assigned to name, the procedure returns a default value: a numeric function returns 0, a string function returns a zero-length string («»), and a Variant function returns Empty. Функция, которая возвращает объектную ссылку, возвращает значение Nothing, если для параметра name не присвоено ни одной объектной ссылки (с помощью Set) в Function. A function that returns an object reference returns Nothing if no object reference is assigned to name (using Set) within the Function.
В приведенном ниже примере показано, как назначить возвращаемое значение функции. The following example shows how to assign a return value to a function. В этом случае значение False присваивается названию, чтобы указать, что некоторые значения не были найдены. In this case, False is assigned to the name to indicate that some value was not found.
Переменные, которые используются в процедурах Function, делятся на две категории: те, которые явно объявлены в процедуре, и те, которые нет. Variables used in Function procedures fall into two categories: those that are explicitly declared within the procedure and those that are not.
Переменные, которые явно объявлены в процедуре (с помощью оператора Dim или его эквивалента), всегда являются локальными в процедуре. Variables that are explicitly declared in a procedure (using Dim or the equivalent) are always local to the procedure. Переменные, которые используются, но не были явно объявлены в процедуре, также являются локальными, если они не были объявлены на более высоком уровне вне процедуры. Variables that are used but not explicitly declared in a procedure are also local unless they are explicitly declared at some higher level outside the procedure.
В процедуре может использоваться переменная, которая не была явно объявлена в процедуре, но если какой-либо элемент, который был определен на уровне модуля, имеет такое же название, может возникнуть конфликт. A procedure can use a variable that is not explicitly declared in the procedure, but a naming conflict can occur if anything you defined at the module level has the same name. Если ваша процедура ссылается на необъявленную переменную, которая называется так же, как и другая процедура, константа или переменная, система посчитает, что ваша процедура ссылается на это название на уровне модуля. If your procedure refers to an undeclared variable that has the same name as another procedure, constant, or variable, it is assumed that your procedure refers to that module-level name. Следует явно объявлять переменные, чтобы избежать таких конфликтов. Explicitly declare variables to avoid this kind of conflict. Оператор Option Explicit можно использовать для принудительного явного объявления переменных. You can use an Option Explicit statement to force explicit declaration of variables.
Visual Basic может менять порядок арифметических выражений для повышения внутренней эффективности. Visual Basic may rearrange arithmetic expressions to increase internal efficiency. Избегайте использования процедуры Function в арифметических выражениях, если функция меняет значение переменных в одном выражении. Avoid using a Function procedure in an arithmetic expression when the function changes the value of variables in the same expression. Дополнительные сведения об арифметических операторах приведены в разделе Операторы. For more information about arithmetic operators, see Operators.
Пример Example
В этом примере оператор Function используется для объявления названия, аргументов и кода, которые составляют основной текст процедуры Function. This example uses the Function statement to declare the name, arguments, and code that form the body of a Function procedure. В последнем примере используются фиксированные инициализированные аргументы Optional. The last example uses hard-typed, initialized Optional arguments.
Использование ключевых слов ParamArray позволяет функции принимать переменное число аргументов. Using the ParamArray keyword enables a function to accept a variable number of arguments. В следующем определении он передается по значению. In the following definition, it is passed by value.
Необязательные аргументы могут иметь значения по умолчанию и типы, отличные от Variant. Optional arguments can have default values and types other than Variant.
См. также See also
Поддержка и обратная связь Support and feedback
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь. Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
Источник статьи: http://docs.microsoft.com/ru-ru/office/vba/language/reference/user-interface-help/function-statement