[Решено] UDF: Как сослаться на диапазон ячеек?

[eeigor]

User defined (custom) function
Надо работать с содержимым диапазона ячеек (с частью текста внутри ячейки), используя пользовательскую функцию на листе.
Как получить доступ к диапазону?
Судя по этой ссылке, никак, если только не использовать опцию совместимости с VBA.

Upd1: Передать ссылку в виде строки (absolute name)? Выбрать мышью, заключить в кавычки. Выполнить разбор строки?.. Статичный вариант, и если вставить столбец/строку, то всё поплывёт...

Или так:
=UDF(COLUMN(A6);ROW(A6);COLUMN(C9);ROW(C9))

[sokol92]

если только не использовать опцию совместимости с VBA

А что, эта опция стала платной?  

В стандартную библиотеку:

Код Выделить Развернуть

Option VbaSupport 1
Function Udf(arg) As Double
 Msgbox "Адрес параметра функции UDF: " & arg.Address
End Sub


В ячейку:

=UDF(A8:B16)

[eeigor]

Сначала не хотел ради "чистоты" кода... Пока не использовал опции совместимости с VBA.
sokol92, насколько всё это стабильно работает: в смысле, не добавляет ли установка флажка каких-л. ошибок сама по себе?
И что, я должен работать (ваш пример) с моделью Excel? Или можно комбинировать код?

Upd1:
Можно, правда, "условиться" передавать левый верхний угол непрерывного диапазона, использовав минимум ссылок ( COLUMN(A2), ROW(), ... ), и ограничиться использованием листа, на котором помещена формула*, а в коде вычислить размер CurrentRegion и с работать с ним. Допустимо, как считаете? До тех пор, пока не допилят... Или переходить на модель Excel и синтаксис VBA?

* С иным листом ( SHEET(), COLUMN(A2), ROW(), ... ) могут быть тоже неведомые мне заморочки...

Код Выделить Развернуть

Function getSheet(byVal vSheet)
REM Helper for sheet functions. Get cell from sheet's name or position; cell's row-position; cell's col-position
on error goto exitErr
  select case varType(vSheet)
  case is = 8
     if thisComponent.sheets.hasbyName(vSheet) then
        getSheet = thisComponent.sheets.getByName(vSheet)
     else
        getSheet = NULL
     endif
  case 2 to 5
     vSheet = cInt(vSheet)
     'Wow! Calc has sheets with no name at index < 0,
     ' so NOT isNull(oSheet), if vSheet <= lbound(sheets) = CRASH!
     'http://www.openoffice.org/issues/show_bug.cgi?id=58796
     if(vSheet <= thisComponent.getSheets.getCount)AND(vSheet > 0) then
        getSheet = thisComponent.sheets.getByIndex(vSheet -1)
     else
        getSheet = NULL
     endif
  end select
exit function
exitErr:
getSheet = NULL
End Function
Примечание. Код выше достаточно древний.

[sokol92]

насколько всё это стабильно работает

Я использую опцию совместимости с Excel только в тех случаях, когда нет соответствующих "родных" конструкций (как, например, для задачи из данной темы форума). Еще пример - функция InStrRev.

Конструкция из #1 представляется стабильной. В этом случае в качестве параметра UDF-функции формируется объект с Implementation Name = SvVbaRange, поддерживающий сервис ooo.vba.excel.Range. Этот объект реализует значительное количество свойств и методов объекта Range Excel - насколько качественно и эффективно я не проверял, поскольку, как уже упомянуто, предпочитаю "родные" методы.

Кроме адреса диапазона ячеек можно еще получить, например,  имя листа:

Код Выделить Развернуть

 Msgbox "Адрес параметра функции UDF: " & arg.Address & " лист " & arg.Worksheet.Name

[eeigor]

Возвращается объект SvVbaRange
Option VBASupport 1
'Option Compatible

Function UDF(oVbaRange As Range, ...)
    For Each oCell In oVbaRange.CellRange .queryContentCells(nCellFlags).Cells

Upd1:
Разбираюсь, не всё так однозначно. При помещении функции на лист начались проблемы. Отпишусь...
Upd2:
Теперь работает (выше). Особенность одна: свойство CellRange возвращает UNO-объект ScCellRange даже для одной ячейки. И дальше весь код пошёл "нативный", то есть родной. Таким образом, степень вмешательства VBA кода минимальна.

[economist]

Более полный набор флагов совместимости для UDF:

Код Выделить Развернуть


Option VBASupport 1
Option Compatible
'----------------------------------
Sub RunOnceVBASupport
ThisComponent.BasicLibraries.VBACompatibilityMode=true  
End sub


[eeigor]

@sokol92, @economist, спасибо за помощь.

[eeigor]

Отписываюсь, как обещал.
Работа пользовательской функции со ссылкой на диапазон Excel в режиме совместимости нареканий не вызывает. Ни одной ошибки. Как родной!
Как, вообще, без этого можно?..

[economist]

Вчера буквально провел анализ последствий личного отказа в LO от VBA в пользу "чистоты" API. Проекты удлинились по срокам реализации в 2 раза, код - увеличился в 3-4 раза. Это слишком высокая цена, и граничит с садомазо.

Принял решение в каждом крупном проекте собирать VBA код в один "жертвенный" модуль с опциями совместимости, обвесить его тестами при запуске и мысленно готовиться к его отваливанию из-за регрессии, что уже наблюдал за 7 лет дважды, версии не записал, но по моему они все были релизы с нулем после первой точки. Причем проблемы были даже с portable LO (тот выходит всегда позже на месяцы и вроде д.б. "вылизан").

Так что можно, но нельзя. Слишком многое теряется. Хотя я не показателен, много кодю в Excel, на чистом VBA.