"Пользовательские" и "обычные" функции VBA
Под пользовательской функцией VBA я понимаю функцию, которая может быть вызвана в формулах рабочего листа Excel. Обычные функции VBA могут вызываться в функциях и процедурах VBA. Возникает естественный вопрос, может ли одна и та же функция одновременно быть пользовательской и обычной? Этот же вопрос может быть сформулирован и по-другому, есть ли особая специфика в пользовательских функциях? Ответ прост - особой специфики нет, и одна и та же функция может вызываться как в формулах, так и в процедурах VBA. Практически не возникает проблем, когда аргументами функции и результатом являются скалярные значения. Когда же, как в случае с MultMatr, аргументами и результатом являются массивы, то возникают определенные трудности. Эти трудности преодолимы, примером тому служит функция MultMatr. Попробуем разобраться, в чем состоят эти трудности. Когда функции нужно предать массив, то в пользовательских функциях при вызове им передаются объекты Range, обычным функциям - пер еменные, описанные, как массивы VBA. Поэтому для обеспечения универсального характера функции в ее теле необходимо производить разбор случаев, определяя тип параметра. В результате растет объем функции, а, следовательно, усложняется ее понимание. Еще одна сложность связана с результатом вычислений. Никаких проблем нет для формулы над массивами, вызывающей пользовательскую функцию, - результат, записывается в область, выделенную при вызове формулы. Обычные функции VBA, как правило, не возвращают массив в качестве результата. Если результатом работы является массив, то при программировании на VBA создается процедура, а не функция. Дело в том, что в VBA присваивания над массивами запрещены, потому просто невозможно присвоить массиву значение обычной функции, возвращающей массив в качестве своего результата. Как же, спросите Вы, MultMatr может использоваться в качестве обычной функции? Только за счет маленьких хитростей и универсального типа Variant, который может быть чем угодно, в том числе и массивом. При вызове MultMatr как обычной функции в процедуре VBA результат вызова присваивается переменной типа Variant, - это допустимо. Затем уже с этой переменной можно работать как с массивом, - это тоже допустимо, что я и продемонстрирую чуть позже. Таким образом, всегда можно написать функцию так, чтобы она служила и как пользовательская и как обычная функция. Другой вопрос, стоит ли это делать. В таком обобщении есть свой резон, поскольку в таких случаях при вызове пользовательской функции ей можно передавать в качестве аргументов не только объекты Range, но и массивы констант, что было продемонстрировано при рассмотрении функции IsMedianaForAll. Заметьте, однако, что в функцию MultMatr передать массивы констант невозможно. Причина этого в том, что для двумерных массивов констант функции UBound и LBound работают некорректно.
Подводя итог, замечу, что, когда приходится работать с массивами, разумнее иметь два варианта - пользовательскую и обычную функцию. Чтобы отчетливее продемонстрировать разницу между обычными и пользовательскими функциями, я написал обычную процедуру MultMatr1, выполняющую умножение матриц. Вот ее текст:
Public Sub MultMatr1(A() As Variant, B() As Variant, C() As Variant) 'Умножение матриц. 'Процедуру можно вызывать в обычных VBA функциях и процедурах, 'передавая ей в качестве параметров массивы VBA. Dim i As Integer, j As Integer, k As Integer Dim N As Integer, M As Integer, Q As Integer Dim P As Integer, NC As Integer, PC As Integer Dim msg1 As String, msg2 As String Dim Uncor1 As Boolean, Uncor2 As Boolean Dim Elem As Variant Uncor1 = True: Uncor2 = True msg1 = " При вызове MultMatr некорректно задана размерность" _ & " перемножаемых матриц!" & vbCrLf & _ "Число столбцов матрицы A = " & M & vbCrLf & _ "Число строк матрицы B = " & Q msg2 = " При вызове MultMatr некорректно задана размерность" _ & " матрицы результата!" & vbCrLf & _ "Число строк матрицы C = " & NC & vbCrLf & _ "Число столбцов матрицы C = " & PC
'Проверка корректности задания размерности N = UBound(A, 1): M = UBound(A, 2) Q = UBound(B, 1): P = UBound(B, 2) NC = UBound(C, 1): PC = UBound(C, 2) If (Q = M) Then ' Размерность исходных матриц задана корректно Uncor1 = False If NC = N And PC = P Then 'Размерность результата задана корректно Uncor2 = False 'Построение произведения матриц AB =A*B For i = 1 To N For j = 1 To P Elem = 0 For k = 1 To M Elem = Elem + A(i, k) * B(k, j) Next k C(i, j) = Elem Next j Next i Else 'некорректно задана размерность If Uncor1 Then MsgBox (msg1) If Uncor2 Then MsgBox (msg2) End If End If End Sub
От функции MultMatr она отличается тем, что в ней опущен разбор случаев и проводится более тщательная проверка корректности размерностей аргументов. Конечно, она ни в коем случае не может быть использована как пользовательская функция, но зато работать с ней в процедурах и функциях VBA с ней не то чтобы проще, но естественнее. Чтобы почувствовать разницу, я продемонстрирую тестовую процедуру, в которой вызываются, как функция MultMatr так и процедура MultMatr1.
Public Sub MultTest() Dim A(1 To 2, 1 To 2) As Variant Dim B(1 To 2, 1 To 2) As Variant Dim C(1 To 2, 1 To 2) As Variant Dim C1 As Variant Dim item As Variant Dim i As Integer, j As Integer A(1, 1) = 1: A(1, 2) = 2: A(2, 1) = 3: A(2, 2) = 4 B(1, 1) = 1: B(1, 2) = 2: B(2, 1) = 3: B(2, 2) = 4 'Переменной типа Variant присваивается массив C1 = MultMatr(A, B) For i = 1 To UBound(C1, 1) For j = 1 To UBound(C1, 2) Debug.Print C1(i, j) Next j Next i 'Здесь С - массив и работаем с ним, как с массивом. Call MultMatr1(A, B, C) For i = 1 To UBound(C, 1) For j = 1 To UBound(C1, 2) Debug.Print C(i, j) Next j Next i 'Вызов тестовой функции, возвращающей массив. C1 = ResArray(A) For Each item In C1 Debug.Print item Next item End Sub
Public Function ResArray(A() As Variant) As Variant 'Возвращает в качестве результата, 'переданный ей массив ResArray = A End Function
Как видите, функция MultMatr, успешно работающая в роли пользовательской функции, с тем же успехом может выполнять и роль обычной функции. Так что я выполнил поставленную задачу, создав "универсальную" функцию. Но, возможно, предпочтительнее в процедурах VBA работать с MultMatr1, не прибегая к переменным типа Variant. Обратите внимание на небольшую тестовую функцию ResArray, которую я написал, чтобы в явной форме продемонстрировать способ возвращения массива в функциях VBA.
