Функции-процедуры VBA

Прерывание процедур и функций VBA

 

Иногда встречаются обстоятельства, при которых нет смысла продолжать выполнение процедуры или функции. VBA имеет операторы Exit и End, которые дают возможность либо завершать процедуру или функцию, либо останавливать всю программу.

Оператор Exit

 

Для того, чтобы процедура или функция прекратила выполнение, используется одна из двух доступных форм VBA-оператора Exit, в зависимости от того, необходимо ли завершить функцию или процедуру:

 

 

Exit Sub

 Exit Function

 

 

Перепишем немного листинг  учебной программы, который использовался  на позапрошлом уроке:

Здесь содержится код проверки того, была ли выбрана пользователем  в окне ввода кнопка "Отмена". Если это так - программа выдает сообщение  о том, что не был введен возраст, и прекращает выполнение кода оператором Exit Sub.

 

Оператор Exit Sub приводит к тому, что VBA немедленно прекращает выполнение кода процедуры. После выполнения этого оператора VBA прекращает выполнение текущей процедуры и возвращается к выполнению той процедуры или функции, которая вызвала процедуру, содержащую оператор Exit Sub.

Оператор End

 

Для полного завершения выполнения программы используется ключевое слово  End в отдельной строке:

 

 

End

 

 

При выполнении этого оператора VBA прекращает все выполнение операторов процедуры и функции. Любые имеющиеся  переменные перестают существовать и их значения теряются.

 

Т.к. программа полностью  прерывается, необходимо перед оператором End выводить пользователю сообщение о том, что будет происходить и почему. В противном случае пользователи вашей процедуры могут не понять, почему процедура или программа, которую они используют, внезапно прекратила работу. Если программа перестанет выполняться вследствие какого-либо действия пользователя, такого как отмена окна ввода, без объяснения, пользователь процедуры может никогда не узнать, почему процедура заканчивается.

 

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

 

Необязательные  аргументы функций VBA.

Задание типа необязательного  аргумента

 

Для того, чтобы правильно использовать функции и обнаруживать операторы, которые передают функции неверные значения объявляется тип необязательного аргумента (иначе они имеют тип Variant). Тип необязательного аргумента объявляется тем же способом, что и тип обязательного аргумента - при помощи ключевого слова As:

 

 

Function NameFunct(tStr As String, Optional neobArgument As Integer) As String

 

Значения по умолчанию  для необязательных аргументов

 

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

 

Значение по умолчанию  присваивается необязательному  аргументу с помощью знака  равенства и предоставления значения так же, как при объявлении именованной  константы:

 

 

Function GetBookName(Optional lDflt As String = "Book1") As String

 

 

Передача аргументов

 

Существуют два способа  для передачи информации в функцию-процедуру: по ссылке (по умолчанию) и по значению.

 

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

 

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

 

При передаче аргумента по значению VBA делает копию исходных данных и передает эту копию функции. Если функция изменяет значение в  аргументе, передаваемом по значению, изменяется только копия данных, а  исходные данные не изменяются.

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

 

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

 

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

 

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

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

 

Циклы VBA (ч.1). Команды  организации циклов

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

 

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

 

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

 

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

 

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

 

Цикл For..Next

 

Синтаксис

 

 

For counter = Start To End [Step StepSize]

   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 Each Element In Group

   Statements

 Next [Element]

 

 

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

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

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

 

 

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

 

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

Циклы VBA (ч.2). Циклы тестирующие условия до и после выполнения тела цикла

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

Цикл Do .. While

 

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

 

Синтаксис:

 

 

Do While Condition

   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 - ключевое слово, указывает на окончание тела цикла и обозначает место, из которого 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

 

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

 

Синтаксис: