Страницы: 1
RSS
Формулы массива некорректно вызывают пользовательскую функцию VBA
 
Добрый день.

Столкнулся с проблемой.
Для мощного поиска использую регулярные выражения. Вызываю их функции через VBA, написав свою "обёртку".
Все работает.

Но я очень много и гибко использую формулы массива при написании формул. И они оказываются не совместимы с пользовательской функцией на VBA.
Когда я вставляю в формулу массива свою функцию (вызова поиска через библиотеку регулярных выражений) - формула массива работает не корректно.

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

Очень упрощенный пример.
Ячейки A1:С3
-1            =СУММ(ABS(A1:A2))                     3

-2            =СУММ(MyAbs1(A1:A2))            #ЗНАЧ!

              =СУММ(MyAbs2(A1:A2))            1

В столбце B все формулы массива (в фигурных скобках).
В столбце C вставлены результаты выполнения формул из столбца 2.

Содержимое Module1 в VBA:
Код
 Function MyAbs1(i As Integer)
  MyAbs1 = Abs(i)
 End Function
 Function MyAbs2(i As Range)
  MyAbs2 = Abs(i.Cells(1).Value)
 End Function


Что мы видим:
В ячейке B2 функция MyAbs1 вообще вызываться не хочет.
А в ячейке B3 функция MyAbs2 вызывается один раз с диапазоном A1:А2 (и выдает неправильный рельтат "1" вместо "3"), вместо двухкратного вызова (сначала с A1, затем с A2 и их суммированием).
В ячейке B1 со стандартной (не пользовательской) функцией - все работает корректно.

Как написать формулу массива с моей пользовательской функцией чтобы она вызывалась корректно и выдавала правильный результат?

Кто то может чем то помочь или подсказать?
Заранее благодарен.
 
Почему вместо формул не писать функции, вызывающие основную функцию?
Пояснять надо с наличием небольшого примера (см. правила форума). С ним не только понятнее, но и решение может прилететь быстрее
 
Цитата
vikttur написал:
но и решение может прилететь быстрее
чем замечание :-)

Алексей К., на листе листе вы можете передать значение , массив или диапазон, который для обработки все равно является массивом, да и единственное значение  - это частный случай массива. Следовательно просто предусматриваете обработку массива в цикле (циклах). В зависимости от того что внутри функции вернуть вы можете значение или массив.
По вопросам из тем форума, личку не читаю.
 
Цитата
Алексей К. написал:
я вставляю в формулу массива свою функцию
Цитата
Алексей К. написал:
Никаким ухищрениями мне не удалось заставить формулу массива вызывать мою функцию для каждой ячейки диапазона
это Ваша функция и Вы должны предусмотреть возможность работы внутри формул массива. Какие ухищрения Вы пробовали? Вот для примера:
Код
'функция перемножает значения
'в случае с вводом как формулы массива - для каждой строки будет свое произведение
Function MyMultiple(arg1 As Range, arg2 As Range)
    Dim rr As Range, lr As Long
    Dim res
    
    Debug.Print Application.Caller.Address
    'если только одна ячейка в качестве аргумента - просто делаем нужное действие
    If arg1.Rows.Count = 1 Then
        res = arg1.Cells(1).Value * arg2.Cells(1).Value
    Else
        'мы передали не одну ячейку, а несколько
        'значит нужно делать цикл
        'а в качестве результата еще и массив создавать
        ReDim res(1 To arg1.Rows.Count, 1 To 1)
        For lr = 1 To arg1.Rows.Count
            res(lr, 1) = arg1.Cells(lr, 1).Value * arg2.Cells(lr, 1).Value
        Next
    End If
    MyMultiple = res
End Function
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Вот пример в работе можете глянуть.
 
Дмитрий уже ответил на этот вопрос в #4. Еще один пример для Вашей функции (попробуйте):
Код
Option Explicit
' Вычисляет абсолютную величину всех элементов аргумента (диапазон, массив, число)
Function MyAbs1(arg)
    Dim v, i As Long, j As Long, j2 As Long
    If IsObject(arg) Then
        v = arg.Value
    Else
        v = arg
    End If
  
    Select Case Ndims(v)
        Case 0
            v = Abs(v)
      
        Case 1
            For i = 1 To UBound(v, 1)
                v(i) = Abs(v(i))
            Next i
            
        Case 2
            For i = 1 To UBound(v, 1)
                For j = 1 To UBound(v, 2)
                    v(i, j) = Abs(v(i, j))
                Next j
            Next i
    End Select
  
    MyAbs1 = v
End Function

