Именованные аргументы функции

Аргументы функции необходимо перечислять в определенном порядке. Так для функции MsgBox первый аргумент является сообщением для отображения, второй - это число и тип кнопок для диалогового окна, третий - заголовок диалогового окна. Об этом подсказывает всплывающее окно "Краткие сведения", появляющееся при вводе имени функции.

Аргументы функции необходимо перечислять в определенном порядке. Так для функции MsgBox первый аргумент является сообщением для отображения, второй - это число и тип кнопок для диалогового окна, третий - заголовок диалогового окна. Об этом подсказывает всплывающее окно "Краткие сведения", появляющееся при вводе имени функции.

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

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

Чтобы предотвратить подобные ошибки, VBA дает возможность использования именованных аргументов функций. Обратите внимание на следующий листинг.

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

Полный синтаксис функции MsgBox следующий:

MsgBox (Prompt [, Buttons] [, Title] [, HelpFile, Context])

Единственным обязательным аргументом является аргумент Prompt, который может быть любым строковым выражением и содержит информацию, которая выводится в диалоговом окне. Все остальные аргументы - необязательные.

Аргумент Title - строка для заголовка диалогового окна.

Аргумент HelpFile - строковое выражение, содержащее имя справочного файла Windows (который уже должен быть создан).

Аргумент Context - численное выражение, указывающее раздел в справочном файле, относящийся к отображаемому диалоговому окну.

Аргумент Buttons - численное выражение, которое задает количество и тип кнопок в диалоговом окне. Также указывает кнопку по умолчанию и, содержит ли это диалоговое окно стандартные значки Windows для предупредительных сообщений и запросов пользователя.

Функция InputBox

InputBox - еще одна довольно часто используемая функция для обмена данными с пользователем.

Полный синтаксис функции InputBox следующий:

InputBox (Prompt [, Title] [, Default] [, XPos] [, YPos] [, HelpFile, Context])

Аргументы Prompt (единственный обязательный аргумент), Title, HelpFile, Context - аналогичны уже рассмотренным одноименным аргументам функции MsgBox.

АргументDefault - строковое выражение, использующееся как значение по умолчанию для пользовательского ввода.

АргументыXPos, YPos - любые численные выражения. Эти аргументы задают координаты вывода диалогового окна на экране монитора. XPos - горизонтальное расстояние от левого края окна. YPos - вертикальное расстояние от верхнего края окна. Оба расстояния измеряются в твипах (1 твип = 1/20 точки = 0,0007 дюйма). Следует быть внимательным при использовании этих аргументов, т.к. при их больших значениях диалоговое окно может "уехать" за пределы экрана монитора.

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

При вызове InputBox можно использовать именованные аргументы.

Нельзя смешивать именованные аргументы с обычным списком аргументов в одном и том же вызове функции

ФУНКЦИИ VBA

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

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

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

Функция(аргумент)	Действие
Abs(N)	Возвращает абсолютное значение аргумента N
Atn(N)	Возвращает арктангенс N (радиан)
Cos(N)	Возвращает косинус N (радиан)
Exp(N)	Возвращает константу е (натуральный логарифм = 2.718282...), возведенную в степень N
Int(N)	Возвращает целую часть N. Не округляет число, а отбрасывает любую дробную часть. Если N отрицательное число, то возвращается ближайшее отрицательное целое меньшее, чем или равное N
Log(N)	Возвращает натуральный логарифм N
Rnd(N)	Возвращает случайное число: аргумент является необязательным.
Sgn(N)	Возвращает знак числа: -1, если N - отрицательное; 1, если N - положительное; 0, если N равно 0
Sin(N)	Возвращает синус N (радиан)
Sqr(N)	Возвращает корень квадратный из N. Если N является отрицательным числом - возвращается ошибка времени исполнения
Tan(N)	Возвращает тангенс N (радиан)

ОПЕРАТОРЫ ВЕТВЛЕНИЯ VBA

