Создаем свою первую функцию в 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
Математические функции (Visual Basic) Math Functions (Visual Basic)
Методы класса System.Math предоставляют тригонометрические, Логарифмические и другие общие математические функции. The methods of the System.Math class provide trigonometric, logarithmic, and other common mathematical functions.
Заметки Remarks
В следующей таблице перечислены методы класса System.Math. The following table lists methods of the System.Math class. Их можно использовать в программе Visual Basic: You can use these in a Visual Basic program:
| Метод .NET .NET method | Описание Description |
|---|---|
| Abs | Возвращает абсолютное значение числа. Returns the absolute value of a number. |
| Acos | Возвращает угол, косинус которого равен указанному числу. Returns the angle whose cosine is the specified number. |
| Asin | Возвращает угол, синус которого равен указанному числу. Returns the angle whose sine is the specified number. |
| Atan | Возвращает угол, тангенс которого равен указанному числу. Returns the angle whose tangent is the specified number. |
| Atan2 | Возвращает угол, тангенс которого равен отношению двух указанных чисел. Returns the angle whose tangent is the quotient of two specified numbers. |
| BigMul | Возвращает полное произведение 2 32-разрядных чисел. Returns the full product of two 32-bit numbers. |
| Ceiling | Возвращает наименьшее целочисленное значение, которое больше или равно указанному Decimal или Double . Returns the smallest integral value that’s greater than or equal to the specified Decimal or Double . |
| Cos | Возвращает косинус указанного угла. Returns the cosine of the specified angle. |
| Cosh | Возвращает гиперболический косинус указанного угла. Returns the hyperbolic cosine of the specified angle. |
| DivRem | Возвращает частное от 2 32-битных или 64-битовых целых чисел со знаком, а также возвращает остаток в выходном параметре. Returns the quotient of two 32-bit or 64-bit signed integers, and also returns the remainder in an output parameter. |
| Exp | Возвращает значение e (основание натуральных логарифмов), возведенное в указанную степень. Returns e (the base of natural logarithms) raised to the specified power. |
| Floor | Возвращает максимальное целое число, которое меньше или равно указанному Decimal или Double ному числу. Returns the largest integer that’s less than or equal to the specified Decimal or Double number. |
| IEEERemainder | Возвращает остаток, полученный от деления указанного числа на другое заданное число. Returns the remainder that results from the division of a specified number by another specified number. |
| Log | Возвращает натуральный (базовый e) логарифм указанного числа или логарифм указанного числа в заданном базовом массиве. Returns the natural (base e) logarithm of a specified number or the logarithm of a specified number in a specified base. |
| Log10 | Возвращает логарифм с основанием 10 указанного числа. Returns the base 10 logarithm of a specified number. |
| Max | Возвращает большее из двух чисел. Returns the larger of two numbers. |
| Min | Возвращает меньшее из двух чисел. Returns the smaller of two numbers. |
| Pow | Возвращает указанное число, возведенное в указанную степень. Returns a specified number raised to the specified power. |
| Round | Возвращает Decimal или Double значение, округленное до ближайшего целого значения или до указанного числа цифр дробной части. Returns a Decimal or Double value rounded to the nearest integral value or to a specified number of fractional digits. |
| Sign | Возвращает значение типа Integer , указывающее знак числа. Returns an Integer value indicating the sign of a number. |
| Sin | Возвращает синус указанного угла. Returns the sine of the specified angle. |
| Sinh | Возвращает гиперболический синус указанного угла. Returns the hyperbolic sine of the specified angle. |
| Sqrt | Возвращает квадратный корень из указанного числа. Returns the square root of a specified number. |
| Tan | Возвращает тангенс указанного угла. Returns the tangent of the specified angle. |
| Tanh | Возвращает гиперболический тангенс указанного угла. Returns the hyperbolic tangent of the specified angle. |
| Truncate | Вычисляет целую часть указанного Decimal или Double числа. Calculates the integral part of a specified Decimal or Double number. |
В следующей таблице перечислены методы класса System.Math, которые не существуют в .NET Framework, но добавлены в .NET Standard или .NET Core: The following table lists methods of the System.Math class that don’t exist in .NET Framework but are added in .NET Standard or .NET Core:
| Метод .NET .NET method | Описание Description | Доступно в Available in |
|---|---|---|
| Acosh | Возвращает угол, гиперболический косинус которого равен указанному числу. Returns the angle whose hyperbolic cosine is the specified number. | Начиная с .NET Core 2,1 и .NET Standard 2,1 Starting with .NET Core 2.1 and .NET Standard 2.1 |
| Asinh | Возвращает угол, гиперболический синус которого равен указанному числу. Returns the angle whose hyperbolic sine is the specified number. | Начиная с .NET Core 2,1 и .NET Standard 2,1 Starting with .NET Core 2.1 and .NET Standard 2.1 |
| Atanh | Возвращает угол, гиперболический тангенс которого равен указанному числу. Returns the angle whose hyperbolic tangent is the specified number. | Начиная с .NET Core 2,1 и .NET Standard 2,1 Starting with .NET Core 2.1 and .NET Standard 2.1 |
| BitDecrement | Возвращает ближайшее самое маленькое значение, которое меньше, чем x . Returns the next smallest value that compares less than x . | Начиная с .NET Core 3,0 Starting with .NET Core 3.0 |
| BitIncrement | Возвращает ближайшее самое большое значение, превышающее x . Returns the next largest value that compares greater than x . | Начиная с .NET Core 3,0 Starting with .NET Core 3.0 |
| Cbrt | Возвращает кубический корень из указанного числа. Returns the cube root of a specified number. | Начиная с .NET Core 2,1 и .NET Standard 2,1 Starting with .NET Core 2.1 and .NET Standard 2.1 |
| Clamp | Возвращает value , ограниченное диапазоном от min до max включительно. Returns value clamped to the inclusive range of min and max . | Начиная с .NET Core 2,0 и .NET Standard 2,1 Starting with .NET Core 2.0 and .NET Standard 2.1 |
| CopySign | Возвращает значение с величиной x и знаком y . Returns a value with the magnitude of x and the sign of y . | Начиная с .NET Core 3,0 Starting with .NET Core 3.0 |
| FusedMultiplyAdd | Возвращает (x * y) + z, округленное как одна операция ternary. Returns (x * y) + z, rounded as one ternary operation. | Начиная с .NET Core 3,0 Starting with .NET Core 3.0 |
| ILogB | Возвращает целочисленный логарифм с основанием 2 указанного числа. Returns the base 2 integer logarithm of a specified number. | Начиная с .NET Core 3,0 Starting with .NET Core 3.0 |
| Log2 | Возвращает логарифм с основанием 2 указанного числа. Returns the base 2 logarithm of a specified number. | Начиная с .NET Core 3,0 Starting with .NET Core 3.0 |
| MaxMagnitude | Возвращает большую величину из двух чисел двойной точности с плавающей запятой. Returns the larger magnitude of two double-precision floating-point numbers. | Начиная с .NET Core 3,0 Starting with .NET Core 3.0 |
| MinMagnitude | Возвращает меньшую величину из двух чисел двойной точности с плавающей запятой. Returns the smaller magnitude of two double-precision floating-point numbers. | Начиная с .NET Core 3,0 Starting with .NET Core 3.0 |
| ScaleB | Возвращает значение x * 2 ^ n, вычисленное эффективно. Returns x * 2^n computed efficiently. | Начиная с .NET Core 3,0 Starting with .NET Core 3.0 |
Чтобы использовать эти функции без уточнения, импортируйте System.Mathое пространство имен в проект, добавив следующий код в начало исходного файла: To use these functions without qualification, import the System.Math namespace into your project by adding the following code to the top of your source file:
Пример-ABS Example — Abs
В этом примере используется метод Abs класса Math для расчета абсолютного значения числа. This example uses the Abs method of the Math class to compute the absolute value of a number.
Пример — Atan Example — Atan
В этом примере используется метод Atan класса Math для вычисления значения PI. This example uses the Atan method of the Math class to calculate the value of pi.
Класс System.Math содержит Math.PI Константное поле. The System.Math class contains Math.PI constant field. Его можно использовать вместо вычисления. You can use it rather than calculating it.
Пример — COS Example — Cos
В этом примере используется метод Cos класса Math, чтобы получить косинус угла. This example uses the Cos method of the Math class to return the cosine of an angle.
Пример — exp Example — Exp
В этом примере используется метод Exp класса Math, возвращающего значение e, возведенное в степень. This example uses the Exp method of the Math class to return e raised to a power.
Пример журнала Example — Log
В этом примере используется метод Log класса Math, чтобы получить натуральный логарифм числа. This example uses the Log method of the Math class to return the natural logarithm of a number.
Пример-Round Example — Round
В этом примере используется метод Round класса Math для округления числа до ближайшего целого числа. This example uses the Round method of the Math class to round a number to the nearest integer.
Пример — подпись Example — Sign
В этом примере используется метод Sign класса Math для определения знака числа. This example uses the Sign method of the Math class to determine the sign of a number.
Пример — Sin Example — Sin
В этом примере используется метод Sin класса Math, чтобы получить синус угла. This example uses the Sin method of the Math class to return the sine of an angle.
Пример. Sqrt Example — Sqrt
В этом примере используется метод Sqrt класса Math для вычисления квадратного корня числа. This example uses the Sqrt method of the Math class to calculate the square root of a number.
Пример — Tan Example — Tan
В этом примере используется метод Tan класса Math, чтобы получить тангенс угла. This example uses the Tan method of the Math class to return the tangent of an angle.
Источник статьи: http://docs.microsoft.com/ru-ru/dotnet/visual-basic/language-reference/functions/math-functions