' Возвращает число измерения массива (не более 9). Если 0, то не массив
Function Ndims(arr) As Long
  Dim i As Long, j As Long
  On Error GoTo Out
  Ndims = 0
  For i = 1 To 9
    j = UBound(arr, i)
    Ndims = Ndims + 1
  Next i
Out:
End Function
Владимир
 

Долго не мог понять того, что мне здесь все предлагают.
Но понял. И главное - понял, почему я так долго не мог Вас понять (сорри за тавтологию).
Вы просто уходите от формул массива!
Переписав свой MyAbs2 чтобы он возвращал диапазон (что я считал неверным!) - действительно формула в ячейке B3 стала возвращать верный результат "3".
Но!
Интересно, что такой результат она возвращает как при вводе формулы в ячейку с CTRL+SHIFT+ENTER, так и без (т.е. как в виде формулы массива, так и в виде обычной формулы)!
Причем если в ячейку B1 ввести формулу не как формулу массива - она даст другой результат ("1").
Так что я настаиваю, что Вы просто перестали использовать формулу массива.
(Все вышеописанные действия надо совершать со старым файлом FA+UDF.xlsm - берите его в сообщении выше)

И готов доказать (что Вы перестали использовать формулу массива).
Понятно, что мне нужна формула массива не для простого суммирования. Там еще должно быть несколько вложенных условий если.
Кстати именно поэтому мне и нужна формула массива - нигде иначе не реализовать так просто и изящно эти условия!
И не надо плиз советовать все это писать на VBA!)
Итак, чуть усложняем пример - впереди добавляем столбец по которому будет условие "ЕСЛИ" для формулы массива:


-1            -1            =СУММ(ЕСЛИ(A1:A2>0;ABS(B1:B2)))                    2

1             -2            =СУММ(ЕСЛИ(A1:A2>0;MyAbs1(B1:B2)))            #ЗНАЧ!

                              =СУММ(ЕСЛИ(A1:A2>0;MyAbs2(B1:B2)))            3

И приложу на всякий случай текст функций в самом простом виде (даже без цикла - строго на 2 значения):
Function MyAbs1(i As Integer)
  MyAbs1 = Abs(i)
End Function
Function MyAbs2(i As Range)
  Dim v(2) As Long
  MsgBox ("Âûçîâ")
  v(1) = Abs(i.Cells(1).Value)
  v(2) = Abs(i.Cells(2).Value)
  MyAbs2 = v
End Function

И что Вы мне можете предложить в этот раз?)
Как получить в C3 правильный результат "2"?
Такой же как и в C1!

Видимо верно утверждение, что:
В Excel формулы массива некорректно работают с пользовательскими функциями?

Буду благодарен за ответы.
Спасибо!

Изменено: Алексей К. - 16.10.2020 09:10:55
 
Цитата
Алексей К. написал:
я настаиваю, что Вы просто перестали использовать формулу массива
Вы, извините, часом не охренели с такими выводами? Не можете разобраться - не надо гнать волну на нас, мы Вам все разжевали давно. Где у Вас обработка того, одно значение передается в функцию или массив? Я это показывал на примере(пока Вашего не было). У Вас тупо две функции - одна работает исключительно как формула массива (MyAbs2), а другая исключительно с одним значением MyAbs1. Так чего Вы ожидаете тогда?
Цитата
Алексей К. написал:
И что Вы мне можете предложить в этот раз?) Как получить в C3 правильный результат "2"?
ответ: следовать советам и правильно записать функцию в коде:
Код
Function MyAbs2(i As Range)
  Dim v, res
  If i.Count = 1 Then
    res = Abs(i)
  Else
    ReDim v(1 To 2, 1 To 1)
    v(1, 1) = Abs(i.Cells(1, 1).Value)
    v(2, 1) = Abs(i.Cells(2, 1).Value)
    res = v
  End If
  MyAbs2 = v
End Function
Цитата
Алексей К. написал:
Видимо верно утверждение
нет. Верно утверждение: три раза отмерь, один отрежь. Переведя на тему: сначала разберитесь до конца в теме, потом волну гоните.
Изменено: Дмитрий(The_Prist) Щербаков - 16.10.2020 10:50:47
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
off
Дмитрий(The_Prist) Щербаков, Дима, спокойнее.  :D

Скрытый текст