До сих пор рассматривались процедуры и функции, которые VBA выполняет в линейном порядке - VBA начинает выполнение кода с первого оператора после строки объявления процедуры (функции) и продолжает выполнять каждый оператор построчно до тех пор, пока не будет достигнут оператор End Sub (End Function). Такой линейный алгоритм можно представить нижеследующей блок-схемой:

Однако довольно часто встречаются ситуации, когда необходимо, чтобы процедуры или функции выполняли различные действия при разных условиях. Такую ситуацию иллюстрируют следующие блок-схемы:

Операторы, выполняющие роль ветвления программы на основании какого-либо условия, называются операторами условного перехода.

Простейшими операторами условного перехода являются операторы If..Then; If..Then..Else. Первый оператор позволяет выбирать единственную ветвь процедуры (левая блок-схема), тогда как второй дает возможность выбирать из двух альтернативных ветвей кода процедуры (правая блок-схема) на основе оценки того, является ли условие равным True или False.

СИНТАКСИС IF..THEN

Вариант 1:

If Condition Then Statements

Вариант 2:

If Condition Then

Statements

End If

Condition - логическое выражение;

Statements - один, несколько или ни одного оператора VBA.

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

Второй вариант позволяет указывать несколько операторов в разных строках.

Логика работы следующая: VBA сначала оценивает логическое выражение, представленное в секции Condition; если это логическое выражение равно True то выполняется оператор (операторы) секции Statements; затем VBA продолжает выполнение кода следующего за строкой If..Then (Вариант 1) или за ключевыми словами End If (Вариант 2). Если же логическое выражение равно False, то операторы секции Statements пропускаются и выполняется код, следующий за этой секцией.

Ниже приведен листинг элементарного использования первого варианта написания If..Then:

А теперь - второй вариант:

Обратите внимание, что операторы секции Statements написаны с использованием отступа (используется табуляция). Это важный момент. Следует сразу приучать себя к подобному оформлению программного кода - это повышает его удобочитаемость и значительно облегчает поиск ошибок.

Синтаксис If..Then..Else

Вариант 1:

If Condition Then Statements Else ElseStatements

Вариант 2:

If Condition Then

Statements

Else ElseStatements

End If

Condition - логическое выражение;

Statements, ElseStatements - один, несколько или ни одного оператора VBA.

По аналогии с If..Then - первый вариант требует написания оператора в одну строку, при этом в секциях Statements и ElseStatements можно указывать несколько операторов, разделяя их двоеточием.

Второй вариант позволяет указывать несколько операторов в разных строках.

Логика работы следующая: VBA сначала оценивает логическое выражение, представленное в секции Condition; если это логическое выражение равно True то выполняется оператор (операторы) секции Statements; затем VBA продолжает выполнение кода следующего за строкой If..Then (Вариант 1) или за ключевыми словами End If (Вариант 2). Если же логическое выражение равно False, то выполняются операторы секции ElseStatements.

Следует сказать, что блок операторов If..Then..Else (Вариант 2) легче читать и понимать. Поэтому, советую использовать именно этот вариант, даже если в секциях Statements и ElseStatements будет находиться по одному оператору.

Если требуется реализовать более сложное ветвление используется следующая запись оператора выбора If..Then..ElseIf..End If:

СИНТАКСИС:

If Condition Then

Statements

ElseIf Condition1 Then

Statements1

…………………………

………………………….

ElseIf ConditionN Then

StatementsN

Else

ElseStatements

End If

Логика работы следующая: VBA сначала оценивает логическое выражение, представленное в секции Condition; если это логическое выражение равно True то выполняется оператор (операторы) секции Statements; затем VBA продолжает выполнение кода следующего ключевым словом End If. Если же логическое выражение равно False, то выполняются проверка условия Condition1; если это логическое выражение равно True то выполняется оператор (операторы) секции Statements1; затем VBA продолжает выполнение кода следующего ключевым словом End If. В противном случае проверяется условие Condition2 и т.д. пока не будет выполнена проверка условия ConditionN; если это логическое выражение равно True то выполняется оператор (операторы) секции StatementsN; затем VBA продолжает выполнение кода следующего ключевым словом End If. Если же логическое выражение равно False, то выполняются операторы секции ElseStatements.

Sub sample3()

