Пользовательские подпрограммы
Часто в программировании одинаковые блоки алгоритма используются в разных частях программы. И тогда удобно применять концепцию пользовательских подпрограмм, т.е. написать подпрограмму, которую можно использовать по имени с различными аргументами (параметрами) в разных частях программы.
В VBA существуют два типа подпрограмм:
· Подпрограммы-процедуры (процедуры).
· Подпрограммы-функции (функции)
12.1 Подпрограмма – процедура– является самостоятельной частью программного кода, имеет свое имя, может иметь параметры, выполняет последовательность инструкций языка VBA.
Синтаксис описания:
[Private ½ Public] Sub <имя процедуры> ([<список параметров>])
<операторы>
[Exit Sub]
< операторы >
End Sub
Здесь:
· Private – ключевое слово, указывающее на то, что процедура доступна для всех процедур в данном модуле и недоступна процедурам, находящимся в других модулях.
· Public - ключевое слово, указывающее на то, что процедура доступна во всех модулях проекта. Эта область действия процедуры принята по умолчанию, поэтому использовать ключевое слово Publicпри объявлении процедуры не обязательно
· <имя процедуры>- Имя процедуры, которое удовлетворяет всем правилам создания идентификатора в VBA
· ([<список параметров>]) – это список параметров, значения которых передаются в процедуру или возвращаются из процедуры. Разделителем в списке формальных параметров является запятая.
· <операторы> - группа инструкций, выполняемых в процедуре
· [Exit Sub] – оператор, приводящий к немедленному выходу из процедуры.
Вызов подпрограммы – процедуры пользователя из другой процедуры можно произвести несколькими способами:
· <имя процедуры> <список аргументов>
· Call <имя процедуры> <список аргументов>
Примечание:
<Список параметров>отличается от <списка аргументов>тем, что первый указывается при описании подпрограммы, второй – при ее вызове в основной программе.
12.2 Подпрограмма – функция – это класс подпрограмм, отличается тем, что в результате выполнения функции всегда вычисляется единственное возвращаемое значение, которое присваивается переменной с именем данной функции.
Синтаксис описания:
[Private ½ Public] Function <имя функции> ([<список параметров>]) [As<тип функции>]
<операторы>
[Exit Function]
< операторы >
<имя функции> = <выражение>
End Function
Здесь:
· <имя функции> - Имя функции, которое удовлетворяет всем правилам создания идентификатора в VBA
· ([<список параметров>])- список параметров, который описывает передаваемые функции данные
· <тип функции> - определяет тип возвращаемого функцией значения ( по умолчанию это тип Variant). Следует помнить, что значения, сохраняемые или обрабатываемые как тип Variant, занимают больше памяти, чем любой другой тип данных, и на их обработку требуется больше времени. Поэтому при объявлении функции всегда следует указывать тип возвращаемого результата.
Примечание:
В теле функции должен быть оператор присваивания, который имени функции присваивает значение, являющееся результатом выполнения функции.
Вызов подпрограммы – функции пользователя осуществляется по имени в выражении, как любой стандартной функции VBA. За именем функции в круглых скобках указывается список аргументов.
Список параметров и список аргументов
<Список параметров>отличается от <списка аргументов> тем, что первый указывается при описании подпрограммы, второй – при ее вызове в основной программе.
<Список параметров>позволяет передать в подпрограмму требуемые значения из вызывающей программы и имеет следующий синтаксис:
Синтаксис:
[ByRef | ByVal] <имя параметра1> [As <Тип>], [ByRef | ByVal] <имя параметра2> [As <Тип>],[ByRef | ByVal] <имя параметра3> [As<Тип>], …
Здесь:
<Тип> позволяет явно задать тип передаваемых значений. Если тип опущен, то по умолчанию принимает значение Variant.
Ключевое слово ByVal (передача по значению) используется в тех случаях, когда желают, чтобы изменение параметров внутри процедуры не приводило к изменению соответствующих аргументов процедуры в основной программе.
Использование ByRef (передача по ссылке) или, если ключевое слово опущено, означает, что при изменении параметров внутри процедуры происходит изменение соответствующих параметров в основной программе.
При вызове подпрограммы пользователя в основной программе используется <Список аргументов>.
В <Списке аргументов> аргументы перечисляется через запятую.
Соответствие между списком параметров и списком аргументов:
· Количество и типы в списке параметров и в списке аргументов должны соответствовать друг другу.
· Аргументы, соответствующие параметрам с ключевым словом ByValмогут быть константами, переменными и выражениями.
· Аргументы, сответсвующие параметрам с ключевывым словом ByRef (или по умолчанию), должны быть переменными.
Для выхода из подпрограммы и возврата в основную программу, опуская оставшиеся операторы, используется Exit Sub(в процедурах) и Exit Function (в функциях).
Примеры:
Пример 1. Вычислить с помощью подпрограммы пользователя площадь прямоугольника.
С помощью процедуры:
Sub Sgure(ByVal a As Long, ByVal b As Long, ByRef ss As Long)
ss = a * b
End Sub
Public Sub Test_sub()
Dim a As Long
Dim b As Long
Dim s As Long
b = 2
'Sgure 5, b, s
Call Sgure(5, b, s)
MsgBox "s=" & s
End Sub
С помощью функции:
Function SgureF(ByVal a As Long, ByVal b As Long) As Long
SgureF = a * b
End Function
Public Sub Test_Function()
Dim a As Long
Dim b As Long
Dim s As Long
b = 2
s = SgureF(5, b)
MsgBox "s=" & s
End Sub
Пример 2. Показывает отличие передачи параметра по ссылке от передачи параметра по значению.
Sub DemoByValByRef(ByVal a, b, ByRef c)
'a - передается по значению
'b - передается по умолчанию
' по ссылке (ByRef)
' c - передается по ссылке
a = a + 1
b = b + a
c = c + a
End Sub
Public Sub Test()
a = 1
b = 10
c = 100
DemoByValByRef a, b, c
MsgBox "a=" & a ‘ a=1
MsgBox "b=" & b ‘ b=12
MsgBox "c=" & c ‘ c=102
End Sub
Пример 3
Вычислить 
. Вычисление суммы прекратить, когда при каком-то значении i, общий член ряда станет <=e
Текст программного кода будет выглядеть следующим образом
Public Sub Summa_ряда()
Const eps = 0.001
Dim sum As Double, ai As Double
Dim n As Integer, i As Integer
sum = 0
i = 1
ai = 1 / 2
Do Until ai <= eps
sum = sum + ai
i = i + 1
ai = Factor(i) / Factor(2 * i)
Loop
MsgBox "Значение i=" & i & Chr(10) & _
"Значение ai=" & ai & Chr(13) & _
"Значение Sum=" & sum
End Sub
Function Factor(n As Integer) As Long
Factor = 1
For i = 1 To n
Factor = Factor * i
Next i
End Function