Пользовательские подпрограммы в VBA

VBA поддерживает два типа подпрограмм: процедуры и функции.

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

Для объявления процедуры в VBA используется ключевое слово Sub:

Sub <имяПроцедуры> [(<списокПараметров>)] <операторы> End Sub 

где:

<имяПроцедуры> – любой допустимый идентификатор VBA;

<списокПараметров> – список формальных параметров процедуры, если он пуст, то такая процедура является макросом;

<операторы> - любая последовательность операторов VBA.

Листинг №.13 Пример объявления процедуры

' Процедура выводит в отладчик максимальное из трех чисел Sub sMax3(A As Long, B As Long, C As Long) If (A > B) And (A > C) Then Debug.Print "Max is "; A ElseIf (B > A) And (B > C) Then Debug.Print "Max is "; B Else Debug.Print "Max is "; C End If End Sub 

Синтаксис объявления функции несколько сложнее, чем синтаксис процедуры:

Function <имяФункции> [(<списокПараметров>)] [As <типФункции>] <операторы> . . . <имяФункции> = <возвращаемое_значение> [<операторы>] End Function 

где:

<имяФункции> - любой допустимый идентификатор;

<списокПараметров> – список формальных параметров процедуры;

<типФункции> - имя любого поддерживаемого VBA типа данных;

<операторы> - любая последовательность операторов VBA.

<возвращаемое_значение> - результат, передаваемый в вызывающую программу.

Листинг №14. Пример объявления функции

' Функция возвращает максимальное из трех чисел Function fMax3(A As Long, B As Long, C As Long) As Long If (A > B) And (A > C) Then fMax3 = A ElseIf (B > A) And (B > C) Then fMax3 = B Else fMax3 = C End If End Function 

Передача параметров в подпрограммы

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

Function | Sub <имяПроцедуры> (<параметр1> As <тип>, _ <параметр2> As <тип>, ..., <параметрN> As <тип>)

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

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

• передача по значению;
• передача по ссылке.

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

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

• ByRef – задает передачу по ссылке;
• ByVal – задает передачу по значению.

По умолчанию выполняется передача по ссылке.

Листниг 15. Передача параметров в подпрограмму

' sample10 - вызывающая программа (макрос) ' ByValByRefDemo - вызываемая процедура Sub sample10() Dim a As Long, b As Long, c As Long ' фактические параметры a = 10 b = 10 c = 10 ByValByRefDemo a, b, c ' передача фактических параметров Debug.Print "2: " & "a = " & a & "; b = " & b & "; c = " & c End Sub Sub ByValByRefDemo(x As Long, ByVal y As Long, ByRef z As Long) ' выполнение действий над формальными параметрами x = x * 2 y = y * 3 z = z * 4 Debug.Print "1: " & "x = " & x & "; y = " & y & "; z = " & z End Sub 

Здесь объявлены две процедуры: sample10 и ByValByRefDemo. Процедура sample10 вызывает процедуру ByValByRefDemo и передает ей предварительно инициализированные переменные a, b и c. Процедура ByValByRefDemo получает значения переменных a, b и c в виде формальных параметров x, y и z соответственно, выполняет над ними указанные действия, выводит результат и завершается. После возврата из подпрограммы процедура sample10 выводит значения переменных a, b, c в окно отладчика (рис. 4).

Рис. 4. Передача параметров в подпрограмму

Именованные параметры

При вызове подпрограмм в VBA параметры необходимо передавать в определенном порядке. Отсутствующие необязательные параметры отмечаются запятыми. Нарушение этого правила часто приводит к ошибкам – легко пропустить или поменять местами параметры. Чтобы избежать этих проблем, в VBA можно использовать именованные параметры функций. Для этого в вызове подпрограммы явно указываются имена параметров (как это было задано при объявлении подпрограммы), каждому из которых присваивается требуемое значение с помощью оператора ":=" ("двоеточие_равно"). При использовании именованных параметров можно не обозначать отсутствующие параметры и, кроме того, порядок перечисления параметров может быть произвольным.

Следующий пример показывает два обращения к функции MsgBox, которые имеют один и тот же результат:

...
' обычный вызов MsgBox "Здравствуй, мир!", , "Окно приветствия" ...
' вызов, с использованием именованных параметров MsgBox Prompt:= "Здравствуй, мир!", Title:= "Окно приветствия" 

Вызов подпрограмм

Подпрограммы из программ на VBA могут быть вызваны различными способами:

• Процедуру (Sub)с непустым списком параметров можно вызвать только из другой процедуры или функции так:

  <имяПроцедуры> <списокПараметров> 

  Или так:

  Call <имяПроцедуры> (<списокПараметров>) ’использована инструкция Call. 

• Процедура с пустым списком параметров рассматривается VBA как командный макрос. Ее также можно вызвать двумя способами:
  • из другой процедуры или функции;
  • с помощью комбинации клавиш быстрого вызова, команд меню или кнопок панелей инструментов.
• Функцию (Function) можно вызывать точно так же, как и процедуру, в виде отдельного оператора. В этом случае возвращаемое функцией значение игнорируется.

Листинг №16. Вызов процедуры

Sub sample11() ' вызывающий макрос Dim usr As String usr = InputBox("Login") Hello usr ' вызов процедуры Helloбез Call Call Hello(usr) ' использование инструкции Call End Sub ' процедура принимает один параметр, формирует строку сообщения, ' выводит сообщение в окно отладчика Sub Hello(usrname As String) Debug.Print "Hello, " & usrname & "!" End Sub 

Если в проекте используется несколько подпрограмм с одинаковыми названиями (это возможно, если они в разных модулях), то при их вызове перед именем подпрограммы надо указывать (через точку) имя модуля, в котором процедура расположена:

<имяМодуля>.<имяПроцедуры> <списокФактическихПараметров> 

Например

MyModule.MySub fArg, sArg ' вызов процедуры из модуля MyModule 

Для вызова общих (Public) подпрограмм из другого проекта дополнительно к именам модуля и подпрограммы указывается имя проекта:

<имяПроекта>.<имяМодуля>.<имяПроцедуры> <списокФактическихПараметров> 

Например

someVal = MyProject.MyModule.MyFunc(fArg, sArg) ' вызов функции из проекта MyProject 

Прерывание подпрограммы

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

Sub <имяПроцедуры> [(<списокпараметров>)] <операторы> Exit Sub <операторы> End Sub 

CC-BY-CA Анатольев А.Г., 29.09.2012