Создаем свою первую функцию в Excel. Операторы и встроенные функции VBA Встроенные функции vba excel

Арифметические выражения Visual Basic.

Теоретическая часть

Выражениями и функциями

ЛАБОРАТОРНАЯ РАБОТА №4. Работа с арифметическими

Вопросы для контроля

1. Какие компоненты позволяют создавать статические изображения в приложении?

2. Как поместить рисунок на форму? Какие методы для этой цели существуют и в чем их отличие?

3. Картинки какого формата может подключать к приложению компонент Image?

4. Какое свойство объекта Image отвечает за масштабирование? За видимость картинки?

Цель работы: ознакомиться с арифметическими выражениями и математическими функциями языка Visual Basic; научиться разрабатывать приложения с использованием выражений и математических функций

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

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

Арифметические операции языка Visual Basic приведены в Таблице 4.1.

Таблица 4.1 – Арифметические операции языка Visual Basic

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

Примеры:

14 /5*2 = 5.6 – операции одного приоритета выполнятся слева направо

14\5*2 = 1 – умножение имеет более высокий приоритет и при целочисленном делении дробная часть отбрасывается

27^1/3 = 9 – возведение в степень обладает наивысшим приоритетом

27^(1/3) = 3 – скобки изменяют последовательность операций

В языке Visual Basic имеется широкий набор встроенных (стандартных ) функций, облегчающий написание программ. Различают математические функции, функции обработки строк, функции для работы с датами и временем, финансовые.

Математические функции:

Abs (x) – вычисляет модуль аргумента

Atn (x) – вычисляет арктангенс числа x

Sin (x) – вычисляет синус числа x

Cos (x) – вычисляет косинус числа x

Tan (x) – вычисляет тангенс числа x

Exp (x) – вычисляет e^x

Log (x) – вычисляет натуральный логарифм

Sqr (x) – вычисляет квадратный корень

Rnd () – возвращает случайное число в диапазоне от 0 до 1

Int (x) – вычисляет наибольшее целое число, не превышающее x

Fix (x) – вычисляет округленное число (отбрасывает дробную часть аргумента)

CInt(x) – возвращает аргумент, округленный до целого по правилам округления математики

Sgn (x) – возвращает знак числа

X^(1/n) – извлекает корень n-ной степени из X

Hex (x) – переводит десятичное число в шестнадцатеричное

Oct (x) – переводит десятеричное число в восьмеричное

Примеры округления чисел:

Int(7.32) = 7 CInt(5.5) = 6 Fix(-7.32) = -7

Int(-7.32) = -8 CInt(-7.5) = -7

Значения аргументов в тригонометрических функциях задаются в радианах!

Для перевода аргумента из градусов в радианы используется формула:

Аргумент (в радианах) = аргумент (в градусах)*ПИ / 180

Число ПИ может быть определено по формуле:

ПИ = Atn(1)*4

Полные сведения о встроенных функциях и правилах их применения можно найти в справочной системе Visual Basic.

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

Математические операторы

Основные математические операторы VBA перечислены в таблице ниже.

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

Строковые операторы

Основной строковый оператор в Excel VBA – это оператор конкатенации & (слияние):

Операторы сравнения

Операторы сравнения используются для сравнения двух чисел или строк и возвращают логическое значение типа Boolean (True или False). Основные операторы сравнения Excel VBA перечислены в этой таблице:

Логические операторы

Логические операторы, как и операторы сравнения, возвращают логическое значение типа Boolean (True или False). Основные логические операторы Excel VBA перечислены в таблице ниже:

В приведённой выше таблице перечислены не все логические операторы, доступные в VBA. Полный список логических операторов можно найти на сайте Visual Basic Developer Center .

Встроенные функции

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

Функция	Действие
Abs	Возвращает абсолютную величину заданного числа. Abs(-20) возвращает значение 20; Abs(20) возвращает значение 20.
Chr	Возвращает символ ANSI, соответствующий числовому значению параметра. Chr(10) возвращает перенос строки; Chr(97) возвращает символ a .
Date	Возвращает текущую системную дату.
DateAdd	Добавляет определённый временной интервал к заданной дате. Синтаксис функции: DateAdd(интервал , число , дата ) Где аргумент интервал определяет тип временного интервала, добавляемого к заданной дате в количестве, указанном в аргументе число . Аргумент интервал может принимать одно из следующих значений: DateAdd(“d”, 32, “01/01/2015”) добавляет 32 дня к дате 01/01/2015 и, таким образом, возвращает дату 02/02/2015. DateAdd(“ww”, 36, “01/01/2015”) добавляет 36 недель к дате 01/01/2015 и возвращает дату 09/09/2015.
DateDiff	Вычисляет количество определённых временных интервалов между двумя заданными датами. DateDiff(“d”, “01/01/2015”, “02/02/2015”) вычисляет количество дней между датами 01/01/2015 и 02/02/2015, возвращает результат 32. DateDiff(“ww”, “01/01/2015”, “03/03/2016”) вычисляет количество недель между датами 01/01/2015 и 03/03/2016, возвращает результат 61.
Day	Возвращает целое число, соответствующее дню месяца в заданной дате. Пример: Day(“29/01/2015”) возвращает число 29.
Hour	Возвращает целое число, соответствующее количеству часов в заданном времени. Пример: Hour(“22:45:00”) возвращает число 22.
InStr	Принимает в качестве аргументов целое число и две строки. Возвращает позицию вхождения второй строки внутри первой, начиная поиск с позиции, заданной целым числом. InStr(1, “Вот искомое слово”, “слово”) возвращает число 13. InStr(14, “Вот искомое слово, а вот еще искомое слово”, “слово”) возвращает число 38. Примечание: Аргумент-число может быть не задан, в таком случае поиск начинается с первого символа строки, заданной во втором аргументе функции.
Int	Возвращает целую часть заданного числа. Пример: Int(5.79) возвращает результат 5.
Isdate	Возвращает True , если заданное значение является датой, или False – если датой не является. IsDate(“01/01/2015”) возвращает True ; IsDate(100) возвращает False .
IsError	Возвращает True , если заданное значение является ошибкой, или False – если ошибкой не является.
IsMissing	В качестве аргумента функции передаётся имя необязательного аргумента процедуры. IsMissing возвращает True , если для рассматриваемого аргумента процедуры не передано значение.
IsNumeric	Возвращает True , если заданное значение может быть рассмотрено как число, в противном случае возвращает False .
Left	Возвращает заданное количество символов от начала переданной строки. Синтаксис функции вот такой: Left(строка , длина ) где строка – это исходная строка, а длина – количество возвращаемых символов, считая от начала строки. Left(“абвгдежзиклмн”, 4) возвращает строку “абвг”; Left(“абвгдежзиклмн”, 1) возвращает строку “а”.
Len	Возвращает количество символов в строке. Пример: Len(“абвгдеж”) возвращает число 7.
Month	Возвращает целое число, соответствующее месяцу в заданной дате. Пример: Month(“29/01/2015”) возвращает значение 1.
Mid	Возвращает заданное количество символов из середины переданной строки. Синтаксис функции: Mid(строка , начало , длина ) где строка – это исходная строка, начало – позиция начала извлекаемой строки, длина – количество символов, которые требуется извлечь. Mid(“абвгдежзиклмн”, 4, 5) возвращает строку “гдежз”; Mid(“абвгдежзиклмн”, 10, 2) возвращает строку “кл”.
Minute	Возвращает целое число, соответствующее количеству минут в заданном времени.Пример: Minute(“22:45:15”) возвращает значение 45.
Now	Возвращает текущую системную дату и время.
Right	Возвращает заданное количество символов от конца переданной строки. Синтаксис функции: Right(строка , длина ) Где строка – это исходная строка, а длина – это количество символов, которые надо извлечь, считая от конца заданной строки. Right(“абвгдежзиклмн”, 4) возвращает строку “клмн”; Right(“абвгдежзиклмн”, 1) возвращает строку “н”.
Second	Возвращает целое число, соответствующее количеству секунд в заданном времени. Пример: Second(“22:45:15”) возвращает значение 15.
Sqr	Возвращает квадратный корень числовой величины, переданной в аргументе. Sqr(4) возвращает значение 2; Sqr(16) возвращает значение 4.
Time	Возвращает текущее системное время.
Ubound	Возвращает верхний индекс измерения заданного массива. Примечание: Для многомерных массивов в качестве необязательного аргумента может быть указано, индекс какого именно измерения нужо возвратить. Если не указано, то по умолчанию равно 1.

В VBAиспользуются следующие виды функций:

Математические встроенные функции;

Математические функции, не представленные в VBA;

Функции форматирования данных;

Функции преобразования типов

Математические встроенные функции

Возвращаемое значение

Абсолютная величина числа

arctg(x) – арктангенс от значения параметра, заданного в радианах

sin(x) – возвращает синус угла от значения параметра, заданного в радианах

cos(x) – косинус указанного в радианах угла

tg(x) – возвращает тангенс угла от значения параметра, заданного в радианах

e x – возвращает числоe, возведенное в указанную степень, где е – основание натурального логарифма

ln(x) – возвращает натуральный логарифм от значения числового выражения

- возвращает квадратный корень числового выражения

Случайное число из интервала )

Именованные числовые форматы

