Поиск по сайту:


«VBA Подпрограмма-функция»

Файл: 4 КБ
Поделиться:
VBA содержит встроенные или стандартные функции, например, sqr, cos или chr. Кроме того, с помощью оператора Function можно писать собственные процедуры Function. Эти функции называют нестандартные или пользовательскими.
Синтаксис процедуры Function таков:
[Private|Public][Static]Function <имя процедуры> (<аргументы>) [As type <имя типа>]
	<операторы> 
End Function
Приведем конкретный пример:
Funcstion toint(b As String) As Integer
Dim i As Integer, l As Integer, k As Integer, j As Integer
l = Len(b): i = 0
For k = l To 1 Step -1
j = CInt(Mid(b, k, 1))
i = i + j * 2 ^ (l - k)
Next
toint = i
End Function
Как и процедура Sub, процедура Function является самостоятельной и может принимать параметры, выполнять ряд операторов и изменять значения своих параметров. В отличие от процедуры Sub, имя процедуры Function может возвращать значение в вызывающую процедуру. Существуют три различия между процедурами Sub и Function:
 Возвращаемое процедурой Function значение присваивается самому имени <имя процедуры> процедуры. Возвращаемое процедурой Function значение можно использовать в выражениях в программе.
 Как и переменные процедуры Function имеют тип, который определяет тип возвращаемого значения. (В отсутствие ключевого слова As в операторе определения процедуры ей назначается по умолчанию тип variant.)
 Вызов процедуры Function, или просто функции, в основном осуществляется заданием ее имени и параметров в правой части большого оператора или в составе выражения.
Вот, например, функция, вычисляющая гипотенузу прямоугольного треугольника при заданных катетах:
Function Hypotenuse (A As Integer, В As Integer) As String
Hypotenuse = Sqr(A^2 + В^2)
End Function
В VBA процедура Function вызывается точно так же, как и любая встроенная функция:
strX = Hypotenuse(Width, Height)
Вызов процедур Function
Если функция не имеет аргументов, скобки после имени функции можно не ставить:
'Все эти операторы вызывают не имеющую аргументов процедуру Function ToDec.
Debug.Print 10 * ToDec 
	X = ToDec
	If ToDec =10 Then Debug. Print "Out of Range" 
X = AnotherFunction(10 * ToDec)
Обычно вызов пользовательской функции (нестандартной функции, написанной программистом) аналогичен вызову встроенной (стандартной) функции VBA, например Abs.
Функцию можно вызывать так же, как и процедуру sub.
'Следующие операторы вызывают одну и ту же функцию:
Call Year(Now)
Year Now
Значение функции при ее вызове подобным образом игнорируется.
Механизмы передачи параметров.
Обычно процедуре для выполнения требуются некоторые исходные данные. Эти данные могут передаваться процедуре при помощи параметров процедуры. При этом различают формальные параметры, то есть описанные в заголовке процедуры и используемые в ее теле, и фактические - те, что были указаны при ее вызове. Заметим, что имена формальных и фактических параметров не обязаны совпадать.
Типы данных параметров
По умолчанию параметры процедур имеют тип variant. Можно объявлять для параметров и другие типы данных. Заметим, что если типы фактически передаваемых в функцию значений отличаются от типа формальных параметров, то происходит неявное преобразование типов, Поэтому рекомендуется описывать в заголовке функции типы ее формальных параметров, а при вызове функции проверять соответствие типов фактических и формальных параметров.
Передача параметров по значению
При передаче параметра по значению (by value) процедуре передается копия переменной, выступающей в качестве параметра процедуры. Если процедура изменяет значение параметра, это затрагивает только копию переменной, а не саму переменную. Значение переменной- оригинала в вызывающей процедуру программе сохраняется прежним.
Для передачи параметров по значению используется ключевое слово ByVal,. например:
Sub PostAccounts(ByVal intAcctNum as Integer)
	<операторы тела процедуры> 
