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

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

 

Функция-процедура - это особый вид процедуры VBA, возвращающей результат. Пользовательские функции-процедуры, как и встроенные функции VBA, могут  иметь необязательные и именованные  аргументы. Для записи функции-процедуры  нельзя использовать макрорекордер, хотя можно редактировать записанный рекордером макрос и превращать его в функцию-процедуру.

 

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

 

Синтаксис:

 

 

Function Name([Arglist]) [As Type]

'VBA Statements

[Name = expression]

End Function

 

Function - ключевое слово, объявляющее начало функции.

 

Name - имя функции. Имена функций следуют тем же правилам, что и имена других идентификаторов VBA.

 

Arglist - список аргументов данной функции, необязательный элемент.

 

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

 

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

 

End Function - ключевые слова, заканчивающие функцию.

 

 

Даже если функция не имеет  аргументов (например, Now, Date) в объявлении функции необходимо использовать круглые скобки.

 

Обычно функция предназначается  для выполнения вычисления и для  возвращения результата. При объявлении функции-процедуры указывается имя  каждого аргумента, передаваемого  функции. Имена аргументов в списке отделяются друг от друга запятой  и должны следовать правилам, применяемым  к любому идентификатору VBA.

 

С1

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

Без использования функции  листинг бы выглядел так:

Из этого простого примера, думаю, понятна основная идея использования  функций-процедур - улучшение читабельности  программного кода и его сокращение (другими словами, функция-процедура  пишется когда в программном коде более 2-3 раз встречается один и тот же "кусок" кода). Действительно, если бы наша функция-процедура состояла не из одной строки, а, скажем, из 10 строк; и программный код использовал бы эту функцию-процедуру 5 раз, то общий листинг программы был бы меньше на 38 строк.

 

С2

Как уже указывалось ранее, VBA передает все аргументы в функцию-процедуру  как типы Variant. Можно объявлять определенные типы данных для каждого аргумента в списке аргументов.

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

 

Пользовательские  функции в Excel

 

Функции-процедуры с некоторыми ограничениями на их действия называются определенными пользователем функциями (сокращенно UDF - user-defined functions), и только их может использовать Excel в формуле ячейки рабочего листа.

 

 

ВНИМАНИЕ! UDF не может никоим образом изменять среду Excel.

 

 

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

 

Обычно определенная пользователем  функция должна только выполнять  вычисления или манипулирование  на основе данных, полученных из ее списка аргументов или выбранных из Excel. Например, всем этим требованиям соответствует наша функция SLen, рассмотренная на предыдущем уроке, поэтому, ее можно использовать как определенную пользователем функцию.

 

При написании функций-процедур для использования в качестве UDF в рабочих листах Excel необходимо знать несколько фактов, помимо общих требований для определенных пользователем функций:

Определенные пользователем  функции, которые будут использоваться в Excel, не должны иметь имена, похожие на записи ссылок на ячейку (например, А2; С1В5);

Любые строковые данные, возвращаемые из VBA в Excel, не должны иметь более 255 символов в длину. Если UDF возвращает строку, имеющую больше 255 символов в длину, в ячейку рабочего листа, Excel укорачивает строку до максимальной длины 255 символов перед вставкой в ячейку;

При написании UDF, возвращающей значение даты для Excel убедитесь, что задаете тип результата функции как Date. Excel применяет формат Date для результата функции в ячейке рабочего листа только, если результат имеет VBA-тип Date.

 

Операторы ветвления VBA: простой выбор

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

 

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

 

С3

Синтаксис 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 пропускаются и выполняется код, следующий за этой секцией.

 

С4

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

С 5 второй вариант:

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

 

С 6

Синтаксис 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 будет находиться по одному оператору

С 7

 

Операторы ветвления VBA: сложный выбор. Безусловный переход

 

В реальных программах зачастую бывает необходимо выполнять более  сложный выбор в процедурах, выбирая  между тремя и более ветвями. В этом случае можно помещать операторы  If..Then..Else друг в друга. Это называется вложением операторов.

Вышепоказанная процедура  использует несколько вложенных  друг в друга операторов условного  перехода. Следует сказать, что такая  процедура будет работать только в Excel, т.к. использует метод Application.InputBox (см. Функции host-приложений). Этот метод не дает пользователю во время работы функции ввести что-либо, кроме числа.

 

Если пользователь вводит не число, то получает об этом сообщение.

Если пользователь ничего не вводит, то получает сведение об ошибке.

Если пользователь воспользуется  кнопкой "Отмена", то получает сообщение "Не введены данные".

 

 

VBA предоставляет сокращенную  версию оператора If..Then..Else, являющуюся сжатым эквивалентом вложенных операторв If..Then..Else, использованных в листинге. Такой краткой формой является операторIf..Then..ElseIf

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

 

 

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

 

VBA имеет условный оператор  перехода для использования в  случаях, когда необходимо выбирать  из большого количества различных  ветвей кода - Select Case. Он работает практически так же, как и Else..If, но более понятен.

 

Ключевые слова Select Case используются со многими операторами Case, где каждый оператор Case проверяет появление другого условия и выполняется только одна из ветвей Case. Ветвь Case может содержать один, несколько или ни одного оператора VBA.

Оператор безусловного перехода

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

 

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

 

 

Синтаксис:

 

GoTo line

 

 

line - любая допустимая метка или номер строки в той же процедуре или функции, которая содержит оператор GoTo.

 

 

При выполнении оператора  GoTo управление выполнения программы немедленно передается оператору в строке, определенной с помощью метки line.

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

 

Использование MsgBox для обеспечения возможности выбора

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

 

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

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

 

Как только пользователь выбирает командную кнопку в окне сообщения, VBA возвращает численное значение, соответствующее  выбору пользователя. Результат функции  присваивается переменной Vibor (в вышеприведенном листинге). VBA использует различные значения в зависимости от того, какую командную кнопку выбрал пользователь. Поскольку каждая кнопка имеет свое определенное возвращаемое значение, VBA предоставляет несколько внутренних констант для представления возможных возвращаемых значений функции MsgBox.