Имя формата	Описание
	Число без разделителя тысяч
	Отображает две цифры справа от десятичной точки
	Отображает одну цифру слева и две справа от десятичной точки
	Отображает одну цифру слева и две справа от десятичной точки и выводит разделитель тысяч
	Отображает число в виде процентов и выводит две цифры справа от десятичной точки
	Использует формат с плавающей десятичной точкой
	Отображает No, если число равно 0, иYes– в противном случае
	Отображает False, если число равно 0, иTrue– в противном случае
	Отображает Off, если число равно 0, иOn– в противном случае

Таблица 6.1. Встроенные математические функции

Функция	Описание
Abs	Абсолютное значение
Atn	Арктангенс
Cos	Косинус числа
Exp	Возвращает число e (2.718282), возведенное в степень аргумента функции.
Fix	Отбрасывает дробную часть числа и возвращает целую. В результате для положительных чисел получается число меньшее, чем входное (Fix(2.5) возвратит 2), для отрицательных - большее (Fix(-2.5) возвратит -2)
Int	Отбрасывает дробную часть числа и возвращает целую. Для положительных получается число меньшее введенного (Int(2.5) возвратит 2), для отрицательных - так же меньшее (Int(-2.5) возвратит -3).
Log	Возвращает натуральный логарифм числа
Rnd	Возвращает случайное число типа Single, причем, это число находится между 0 и 1. Для инициализации генератора случайных чисел используйте директиву Randomize - ее надо вызвать до вызова Rnd .
Sgn	Функция предназначена для определения знака числа. Если число положительное - она возвращает 1. Для нуля функция возвратит 0, для отрицательного числа -1.
Sin	Синус
Sqr	Квадратный корень
Tan	Тангенс

Давайте рассмотрим пример. Добавим в документ Microsoft Word кнопку, назовем ее cmd_Calc , надпишем ее как Вычисления и добавим следующий код (листинг 6.1 .), иллюстрирующий работу рассмотренных функций.

Dim dblNumber As Double "Переменная, используемая в вычислениях Dim varResult "Переменная типа Variant dblNumber = Val(InputBox("Введите число")) "Вычисляем абсолютное значение введенного числа "Сначала присвоим результат переменной varResult "Далее - выведем подписанный результат в окне "сообщения, воспользуемся знаком "+" для "конкатенации строк, в других случаях "будем вызывать функции непосредственно "в MsgBox"e "Обратите внимание на то, что мы конвертируем "числовые значения в строки с помощью функции Str varResult = Abs(dblNumber) MsgBox ("Абсолютное значение " + _ Str(dblNumber) + " равняется " + Str(varResult)) "Арктангенс MsgBox ("Арктангенс " + _ Str(dblNumber) + " равняется " + _ Str(Atn(dblNumber))) "Косинус MsgBox ("Косинус " + _ Str(dblNumber) + " равняется " + _ Str(Cos(dblNumber))) "e в степени введенного числа MsgBox ("Число e в степени " + _ Str(dblNumber) + " равняется " + _ Str(Exp(dblNumber))) "Функция Fix MsgBox ("Результат работы функции Fiх для " + _ Str(dblNumber) + " равняется " + _ Str(Fix(dblNumber))) "Функция Int MsgBox ("Результат работы функции Int для " + _ Str(dblNumber) + " равняется " + _ Str(Int(dblNumber))) "Натуральный логарифм MsgBox ("Натуральный логарифм " + _ Str(dblNumber) + " равняется " + _ Str(Log(dblNumber))) "Получим несколько случайных чисел "первое число - от 0 до 1 "второе - от 0 до 10. "Третье - от 25 до 100 "Четвертое - целое от 0 до 34 Randomize MsgBox ("Группа случайных чисел: " + _ Str(Rnd()) + ", " + _ Str(Rnd() * 10) + ", " + _ Str(Rnd() * 75 + 25) + ", " + _ Str(Int(Rnd() * 34))) "Функция Sgn MsgBox ("Результат работы Sgn для " + _ Str(dblNumber) + " равняется " + _ Str(Sgn(dblNumber))) "Cинус MsgBox ("Синус " + _ Str(dblNumber) + " равняется " + _ Str(Sin(dblNumber))) "Квадратный корень MsgBox ("Квадратный корень " + _ Str(dblNumber) + " равняется " + _ Str(Sqr(dblNumber))) "Тангенс MsgBox ("Тангенс " + _ Str(dblNumber) + " равняется " + _ Str(Tan(dblNumber))) Листинг 6.1. Обработчик события Click кнопки cmd_Calc

Обратите внимание на алгоритм получения случайного числа, находящегося в определенном диапазоне, с помощью функции Rnd . Предположим, нам нужно получить случайное число от 15 до 40 . Получим, для начала, число от 0 до 40 . Очевидно, что для этого нам понадобится такой вызов: Rnd ()*40 .

