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

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

В 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, общий член ряда станет <=

Текст программного кода будет выглядеть следующим образом

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

Тестовые задания:

1. В языке VBA выражением является:

а) последовательность символов, разделяемая пробелами и знаками операций;

б) отдельное значение или несколько значений, соединенных знаками операций;

в) последовательность операторов, ограниченная операторами Sub . . . End Sub;

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

2. Язык VBA поддерживает следующие арифметические операции:

а) умножения;

б) извлечение квадратного корня;

в) взятие модуля числа;

г) сложения;

д) преобразования двоичного значения в десятичное.

3. Язык VBA поддерживает следующие строковые операции:

а) сложения;

б) конкатенации;

в) взятие модуля числа;

г) суммирования;

д) преобразования в двоичное значение.

4. Метка в операторе безусловного перехода GoTo определяет:

а) имя процедуры, которой принадлежит данный оператор;

б) оператор, с которого будет продолжено выполнение программы;

в) оператор, который должен быть выполнен перед данным оператором;

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

5. Если исходное значение А было равно 5, а переменной B было равно 3, то какое значение будет иметь переменная А после выполнения оператора:

If (A-B) > 0 Then A=A-B Else A=A+B

а) 2;

б) 8;

в) 5;

г) оператор содержит ошибку, и программа не сможет его выполнить.

6. Если значение А равно 8, то какой из операторов будет выполнен при выполнении оператора:

Select Case A

Case 2

B=1

Case 3 To 7

B=2

Case 20,100

B=3

Case 2, 4, 6, 8, 10

B=4

End Select

а) B=1;

б) B=2;

в) B=3;

г) B=4;

д) оператор, следующий после оператора Select Case;

е) оператор содержит ошибку, и программа не сможет его выполнить.

7. Сколько раз будет выполнен оператор a=a+1 при выполнении следующего цикла:

Dim i As Double

For i = 10 To 5.5 Step -0.5

a = a + 1

Next i

а) 10;

б) 9;

в) 8;

г) 7;

д) ни одного.