Dim value As Long

Const MSG = "Вы ввели число, "

value = InputBox(prompt:="Введите число", Title:="Пример 4")

If value = 0 Then

MsgBox (MSG & "равное 0")

ElseIf value > 0 Then

MsgBox (MSG & "большее 0")

Else

MsgBox (MSG & "меньшее 0")

End If

End Sub

ЦИКЛЫ VBA

Какие либо действия процедуры повторяющиеся заданное количество раз или пока выполняется или не выполняется некоторое условие называют циклом.

Процесс выполнения все операторов, заключенных в структуру цикла, один раз называется итерацией цикла.

Структуры цикла, всегда выполняющиеся заданное количество раз, называются циклами с фиксированным числом итераций. Другие типы структур цикла повторяются переменное количество раз в зависимости от некоторого набора условий. Такие циклы называются неопределенными циклами.

Блок операторов, находящийся между началом и концом цикла называется "тело цикла".

Самой простой структурой цикла является фиксированный цикл.

Цикл For..Next

Синтаксис

Forcounter=StartToEnd[StepStepSize]

Statements

Next [counter]

Counter- любая численная переменная VBA

Start - любое численное выражение , определяет начальное значение для переменной counter

End - численное выражение, определяет конечное значение для переменной counter

Statements - один, несколько или ни одного оператора VBA (тело цикла).

По умолчанию VBA увеличивает переменную counter на 1 каждый раз при выполнении операторов в цикле. Можно задать другое значение (SterSize - любое численное выражение), на которое будет изменяться counter.

Ключевое слово Next сообщает VBA о том, что достигнут конец цикла. Необязательная переменная counter после ключевого слова Next должна быть той же самой переменной counter, которая была задана после ключевого слова For в начале структуры цикла.

Ниже представлен листинг простейшего цикла For..Next, который считает сумму цифр от 1 до 10:

А теперь два варианта цикла For..Next с использованием шага цикла отличного от единицы:

Обратите внимание! При уменьшении счетчика цикла For..Next цикл выполняется, пока переменная счетчика больше или равна конечному значению, а когда счетчик цикла увеличивается, цикл выполняется, пока переменная счетчика меньше или равна конечному значению.

Цикл For Each..Next

Цикл For Each..Next не использует счетчик цикла. Циклы For Each..Next выполняются столько раз, сколько имеется элементов в определенной группе, такой как коллекция объектов или массив (которые будут рассматриваться позже). Проще говоря, цикл For Each..Next выполняется один раз для каждого элемента в группе.

СИНТАКСИС:

For EachElementInGroup

Statements

Next [Element]

Element - переменная, используемая для итерации по всем элементам в определенной группе

Group - это объект коллекции или массив

Statements - один, несколько или ни одного оператора VBA (тело цикла).

Цикл For Each..Next всегда выполняется столько раз, сколько имеется элементов в определенной группе.

В нижеприведенном листинге показана функция SheetExists, использующая цикл For Each..Next для определения того, существует ли определенный лист в рабочей книге Excel:

Цикл Do .. While

Конструкция цикла, тестирующая свое условие детерминанта до выполнения цикла.

СИНТАКСИС:

Do WhileCondition

Statements

Loop

Condition - логическое выражение для детерминанта цикла

Statements - один, ни одного или несколько операторов, которые составляют тело цикла

Loop - ключевое слово, указывает на окончание тела цикла и обозначает место, из которого VBA возвращается в начало цикла для проверки условия

VBA выполняет цикл пока логическое выражение, представленное с помощью Condition, равно True.

При выполнении цикла Do While сначала тестируется логическое выражение (Condition); если оно равно True - выполняется тело цикла. При достижении ключевого слова Loop управление опять передается в начало цикла и снова проверяется логическое выражение. Так происходит до тех пор, пока логическое выражение не станет False. Когда логическое выражение становится False - управление передается оператору, следующему за ключевым словом Loop.

Обратите внимание! Если логическое выражение равно False при первом выполнении цикла Do While, то управление сразу передается оператору, следующему за Loop, а операторы, находящиеся в теле цикла соответственно пропускаются. Другими словами говоря, цикл Do While позволяет ни разу не выполнять операторы внутри него.

