Процедуры, подпрограммы и функции

Параметры и аргументы

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

• описывается при объявлении процедуры;
• представляет собой переменную.

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

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

Пример

Для первых пяти натуральных чисел рассчитать квадрат и куб числа ( рис. 7.2).

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

Рис. 7.2. Пример основной и вызываемой процедуры для расчета куба и квадрата числа

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

Call Proc_print (i) - альтернативная запись вызова процедуры.

Синтаксис описания каждого параметра процедуры имеет следующий вид:

[[Optional][ByVal|ByRef][ParamArray] varname[( )] As type][= defaultvalue]

• Optional - ключевое слово. Указывает, что параметр может быть опущен.

• ByVal ^{1Указывается одно из двух ByVal или ByRef.} - передача параметра по значению. Предполагается, что при вызове процедуры передается только значение переменной и, следовательно, в процессе выполнения процедуры оно не может быть изменено. Используйте этот способ передачи аргументов, чтобы избежать случайной модификации передаваемых данных.
• ByRef ^{2Указывается одно из двух ByVal или ByRef.} - передача параметров по ссылке. Предполагается, что процедуре передается адрес переменной. В этом случае вызывающая процедура может не только использовать переданное значение переменной, но и изменить его в процессе выполнения. Используется по умолчанию.
• ParamArray означает передачу необязательного аргумента - массива типа Variant с неопределенным количеством элементов.

• Varname - идентификатор переменной.
• type - тип передаваемого аргумента. Если тип данных не указан, то VBA трактует аргумент как Variant.

• Value on default - для необязательных аргументов устанавливает значение по умолчанию.

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

Пример

Для сотрудников фирмы (максимально 2000 сотрудников) рассчитать выплаты в соответствии с количеством отработанных часов и ставкой оплаты за час работы. Налог составляет 13%. Если зарплата превышает 300$, то страховой взнос составляет 5%.

Процедура salary_employee (основная) запрашивает ввод количества отработанных часов и размер почасовой оплаты максимально для 2000 сотрудников и передает по значению эти данные в процедуру salary_em_proc.

Вызываемая процедура salary_em_proc рассчитывает и распечатывает денежную выплату. В вызываемой процедуре salary_em_proc определены три параметра. Это io - код сотрудника, ho - количество часов, ra - ставка почасовой оплаты.

Рис. 7.3. Передача аргументов по значению

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

В основной процедуре salary_employee объявите переменную AC (Dim AC as Double), в которой будет накапливаться суммарная выплата. В вызываемой процедуре введите четвертый параметр SI - накапливаемая сумма. Этот параметр передается по ссылке, т.к. его значение изменяется в вызываемой процедуре. Оператор вызова процедуры будет выглядеть так: salary_em_sum id, h, r, AC.

Рис. 7.4. Передача аргумента по ссылке (параметр SI)

Возврат значения функции

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

function name=expression

Если значение явно не присваивается, то оно устанавливается по умолчанию в соответствии с типом возвращаемого значения: 0 - для числовых типов, строку нулевой длины - для строковых переменных, Empty - для типа Variant и Nothing - для ссылок на объекты.

Пример

Функция возвращает количество выделенных ячеек, если тип выделения - объект Range (подробно об объекте Range см. "Объекты MS Excel" ). Если выделен объект другого типа, например, встроенная диаграмма или рисунок, то возвращаемое значение - символьная строка с сообщением о некорректности выделения.

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

В вызывающей процедуре вычисленное функцией значение сохраняется на активном листе в ячейках D1 (количество ячеек в интервале A1:C5 ) и D2 (сообщение о некорректности выделения).

увеличить изображение
Рис. 7.5. Пример сохранения вычисленного функцией значения на активном листе

Использование процедур-функций на рабочем листе

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

При построении формулы с помощью мастера функций пользовательские функции можно найти в категории Определенные пользователем (User Defined).

Пример

Создать функцию расчета выплат, учитывая, что налог составляет 13% и, если зарплата превышает 300 $, то в страховой фонд дополнительно взимается 5%.

В отличие от процедуры salary_em_proc функция salary_em_ возвращает рассчитанное значение. Возврат значения происходит при помощи операторов присваивания, в которых слева используется имя функции. В вызывающей процедуре salary_employee вместо оператора вызова процедуры salary_em_proc io, h, r записан оператор, распечатывающий рассчитанную выплату как MsgBox io & " salary " & salary_em(h, r).

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

Функцию salary_em_ можно использовать как в процедурах VBA, так и на рабочем листе, как пользовательскую функцию.

На рис. 7.7 в ячейку E3 введены отработанные часы, а в ячейку F3 - ставка оплаты за час. В ячейке G3 рассчитана выплата по формуле =salary_em_(E3;F3).

Рис. 7.7. Использование функции salary_em_ как пользовательской функции на рабочем листе