End Sub
Фактические параметры, заданные константами, всегда передаются по значению.
Передача параметров по ссылке
Передача процедуре параметров по ссылке (by reference) открывает ей доступ к области памяти, где хранится содержимое переменной. В результате процедура может изменять значение переменной, являющейся ее параметром. По умолчанию в VBA все параметры передаются по ссылке.
Если задается тип данных параметра, передаваемого по ссылке, то передаваемый параметр должен содержать значение специфицированного типа данных. Можно обойти это правило, передавая процедуре в качестве параметра выражение. В этом случае VBA вычислит выражение и передаст его значение процедуре, преобразовав его в нужный тип, если это окажется возможным.
Проще всего представить переменную как выражение, заключив ее в круглые скобки. Например, чтобы передать процедуре целое число через строковый параметр, можно использовать следующий код:
Private Sub Form_Load()
Dim intX As Integer
intX = 12 * 3
ВызываемаяПроцедура(intX)
End Sub
Sub ВызываемаяПроцедура (Bar As String)
MsgBox Bar   'Значение переменной Bar - строка "36"
End Sub
Необязательные параметры
С помощью ключевого слова Optional в списке параметров можно задавать необязательные параметры 
(optional arguments) процедуры. Если какой-то аргумент задан как необязательный, 
то и все последующие аргументы в списке аргументов должны быть необязательными и объявляются с ключевым словом optional. 
В этом коде все аргументы не являются обязательными:
Dim strИмя As String
Dim strАдрес As String
Sub Text(Optional x As String, Optional y As String)
MsgBox x
MsgBox y
End Sub
Private Sub Exec()
strИмя = "ВашеИмя"
strАдрес = 12345   ' Передаются два параметра.
Call Text(strИмя, strАдрес)
End Sub
В этом коде некоторые аргументы обязательны:
Dim strИмя As String 
Dim varАдрес As Variant
Sub Text(x As String, Optional у As Variant)
MsgBox x
If Not IsMissing(y) Then
MsgBox y
End If 
End Sub
Private Sub Exec()
strИмя = "ВашеИмя"   ' Второй параметр не передается.
Call Text(strИмя) 
End Sub
Если необязательный параметр отсутствует, то он рассматривается как параметр с типом variant, имеющий значение Empty. В предыдущем при мере показано, как с помощью функции IsMissing проверять необязательные параметры.
Значения по умолчанию для необязательных параметров
В следующем примере процедура возвращает значение по умолчанию необязательного параметра, если при ее вызове он опущен:
Sub ListText (x As String, Optional у As Integer = 12345) 
MsgBox x
MsgBox y
End Sub
Private Sub Exec()
strName = "yourname"   ' Второй параметр не передается. 
Call Text (strName)   ' Добавляет "yourname" and "12345".
End Sub
Неизвестное число параметров
Обычно при вызове процедуры число ее параметров должно быть точно таким же, как и при ее объявлении. Ключевое слово РaramArray "разрешает" процедуре принимать произвольное число параметров. Приведем пример:
Dim x As Integer Dim у As Integer Dim intSum As Integer
Sub Sum(ParamArray intNums())
For Each x In intNums 
у = у + x
Next x
intSum = у 
End Sub
Private Sub Exec()
Sum 1, 3, 5, 7, 8
MsgBox intSum 
End Sub
Рекурсия. Под рекурсией понимают способность программы вызывать саму себя. Процедуры выделяют для хранения переменных ограниченный объем памяти в стеке. Стек - это рабочая область памяти, которая заполняется и освобождается динамически в соответствии с потребностями выполняемой программы. При каждом вызове процедурой самой себя выделяется дополнительный объем этой памяти. Процедура, вызывающая сама себя, называется рекурсивной. Рекурсивная процедура, которая бесконечно вызывает саму себя, приводит к ошибке. Например:
Function RunOut(Maximum)
	RunOut = RunOut(Maximum)
End Function
Эта ошибка менее очевидна, если две процедуры бесконечное число раз вызывают друг друга, или некоторое условие, ограничивающее рекурсию, никогда не выполняется. Рекурсия имеет свои области применения. Например, следующая процедура использует рекурсию для вычисления факториалов:
Function Factorial (N)
	If N <= 1 Then	' Достигнут конец рекурсивных вызовов.
Factorial = 1	' (N = 0) завершение вызовов.
	Else	' Повторный вызов функции, если N > 0.
	Factorial = Factorial(N - 1) * N
	End If
End Function
Необходимо проверить, что рекурсивная функция не вызывает себя столько раз, что начинает сказываться нехватка памяти. При возникновении ошибки, убедитесь, что процедура не вызывает себя бесконечное число раз. После этого попробуйте сэкономить память с помощью:
 устранения ненужных переменных;
 использования типов данных , отличных от Variant;
 переосмысления логики процедуры. Часто вместо рекурсии можно воспользоваться вложенными циклами.
Создание пользовательской функции на VBA в среде EXCEL. Любая процедура и функция, содержащиеся в общем модуле (но не в модуле формы и не в модуле класса), могут быть использована на рабочем листе Excel. Для выполнения процедур используется команда меню Сервис/Макрос/Макросы, а для вставки функций применяется команда меню Вставка/Функция или кнопка стандартной панели инструментов Вставка функции. При вставке функции запускается мастер функций, и если в проекте VBA имеется хотя бы одна функция, то в правом окне мастера функций появляется категория Определенные пользователем. При выборе этой категории в левом окне мастера функций отображается перечень функций проекта VBA. После выбора необходимой функции из списка, мастер функций предлагает ввести ее параметры, количество которых определено в ее заголовке. Если функция имеет необязательные параметры, или их число не определено, то мастер функций будет открывать окна для их ввода последовательно, по мере заполнения ранее открытых, как это происходит, например, при вставке функции рабочего листа СУММ. Значения параметров можно ввести с клавиатуры, либо можно ввести адрес ячейки, содержащей значение, либо можно указать ячейку с параметром, щелкнув по ней правой кнопкой мыши. Заметим, что ввод в качестве параметра диапазона ячеек допустим лишь в том случае, когда функция имеет неопределенное количество параметров, либо вводимый параметр представляет собой массив, либо он имеет объектовый тип данных Range.
В качестве примера перепишем в виде функции процедуру замены букв "А", "Б", "В" на цифры 1, 2, 3 соответственно (пример 6):
Function пример11(s As String)
Dim sn As String, t As String
Dim l As Integer, i As Integer
l = Len(s)
For i = 1 To l
t = Mid(s, i, 1)
Select Case t
Case "А": sn = sn + "1"
Case "Б": sn = sn + "2"
Case "В": sn = sn + "3"
Case Else: sn = sn + t
End Select
Next i
пример11 = sn
End Function
Отметим, что поскольку функция не может быть выполнена непосредственно, а должна быть вызвана, то для ее отладки (например, в пошаговом режиме) необходимо написать процедуру, обеспечивающую ввод параметров функции, ее вызов и вывод результата работы функции. Если функция возвращает более одного значения (используя список формальных параметров), то эти значения не могут быть выведены на рабочий лист с помощью мастера функций.