Процедуры и функции

Функции с побочным эффектом

В классическом варианте все аргументы функции являются входными параметрами, и единственный результат вычисления функции - это ее возвращаемое значение., примером является функция Cube. Чаще всего, однако, используются функции с побочным эффектом, то есть такие функции, которые помимо получения значения функции изменяют значения некоторых результирующих параметров, передаваемых функции по ссылке. Например,

Public Function SideEffect(ByVal X As Integer, ByRef Y As Integer) As Integer
	SideEffect = X + Y
	Y = Y + 1
End Function

Public Sub TestSideEffect()
	Dim X As Integer, Y As Integer, Z As Integer
	X = 3: Y = 5
	Z = X + Y + SideEffect(X, Y)
	Debug.Print X, Y, Z
	X = 3: Y = 5
	Z = SideEffect(X, Y) + X + Y
	Debug.Print X, Y, Z
End Sub

Вот результаты вычислений:

3			 6			 16 
3			 6			 17

Как видите, обращаться с функциями, обладающими побочным эффектом, следует с осторожностью. В нашем примере результат вычисления суммы трех слагаемых зависит от порядка их записи. Это противоречит основным принципам математики. Более того, следует понимать, что результат вычисления непредсказуем, поскольку VBA может для увеличения эффективности изменять порядок действий при вычислении арифметического выражения. Поэтому лучше не использовать в выражении вызов функции с побочным эффектом, изменяющей значения входящих в него переменных.

Создание процедуры

Здесь мы рассмотрим создание процедур, текст которых пишется вручную. Чтобы создать новую процедуру, нужно:

Как правило, следует "автоматизировать" работу, вызвав диалоговое окно "Вставка процедуры" (Insert Procedure). Последовательность действий в этом случае такая:

Создание процедур обработки событий

VBA является языком, в котором, как и в большинстве современных объектно-ориентированных языков, реализована концепция программирования, управляемого событиями (event driven programming). Здесь нет понятия программы, которая начинает выполняться от Begin до End. В противоположность этому есть множество объектов Office 2000, представляющих документы и их компоненты, каждый из которых может реагировать на события. Пользователи системы документов и операционная система могут инициировать события в мире объектов. В ответ на возникновение события операционная система посылает сообщение соответствующему объекту. Реакцией объекта на получение сообщения является вызов процедуры - обработчика события. Задача программиста сводится к написанию обработчиков событий для объектов. Заметьте, все обычные процедуры и функции VBA, о которых мы говорили выше, вызываются прямо или косвенно из процедур обработки событий, если только речь не идет о режиме отладки. Именно эти процедуры являются спусковым крючком, приводящим к последовательности вызовов обычных процедур и функций.

С каждым из объектов Office 2000 связан набор событий, на которые он может реагировать. Процедуры обработки этих событий располагаются в модулях, связанных с объектами, реагирующими на события. Для кнопок меню, у которых есть только один обработчик события, соответствующая процедура может находиться в стандартном модуле или модуле макросов. Office 2000 позволяет при описании собственных классов, создаваемых программистом, задать определенный набор событий. Обработчики событий таких объектов создаются по определенной технологии. Все эти вопросы были уже подробно рассмотрены в предыдущих лекциях. Не будем сейчас повторяться и для напоминания ограничимся лишь простым примером. Вот, например, заготовка для события Close документа Word:

Private Sub Document_Close()
End Sub

Дополнив ее:

Private Sub Document_Close()
	Dim I As Integer

	For I = 1 To 5		' 5 раз подается
		Beep		' звуковой сигнал
	Next I
End Sub

мы получим процедуру, которая будет 5 раз подавать звуковой сигнал при закрытии документа. Эта процедура находится в модуле, связанном с объектом ThisDocument, и вызывается в момент закрытия документа.

Вызовы процедур и функций

Вызовы процедур Sub

Вызов обычной процедуры Sub из другой процедуры можно оформить по-разному. Первый способ:

имя список-фактических-параметров

где имя - имя вызываемой процедуры, а список-фактических-параметров - список аргументов, передаваемых процедуре; он должен соответствовать списку аргументов, заданному в описании процедуры. Задать этот список можно разными способами. В простейшем случае в нем перечисляются через запятую значения передаваемых процедуре аргументов в том же порядке, что и в списке аргументов из заголовка процедуры.

Может оказаться, что в одном проекте несколько модулей содержат процедуры с одинаковыми именами. Для различения этих процедур нужно при их вызове указывать имя процедуры через точку после имени модуля, в котором она определена. Например, если каждый из двух модулей Mod1 и Mod2 содержит определение процедуры ReadData, а в процедуре MyProc нужно воспользоваться процедурой из Mod2, этот вызов имеет вид:

