При написании кода VBA в Excel набор встроенных операторов используют на каждом шагу. Эти операторы делятся на математические, строковые, операторы сравнения и логические. Далее мы подробно рассмотрим каждую группу операторов.
Математические операторы
Основные математические операторы VBA перечислены в таблице ниже.
В правом столбце таблицы указан приоритет операторов, принятый по умолчанию при отсутствии скобок. Добавляя в выражение скобки, можно изменять порядок выполнения операторов VBA по своему желанию.
Строковые операторы
Основной строковый оператор в Excel VBA – это оператор конкатенации & (слияние):
Операторы сравнения
Операторы сравнения используются для сравнения двух чисел или строк и возвращают логическое значение типа Boolean (True или False). Основные операторы сравнения Excel VBA перечислены в этой таблице:
Логические операторы
Логические операторы, как и операторы сравнения, возвращают логическое значение типа Boolean (True или False). Основные логические операторы Excel VBA перечислены в таблице ниже:
В приведённой выше таблице перечислены не все логические операторы, доступные в VBA. Полный список логических операторов можно найти на сайте Visual Basic Developer Center .
Встроенные функции
В VBA доступно множество встроенных функций, которые могут быть использованы при написании кода. Ниже перечислены некоторые из наиболее часто используемых:
| Функция | Действие |
|---|---|
| Abs | Возвращает абсолютную величину заданного числа.
|
| Chr | Возвращает символ ANSI, соответствующий числовому значению параметра.
|
| Date | Возвращает текущую системную дату. |
| DateAdd | Добавляет определённый временной интервал к заданной дате. Синтаксис функции:
DateAdd(интервал , число , дата ) Где аргумент интервал определяет тип временного интервала, добавляемого к заданной дате в количестве, указанном в аргументе число . Аргумент интервал может принимать одно из следующих значений:
|
| DateDiff | Вычисляет количество определённых временных интервалов между двумя заданными датами.
|
| Day | Возвращает целое число, соответствующее дню месяца в заданной дате.
Пример: Day(“29/01/2015”) возвращает число 29. |
| Hour | Возвращает целое число, соответствующее количеству часов в заданном времени.
Пример: Hour(“22:45:00”) возвращает число 22. |
| InStr | Принимает в качестве аргументов целое число и две строки. Возвращает позицию вхождения второй строки внутри первой, начиная поиск с позиции, заданной целым числом.
Примечание: Аргумент-число может быть не задан, в таком случае поиск начинается с первого символа строки, заданной во втором аргументе функции. |
| Int | Возвращает целую часть заданного числа.
Пример: Int(5.79) возвращает результат 5. |
| Isdate | Возвращает True
, если заданное значение является датой, или False
– если датой не является.
|
| IsError | Возвращает True , если заданное значение является ошибкой, или False – если ошибкой не является. |
| IsMissing | В качестве аргумента функции передаётся имя необязательного аргумента процедуры. IsMissing возвращает True , если для рассматриваемого аргумента процедуры не передано значение. |
| IsNumeric | Возвращает True , если заданное значение может быть рассмотрено как число, в противном случае возвращает False . |
| Left | Возвращает заданное количество символов от начала переданной строки. Синтаксис функции вот такой:
Left(строка , длина ) где строка – это исходная строка, а длина – количество возвращаемых символов, считая от начала строки.
|
| Len | Возвращает количество символов в строке.
Пример: Len(“абвгдеж”) возвращает число 7. |
| Month | Возвращает целое число, соответствующее месяцу в заданной дате.
Пример: Month(“29/01/2015”) возвращает значение 1. |
| Mid | Возвращает заданное количество символов из середины переданной строки. Синтаксис функции:
Mid(строка , начало , длина ) где строка – это исходная строка, начало – позиция начала извлекаемой строки, длина – количество символов, которые требуется извлечь.
|
| Minute | Возвращает целое число, соответствующее количеству минут в заданном времени.Пример: Minute(“22:45:15”) возвращает значение 45. |
| Now | Возвращает текущую системную дату и время. |
| Right | Возвращает заданное количество символов от конца переданной строки. Синтаксис функции:
Right(строка , длина ) Где строка – это исходная строка, а длина – это количество символов, которые надо извлечь, считая от конца заданной строки.
|
| Second | Возвращает целое число, соответствующее количеству секунд в заданном времени.
Пример: Second(“22:45:15”) возвращает значение 15. |
| Sqr | Возвращает квадратный корень числовой величины, переданной в аргументе.
|
| Time | Возвращает текущее системное время. |
| Ubound | Возвращает верхний индекс измерения заданного массива.
Примечание: Для многомерных массивов в качестве необязательного аргумента может быть указано, индекс какого именно измерения нужо возвратить. Если не указано, то по умолчанию равно 1. |
Функция представляет собой группу повторно используемого кода, который может быть вызван в любом месте в вашей программе. Это устраняет необходимость повторного написания одного и того же кода. Это позволяет программистам разделить большую программу на множество небольших и управляемых функций.
Помимо встроенных функций, VBA также позволяет писать пользовательские функции. В этой статье вы узнаете, как писать свои собственные функции в VBA.
Определение функции
Функция VBA может иметь необязательный оператор return. Это необходимо, если вы хотите вернуть значение из функции.
Например, вы можете передать два числа в функции, а затем вы можете ожидать от функции возврата своего умножения в вашу вызывающую программу.
Примечание . Функция может возвращать несколько значений, разделенных запятой, как массив, назначенный самому имени функции.
Прежде чем использовать функцию, нам нужно определить эту конкретную функцию. Наиболее распространенным способом определения функции в VBA является использование ключевого слова Function , за которым следует уникальное имя функции, и оно может содержать или не содержать список параметров и оператор с ключевым словом End Function , который указывает конец функции. Ниже приведен базовый синтаксис.
Синтаксис
Добавьте кнопку и добавьте следующую функцию.
Function Functionname(parameter-list) statement 1 statement 2 statement 3 ....... statement n End Function
Пример
Добавьте следующую функцию, которая возвращает область. Обратите внимание, что значение / значения могут быть возвращены с именем самой функции.
Function find_area(Length As Double, Optional Width As Variant) If IsMissing(Width) Then find_area = Length * Length Else find_area = Length * Width End If End Function
Вызов функции
Чтобы вызвать функцию, вызовите функцию, используя имя функции, как показано на следующем снимке экрана.
Вывод области, как показано ниже, будет отображаться пользователю.
Функция-процедура - это особый вид процедуры VBA, возвращающей результат. Пользовательские функции-процедуры, как и встроенные функции 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 , которая показывает пользователю всплывающее окно с предупреждением.