4. Функции

4. Определение функции

Как было сказано, функция– это подпрограмма, которая возвращает значение. Функция имеет следующий синтаксис:

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

5. Вызов функции

Что функция заработала, её, также как и процедуру, необходимо вызвать. Для вызова функции существуют две возможности:

• функция может быть использована как формула (или часть формулы) ячейки рабочего листа;

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

Например, можно записать в ячейку следующие формулы:

=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

---

6. Функции, возвращающие массивы

В качестве результата функция VBA может возвращать массив значений. Такая функция вставляется не в одну ячейку, а в диапазон, и вставка завершается нажатием клавиш Ctrl + Shift + Ввод.

Public Function GreaterThanAverage(m As Range) As Variant

Dim r() As Integer

Dim n As Integer, i As Integer, j As Integer

Dim av As Double

ReDim r(1 To m.Rows.Count, 1 To 1)

av = 0

For i = 1 To m.Rows.Count

For j = 1 To m.Columns.Count

av = av + m.Cells(i, j)

Next j

Next i

av = av / m.Rows.Count / m.Columns.Count

For i = 1 To m.Rows.Count

n = 0

For j = 1 To m.Columns.Count

If m.Cells(i, j) > av Then n = n + 1

Next j

r(i, 1) = n

Next i

GreaterThanAverage=r

EndFunction

Обратите внимание, что одномерный массив соответствует строке, поэтому в данном случае не может быть использован. Одномерный массив, который надо расположить в виде столбца, должен быть объявлен как двумерный массив с одним столбцом.

5. Параметры

Существуют две точки, где используется список параметров подпрограммы – заголовок процедуры или функции и вызов процедуры или функции. Параметры, записанные в заголовке, называются формальными, а параметры, записанные в вызове, –фактическими.

Между этими двумя списками существует разница, во-первых, в синтаксисе, а во-вторых, что более важно, в семантике. Список формальных параметров – это список неких условныхпеременных. Он описывает данные, которые должны быть переданы в подпрограмму, в общем виде. Например, в функциюAverageнеобходимо передавать диапазон и число.

Список фактических параметров – это список вполне конкретных значений, которые реально передаются в подпрограмму и которые она обрабатывает. Мы рассматривали примеры вызова функции Average, в которых в функцию передавались конкретные диапазоны (A1:D5,B2:F7) и конкретные числа (10, число из ячейкиE14). Формальные параметры – это, в общем-то, абстракция. Фактические параметры должны реально существовать, т.е. это должны быть конкретные диапазоны, константы, числа, содержащиеся в конкретной ячейке. Можно провести аналогию с математическим выражением, записанным в общем виде с использованием переменных, например,x² + y². Можно построить график функции, исследовать свойства этого выражения, оперировать с ним в общем виде, но нельзя вычислить значение этого выражения, пока мы не подставим конкретные числа вместо переменныхxиy. Формальные параметры соответствуют переменным математического выражения, а фактические параметры – конкретным числам.

---

Список формальных параметров определяется количество, порядок и типы параметров, которые должны быть переданы в подпрограмму при вызове.

Список фактических параметров представляет собой список выражений, разделённых запятыми. Значения этих выражений подставляются вместо формальных параметров последова­тельно, т.е. значение первого фактического параметра – вместо первого формального параметра, значение второго фактического параметра – вместо второго формального параметра и т.д.

Список фактических параметров должен соответствовать списку формальных параметров по следующим критериям.

1. По количеству.

2. По типу.

3. По порядку следования.

---

7. Необязательные параметры

Язык VBA позволяет объявлять параметр как необязательный, а также задавать так называемыезначения по умолчанию.

Для указания того, что параметр является необязательным, используется ключевое слово Optional, которое ставится перед именем параметра.

Для задания значения по умолчанию после описания формального параметра ставится знак равенства и значение, которое подставляется в подпрограмму, в случае отсутствия в вызове соответствующего фактического параметра.

Рассмотрим функцию, которая может возвращать целый диапазон, одну строку из диапазона, один столбец из диапазона или одну ячейку диапазона.

Public Function RangePart(r As Range, Optional row As Integer = 0, Optional column As Integer = 0) As Variant

If row = 0 And column = 0 Then

RangePart = r

ElseIf row = 0 Then

RangePart = r.Columns(column)

ElseIf column = 0 Then

RangePart = r.Rows(row)

Else

RangePart = r.Cells(row, column)

EndIf

EndFunction

'Функция возвращает весь диапазон, переданный в качестве первого параметра

r = RangePart(Worksheets(5).Range("A1:C4"))

'Функция возвращает одну ячейку, находящуюся в 1 строке 3 столбце

r = RangePart(Worksheets(5).Range("A1:C4"), 1, 3)

'Функция возвращает одну строку

r = RangePart(Worksheets(5).Range("A1:C4"), 5)

'Функция возвращает один столбец

r = RangePart(Worksheets(5).Range("A1:C4"), , 4)

r = RangePart(Worksheets(5).Range("A1:C4"), column:=4)

Параметры с типом Variantможно просто опускать, без задания значения по умолчанию, т.к. типVariantпозволяет в самом параметре указать факт отсутствия параметра. Для проверки, был ли параметр задан, используется функцияIsMissing. Рассмотрим для примера процедуру, которая в заданном диапазоне заменяет отрицательные числа. Кроме исходного диапазона можно также задать диапазон, откуда берутся числа для замены (в случае отсутствия этого параметра отрицательные числа заменяются модулями), и диапазон, куда копируется исходный диапазон с изменёнными значениями.

Public Sub Change(source As Range, Optional replace, Optional dest)

Dim i As Integer, j As Integer

If IsMissing(dest) Then

Set dest = source

Else

If TypeName(dest) <> "Range" Then Exit Sub

End If

If Not IsMissing(replace) And TypeName(replace) <> "Range" Then Exit Sub

For i = 1 To source.Rows.Count

For j = 1 To source.Columns.Count

If source.Cells(i, j) < 0 Then

If IsMissing(replace) Then

dest.Cells(i, j) = -source.Cells(i, j)

Else

dest.Cells(i, j) = replace.Cells(i, j)

End If

Else

dest.Cells(i, j) = source.Cells(i, j)

End If

Next j

Next i

EndSub

'Замена отрицательных чисел диапазона A1:C2 на их модули

---

Смотрите также файлы

• Lection6.docx

• Lection5.docx

• Lection4.docx

• Lection3.docx

• Lection2.docx