Чтобы "поднять" уровень наименьшего случайного числа, возвращаемого выражением, до 15 , сделаем следующее.

Во-первых, вычислим разность 40 и 15 - у нас получится 25 . Значит, чтобы получить случайное число от 0 до 25 , можно использовать вызов Rnd ()*25 .

Во-вторых, прибавим к полученному случайному числу 15 . Теперь выражение для получения случайного числа от 15 до 40 выглядит так: Rnd ()*25+15 .

Проверим это высказывание на правильность. Функция Rnd , как известно, возвращает случайные числа от 0 до 1 . Если функция возвратит 0 - результат вычисления выражения будет равен 15 (0*25+15) . Если функция возвратит 1 - результат будет равен 40 (25*1+15) . Промежуточные значения Rnd дадут искомые случайные числа между 15 и 40 .

Обработчик нажатия кнопки cmd_Len (листинг 6.2 .) будет содержать решение следующей задачи: сообщить пользователю длину введенного текста. Очевидно, для решения этой задачи нам понадобится функция Len .

"Переменная для хранения входной строки Dim str_InpStr As String "Переменная для хранения найденной длины строки Dim lng_StrLen As Long str_InpStr = InputBox("Введите строку") "Вычисляем длину строки lng_StrLen = Len(str_InpStr) MsgBox ("Длина введенной строки: _" + _ str_InpStr + "_ равняется " + Str(lng_StrLen) + _ " символам") Листинг 6.2. Пример использования функции Len

На рис. 6.2 . вы можете видеть результат вычисления длины строки.

Рис. 6.2.

Теперь займемся конверсией символов - функциями LCase и UCase (листинг 6. 3.).

"Переменная для хранения входной строки Dim str_InpStr As String "Переменная для хранения измененной строки Dim str_NewStr As String str_InpStr = InputBox("Введите текст") "В str_NewStr окажется введеная строка "в которой все прописные буквы заменены строчными str_NewStr = LCase(str_InpStr) MsgBox ("Измененная строка: " + str_NewStr) "Теперь в str_NewStr будет та же строка "в которой все буквы стали прописными str_NewStr = UCase(str_InpStr) MsgBox ("Измененная строка: " + str_NewStr) Листинг 6.3. Пример использования функций LCase и UCase

На очереди - вырезание символов - функции Mid , LTrim , Rtrim , Left , Right (листинг 6.4 .) Среди этих функций наиболее мощной является Mid - используя ее, можно делать со строками очень много всего.

"Переменная для хранения входной строки Dim str_InpStr As String "Переменная для хранения вырезанных символов Dim str_NewStr As String "Зададим строку, с которой удобно будет работать str_InpStr = " Здравствуйте, Александр " "Функции удаления пробелов "При выводе строки ставим перед ее началом "и концом символ "_" для того чтобы "лучше было видно наличие "или отсутствие пробелов MsgBox ("Мы работаем с такой строкой: " + _ "_" + str_InpStr + "_") "LTrim - присваиваем результаты работы "переменной и выводим информацию в MsgBox str_NewStr = LTrim(str_InpStr) MsgBox ("Результат работы LTrim: " + _ "_" + str_NewStr + "_") "RTrim MsgBox ("Результат работы RTrim: " + _ "_" + RTrim(str_InpStr) + "_") "Trim MsgBox ("Результат работы Trim: " + _ "_" + Trim(str_InpStr) + "_") "Left - вырезаем из строки str_InpStr 12 "символов предварительно очистив ее "от начальных пробелов str_NewStr = Left(LTrim(str_InpStr), 12) MsgBox ("Первые 12 символов слева: " + _ str_NewStr) "Right - аналогично Вырезаем 9 символов справа str_NewStr = Right(RTrim(str_InpStr), 9) MsgBox ("Первые 9 символов справа: " + _ str_NewStr) "Функция Mid - для начала выведем по одному символу "со второй и пятнадцатой позиции строки "преварительно очищенной от лишних пробелов "в начале и в конце str_NewStr = Mid(Trim(str_InpStr), 2, 1) MsgBox ("Второй символ введенной строки: " + _ str_NewStr) str_NewStr = Mid(Trim(str_InpStr), 15, 1) MsgBox ("Пятнадцатый символ введенной строки: " + _ str_NewStr) "Выведем 5 символов, начиная с 15 символа str_NewStr = Mid(Trim(str_InpStr), 15, 5) MsgBox ("Пять символов строки с 15-й позиции: " + _ str_NewStr) Листинг 6.4. Пример использования функций для вырезания символов

Теперь рассмотрим примеры работы функций