В нижеприведенном листинге представлен элементарный цикл Do While, подсчитывающий сумму цифр от 1 до 10:

Цикл Do .. Until

Еще один цикл, тестирующий условие детерминанта до выполнения цикла.

СИНТАКСИС:

Do Until Condition

Statements

Loop

Condition - логическое выражение для детерминанта цикла

Statements - один, ни одного или несколько операторов, которые составляют тело цикла

Loop - ключевое слово, указывает на окончание тела цикла и обозначает место, из которого VBA возвращается в начало цикла для проверки условия

VBA выполняет цикл пока логическое выражение, представленное с помощью Condition, равно False.

В остальном цикл Do Until полностью аналогичен циклу Do While.

Листинг, использующий цикл Do Until для подсчета цифр от 1 до 10, будет выглядеть так:

Для тестирования условий после выполнения тела цикла надо поместить логическое выражение в конец блока операторов, составляющих тело цикла, после ключевого слова Loop, которое сообщает о конце цикла.

Цикл Do .. Loop While

Конструкция цикла, тестирующая свое условие детерминанта после выполнения цикла.

СИНТАКСИС:

Do

Statements

Loop While Condition

Condition - логическое выражение для детерминанта цикла

Statements - один, ни одного или несколько операторов, которые составляют тело цикла

Loop While - ключевое слово, указывает на окончание тела цикла и обозначает место, из которого VBA возвращается в начало цикла после проверки условия

VBA выполняет цикл пока логическое выражение, представленное с помощью Condition, равно True.

При выполнении цикла Do Loop While сначала выполняются операторы тела цикла, затем по достижении ключевого слова Loop тестируется логическое выражение (Condition); если оно равно True - управление передается в начало тела цикла и цикл повторяется снова. Так происходит до тех пор, пока логическое выражение не станет False. Когда логическое выражение становится False - управление передается оператору, следующему за строкой Loop While...

Обратите внимание! Даже если при первом выполнении цикла Do Loop Whileлогическое выражение равно False тело цикла всё равно будет выполнено. Другими словами говоря, независимо от значения логического выражения, представленного с помощью Condition, этот цикл всегда выполняется, по крайней мере, один раз.

Листинг, использующий цикл Do Loop While для подсчета цифр от 1 до 10, будет выглядеть так:

Цикл Do .. Loop Until

Еще один цикл, тестирующий условие детерминанта после выполнения цикла.

Синтаксис:

Do

Statements

Loop Until Condition

Condition - логическое выражение для детерминанта цикла

Statements - один, ни одного или несколько операторов, которые составляют тело цикла

Loop - ключевое слово, указывает на окончание тела цикла и обозначает место, из которого VBA возвращается в начало цикла после проверки условия

VBA выполняет цикл пока логическое выражение, представленное с помощью Condition, равно False.

В остальном цикл Do Loop Until полностью аналогичен циклу Do Loop While.

Листинг, использующий цикл Do Loop Until для подсчета цифр от 1 до 10, будет выглядеть так:

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

Вложенные циклы

Циклы можно помещать внутрь друг друга. Помещение одной структуры цикла в другую называется вложением циклов. Можно делать вложение структур циклов разного типа.

При вложении циклов надо соблюдать определенные правила:

При вложении циклов For..Next каждый цикл должен иметь свою уникальную переменную счетчика;

При вложении циклов For Each..Next каждый цикл должен иметь свою уникальную element-переменную;

Если используется оператор Exit For или Exit Do во вложенном цикле, этим оператором заканчивается только выполняемый в данный момент цикл. VBA продолжает выполнение следующего цикла более высокого уровня.

В нижеприведенном листинге показан простейший пример использования вложенных циклов For..Next:

МАССИВЫ VBA

Массив - это коллекция переменных, которые имеют общие имя и базовый тип. Все элементы данных, сохраняемых в массиве, должны иметь один и тот же тип. Информация, сохраненная в массиве, может быть доступна в любом порядке.

