- •Лекция 7. Процедуры и функции в vba
- •Процедуры, функции и макросы
- •Вставка процедур и функций
- •Процедуры
- •Определение процедуры
- •Вызов процедуры
- •Обработчики событий
- •Функции
- •Определение функции
- •Вызов функции
- •Функции, возвращающие массивы
- •Параметры
- •Необязательные параметры
- •Передача параметров по значению и по ссылке
Функции
Определение функции
Как было сказано, функция– это подпрограмма, которая возвращает значение. Функция имеет следующий синтаксис:
Function<имя> (<список параметров>)As<тип>
<инструкции>
<имя> = <выражение>
[Exit Function]
<инструкции>
<имя> = <выражение>
End Function
Первая строка, содержащая имя функции, список параметров и тип результата, называется заголовкомфункции. Имя и параметры функции создаются и анализируются компилятором по тем же правилам, что и имя и параметры процедуры.Тип, указываемый после списка параметров, задаёт тип результата функции.
Поскольку функция должна возвращать некоторый результат, необходимо указать, какое именно значение будет результатом функции. Для этого используется инструкция <имя> = <выражение>.
Рассмотрим для примера разработку функции, вычисляющей в массиве среднее арифметическое значений, больших заданного числа.
Public Function Average(mas As Range, h As Double) As Variant
Dim s As Double, n As Integer
Dim cell As Range
s = 0
n = 0
For Each cell In mas
If cell.Value > h Then
s = s + cell.Value
n = n + 1
End If
Next cell
If n = 0 Then
Average = CVErr(xlErrDiv0)
Else
Average = s / n
End If
EndFunction
Обратите внимание на то, что тип параметров указан как RangeиDouble, т.е. использованы конкретные типы данных. Параметры этой функции должны иметь именно такие типы – иначе не логично. При передаче в функцию параметров, типы которых не соответствуют указанным, а также если диапазон содержит не числа, функция будет возвращать признак ошибки #ЗНАЧ!. Проверка соответствия типов производится приложениемMicrosoftExcel. Если бы тип параметров был указан какVariant, разработчику функции пришлось бы добавлять инструкции для проверки типов параметров.
Тип результата функции указан как Variant, поскольку функция может вернуть как число, так и признак ошибки.
Объект Rangeобычно представляет собой двумерный массив. В данной функции, однако, используется только один циклFor Each, который позволяет перебрать все ячейки массива, в том числе и для двумерного массива. Другие языки программирования, в частности, Паскаль и С++ не имеют подобный циклов и для обработки двумерных массивов необходимо использовать два вложенных параметрических цикла. Однако, обратите внимание, что в данной функции расположение элементов по строкам и столбцам не принципиально и на результат не влияет. Поэтому можно использовать один циклFor Each. Но в других случаях, возможно, также придётся использовать два параметрических цикла.
Рассмотрим разработку функции, проверяющей в диапазоне наличие пустых ячеек.
Public Function Check(mas As Range) As Boolean
Dim cell As Range
For Each cell In mas
If cell.Value = "" Then
Check = True
Exit Function
End If
Next cell
Check = False
End Function
Вызов функции
Что функция заработала, её, также как и процедуру, необходимо вызвать. Для вызова функции существуют две возможности:
функция может быть использована как формула (или часть формулы) ячейки рабочего листа;
функция может быть вызвана из другой процедуры или функции.
Например, можно записать в ячейку следующие формулы:
=Average(A1:D5;10)
=Average(B2:F7;E14)
Можно вызвать функцию Checkиз функцииAverage.
Public Function Average(mas As Range, h As Double) As Variant
Dim s As Double, n As Integer
Dim cell As Range
If Check(mas) Then
Average = CVErr(xlErrNull)
Exit Function
End If
s = 0
n = 0
For Each cell In mas
If cell.Value > h Then
s = s + cell.Value
n = n + 1
End If
Next cell
If n = 0 Then
Average = CVErr(xlErrDiv0)
Else
Average = s / n
End If
End Function