Sub Myproc()
...
	Mod2.ReadData
...
End Sub

Если требуется использовать процедуры с одинаковыми именами из разных проектов, добавьте к именам модуля и процедуры имя проекта. Например, если модуль Mod2 входит в проект MyBook, тот же вызов можно уточнить так:

MyBooks.Mod2.ReadData

Второй способ вызова процедур связан с использованием оператора Call. В этом случае вызов процедуры выглядит так:

Call имя(список-фактических-параметров)

Обратите внимание на то, что в этом случае список-фактических-параметров заключен в круглые скобки, а в первом случае - нет. Попытка вызывать процедуру без оператора Call, но с заданием круглых скобок является источником синтаксических ошибок особенно для разработчиков с большим опытом программирования на Паскале или С, где списки параметров всегда заключаются в скобки. Следует обратить внимание на одну важную и, пожалуй, неприятную особенность вызова процедур VBA. Если процедура VBA имеет только один параметр, то она может быть вызвана без оператора Call и с использованием круглых скобок, не сообщая об ошибке вызова. Это было бы не так страшно, если бы возвращался правильный результат. К сожалению, это не так, проиллюстрируем сказанное примером:

Public Sub MyInc(ByRef X As Integer)
	X = X + 1
End Sub

Public Sub TestInc()
	Dim X As Integer
	X = 1
	'Вызов процедуры с параметром, заключенным в скобки,
	'синтаксически допустим, но работает не корректно!
	MyInc (X)
	Debug.Print X
	
	'Корректный вызов
	MyInc X
	Debug.Print X
	
	'Это тоже корректный вызов
	Call MyInc(X)
	Debug.Print X
 
End Sub

Вот результаты ее работы:

1 
2 
3

Хотя при первом вызове процедура нормально вызывается и увеличивает значение результата, но по завершении ее работы значение аргумента не изменяется. В этой ситуации не действует описатель ByRef, вызов идет так, будто параметр описан с описателем ByVal.

Если же процедура имеет более одного параметра, то попытка вызвать ее, заключив параметры в круглые скобки и не предварив этот вызов ключевым словом Call, приводит к синтаксической ошибке. Вот простой пример:

Public Sub SumXY(ByVal X As Integer, ByVal Y As Integer, ByRef Z As Integer)
	Z = X + Y
End Sub

Public Sub TestSumXY()
 Dim a As Integer, b As Integer, c As Integer
 a = 3: b = 5
 'SumXY (a, b, c)	'Синтаксическая ошибка
 SumXY a, b, c
 Debug.Print c
 
End Sub

В этом примере некорректный вызов процедуры SumXY будет обнаружен на этапе проверки синтаксиса.

Рассмотрим еще одну особенность вызова VBA процедур, связанную с аргументами, передаваемыми по ссылке. Как правило, в языках программирования для таких аргументов возможное значение фактического параметра ограничивается, - он должен быть именем переменной, ссылка на которую передается процедуре. VBA допускает возможность задания для таких аргументов констант и выражений в момент вызова. Все эти допущения делают язык менее надежным и чреваты серьезными ошибками. Именно поэтому мы останавливаем на них Ваше внимание.

Пусть, например, процедура CompVal c 4 аргументами, которая в зависимости от положительности z возвращает в переменной y либо увеличенное, либо уменьшенное на 100 значение x и сообщает об этом в строковой переменной w, определена следующим образом.

Sub CompVal(ByVal x As Single, ByRef y As Single, _
	ByVal z As Integer, ByRef w As String)
	
	If z > 0 Then		' увеличение
		y = x + 100
		w = "increase"
	Else			' уменьшение
		y = x - 100
		w = "decrease"
	End If
End Sub

Рассмотрим процедуру TestCompVal, в которой несколько раз вызывается процедура CompVal:

Sub TestCompVal()
	Dim a As Single
	Dim b As Single
	Dim n As Integer
	Dim S As String
	
	n = 5: a = 7.4	' значения параметров
	CompVal a, b, n, S		' 1-ый вызов
	Debug.Print b, S
	
	CompVal 7.4, b, 5, S ' 2-ой вызов
	Debug.Print b, S
	
	CompVal 0, 0, 0, S ' 3-ий вызов
	Debug.Print b, S
	
	CompVal 0, 0, 0, "В чем дело?" ' 4-ый вызов
	Debug.Print b, S
End Sub

В результате выполнения этой процедуры будут напечатаны следующие результаты:

107,4		increase
107,4		increase
107,4		decrease
107,4		decrease

Первые два вызова корректны. Следующие два вызова хотя и допустимы в языке VBA, но приводят к тому, что параметры, переданные по ссылке, не меняют своих значений в ходе выполнения процедуры и, по существу, вызов ByRef по умолчанию заменяется вызовом ByVal. Конечно, было бы лучше, если бы эта программа выдавала ошибки на этапе проверки синтаксиса.