Дмитрий, Если для примера только то вопросов нет, если для общего случая то ограничивать аргументы только диапазоном наверно не супер. на мой взляд Varianт a в теле
сразу IsArray(i) и тут или сразу присвоить другому массиву и с ним работать или после  - вопрос творческий.
Изменено: БМВ - 16.10.2020 10:02:02
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
Дима, спокойнее
да я спокоен. Мне-то вообще все равно. Но примеров вроде накидали, как сделать подсказали, а тут "Вы формулу массива не используете...". Абыдна, да?  :D
Цитата
БМВ написал:
если для общего случая
специально усложнять не стал и сделал именно под пример ТС. Чтобы он на своем файле понял принцип и уже ПОСЛЕ ЭТОГО двигался дальше, в сторону определения типов и т.п. В своих функциях я делаю все максимально универсально в зависимости от передаваемых параметров. Но ТС-у надо сначала хоть сам изначальный принцип понять. А то он явно совершенно не понял, как именно и в каких случаях надо делать.

P.S. чуть подправил предыдущее сообщение - добавил "извините" :) Так должно быть лучше :D  
Изменено: Дмитрий(The_Prist) Щербаков - 16.10.2020 10:51:27
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Алексей К., зачем Вы повторяете то, что в первом сообщении?
Код в сообщении нужно оформлять с помощью кнопки <...>
 
ну не сильно сложнее
Код
Function MyAbs3(i As Variant)
  Dim v, res
  If IsArray(i) Then
    s = i
    ReDim v(1 To UBound(s), 1 To UBound(s, 2))
    For j = 1 To UBound(s)
        For k = 1 To UBound(s, 2)
            v(j, k) = Abs(s(j, k))
        Next
    Next
    MyAbs3 = v
  Else
    MyAbs3 = Abs(i)
  End If
End Function
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
не сильно сложнее
это как посмотреть. Все зависит от опыта ТС. Ведь показали уже выше несколько примеров - он не смог адаптировать. Значит надо показать самое-самое примитивное под его конкретный пример. Чтобы сам принцип был понятен и усвоен. Я так думаю. Но для расширения кругозора все варианты хороши - на одном потренируется, над другим подумает - глядишь и уляжется информация правильно.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Дмитрий(The_Prist) Щербаков,  Я не критиковал естественно. просто для меня (в подавляющем большинстве) аргументом для массивной функции является результат предыдущих вычислений, а это массив. Ну а если нужно обрабатывать не значения ячейки, а её свойства допустимые для функции вызванной с листа, то  конечно тут конечно работа с диапазоном.

P.S, подправь в #8 . там результат в воздухе повиснет, при единственной ячейке. Опечатался.
По вопросам из тем форума, личку не читаю.
 
Добрый день, коллеги! А чем не подошел пример из #6? Одномерные массивы тоже нужно обрабатывать:
Код
=СУММ(MyAbs1({1;2;3}))
Владимир
 
Всем спасибо огромное!
Я разобрался, но очень разочаровался в эффективности формул массива в Excel.
Постараюсь пояснить почему.

Пример:
формула массива с 5 вложенными условиями и крайне ресурсоемкой функцией в самом внутри, типа:
Код
=сумм(если(Условие1;если(Условие2;если(Условие3;если(Условие4;если(Условие5;РесурсоемкаяФункция(параметры);););););))

Допустим есть 1000 строк на входе, но все 5 условий проходят только 10 из них.
Я рассчитывал, что в итоге Excel вызовет ресурсоемкую функцию только 10 раз и каждый раз с одной ячейкой диапазона (прошедших все фильтры).
А получается что Excel вызовет мою ресурсоемкую функцию 1 раз, но с 1000 ячеек диапазона, а значит вся формула будет вычислять в 100 раз медленнее, чем я ожидал.

Видимо действительно остается только писать на VBA аналог всей формулы (с условиями) и вызывать ресурсоемкую функцию 10 раз с одним значением на входе.
Я прав?

Спасибо.
Изменено: Алексей К. - 16.10.2020 16:47:23
 
Цитата
Алексей К. написал:
Я прав?
и да и нет, иногда достаточно переписать ресурсоемкую функцию так, чтоб она стала не ресурсоемкой, иногда даже в ущерб её длине.

Цитата
sokol92 написал:
Одномерные массивы тоже нужно обрабатывать:
ну Владимир Вы там на 9е измерение замахнулись :-) Так это, с листа не больше 2х
Изменено: БМВ - 16.10.2020 17:14:48
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
Вы там на 9е измерение замахнулись
Так то ж универсальное оружие, из закромов... :D  
Владимир
 
Цитата
sokol92 написал:
из закромов.
https://www.planetaexcel.ru/forum/?FID=8&PAGE_NAME=read&TID=25891
По вопросам из тем форума, личку не читаю.
 
Мои закрома года примерно 1998... :)

Естественно, ни на какие авторские права (и даже отчисления) не претендую...
Изменено: sokol92 - 16.10.2020 21:54:55
Владимир
Страницы: 1
Наверх