Массив позволяет сохранять и манипулировать многими элементами данных посредством единственной переменной. Обработку массивов значительно упрощает использование циклов.

Одномерные массивы

Одномерный массив - это самый простой вариант массива, использующий обыкновенный список данных. Например:

Вася, Петя, Коля, Миша, Ваня, Слава, Игорь, Юра, Саша, Вова

Это строковый массив, состоящий из 10 элементов. Дадим ему название

My_Array.

Нумерация элементов в массиве начинается с 0. Такая система нумерации довольно распространена в программировании и называется нумерацией с нулевой базой.

Для доступа к данным, хранящимся в определенном элементе массива, следует указывать имя массива с последующим числом, называемым индексом элемента. Индекс всегда заключается в круглые скобки. Например: My_Array(3) - этому элементу нашего массива соответствует "Миша" (не забывайте, что по умолчанию нумерация элементов массива начинается с 0).

Поскольку нумерация с нулевой базой не очень удобна (т.к. мы привыкли считать с 1, а не с 0), то в VBA имеется директива компилятора, позволяющая исправить это "неудобство": Option Base.

Директива компилятора имеет два варианта написания:

Option Base 0 - индексы массивов начинаются с 0 (установка по умолчанию)

Option Base 1 - индексы массивов начинаются с 1

Данная директива компилятора помещается в область объявлений модуля перед объявлениями любых переменных, констант или процедур. Нельзя помещать Option Base внутри процедуры. Можно иметь только один оператор Option Base в модуле, который влияет на все массивы, объявляемые в модуле.

Многомерные массивы

Одномерные массивы хорошо подходят для представления простых списков данных. Однако часто бывает необходимо представить таблицы данных в программах с организацией данных в формате строк и столбцов, подобно ячейкам в рабочих листах Excel. Для этого необходимо использовать многомерные массивы. Так адрес каждой ячейки листа состоит из двух чисел, одно из которых (номер строки) является первым индексом, а второе (номер столбца) - вторым индексом массива. Такой массив называется двумерным массивом. Добавив еще номер листа, получим трехмерный массив. VBA позволяет создавать массивы, имеющие до 60 измерений.

Статические и динамические массивы

Массивы, не меняющие число своих элементов, называются статическими массивами. Примером такого массива может служить вышеприведенный массив My_Array, содержащий 10 элементов.

Однако бывают ситуации, когда изначально неизвестно количество элементов в массиве, или же, в процессе работы это количество может изменяться. Такие массивы называются динамическими массивами.

Динамический массив может увеличиваться или сжиматься, чтобы вмещать точно необходимое число элементов без напрасного расходования памяти.

Объявление массивов

Объявление массива с использованием оператора Dim имеет следующий синтаксис:

Dim VarName([Subscripts]) [As Type]

VarName - любое имя массива, использующее допустимый идентификатор имени;

Subscripts - измерение массива. Если размерность массива больше единицы, то Subscripts разделяются запятыми.

Оператор Subscripts имеет следующий синтаксис:

[lower To] upper [,[lower To] upper]..

Lower - определяет нижний диапазон допустимых индексов для массива (необязательный аргумент);

upper - определяет верхний предел для индексов массива (обязательный аргумент).

Примеры правильного объявления массивов:

Dim Array_Str (1 To 10) As String- одномерный статический строковый массив, включающий 10 элементов;

Dim Array_Var() - динамический массив;

Dim Array_Mult (0 To 5, 0 To 7) As Integer - двумерный статический массив целых чисел, включающий 6*8=48 элементов.

При объявлении массивов следует помнить, что включение оператора Subscripts в объявлении массива создает статический массив с фиксированным числом элементов, пропуск оператора Subscripts в объявлении массива создает динамический массив, а установка директивы компилятора Option Base влияет на общее число элементов в массиве.

Использование массивов

Для доступа к элементу массива необходимо указывать имя массива, за которым следует значение индекса, заключенное в круглые скобки.

Нижеприведенный листинг показывает элементарное объявление и использование массива (в качестве элементов массива используется факториал номера элемента массива):

При помощи вложенных циклов довольно легко можно организовать инициализацию двумерного массива: