Программирование в Microsoft Excel
Лабораторная работа №5
Процедуры и функции
5.1. Процедуры-подпрограммы и процедуры-функции
В процессе создания программ, содержащих десятки, сотни или более строк программного кода высока вероятность того, что группы операторов, выполняющие одни и те же действия, будут дублироваться в различных местах программы. Это приводит к неоправданному увеличению размеров программы, затрудняет её проверку и повышает риск возникновения ошибок. Для решения этой проблемы в языках программирования предусмотрена возможность создания процедур и функций – программных единиц, которые программируются один раз, а затем вызываются по мере необходимости из других программных единиц.
Процедура имеет следующий общий вид:
Sub ИмяПроцедуры( список_аргументов )
группа_операторов
End Sub
где ИмяПроцедуры – идентификатор подпрограммы, удовлетворяющий правилам задания имён в VBA;
список_аргументов – список входных и выходных параметров подпрограммы следующего вида:
переменная1 As ТипДанных, переменная2 As ТипДанных,… где тип – любой встроенный или созданный программистом тип данных;
группа_операторов – операторы VBA, выполняющиеся при вызове подпрограммы.
Перед Sub могут находиться:
1. Одно из следующих слов определяют так называемую область видимости функций – область программного приложения, где процедура или функция может вызываться:
слово Public – указывает, что процедура доступна для других процедур во всех модулях
слово Private – указывает, что процедура доступна только для процедур того модуля, в
котором она объявлена
слово Friend – используется только в модулях классов. Позволяет вызывать процедуру из
другого модуля проекта.
2. Слово Static – указывает, что локальные переменные процедуры Sub сохраняют свои значения между вызовами этой процедуры.
Эти же ключевые слова (кроме Friend) могут находиться перед словом Dim при объявлении переменных, выполняя аналогичную функцию – определяя область видимости перменных. По умолчанию все переменные считаются объявленными как Private. Переменные класса Public объявляются вне всех программ, и они доступны во всех программах данного модуля. При этом если в нескольких программах есть переменные с одинаковыми именами и типами, то в каждой программе доступна только своя переменная – не доступна даже переменная типа Public с таким же именем и типом.
Аргументы процедуры необходимы для передачи в неё параметров различных типов, над которыми необходимо выполнить какие-либо действия, и использования полученных значений в вызывающей программе. Вызов процедуры из другой программной единицы осуществляется при помощи её имени и списка параметров, разделённых после него запятыми:
ИмяПодпрограммы список аргументов
При вызове процедуры в списке аргументов могут находиться константы, переменные, элементы массивов соответствующих типов; выражения или вызовы функций, возвращающие результат соответствующего типа.
Пример: процедура LV вызывает ранее созданную процедуру Vector, вычисляющую величину вектора в пространстве по 3 входным параметрам (проекциям вектора на координатные оси) и записывающую результат в выходной параметр L.
Sub Vector(x As Single, y As Single, z As Single, L As Single)
L = Sqr(x ^ 2 + y ^ 2 + z ^ 2)
End Sub
Sub LV()
Dim x As Single, y As Single, z As Single, L As Single
x = InputBox("Введите x:")
y = InputBox("Введите y:")
z = InputBox("Введите z:")
Vector x, y, z, L ‘ вызов подпрограммы Vector
MsgBox "Длина вектора равна: " & CStr(L)
End Sub
Процедура может также вызываться оператором Call:
Call ИмяПроцедуры(список параметров в круглых скобках)
Это современный вариант вызова. Например, процедуру Vector из предыдущего примера можно вызвать следующим образом:
Call Vector(x, y, z, L)
Для немедленного выхода из процедуры используется оператор Exit Sub. Функция имеет следующий синтаксис:
Function ИмяФункции (список_аргументов) As ТипДанных
Группа_операторов
…
ИмяФункции = результат
…
End Function
где тип – тип результата функции
Перед словом Function также могут находиться Public, Private или Friend и Static.
Функция в списке аргументов не имеет выходных параметров – вызываемая функция сама возвращает результат в точку её вызова. В тексте функции обязательно должен присутствовать оператор присвоения имени функции выходного результата(!), иначе функция будет возвращать значение 0. Функция вызывается по своему имени и аргументами в круглых скобках после него, причём только в составе какого-нибудь оператора: в правой части оператора присваивания, в составе выражения или в качестве входного параметра подпрограммы или функции. Для немедленного выхода из функции используется оператор Exit Function.
Пример.
Function Expression(x As Integer) As Double
If x = 0 Then
Exit Function ' выйти из функции, если x = 0
Else
Expression = (x^3+2*x^2+8)/x ' присвоение имени функции результата
End If
End Function
Sub UsingOfExpession()
Dim F As Double, S(1 To 8) As Integer
Dim j As Integer
S(1) = 0: S(2) = 9: S(3) = 56: S(4) = -24
S(5) = -4: S(6) = -50: S(7) = -57: S(8) = -4
Sheets("Лист2").Cells(1, 1).Value = " Элементы массива:"
Sheets("Лист2").Cells(1, 2).Value = " Значение функции:"
For j = 1 To 8
Sheets("Лист2").Cells(j + 1, 1).Value = S(j)
Sheets("Лист2").Cells(j + 1, 2).Value = _
Expression(0.8 * S(j) + 40) ' в вызове функции, в качестве параметра,
' выражение типа Single c элементом массива
Next j
End Sub
В данном примере значениям массива S(1:8) целого типа присваиваются значения. Затем в цикле элементы массива используются при вызове функции.
В VBA допустимо использование массива в качестве параметра процедуры или функции. В этом случае, массив, выступающий в качестве параметра, при описании процедуры надо объявить как динамический. При вызове подпрограммы массив-параметр указывается только своим именем, без скобок и размерностей. В следующем примере процедура SumArray возвращает сумму элементов массива, который является её первым параметром.
Sub SumArray(A() As Integer, n As Integer, m As Integer, S As Integer)
S = 0
For i = 1 To n
For j = 1 To m
S = S + A(i, j) ' результат присваивается выходному параметру S
Next j
Next i
End Sub
Sub Test()
Dim B(1 To 3, 1 To 2) As Integer
Dim S As Integer
B(1, 1) = 1: B(1, 2) = 5
B(2, 1) = 4: B(2, 2) = 5
B(3, 1) = 3: B(3, 2) = 1
SumArray B, 3, 2, S
MsgBox "Сумма элементов массива равна : " & CStr(S)
End Sub
Функция может также возвращать массив в качестве своего результата. Для этого, во-первых, при объявлении функции следует объявить типом результата массив нужного типа:
Function ИмяФункции (список аргументов) As ТипДанных()
Группа операторов
…
ИмяФункции = Массив-результат
…
End Function
Например:
Sub C()
Dim Test() As Double
Test = Fun(2)
MsgBox Test(1) & " " & Format(Test(2), " ##0.###")
End Sub
Function Fun(i As Integer) As Double()
Dim Temp(1 To 2) As Double
Temp(1) = i
Temp(2) = Sin(i)
Fun = Temp
End Function
Во-вторых, если аргументом такой функции является массив, то его нужно объявить динамическим и в объявлении функции, и в вызывающей функцию программной единице. В следующем примере обратите внимание на различные способы обращения к такой функции:
Sub array1()
Dim i As Integer, j As Integer, Temp As Integer, n As Integer
Dim mas() As Single, mas2() As Single
ReDim mas(1 To 3, 1 To 3) As Single, mas2(1 To 3, 1 To 3) As Single
For i = 1 To 3
For j = 1 To 3
mas(i, j) = Sheets("Лист1").Cells(i, j).Value
Next j
Next i
‘ 1-ый вариант обращения – обычное присвоение массиву вызова функции, воз-
‘ вращающей массив
mas2 = massiv5(mas)
For i = 1 To 3
For j = 1 To 3
Sheets("Лист1").Cells(i+4, j).Value = Format(mas2(i, j), "##.###")
Next j
Next i
‘ 2-ый вариант обращения – с индексами в скобках после вызова функции, ‘ возвращающей массив – уже как к массиву
For i = 1 To 3
For j = 1 To 3
Sheets("Лист1").Cells(i+4, j).Value = Format(massiv5(mas)(i, j), "##.###")
Next j
Next i
End Sub
Function massiv5(mas() As Single) As Single()
Dim mas1(1 To 3, 1 To 3) As Single
For i = 1 To 3
For j = 1 To 3
mas1(i, j) = mas(i, j) * 5
Next j
Next i
massiv5 = mas1
End Function
В VBA возможно создание процедур и функций, вызывающих самих себя – так называемых рекурсивных процедур. В следующем примере пользовательская функция, вычисляющая факториал числа, обращается к самой себе:
Function Fact(n As Long) As Long
If n = 1 Then
Fact = 1
Else
Fact = Fact(n - 1) * n
End If
End Function