Страницы: 1
RSS
Подавление ошибки в массивной UDF
 
Добрый всем вечер.
В этой теме соорудил UDF, которую нужно вводить как формулу массива. Соответственно размер диапазона, куда она вводится, заранее не известен и, при включении в него "лишних" ячеек в них появляется #Н/Д. Как с этим бороться? Штатные ЕСЛИОШИБКА(), связка ЕСЛИ() и всяких ЕНД() не работают. Условное форматирование НЕ предлагать.
Спасибо
Код
Function UNIQUE(rng As Range) As Variant
Dim tmpArray()
Dim tmpCol As New Collection
On Error Resume Next
tmpArray = rng.Value
For I = LBound(tmpArray, 1) To UBound(tmpArray, 1)
    For J = 1 To rng.Columns.Count
        tmpCol.Add tmpArray(I, J), CStr(tmpArray(I, J))
    Next
Next
ReDim tmpArray(tmpCol.Count - 1, 1)
For I = 0 To tmpCol.Count - 1
    tmpArray(I, 0) = tmpCol(I + 1)
Next
UNIQUE = tmpArray
End Function
Согласие есть продукт при полном непротивлении сторон
 
Sanja, раз уж Вы в VBA. то определитесь какого размера ответ получили и сколько ячеек нужно, что принять этот ответ
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Цитата
Sanja написал:
размер диапазона, куда она вводится, заранее не известен
Application.Caller. Пример: http://www.planetaexcel.ru/forum/?PAGE_NAME=read&FID=8&TID=37461
 
Sanja, может вот это поможет:
 
Цитата
Казанский написал: Application.Caller
Казанский, спасибо большое, разобрался. Раньше, конечно, в чужих кодах встречал подобные строки, но до сегодняшнего дня судьба хранила от использования этого Caller'а. Вот и мое время, видимо, пришло :D.
Однако, все замечательно, если результатом работы функции является массив ТЕКСТОВЫХ значений. В лишние строки просто вставляется "пусто", а вот если нужно вывести массив ЧИСЕЛ, то, соответственно, вместо пусто появляются "0". Вот если-бы эти пусто и нули заменить на EMPTY...Хотя это в любом случае лучше чем #Н/Д. Если отключить показ нулей в настройках, то и не придерешься. Спасибо еще раз.

С.М., спасибо и за Ваш вариант. В принципе, при обратном транспонировании, получим желаемый столбец. Спасибо.

Отдельное спасибо Ігор Гончаренко, открыл глаза неразумному

Варианты UDF в файле.
Изменено: Sanja - 08.10.2016 17:03:09
Согласие есть продукт при полном непротивлении сторон
 
Цитата
Sanja написал:
С.М. , спасибо и за Ваш вариант. В принципе, при обратном транспонировании, получим желаемый столбец.
Вообще-то транспонированный массив я просто
забыл удалить  :(  (тестировал), то что я хотел
показать - в Module2.
Application.Caller вернёт столько .Rows.Count, на сколько Вы выдели диапазон на листе
и нажали Ctrl+Shift+Enter, т.е. Вы всё равно заранее не знаете сколько уникальных
насчитает ваша функция и придётся протягивать её с запасом, который Вы априори не знаете.
Оптимально (максимум) - посчитать глазами  :)  число ячеек в исходном диапазоне.
Если строку
ReDim unArray(0 To Application.Caller.Rows.Count, 0 To 0)
заменить на
ReDim unArray(0 To rng.Cells.Count - 1, 0 To 0) ,
то Ваша функция вернёт этот максимально-возможный массив.
(Тогда мой(и) макрос(ы) выделит автоматом этот максимальный диапазон и заполнит его
формулами автоматически).
Страницы: 1
Наверх