Вызовы функций

Оформление вызова функции зависит от того, требуется ли использовать ее значение в вызывающей процедуре. Если Вы хотите передать вычисляемое функцией значение в переменную или применить его в выражении правой части оператора присвоения, то вызов пользовательской функции имеет тот же вид, что и вызов встроенной функции, например, sin(x). При вызове указывается имя функции, а после него идет заключенный в круглые скобки список фактических параметров. Например, если заголовок функции MyFunc:

Func Myfunc(Name As String, Age As Integer, Newdate As Date) As Integer

использовать ее значение можно с помощью вызовов:

val= Myfunc("Alex",25, "10/04/97")

или

x = sqrt(Myfunc("Alex",25, "10/04/97")) + x

Если же значение, вычисляемое функцией, нас не интересует и нужно воспользоваться лишь ее побочными эффектами, вызов функции может иметь ту же форму, что и вызов процедуры Sub. Например:

Myfunc "Alex",I, "10/04/97"

или:

Call Myfunc(Myson, 25, DateOfArrival)

Использование именованных аргументов

В предыдущих примерах фактические параметры вызова процедуры или функции располагались в том же порядке, что и формальные параметры в ее заголовке. Это не всегда удобно, особенно если некоторые аргументы необязательны ( Optional ). VBA позволяет указывать значения аргументов в произвольном порядке, используя их имена. При этом после имени аргумента ставятся двоеточие и знак равенства, после которого помещается значение аргумента (фактический параметр). Например, вызов рассмотренной выше процедуры-функции MyFunc может выглядеть так:

Myfunc Age:= 25, Name:= "Alex", Newdate:= DateOfArrival

Удобство такого способа особенно проявляется при вызове процедур с необязательными аргументами, которые всегда помещаются в конец списка аргументов в заголовке процедуры. Пусть, например, заголовок процедуры ProcEx:

Sub ProcEx(Name As String, Optional Age As Integer, Optional City = "Москва")

Список ее аргументов включает один обязательный аргумент Name и два необязательных: Age и City, - причем для последнего задано значение по умолчанию " Москва ". Если при вызове этой процедуры второй аргумент не требуется, то при вызове, не использующем именованных параметров, сам параметр опускается, но, выделяющая его запятая, должна оставаться:

ProcEx "Оля",,"Тверь"

Вместо этого можно использовать вызов с именами аргументов:

ProcEx City:="Тверь", Name:="Оля"

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

ProcEx Name:="Оля"

в качестве значения аргумента Age в процедуру передастся 0, а в качестве аргумента City - явно заданное по умолчанию значение " Москва ".

Как процедура "узнает", передан ли ей при вызове необязательный аргумент? Для этого можно воспользоваться функцией IsMissing. Она по имени аргумента возвращает логическое значение True, когда значение аргумента не передано в процедуру, и False, если аргумент задан. Но это все работает только в том случае, если параметр имеет тип Variant. Для всех остальных типов данных полагается, что в процедуру всегда передано значение параметра, явно или неявно заданное по умолчанию. Поэтому, если такая проверка необходима, то параметр должен иметь тип Variant. Отметим также, что для массива аргументов ParamArray функция IsMissing всегда возвращает False, и для установления его пустоты нужно проверять, что верхняя граница индекса меньше нижней.

Рассмотрим функцию от двух аргументов, второй из которых необязателен:

Function TwoArgs(I As Integer, Optional X As Variant) As Variant
	If IsMissing(X) Then
		' если 2-ой аргумент отсутствует, то вернуть 1-ый.
		TwoArgs = I
	Else
		' если 2-ой аргумент есть, то вернуть их произведение
		TwoArgs = I * X
	End If
End Function

Вот результаты нескольких вызовов этой функции в окне отладки:

? TwoArgs(5,7)
 35 
? TwoArgs(5.5)
 6 
? TwoArgs(5, 5.5)
 27,5 
? TwoArgs(5, "6")
 30

Аргументы, являющиеся массивами

Аргументы процедуры могут быть массивами. Процедуре передается имя массива, а размерность массива определяется встроенными функциями LBound и UBound. Приведем пример процедуры, вычисляющей скалярное произведение векторов:

Public Function ScalarProduct(X() As Integer, Y() As Integer) As Integer
	'Вычисляет скалярное произведение двух векторов.
	'Предполагается, что границы массивов совпадают.
	
	Dim i As Integer, Sum As Integer
	Sum = 0
	For i = LBound(X) To UBound(X)
		Sum = Sum + X(i) * Y(i)
	Next i
	ScalarProduct = Sum
End Function

