Встроенные математические функции visual. Встроенные функции Visual Basic. Функции проверки типов
Функция-процедура - это особый вид процедуры 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, полезно знать, что Excel VBA располагает обширной коллекцией готовых встроенных функций, которые можно использовать при написании кода.
Список этих функций можно посмотреть в редакторе VBA:
- Откройте рабочую книгу Excel и запустите редактор VBA (нажмите для этого Alt+F11 ), и затем нажмите F2 .
- В выпадающем списке в верхней левой части экрана выберите библиотеку VBA .
- Появится список встроенных классов и функций VBA. Кликните мышью по имени функции, чтобы внизу окна отобразилось её краткое описание. Нажатие F1 откроет страницу онлайн-справки по этой функции.
Кроме того, полный список встроенных функций VBA с примерами можно найти на сайте Visual Basic Developer Centre .
Пользовательские процедуры «Function» и «Sub» в VBA
В Excel Visual Basic набор команд, выполняющий определённую задачу, помещается в процедуру Function (Функция) или Sub (Подпрограмма). Главное отличие между процедурами Function и Sub состоит в том, что процедура Function возвращает результат, процедура Sub – нет.
Поэтому, если требуется выполнить действия и получить какой-то результат (например, просуммировать несколько чисел), то обычно используется процедура Function , а для того, чтобы просто выполнить какие-то действия (например, изменить форматирование группы ячеек), нужно выбрать процедуру Sub .
Аргументы
При помощи аргументов процедурам VBA могут быть переданы различные данные. Список аргументов указывается при объявлении процедуры. К примеру, процедура Sub в VBA добавляет заданное целое число (Integer) в каждую ячейку в выделенном диапазоне. Передать процедуре это число можно при помощи аргумента, вот так:
Sub AddToCells(i As Integer) ... End Sub
Имейте в виду, что наличие аргументов для процедур Function и Sub в VBA не является обязательным. Для некоторых процедур аргументы не нужны.
Необязательные аргументы
Процедуры VBA могут иметь необязательные аргументы. Это такие аргументы, которые пользователь может указать, если захочет, а если они пропущены, то процедура использует для них заданные по умолчанию значения.
Возвращаясь к предыдущему примеру, чтобы сделать целочисленный аргумент функции необязательным, его нужно объявить вот так:
Sub AddToCells(Optional i As Integer = 0)
В таком случае целочисленный аргумент i по умолчанию будет равен 0.
Необязательных аргументов в процедуре может быть несколько, все они перечисляются в конце списка аргументов.
Передача аргументов по значению и по ссылке
Аргументы в VBA могут быть переданы процедуре двумя способами:
- ByVal – передача аргумента по значению. Это значит, что процедуре передаётся только значение (то есть, копия аргумента), и, следовательно, любые изменения, сделанные с аргументом внутри процедуры, будут потеряны при выходе из неё.
- ByRef – передача аргумента по ссылке. То есть процедуре передаётся фактический адрес размещения аргумента в памяти. Любые изменения, сделанные с аргументом внутри процедуры, будут сохранены при выходе из процедуры.
При помощи ключевых слов ByVal или ByRef в объявлении процедуры можно задать, каким именно способом аргумент передаётся процедуре. Ниже это показано на примерах:
Помните, что аргументы в VBA по умолчанию передаются по ссылке. Иначе говоря, если не использованы ключевые слова ByVal или ByRef , то аргумент будет передан по ссылке.
Перед тем как продолжить изучение процедур Function и Sub более подробно, будет полезным ещё раз взглянуть на особенности и отличия этих двух типов процедур. Далее приведены краткие обсуждения процедур VBA Function и Sub и показаны простые примеры.
VBA процедура «Function»
Редактор VBA распознаёт процедуру Function
Function ... End Function
Как упоминалось ранее, процедура Function в VBA (в отличие от Sub ), возвращает значение. Для возвращаемых значений действуют следующие правила:
- Тип данных возвращаемого значения должен быть объявлен в заголовке процедуры Function .
- Переменная, которая содержит возвращаемое значение, должна быть названа так же, как и процедура Function . Эту переменную не нужно объявлять отдельно, так как она всегда существует как неотъемлемая часть процедуры Function .
Это отлично проиллюстрировано в следующем примере.
Пример VBA процедуры «Function»: Выполняем математическую операцию с 3 числами
Ниже приведён пример кода VBA процедуры Function , которая получает три аргумента типа Double (числа с плавающей точкой двойной точности). В результате процедура возвращает ещё одно число типа Double , равное сумме первых двух аргументов минус третий аргумент:
Function SumMinus(dNum1 As Double, dNum2 As Double, dNum3 As Double) As Double SumMinus = dNum1 + dNum2 - dNum3 End Function
Эта очень простая VBA процедура Function иллюстрирует, как данные передаются процедуре через аргументы. Можно увидеть, что тип данных, возвращаемых процедурой, определён как Double (об этом говорят слова As Double после списка аргументов). Также данный пример показывает, как результат процедуры Function сохраняется в переменной с именем, совпадающим с именем процедуры.
Вызов VBA процедуры «Function»
Если рассмотренная выше простая процедура Function вставлена в модуль в редакторе Visual Basic, то она может быть вызвана из других процедур VBA или использована на рабочем листе в книге Excel.
Вызов VBA процедуры «Function» из другой процедуры
Процедуру Function можно вызвать из другой VBA процедуры при помощи простого присваивания этой процедуры переменной. В следующем примере показано обращение к процедуре SumMinus , которая была определена выше.
Sub main() Dim total as Double total = SumMinus(5, 4, 3) End Sub
Вызов VBA процедуры «Function» из рабочего листа
VBA процедуру Function можно вызвать из рабочего листа Excel таким же образом, как любую другую встроенную функцию Excel. Следовательно, созданную в предыдущем примере процедуру Function – SumMinus можно вызвать, введя в ячейку рабочего листа вот такое выражение:
SumMinus(10, 5, 2)
VBA процедура «Sub»
Редактор VBA понимает, что перед ним процедура Sub , когда встречает группу команд, заключённую между вот такими открывающим и закрывающим операторами:
Sub ... End Sub
VBA процедура «Sub»: Пример 1. Выравнивание по центру и изменение размера шрифта в выделенном диапазоне ячеек
Рассмотрим пример простой VBA процедуры Sub , задача которой – изменить форматирование выделенного диапазона ячеек. В ячейках устанавливается выравнивание по центру (и по вертикали, и по горизонтали) и размер шрифта изменяется на заданный пользователем:
Sub Format_Centered_And_Sized(Optional iFontSize As Integer = 10) Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize End Sub
Данная процедура Sub выполняет действия, но не возвращает результат.
В этом примере также использован необязательный (Optional) аргумент iFontSize . Если аргумент iFontSize не передан процедуре Sub , то его значение по умолчанию принимается равным 10. Однако же, если аргумент iFontSize передается процедуре Sub , то в выделенном диапазоне ячеек будет установлен размер шрифта, заданный пользователем.
VBA процедура «Sub»: Пример 2. Выравнивание по центру и применение полужирного начертания к шрифту в выделенном диапазоне ячеек
Следующая процедура похожа на только что рассмотренную, но на этот раз, вместо изменения размера, применяется полужирное начертание шрифта в выделенном диапазоне ячеек. Это пример процедуры Sub , которой не передаются никакие аргументы:
Sub Format_Centered_And_Bold() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Bold = True End Sub
Вызов процедуры «Sub» в Excel VBA
Вызов VBA процедуры «Sub» из другой процедуры
Чтобы вызвать VBA процедуру Sub из другой VBA процедуры, нужно записать ключевое слово Call , имя процедуры Sub и далее в скобках аргументы процедуры. Это показано в примере ниже:
Sub main() Call Format_Centered_And_Sized(20) End Sub
Если процедура Format_Centered_And_Sized имеет более одного аргумента, то они должны быть разделены запятыми. Вот так:
Sub main() Call Format_Centered_And_Sized(arg1, arg2, ...) End Sub
Вызов VBA процедуры «Sub» из рабочего листа
Процедура Sub не может быть введена непосредственно в ячейку листа Excel, как это может быть сделано с процедурой Function , потому что процедура Sub не возвращает значение. Однако, процедуры Sub , не имеющие аргументов и объявленные как Public (как будет показано далее), будут доступны для пользователей рабочего листа. Таким образом, если рассмотренные выше простые процедуры Sub вставлены в модуль в редакторе Visual Basic, то процедура Format_Centered_And_Bold будет доступна для использования на рабочем листе книги Excel, а процедура Format_Centered_And_Sized – не будет доступна, так как она имеет аргументы.
Вот простой способ запустить (или выполнить) процедуру Sub , доступную из рабочего листа:
- Нажмите Alt+F8 (нажмите клавишу Alt и, удерживая её нажатой, нажмите клавишу F8 ).
- В появившемся списке макросов выберите тот, который хотите запустить.
- Нажмите Выполнить (Run)
Чтобы выполнять процедуру Sub быстро и легко, можно назначить для неё комбинацию клавиш. Для этого:
- Нажмите Alt+F8 .
- В появившемся списке макросов выберите тот, которому хотите назначить сочетание клавиш.
- Нажмите Параметры (Options) и в появившемся диалоговом окне введите сочетание клавиш.
- Нажмите ОК и закройте диалоговое окно Макрос (Macro).
Внимание: Назначая сочетание клавиш для макроса, убедитесь, что оно не используется, как стандартное в Excel (например, Ctrl+C ). Если выбрать уже существующее сочетание клавиш, то оно будет переназначено макросу, и в результате пользователь может запустить выполнение макроса случайно.
Область действия процедуры VBA
В части 2 данного самоучителя обсуждалась тема области действия переменных и констант и роль ключевых слов Public и Private . Эти ключевые слова так же можно использовать применительно к VBA процедурам:
Помните о том, что если перед объявлением VBA процедуры Function или Sub ключевое слово не вставлено, то по умолчанию для процедуры устанавливается свойство Public (то есть она будет доступна везде в данном проекте VBA). В этом состоит отличие от объявления переменных, которые по умолчанию бывают Private .
Ранний выход из VBA процедур «Function» и «Sub»
Если нужно завершить выполнение VBA процедуры Function или Sub , не дожидаясь её естественного финала, то для этого существуют операторы Exit Function и Exit Sub . Применение этих операторов показано ниже на примере простой процедуры Function , в которой ожидается получение положительного аргумента для выполнения дальнейших операций. Если процедуре передано не положительное значение, то дальнейшие операции не могут быть выполнены, поэтому пользователю должно быть показано сообщение об ошибке и процедура должна быть тут же завершена:
Function VAT_Amount(sVAT_Rate As Single) As Single VAT_Amount = 0 If sVAT_Rate <= 0 Then MsgBox "Expected a Positive value of sVAT_Rate but Received " & sVAT_Rate Exit Function End If ... End Function
Обратите внимание, что перед тем, как завершить выполнение процедуры Function – VAT_Amount , в код вставлена встроенная VBA функция MsgBox , которая показывает пользователю всплывающее окно с предупреждением.
| Функция | Описание |
|---|---|
| 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. Пример использования функций для вырезания символов
Теперь рассмотрим примеры работы функций
Function имя
([список
_
аргументов
])
[инструкции
]
[имя
= выражение
]
[инструкции
]
[имя
= выражение
]
End Function
Значение всегда присваивается названию функции минимум один раз и, как правило, тогда, когда функция завершила выполнение. Создание пользовательской функции начните с создания модуля VBA (можно также использовать существующий модуль). Введите ключевое слово Function, после которого укажите название функции и список ее аргументов (если они есть) в скобках. Вы также можете объявить тип данных значения, которое возвращает функция, используя ключевое слово As (это делать необязательно, но рекомендуется). Вставьте код VBA, выполняющий требуемые действия, и убедитесь, что необходимое значение присваивается переменной процедуры, соответствующей названию функции, минимум один раз в теле функции. Функция заканчивается оператором End Function.
Имена функций подчиняются тем же правилам, что и . Если вы планируете использовать функцию в формуле рабочего листа, убедитесь, что название не имеет форму адреса ячейки. Также не присваивайте функциям имена, которые соответствуют названиям встроенных функций Excel. Если область действия функции не задана, то по умолчанию подразумевается Public. Функции, объявленные как Private, не отображаются в диалоговом окне Мастер функций .
Функцию можно вызвать одним из следующих способов:
- вызвать ее из другой процедуры;
- включить ее в формулу рабочего листа;
- включить в формулу условного форматирования;
- вызвать ее в окне отладки VBE (Immediate ). Этот метод обычно применяется на этапе тестирования (рис. 3).
Рис. 3. Вызов функции в окне отладки
В отличие от процедур, функции не отображаются в диалоговом окне Макрос (меню Разработчик –> Код –> Макросы ; или Alt+F8).
Аргументы функций
Аргументы могут представляться переменными (в том числе массивами), константами, символьными данными или выражениями. Некоторые функции не имеют аргументов. Функции имеют как обязательные, так и необязательные аргументы.
Функции без аргументов
В Excel есть несколько встроенных функций, не имеющих аргументов, например, СЛЧИС, СЕГОДНЯ, ТДАТА. Несложно создать аналогичные пользовательские функции. Например:
Function User()
" Возвращает имя пользователя
User = Application.UserName
End Function
При вводе формулы =User() ячейка возвращает имя текущего пользователя (рис. 4). Обратите внимание: при использовании функции без аргумента в формуле рабочего листа необходимо указать пустые скобки.
Рис. 4. Формула =User() возвращает имя текущего пользователя
Пользовательские функции ведут себя подобно встроенным функциям Excel. Обычно пользовательская функция пересчитывается тогда, когда это нужно, т.е. в случае изменения одного из аргументов функции. Однако вы можете выполнять пересчет функций чаще. Функция пересчитывается при изменении любой ячейки, если в процедуру добавлен оператор
Application.Volatile True
Метод Volatile объекта Application имеет один аргумент (True или False). Если функция выделена как volatile (изменяемая), она пересчитывается всякий раз, когда изменяется любая ячейка листа. При использовании аргумента False метода Volatile функция пересчитывается только тогда, когда в результате пересчета изменяется один из ее аргументов.
В Excel есть встроенная функция СЛЧИС. Но мне не слишком понравилось, что случайные числа изменяются при каждом пересчете рабочего листа. Поэтому я разработал функцию, которая возвращает случайные числа, не изменяющиеся при пересчете формул. Для этого была использована встроенная функция VBA Rnd:
Function StaticRand()
" Возвращает случайное число, не изменяемое при пересчете формул
StaticRand = Rnd()
End Function
Значения, полученные с помощью этой формулы, никогда не изменяются. Но у пользователя остается возможность принудительного пересчета формулы с помощью комбинации клавиш
Функция с одним аргументом
Рис. 5. Таблица комиссионных
Существует несколько способов вычислить комиссионные. Например, с помощью следующей формулы (если объем продаж поместить в ячейку D1):
ЕСЛИ(И(D1>=0;D1<=9999,99);D1*0,08;ЕСЛИ(И(D1>=10000;D1<=19999,99);D1*0,105; ЕСЛИ(И(D1>=20000;D1<=39999,99);D1*0,12;ЕСЛИ(D1>=40000;D1*0,14))))
Эта формула неудачна по нескольким причинам. Во-первых, она сложна, ее нелегко набрать, и в дальнейшем редактировать. Во-вторых, значения строго определены в формуле, из-за чего ее сложно изменять. Гораздо лучше использовать ВПР (рис. 6).
Рис. 6. Использование функции ВПР для вычисления комиссионных
Еще лучше (тогда не нужно использовать таблицу соответствия) создать пользовательскую функцию:
Function Commission(Sales)
Const Tier1 = 0.08
Const Tier2 = 0.105
Const Tier3 = 0.12
Const Tier4 = 0.14
" Вычисление комиссионных с продаж
Select Case Sales
Case 0 To 9999.99: Commission = Sales * Tier1
Case 10000 To 19999.99: Commission = Sales * Tier2
Case 20000 To 39999.99: Commission = Sales * Tier3
Case Is >= 40000: Commission = Sales * Tier4
End Select
End Function
После ввода в модуль VBA эту функцию можно использовать в формуле на рабочем листе или вызвать из других процедур VBA. При вводе в ячейку следующей формулы будет получен результат 3000:
Commission(В2)
Function DoubleCell()
DoubleCell = Range(" Al ") * 2
End Function
Хотя эта функция работает, в некоторых случаях она выдает неправильный результат. Причина в том, что вычислительный механизм Excel не учитывает диапазоны, которые не передаются в качестве аргументов. Вследствие этого иногда перед возвратом функцией значения, не вычисляются все связанные величины. Следует также написать функцию DoubleCell, в качестве аргумента которой передается значение ячейки А1.
Function DoubleCell(cell)
DoubleCell = cell * 2
End Function
Функция с двумя аргументами
Представим, что менеджер, о котором речь шла выше, внедряет новую политику, разработанную для уменьшения текучести кадров: общая сумма комиссионных, подлежащих выплате, увеличивается на 1% за каждый год, который служащий проработал в компании. Изменим пользовательскую функцию Commission так, чтобы она принимала два аргумента. Новый аргумент представляет количество лет, отработанных сотрудником в компании. Назовем эту новую функцию Commission2:
Function Commission2(Sales, Years) As Single
" Вычисление комиссионных с продаж на основе
" длительности стажа
Commission2 = Commission(Sales) + _
(Commission(Sales) * Years / 100)
End Function
Функция с аргументом в виде массива
В качестве аргументов функции могут принимать один или несколько массивов, обрабатывать этот массив (массивы) и возвращать единственное значение. Функция, представленная ниже, принимает в качестве аргумента массив и возвращает сумму его элементов.
Function SumArray(List) As Double
Dim Item As Variant
SumArray = 0
For Each Item In List
If WorksheetFunction.IsNumber(Item) Then _
SumArray = SumArray + Item
Next Item
End Function
Функция Excel ЕЧИСЛО проверяет, является ли каждый элемент числом, прежде чем добавить его к общему целому. Добавление этого простого оператора проверки данных устраняет ошибки несоответствия типов при попытке выполнить арифметическую операцию над строкой.
Функция с необязательными аргументами
Многие встроенные функции Excel имеют необязательные аргументы. Пример - функция ЛЕВСИМВ, возвращающая символы с левого края строки. Она имеет следующий синтаксис:
ЛЕВСИМВ(текст, кол_символов )
Первый аргумент - обязательный, в отличие от второго. Если не указан второй аргумент, Excel предполагает значение 1.
Пользовательские функции, разработанные в VBA, также могут иметь необязательные аргументы. Необязательный аргумент вы зададите, если введете перед именем аргумента ключевое слово Optional. В списке аргументов необязательные аргументы определяются после всех обязательных. Например:
Function User2(Optional Uppercase As Variant)
If IsMissing(Uppercase) Then Uppercase = False
User2 = Application.UserName
If Uppercase Then User2 = UCase(User2)
End Function
Если аргумент равен False или опущен, то имя пользователя возвращается без каких-либо изменений. Если же аргумент функции True, то имя пользователя возвращается в символах верхнего регистра (с помощью VBA-функции Ucase). Обратите внимание на первый оператор функции - он содержит VBA-функцию IsMissing, которая определяет наличие аргумента. Если аргумент отсутствует, оператор присваивает переменной Uppercase значение False (задано по умолчанию).
Функция VBA, возвращающая массив
VBA содержит весьма полезную функцию с названием Array. Она возвращает значение с типом данных Variant, которое содержит массив (т.е. несколько значений). Если вы не знакомы с формулами массивов в Excel, предлагаю начать с . Формула массива вводится в ячейку после нажатия
Функция MonthNames - простой пример применения функции Array в пользовательской функции.
Function MonthNames()
MonthNames = Array(" Январь " , " Февраль " , " Март " , _
" Апрель " , " Май " , " Июнь " , " Июль " , " Август " , _
" Сентябрь " , " Октябрь " , " Ноябрь " , " Декабрь "
End Function
Функция MonthNames возвращает горизонтальный массив названий месяцев. На рабочем листе выделите 12 ячеек, введите формулу =MonthNames() и нажмите
Функция, возвращающая значение ошибки
- xlErrDivO (для ошибки #ДЕЛ/0!);
- xlErrNA (для ошибки #Н/Д);
- xlErrName (для ошибки #ИМЯ?);
- xlErrNull (для ошибки #ПУСТО!);
- xlErrNum (для ошибки #ЧИСЛО!);
- xlErrRef (для ошибки #ССЫЛ!);
- xlErrValue (для ошибки #ЗНАЧ!).
Ниже приведена преобразованная функция RemoveVowels (см. пример в начале). Конструкция If-Then применяется для выполнения альтернативного действия в случае, когда аргумент не является текстовым. Эта функция вызывает функцию Excel ЕТЕКСТ, которая определяет, содержит ли аргумент текст. Если ячейка содержит текст, то функция возвращает нормальный результат. Если же ячейка содержит не текст (или пуста), то функция возвращает ошибку #ЗНАЧ!
Function RemoveVowels3(txt) As Variant
" Удаляет все гласные буквы из аргумента Txt
" Возвращает ошибку #ЗНАЧ!, если аргумент — не строка
Dim i As Long
RemoveVowels3 = " "
If Application.WorksheetFunction.IsText(txt) Then
For i = 1 To Len(txt)
If Not UCase(Mid(txt, i, 1)) Like " " Then
RemoveVowels3 = RemoveVowels3 & Mid(txt, i, 1)
End If
Next i
Else
RemoveVowels3 = CVErr(xlErrValue)
End If
End Function
Обратите внимание, что был изменен тип данных для возвращаемого функцией значения. Поскольку функция может возвращать что-то еще, кроме строки, тип данных был изменен на Variant.
Функция с неопределенным количеством аргументов
Существует возможность создавать пользовательские функции, имеющие неопределенное количество аргументов. Примените в качестве последнего (или единственного) аргумента массив и добавьте перед ним ключевое слово ParamArray (ParamArray относится только к последнему аргументу в списке аргументов процедуры. Он всегда имеет тип данных Variant и всегда является необязательным аргументом). Следующая функция возвращает сумму всех аргументов, в качестве которых может выступать, как одно значение (ячейка), так и диапазон.
Function SimpleSum(ParamArray arglist() As Variant) As Double
Dim cell As Range
Dim arg As Variant
For Each arg In arglist
For Each cell In arg
SimpleSum = SimpleSum + cell
Next cell
Next arg
End Function
Отладка функций
При использовании формулы на рабочем листе для тестирования функции происходящие в процессе выполнения ошибки не отображаются в знакомом диалоговом окне сообщений. Формула просто возвращает значение ошибки (#ЗНАЧ!). К счастью, это не представляет большой проблемы при отладке функций, так как всегда существует несколько обходных путей.
- Поместите в важных местах функцию MsgBox, чтобы контролировать значения отдельных переменных.
- Протестируйте функцию, вызвав ее из процедуры, а не в формуле рабочего листа. Ошибки в процессе выполнения отображаются обычным образом.
- Определите точку остановки в функции и просмотрите функцию пошагово. При этом можно воспользоваться всеми стандартными инструментами отладки. Чтобы добавить точку остановки, поместите курсор в операторе, в котором вы решили приостановить выполнение, и выберите команду Debug
–> Toggle Breakpoint
(Отладка
–> Точка остановки
) или нажмите
. - Используйте в программе один или несколько временных операторов Print (Отладка, Печать), чтобы отобразить значения в окне Immediate редактора VBA. Например, чтобы проконтролировать циклически изменяемое значение, используйте следующий метод:
Рис. 7. Используйте окно отладки для отображения результатов при выполнении функции
В данном случае значения двух переменных, Ch и i, выводятся в окне отладки (Immediate ) всякий раз, когда в программе встречается оператор Debug.Print. Встаньте курсором в любое место процедуры Test() и нажмите F5. На рис. 7 показан результат для случая, когда функция принимает аргумент TusconArizona.
Использование метода MacroOptions
Можно воспользоваться методом MacroOptions объекта Application, который позволяет включить в состав встроенных функций Excel разработанные вами функции. Этот метод позволяет:
- добавить описание функции (начиная с версии Excel 2010;
- указать категорию функции;
- добавить описание аргументов функции.
Sub DescribeFunction()
Dim FuncName As String
Dim FuncDesc As String
Dim FuncCat As Long
Dim Arg1Desc As String, Arg2Desc As String
FuncName = " Draw "
FuncDesc = " Содержимое случайной ячейки диапазона "
FuncCat = 5 " Ссылки и массивы
Arg1Desc = " Диапазон, который содержит значения "
Arg2Desc = " (не обязательный) Если False или отсутствует, _
функция Rnd не пересчитывается. "
Arg2Desc = Arg2Desc & " Если True, функция Rnd пересчитывается "
Arg2Desc = Arg2Desc & " при любом изменении на листе. "
Application.MacroOptions _
Macro:=FuncName, _
Description:=FuncDesc, _
Category:=FuncCat, _
ArgumentDescriptions:=Array(Arg1Desc, Arg2Desc)
End Sub
На рис. 8 показаны диалоговые окна Мастер функций и Аргументы функции после выполнения процедуры DescribeFunction().
Рис. 8. Вид диалоговых окон Мастер функций и Аргументы функции для пользовательской функции
Процедуру DescribeFunction()следует вызывать только один раз. После ее вызова информация, связанная с функцией, сохраняется в рабочей книге. Но если вы модифицировали процедуру, повторите ее вызов.
Если вы не укажете категорию функции с помощью метода MacroOptions, пользовательская функция рабочего листа появится в категории Определенные пользователем диалогового окна Мастер функций . В таблице (рис. 9) перечислены номера категорий, которые можно использовать в качестве значений аргумента Category метода MacroOptions. Обратите внимание, что некоторые из этих категорий (от 10 до 13) обычно не отображаются в диалоговом окне Мастер функций . Если же отнести одну из пользовательских функций в подобную категорию, она появится в диалоговом окне.
Использование надстроек для хранения пользовательских функций
При желании можно сохранить часто используемые пользовательские функции в файле надстройки. Основное преимущество такого подхода заключается в следующем: функции могут быть применены в формулах без спецификатора имени файла. Предположим, у вас есть пользовательская функция ZapSpaces; она хранится в файле Myfuncs.xlsm. Чтобы применить ее в формуле другой рабочей книги (отличной от Myfuncs.xlsm), необходимо ввести следующую формулу: =Myfuncs.xlsm!ZapSpaces(А1:С12).
Если вы создадите надстройку на основе файла Myfuncs.xlsm и эта надстройка будет загружена в текущем сеансе работы Excel, то ссылку на файл можно пропустить, введя следующую формулу: =ZapSpaces(А1:С12). Создание надстроек будет рассмотрено отдельно.
Потенциальная проблема, которая может возникнуть из-за использования надстроек для хранения пользовательских функций, связана с зависимостью рабочей книги от файла надстроек. Если вы передаете рабочую книгу сотруднику, не забудьте также передать копию надстройки, которая содержит требуемые функции.
Использование функций Windows API
VBA может заимствовать методы из других файлов, которые не имеют ничего общего с Excel или VBA, например, файлы DLL (Dynamic Link Library - динамически подключаемая библиотека), которые используются Windows и другими программами. В результате в VBA появляется возможность выполнять операции, которые без заимствованных методов находятся за пределами возможностей языка.
Windows API (Application Programming Interface - интерфейс прикладного программирования) представляет собой набор функций, доступных программистам в среде Windows. При вызове функции Windows из VBA вы обращаетесь к Windows API. Многие ресурсы Windows, используемые программистами Windows, можно получить из файлов DLL, в которых хранятся программы и функции, подсоединяемые в процессе выполнения программы, а не во время компиляции.
Прежде чем использовать функцию Windows API, ее необходимо объявить вверху программного модуля. Если программный модуль - это не стандартный модуль VBA (т.е. модуль для UserForm , Лист или ЭтаКнига ), то API-функцию необходимо объявить, как Private.
Объявление API-функции имеет некоторую сложность - функция должна объявляться максимально точно. Оператор объявления указывает VBA следующее:
- какую API-функцию вы используете;
- в какой библиотеке расположена API-функция;
- аргументы API-функции.
После объявления API-функцию можно использовать в программе VBA.
Рассмотрим пример API-функции, которая отображает имя папки Windows (с помощью стандартных операторов VBA эту задачу порой выполнить невозможно). Для начала объявим API-функцию:
Declare PtrSafe Function GetWindowsDirectoryA Lib " kernel32 " _
(ByVal lpBuffer As String, ByVal nSize As Long) As Long
Эта функция, имеющая два аргумента, возвращает название папки, в которой установлена операционная система Windows. После вызова этой функции путь к папке Windows будет храниться в переменной lpBuffer, а длина строки пути - в переменной nSize.
Следующий пример отображает результат в окне сообщения:
Sub ShowWindowsDir()
Dim WinPath As String * 255
Dim WinDir As String
WinPath = Space(255)
WinDir = Left(WinPath, GetWindowsDirectoryA _
(WinPath, Len(WinPath)))
MsgBox WinDir, vbInformation, " Windows Directory "
End Sub
В процессе выполнения процедуры ShowWindowsDir отображается окно сообщения с указанием расположения папки Windows.
Иногда требуется создать оболочку (wrapper) для API-функций. Другими словами, вы создадите собственную функцию, использующую API-функцию. Такой подход существенно упрощает использование API-функции. Ниже приведен пример такой функции VBA:
Function WindowsDir() As String
" Название папки Windows
Dim WinPath As String * 255
WinPath = Space(255)
WindowsDir = Left(WinPath, GetWindowsDirectoryA _
(WinPath, Len(WinPath)))
End Function
После объявления этой функции можно вызвать ее из другой процедуры: MsgBox WindowsDir(). Можно также использовать эту функцию в формуле рабочего листа: =WindowsDir().
Внимание! Не удивляйтесь сбоям в системе при использовании в VBA функций Windows API. Заранее сохраните свою работу перед тестированием.
Определение состояния клавиши
Предположим, вы написали макрос VBA, который будет выполняться с помощью кнопки на панели инструментов. Необходимо, чтобы этот макрос выполнялся по-другому, если пользователь после щелчка на кнопке удерживает клавишу
Ниже приведена программа, которая выявляет, что при выполнении процедуры обработки события Button_Click была нажата клавиша
Рис. 10. Проверка нажатия клавиш Shift, Ctrl и Alt
Код функции VBA можно найти в приложенном Excel-файле
Работа с функциями Windows API может быть довольно сложной. Во многих книгах по программированию перечислены операторы объявления API-функций с соответствующими примерами. Как правило, можно просто скопировать выражения объявления и использовать функции, не вникая в их суть. Большинство VBA-программистов в Excel рассматривают API-функции как панацею для решения большинства задач. В Интернете вы найдете сотни вполне надежных примеров, которые можно скопировать и вставить в собственную программу.
В текстовом файле содержатся объявления и константы Windows API. Можно открыть этот файл в текстовом редакторе и скопировать соответствующие объявления в модуль VBA.
По материалам книги . – М: Диалектика, 2013. – С. 287–323.
Функция написанная на 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.