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

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

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

Public Function IsMedianaForAll(Cand As Variant, _ ParamArray M() As Variant) As Integer 'Эта функция осуществляет те же вычисления, что и функция IsMediana 'Важное отличие состоит в том, что аргумент M может быть 'задан сложным объектом Range 'или представлять объединение массивов. Dim Pos As Integer, Neg As Integer Pos = 0: Neg = 0 Dim Elem As Variant 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 Else MsgBox ("При вызове IsMedianaForAll один из аргументов" & _ vbCrLf & "не является массивом или объектом Range!") End If Next Elem IsMedianaForAll = Pos - Neg

End Function

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

Формально эта функция по- прежнему имеет два параметра Cand и M. Правда, теперь они поменялись местами, и параметр M стал последним. Фактически у этой функции теперь произвольное число параметров, поскольку параметр M, сохранив тип Variant, стал массивом параметров. Спецификатор ParamArray подчеркивает, что это специальный массив с произвольным числом элементов.
Для работы с массивом M используется цикл типа For Each. В цикле выделяется очередной элемент Elem типа Variant, а дальше используется уже знакомый по функции IsMediana алгоритм проверки элемента Cand.
Разбор случаев делается независимо для каждого из элементов массива M. Это значит, что на входе могут быть заданы одновременно как массивы рабочего листа, так и массивы констант Visual Basic.

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

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

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

=IsMedianaForAll(7;M;N). В этом вызове наш кандидат - число 7 - проверяется по отношению к объединению двух массивов рабочего листа, заданных своими именами M и N. Формальных параметров у функции два, а фактических при вызове задается три. Два последних можно рассматривать как сложный объект Range, представляющий несмежную область ячеек, задающую объединение вектора M и матрицы N. С программистской точки зрения, можно полагать, что передается массив с произвольным числом элементов, где каждый из них в свою очередь является массивом. Такой фактический параметр является допустимым значением формального параметра нашей функции, имеющего спецификатор ParamArray.
=IsMedianaForAll(4,5;N;M). В этом вызове мало нового в сравнении с предыдущим. Изменен порядок следования массивов N и M, изменен кандидат, - им стало число 4.5, не входящее ни в один из массивов. Как показывает результат, это число является медианой объединенных массивов.
=IsMedianaForAll(7; {4;7;2}; {9;12;5}). Здесь в роли аргументов выступают массивы Visual Basic, заданные в виде констант, заключенных в фигурные скобки. Фактическое значение параметра M в этом случае представляет массив из двух элементов, каждый из которых в свою очередь является массивом.
=IsMedianaForAll(7; {4;7;2}; {9;12;5}; M). Ситуация в этом вызове сложнее, так как число аргументов возросло, но, что более важно, среди них есть как массивы Visual Basic, так и массив рабочего листа - вектор M. Тем не менее, все работает правильно.

Содержание раздела