Оба параметра процедуры, передаваемые по ссылке, являются массивами, работа с которыми в теле процедуры не представляет затруднений, благодаря тому, что функции LBound и UBound позволяют установить границы массива по любому измерению. Приведем программу, в которой вызывается функция ScalarProduct:

Public Sub TestScalarProduct()
	Dim A(1 To 5) As Integer
	Dim B(1 To 5) As Integer
	Dim C As Variant
	Dim Res As Integer
	Dim i As Integer
	
	C = Array(1, 2, 3, 4, 5)
	For i = 1 To 5
		A(i) = C(i - 1)
	Next i
	C = Array(5, 4, 3, 2, 1)
	For i = 1 To 5
		B(i) = C(i - 1)
	Next i
	Res = ScalarProduct(A, B)
	Debug.Print Res
End Sub

Конструкция ParamArray

Иногда, когда в процедуру следует передать только один массив, для этой цели можно использовать конструкцию ParamArray. Следующая процедура PosNeg подсчитывает суммы поступлений Positive и расходов Negative, указанные в массиве Sums:

Sub PosNeg(Positive As Integer, Negative As Integer, ParamArray Sums() As Variant)
	Dim I As Integer
	Positive = 0: Negative = 0
	For I = 0 To UBound(Sums()) ' цикл по всем элементам массива
		If Sums(I) > 0 Then
			Positive = Positive + Sums(I)
		Else
			Negative = Negative - Sums(I)
		End If
	Next I
End Sub

Вызов процедуры PosNeg может иметь такой вид:

Public Sub TestPosNeg()
	Dim Incomes As Integer, Expences As Integer
	
	PosNeg Incomes, Expences, -20, 100, 25, -44, -23, -60, 120
	Debug.Print Incomes, Expences
End Sub

В результате переменная Incomes получит значение 245, а переменная Expences - 147. Заметьте, преимуществом использования массива аргументов ParamArray является возможность непосредственного перечисления элементов массива в момент вызова.

Однако такое использование массива аргументов ParamArray не исчерпывает всех его возможностей. В более сложных ситуациях передаваемые аргументы могут иметь разные типы. Мы приведем сейчас пример, в котором, во-первых, действуют объекты Office 2000, а, во-вторых, используется передача параметров через массив аргументов ParamArray.

Задача о медиане

Для массива M и элемента Cand вычислить разность между числом элементов массива M, больших и меньших Cand.

Это вариация задачи о медиане - "среднем" элементе - массива. Медиану можно определить, например, таким алгоритмом: упорядочив массив, взять элемент, находящийся в середине. Есть и более эффективные алгоритмы. Но мы решили ограничиться более простой задачей - проверкой на "медианность". Заметим: если все элементы массива M различны и число их нечетно, то для медианы искомая в задаче разность равна 0. В общем случае, значение разности является мерой близости параметра Cand к медиане массива M. Но займемся программистскими аспектами этой задачи. У функции, ее реализующей, на входе - массив, а на выходе - скаляр. Мы хотели бы, чтобы эта функция могла вызываться в формулах рабочего листа, а в качестве фактического параметра ей могли быть переданы как объект Range, так и массив Visual Basic. Вот как мы реализовали эту функцию, назвав ее IsMediana:

Public Function IsMediana(M As Variant, Cand As Variant) As Integer
	'Дан массив M и элемент Cand. В качестве результата возвращается
	'разность между числом элементов массива M, больших и меньших Cand.
	Dim i As Integer, j As Integer
	Dim Pos As Integer, Neg As Integer
	Pos = 0: Neg = 0
'Анализ типа параметра M
	If TypeName(M) = "Range" Then
		For i = 1 To M.Rows.Count
			For j = 1 To M.Columns.Count
				If M.Cells(i, j) > Cand Then
					Pos = Pos + 1
				ElseIf M.Cells(i, j) < Cand Then
					Neg = Neg + 1
				End If
			Next j
		Next i
		IsMediana = Pos - Neg
	ElseIf TypeName(M) = "Variant()" Then
		'TypeName is "Variant()"
		'Это массив, но не совсем настоящий, для него не определены,
		'например, функции границ: LBound, UBound.
		Dim Val As Variant
		For Each Val In M
			If Val > Cand Then
				Pos = Pos + 1
			ElseIf Val < Cand Then
				Neg = Neg + 1
			End If
		Next Val
		IsMediana = Pos - Neg
	ElseIf TypeName(M) = "Integer()" Then
		'Это настоящий массив целых VBA, для которого
		'определены функции границ.
		For i = LBound(M) To UBound(M)
			If M(i) > Cand Then
				Pos = Pos + 1
			ElseIf M(i) < Cand Then
				Neg = Neg + 1
			End If
		Next i
		IsMediana = Pos - Neg
	Else
		MsgBox ("При вызове функции:IsMediana(M,Cand)" _
			 "- M не является массивом или объектом Range!")
	End If
End Function

Прокомментируем работу функции IsMediana.

Начнем с того, что приведем процедуру VBA, вызывающую нашу функцию. Вот ее текст:

Public Sub TestIsMediana()
	Const Size = 7
	Dim Mas(1 To Size) As Integer
	Dim Cand As Integer
	Dim i As Integer
	Dim Res As Integer
	'Инициализация массива целыми в интервале 1-20
	Debug.Print TypeName(Mas)
	Randomize
	For i = 1 To Size
		Mas(i) = Int(Rnd * 21)
	Next i
	Cand = Int(Rnd * 21)
	Res = IsMediana(Mas, Cand)
	Debug.Print "Массив:"
	For i = 1 To Size
		Debug.Print Mas(i)
	Next i
	Debug.Print "Кандидат:", Cand
	Debug.Print "Результат:", Res
End Sub

Вот результаты ее работы:

Массив:
3	8	14		0	3	8	2 
Кандидат:		2 
Результат:	 4

В данном варианте вызове анализ типа переданного параметра показал, что он является обычным массивом, соответственно был выбран третий вариант обработки, не требующий работы с объектами Office 2000.

Теперь покажем, что эту же функцию можно вызывать в формулах рабочего листа Excel, передавая ей в момент вызова объекты Range в разной форме, а также массивы, заданные константой - массивом. Посмотрим, как это выглядит на экране, и разберем примеры нескольких различных вызовов функции IsMediana в формулах рабочего листа:

На рабочем листе мы сформировали два массива: вектор M, вытянутый в виде столбца, и прямоугольную матрицу N. Вектор M записан в ячейках C6:C11, матрица N - в F5:I6. В ячейки E8:E15 мы поместили формулы, вызывающие функцию IsMediana. Они не являются формулами над массивами, несмотря на то, что параметром может быть массив рабочего листа. Важно, что результат - скаляр. Если бы результат, возвращаемый функцией, был массивом, формулу следовало бы вызывать как формулу над массивами. Для скалярного результата это не так.

В двух первых вызовах функции IsMediana (в ячейках E8, E9) передается в качестве параметров имя массива рабочего листа " M " и разные кандидаты: 7 и 8. Они оба годятся на роль медианы этого массива. В следующих двух вызовах проверяются кандидаты на медиану массива N. Как видите, оба кандидата 4 и 3 одинаково близки к медиане. Следующие два вызова в ячейках E12 и E13 демонстрируют возможность указания непосредственно диапазона ячеек в момент вызова, что позволяет, например, работать с частью массива. В следующем вызове вообще не используются в качестве входных данных элементы рабочего листа. Входным параметром M является массив - константа, заключенный в фигурные скобки, а его элементы разделяются символом " ; ". В этих случаях фактический параметр уже не является объектом Range, а имеет тип массива с элементами Variant. Поэтому и функция IsMediana будет работать по-другому, в отличие от предыдущих вызовов. Разбор случаев в зависимости от результата, возвращаемого функцией TypeName, приведет к выбору второго варианта. Наконец, вызов, записанный в формуле из ячейки E15, демонстрирует случай, когда входной параметр M - обычное число и, следовательно, не является ни объектом Range, ни массивом. Как следствие, разбор случаев в функции IsMediana приводит к четвертому варианту и появлению на экране окна сообщений.

Пользовательские функции, принимающие сложный объект Range

Известно, что в Excel объект Range может представлять несмежную область и являться объединением нескольких интервалов ячеек. Иначе говоря, один объект Range может задавать несколько массивов рабочего листа. Можно ли такой объект передать пользовательской функции и, если да, как его обрабатывать? Ответ: "можно", хотя соответствующий формальный параметр следует описывать особым образом. Процедуры и функции VBA допускают произвольное число параметров, это достигается за счет того, что один, последний по счету формальный параметр может иметь спецификатор ParamArray. В этом случае данный параметр задает фактически массив параметров с произвольным числом элементов. Именно эта техника и применяется для передачи в пользовательскую функцию сложного объекта Range, представляющего не один, а произвольное число массивов. У такой функции последний параметр должен иметь спецификатор ParamArray и быть массивом типа Variant.

Рассмотрим предыдущую задачу, немного усложнив ее, полагая, что при проверке кандидата на "медианность" используется произвольное число массивов. Вот как выглядит функция, решающая эту задачу:

Public Function IsMedianaForAll(Cand As Variant, ParamArray M() As Variant) As Integer
'Эта функция осуществляет те же вычисления, что и функция IsMediana
'Важное отличие состоит в том, что аргумент M может быть задан сложным объектом 
'Range как объединение массивов.
	Dim i As Integer, j As Integer
	Dim Pos As Integer, Neg As Integer
	Pos = 0: Neg = 0
	Dim Elem As Variant
'Теперь M - это массив параметров, а Elem - его элемент.
	For Each Elem In M
	'Анализ типа параметра Elem
		If TypeName(Elem) = "Range" Then
		For i = 1 To Elem.Rows.Count
			For j = 1 To Elem.Columns.Count
				If Elem.Cells(i, j) > Cand Then
					Pos = Pos + 1
				ElseIf Elem.Cells(i, j) < Cand Then
					Neg = Neg + 1
				End If
			Next j
		Next i
	ElseIf TypeName(Elem) = "Variant()" Then
		'TypeName is "Variant()"
		'Это массив, но не совсем настоящий, для него не определены,
		'например, функции границ: LBound, UBound.
		Dim Val As Variant
		For Each Val In Elem
			If Val > Cand Then
				Pos = Pos + 1
			ElseIf Val < Cand Then
				Neg = Neg + 1
			End If
		Next Val
	ElseIf TypeName(Elem) = "Integer()" Then
		'Это настоящий массив целых VBA, для которого
		'определены функции границ.
		For i = LBound(Elem) To UBound(Elem)
			If Elem(i) > Cand Then
				Pos = Pos + 1
			ElseIf Elem(i) < Cand Then
				Neg = Neg + 1
			End If
		Next i
	Else
		MsgBox ("При вызове IsMedianaForAll один из аргументов" _
			 "не является массивом или объектом Range!")
	End If
	Next Elem
	IsMedianaForAll = Pos - Neg
End Function

Комментируя работу этой функции, отметим:

Демонстрацию использования этой функции начнем с ее вызова в процедуре VBA, которая передает ей целочисленный массив элементов:

Public Sub TestIsMedianaForAll()
	Const Size = 7
	Dim Mas(1 To Size) As Integer
	Dim Cand As Integer
	Dim i As Integer
	Dim Res As Integer
	'Инициализация массива целыми в интервале 1-20
	Debug.Print TypeName(Mas)
	Randomize
	For i = 1 To Size
		Mas(i) = Int(Rnd * 21)
	Next i
	Cand = Int(Rnd * 21)
	Res = IsMedianaForAll(Cand, Mas)
	Debug.Print "Массив:"
	For i = 1 To Size
		Debug.Print Mas(i)
	Next i
	Debug.Print "Кандидат:", Cand
	Debug.Print "Результат:", Res
End Sub

Приведем результаты выполнения этой процедуры:

Integer()
Массив:
 9	1	14	11	11	18	0 
Кандидат:		12 
Результат:	-3

А теперь покажем, как эта функция вызывается в формулах рабочего листа Excel На том же рабочем листе, где мы проводили эксперименты с формулами, вызывающими функцию IsMediana, мы записали еще несколько формул, вызывающих функцию IsMedianaForAll. Заметьте, ее аргумент может иметь значительно более сложный вид, чем при вызове функции IsMedina.

Проанализируем четыре сделанных вызова:

Рекурсивные процедуры

VBA допускает создание рекурсивных процедур, т. е. процедур, при вычислении вызывающих самих себя. Вызовы рекурсивной процедуры могут непосредственно входить в ее тело, или она может вызывать себя через другие процедуры. В последнем случае в модуле есть несколько связанных рекурсивных процедур. Стандартный пример рекурсивной процедуры - функция-факториал Fact(N)= N!. Вот ее определение в VBA:

Function Fact(N As Integer) As Long
	If N <= 1 Then		' базис индукции.
		Fact = 1		' 0! =1.
	Else	' рекурсивный вызов в случае N > 0.
		Fact = Fact(N - 1) * N
	End If
End Function

Так как каждый вызов процедуры требует накладных расходов, эффективнее для факториала итеративная программа:

Function Fact1(N As Integer) As Long
Dim Fact As Long, i As Integer
Fact = 1		' 0! =1.
If N > 1 Then		' цикл вместо рекурсии.
	For i = 1 To N
		Fact = Fact * i
		Next i
	End If
	Fact1 = Fact
End Function

Приведем процедуру, оценивающую время исполнения рекурсивного и не рекурсивного варианта:

Public Sub TestRecursive()
	'Сравнение по времени рекурсивной и нерекурсивной реализации факториала.
	Dim i As Long, Res As Long
	Dim Start As Single, Finish As Single
	'Рекурсивное вычисление факториала
	Start = Timer
	For i = 1 To 100000
		Res = Fact(12)
	Next i
	Finish = Timer
	Debug.Print "Время рекурсивных вычислений:", Finish - Start
	
	'Нерекурсивное вычисление факториала
	Start = Timer
	For i = 1 To 100000
		Res = Fact1(12)
	Next i
	Finish = Timer
	Debug.Print "Время нерекурсивных вычислений:", Finish - Start
End Sub

Вот результаты вычислений, приведенные для двух запусков тестовой процедуры:

Время рекурсивных вычислений:				6,238281 
Время нерекурсивных вычислений:			2,304688 
Время рекурсивных вычислений:				6,25 
Время нерекурсивных вычислений:			2,253906

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

Польза от рекурсивных процедур в большей мере может проявиться при обработке данных, имеющих рекурсивную структуру (скажем, иерархическую или сетевую). Основные структуры данных (объекты) Office 97 вообще-то не являются рекурсивными: один рабочий лист Excel не может быть значением ячейки другого, одна таблица Access - элементом другой и т.д. Но данные, хранящиеся на рабочих листах Excel или в БД Access, сами по себе могут задавать "рекурсивные" отношения, и для их успешной обработки следует пользоваться рекурсивными процедурами. Мы рассмотрим сейчас класс, для работы с двоичными деревьями поиска. Деревья представляют рекурсивную структуру данных, поэтому и операции над ними естественным образом определяются рекурсивными алгоритмами.

Деревья поиска

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

Бинарным будем называть дерево, у которого каждая вершина имеет одного или двух потомков, называемых левым и правым сыном (поддеревом). В дальнейшем будем полагать, что узел нашего дерева содержит информационное поле info и поле ключа - key. Деревом поиска (двоичным или лексикографическим деревом) будем называть бинарное дерево, в котором ключ каждой вершины больше ключа, хранящегося в корне левого поддерева, и меньше ключа, хранящегося в корне правого поддерева.

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

Класс TreeNode

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

'Class TreeNode
'Элемент дерева

Public key As String
Public info As String
Public left As New BinTree
Public right As New BinTree

Класс BinTree

Класс BinTree содержит одно свойство - объект класса TreeNode, задающий корень дерева и группу операций над элементами дерева. Одним из основных методов класса является метод SearchAndInsert, который, по существу, реализует две операции - поиска элемента в дереве по заданному ключу и вставки элемента в дерево. Заметьте, что вставка должна быть реализована так, чтобы выполнялось основное условие дерева поиска: для каждой вершины ее ключ больше всех ключей вершин левого поддерева и меньше всех ключей вершин правого поддерева. Такая структура обеспечивает эффективное выполнение операций поиска, так как в этом случае для поиска требуется просмотр вершин, лежащих только на одной ветви дерева, ведущей от корня к искомому элементу. Этот метод обеспечивает создание класса, позволяя, начав с корня, вставлять элемент за элементом. Кроме этого метода мы определим методы для удаления элементов и группу методов обхода дерева. Все методы реализованы с использованием рекурсивных алгоритмов. Приведем описание класса:

Option Explicit

'Класс BinTree
'Бинарным будем называть дерево, у которого каждая вершина имеет
'одного или двух потомков, называемых левым и правым сыном (поддеревом).
'В дальнейшем будем полагать, что узел нашего дерева содержит
'информационное поле info и поле ключа - key.
'Деревом поиска (двоичным или лексикографическим деревом) будем называть
'бинарное дерево, в котором ключ каждой вершины больше ключа, хранящегося
'в корне левого поддерева, и меньше ключа, хранящегося в корне правого поддерева.
'Рассмотрим операции над деревом поиска: поиск, включение, удаление элементов
'и обход дерева. Все операции сохраняют структуру дерева поиска.

Public root As TreeNode

Public Sub PrefixOrder()
	'Префиксный обход дерева (корень, левое поддерево, правое)

	If Not (root Is Nothing) Then
		With root
			Debug.Print "key: ",.key, "info: ",.info
			.left.PrefixOrder
			.right.PrefixOrder
		End With
	End If
				
End Sub

Public Sub InfixOrder()
	'Инфиксный обход дерева (левое поддерево, корень, правое)

	If Not (root Is Nothing) Then
		With root
			.left.InfixOrder
			Debug.Print "key: ",.key, "info: ",.info
			.right.InfixOrder
		End With
	End If
				
End Sub

Public Sub PostfixOrder()
	'Постфиксный обход дерева (левое поддерево, правое, корень)

	If Not (root Is Nothing) Then
		With root
			.left.PostfixOrder
			.right.PostfixOrder
			Debug.Print "key: ",.key, "info: ",.info
		End With
	End If
				
End Sub

Public Sub SearchAndInsert(key As String, info As String)
	'Если в дереве есть узел с ключом key,
	'то возвращается информация в этом узле - работает поиск
	'Если такого узла нет, то создается новый узел и его поля
	'заполняются информацией, - работает вставка.
	'Вначале поиск
	If root Is Nothing Then ' элемент не найден и происходит вставка
		Set root = New TreeNode
		root.key = key: root.info = info
	ElseIf key < root.key Then
		'Поиск в левом поддереве
		root.left.SearchAndInsert key, info
	ElseIf key > root.key Then
		'Поиск в правом поддереве
		root.right.SearchAndInsert key, info
	Else 'Элемент найден - возвращается результат поиска
		info = root.info
	End If
		
End Sub

Public Sub DelInTree(key As String)
	'Эта процедура позволяет удалить элемент дерева с заданным ключом
	'Удаление с сохранением структуры дерева более сложная операция,
	'чем вставка или поиск. Причина сложности в том, что при удалении
	'элемента остаются два его потомка, которые необходимо корректно
	'связать с оставшимися элементами, чтобы не нарушить структуру дерева поиска.
	'В программе анализируются три случая:
	'Удаляется лист дерева (нет потомков - нет проблем),
	'Удаляется узел с одним потомком (потомок замещает удаленный узел),
	'Есть два потомка. В этом случае узел может быть заменен одним из двух
	'возможных кандидатов, не имеющих двух потомков.
	'Кандидатами являются самый левый узел правого подддерева и
	'самый правый узел левого поддерева.
	'Мы производим удаление в левом поддереве.
	
	Dim q As TreeNode
	If root Is Nothing Then
		Debug.Print "Key is not found"
	ElseIf key < root.key Then
		'Удаляем из левого поддерева
		root.left.DelInTree key
	ElseIf key > root.key Then
		'Удаляем из правого поддерева
		root.right.DelInTree key
	Else
		'Удаление узла
		Set q = root
		If q.right.root Is Nothing Then
			Set root = q.left.root
		ElseIf q.left.root Is Nothing Then
			Set root = q.right.root
		Else 'есть два потомка
			q.left.ReplaceAndDelete q
		End If
		Set q = Nothing
	End If
	
End Sub

Public Sub ReplaceAndDelete(q As TreeNode)
	'Заменяет узел на самый правый
	If Not (root.right.root Is Nothing) Then
		root.right.ReplaceAndDelete q
	Else	'Найден самый правый
		q.key = root.key: q.info = root.info
		Set root = root.left.root
	End If
	
End Sub

Все методы класса довольно подробно прокомментированы, однако хотелось бы подчеркнуть некоторые моменты:

Работа со словарем

Используем класс BinTree для работы со словарем. В нашем примере работы с классом будет создаваться словарь, в нем будет осуществляться поиск и удаление элементов. Вот текст процедуры, выполняющей эти операции:

Public Sub WorkwithBinTree()
 Dim MyDict As New BinTree
 Dim englword As String, rusword As String
 'Создание словаря

 MyDict.SearchAndInsert key:="dictionary", info:="словарь"
 MyDict.SearchAndInsert key:="hardware", info:="аппаратура, аппаратные средства"
 MyDict.SearchAndInsert key:="processor", info:="процессор"
 MyDict.SearchAndInsert key:="backup", info:="резервная копия"
 MyDict.SearchAndInsert key:="token", info:="лексема"
 MyDict.SearchAndInsert key:="file", info:="файл"
 MyDict.SearchAndInsert key:="compiler", info:="компилятор"
 MyDict.SearchAndInsert key:="account", info:="учетная запись"

 'Обход словаря
 MyDict.PrefixOrder
 
 'Поиск в словаре
 englword = "account": rusword = ""
 MyDict.SearchAndInsert key:=englword, info:=rusword
 Debug.Print englword, rusword
 
 'Удаление из словаря
 MyDict.DelInTree englword
 englword = "hardware"
 MyDict.DelInTree englword

 'Обход словаря
 MyDict.PrefixOrder
 
End Sub

Приведем результаты ее работы:

key:			dictionary	info:		 словарь
key:			backup		info:		 резервная копия
key:			account		info:		 учетная запись
key:			compiler	info:		 компилятор
key:			hardware	info:		 аппаратура, аппаратные средства
key:			file		info:		 файл
key:			processor	info:		 процессор
key:			token		info:		 лексема
account		учетная запись
key:			dictionary	info:		 словарь
key:			backup		info:		 резервная копия
key:			compiler	info:		 компилятор
key:			file		info:		 файл
key:			processor	info:		 процессор
key:			token		info:		 лексема

Обратите внимание, процедура обхода дерева в префиксном порядке печатает слова из словаря не в том порядке, в каком он создавался. Это и понятно, поскольку дерево создается, как лексикографическое дерево поиска. Взгляните, как выглядит дерево поиска нашего словаря после его первоначального создания.