Использование функции ВПР(и ДВССЫЛ) в макросе

Автор Tigrik, 16 марта 2022, 01:58

0 Пользователи и 1 гость просматривают эту тему.

Tigrik

Здравствуйте!

Всё, когда-то, начинается впервые.
Решил продолжить свою задачу опираясь на "могучие плечи" макрос Basic (не все же время Libre Calc трудиться).
Тут то я и попал в "засаду". ВПЕРВЫЕ столкнулся с использованием в макросах стандартных функций Calc - на данном этапе, меня интересуют ВПР("VLOOKUP") и ДВССЫЛ("INDIRECT").
Три дня рыскал по Форуму и в Интернете, но "не выходит каменный цветок" - сегодня "сдался" и решил обратиться к помощи уважаемых форумчан.

Во вложение файл, в котором 3 листа.
На листе "Словарь" - две таблицы. Первая с поддиапазонами для второй таблицы.
Лист "Dict" дублирует предыдущий лист - для проверки - думал, что английское наименование поможет.
Основной лист - "Table", на котором находится значение для ВПР (в ячейке "A1"). Там же дубликат первой таблицы из листа "Словарь" (для проверки одного из вариантов в макросе).

Вот код макроса:

Sub macroFuncAcc
  Dim oSheet
  Dim oFuncAcc
  Dim ChoiceRange
  Dim oRangeFirst
  Dim oRangeSecond
  Dim oAdress
  Dim nameSheet As String, s As String
 
If Not ThisComponent.CurrentSelection.supportsService("com.sun.star.sheet.SheetCell") Then Exit Sub
nameSheet = "Словарь" '   Пробовал и английское наименование nameSheet = "Dict"
oSheet = ThisComponent.CurrentSelection.SpreadSheet
ChoiceRange = oSheet.getCellRangeByName("A1").String
oFuncAcc = CreateUNOService("com.sun.star.sheet.FunctionAccess")

'======== По массиву данных замечательно работает! =========
  Dim myData(1 to 3, 1 to 2) as Variant
myData(1, 1) = "а" : myData(1, 2) = "$Словарь.$A$1:$Словарь.$A$4"
myData(2, 1) = "б" : myData(2, 2) = "$Словарь.$A$1:$Словарь.$A$4"
myData(3, 1) = "в" : myData(3, 2) = "$Словарь.$A$1:$Словарь.$A$4"
oRangeSecond = oFuncAcc.callFunction("VLOOKUP", Array(ChoiceRange, myData, 2, True))
'=================

'======== По диапазону - не работает!??? =========
oRangeFirst = ThisComponent.Sheets.GetByName(nameSheet).getCellRangeByName("D1:E3")
'=== Попытка использовать через структуру «CellRangeAddress»
' oRangeFirst = ThisComponent.Sheets.GetByName(nameSheet).getCellRangeByName("D1:E3").RangeAddress
'=== Вариант, когда диапазон на активном листе ("Table") - тогда работает!?
' oRangeFirst = oSheet.getCellRangeByName("D1:E3")

oRangeSecond = oFuncAcc.callFunction("VLOOKUP", Array(ChoiceRange, oRangeFirst, 2, True))

'=== По прямым ссылкам, также, не работает
' oRangeSecond = oFuncAcc.callFunction("VLOOKUP", Array(ChoiceRange, "$Словарь.$D$1:$Словарь.$E$3", 2, True))
' oRangeSecond = oFuncAcc.callFunction("VLOOKUP", Array(ChoiceRange, "$Словарь.$D$1:$E$3", 2, True))

'=====================================================
'=== Попытка использовать функцию ДВССЫЛ - тоже не работает
' s = "$" & nameSheet & ".$D$1:$" & nameSheet & ".$E$3"
' oRangeFirst = oFuncAcc.callFunction("INDIRECT", s)
'===

End Sub


В документации по интерфейсу XFunctionAccess, в который входит сервис FunctionAccess нашёл:
"Each element must be of one of the following types:
...
com::sun::star::table::XCellRange
for a SheetCellRange object that contains the data."
Вероятно, что этот тип аргумента, именно, для диапазонов. Но никак не могу этот аргумент создать для использование в функции ВПР.
В макросе есть несколько вариантов попыток (и через объект, и через структуру - были и другие варианты, но удалил; рабочий вариант - через массив с данными) решить этот вопрос, но не получилось.
Причём, если использовать ВПР для поиска в текущем листе, то всё прекрасно работает - значит я как-то не правильно задаю имя листа!? Смотрел по контрольным значениям - строки ссылок получается одинаковыми по своему синтаксису.
Что я не правильно делаю? Вероятнее всего, тип аргумента у меня не правильный?
А как сделать правильно???

---
Решил, в эту тему, добавить вопрос и про функцию ДВССЫЛ().
В макросе, конечно, вызов этой функции не верный, потому что я не знаю как правильно.
В этой функции первый аргумент - это, опять же, ссылка на ячейку, где находится строка диапазона (или ячейки).
Хотелось бы, ссылку на диапазон или ячейку "собирать" в макросе и использовать это в этих функциях, но, возможно, что так не получится. Более "длинный" вариант - "собрать" в макросе, положить в ячейку на листе и оттуда забрать для этих функции (или "собирать" уже на листе).

eeigor

#1
Цитата: Tigrik от 16 марта 2022, 01:58Причём, если использовать ВПР для поиска в текущем листе, то всё прекрасно работает - значит я как-то не правильно задаю имя листа!? Смотрел по контрольным значениям - строки ссылок получается одинаковыми по своему синтаксису.
Что я не правильно делаю? Вероятнее всего, тип аргумента у меня не правильный?
А как сделать правильно???
Попробуйте создать именованный диапазон и использовать это имя при ссылке на диапазон, расположенный на другом листе.
   oDataRange = ThisComponent.NamedRanges.getByName(DATA).ReferredCells
где DATA - это имя диапазона на другом листе.
Я не открывал Ваш пример и не тестировал.

Цитата: Tigrik от 16 марта 2022, 01:58Решил, в эту тему, добавить вопрос и про функцию ДВССЫЛ().
В макросе, конечно, вызов этой функции не верный, потому что я не знаю как правильно.
Function Indirect$(Reference$, Optional A1)
''' Returns a reference to a cell from a defined range.
''' Arguments:
'''     Reference (required):
'''         The cell whose contents are to be evaluated is to be referenced
'''         in text form (e.g. "A1").
'''     A1 (optional):
'''         he reference style: 0 or FALSE means R1C1 style,
'''         any other value or omitted means A1 style.

   On Local Error GoTo Failed
   If IsMissing(A1) Then A1 = 1  'A1 notation is used
   With CreateUnoService("com.sun.star.sheet.FunctionAccess")
       Indirect = .callFunction("INDIRECT", Array(Reference, A1))
   End With
   Exit Function
Failed:
   Msgbox "Error " & Err & " at line " & Erl & ": " & Error _
    , MB_ICONSTOP, "macro:Indirect()"
End Function


Я только написал функцию Indirect(), и тоже не протестировал её. Чуть позже... Собственно, что там писать? Больше описывать.
Но у меня пока не получается вызвать функцию Indirect(). Разбираюсь...
Edit:
Это не имеет смысла. Пояснение ниже (ответ #3).

Calc-функцию Vlookup() лучше создать по аналогии с функцией Indirect (см. выше) и поместить её в модуль CalcFunctions.
Ну, я так делаю...
Описания функций прилагаю (см. прикреплённый файл).

Edit:
Function VLookup(SearchCriterion, Data, Index, Optional SortedRangeLookup)
''' Vertical search and reference to indicated cells.
''' Arguments:
'''     SearchCriterion (required):
'''         The value to be found in the first column.
'''     Data (required) instead of the name "Array"*:
'''         The array or range for referencing.
'''         * According to the function signature in the documentation,
'''         the Data argument is called "Array". This name leads to an error.
'''     Index (required):
'''         Column index number in the array.
'''     SortedRangeLookup (optional, default is TRUE):
'''         If the value is TRUE or not given,
'''         the search column of the array represents a series of ranges,
'''         and must be sorted in ascending order.

   On Local Error GoTo Failed
   If IsMissing(SortedRangeLookup) Then SortedRangeLookup = True
   With CreateUnoService("com.sun.star.sheet.FunctionAccess")
       VLookup = .callFunction("VLOOKUP", Array(SearchCriterion, Data, Index, SortedRangeLookup))
   End With
   Exit Function
Failed:
   Msgbox "Error " & Err & " at line " & Erl & ": " & Error _
    , MB_ICONSTOP, "macro:VLookup()"
End Function


Можно искать свою строку в своём массиве массивов, а можно осуществлять поиск в данных на листе, передавая в качестве параметра Data как диапазон ячеек, так и свойство диапазона DataArray (пример приведён у A.Pitonyak, см. скриншот).

Sub Test_VLookup()
   Dim aData()
   aData = Array(Array("?", "1", "2"), Array("X", "21", "22"))
   Print CalcFunctions.VLookup("?", aData(), 2, 0)
   Print CalcFunctions.VLookup("X", aData(), 2, 0)

   ' You can also pass a cell range, or the data array from a cell range.
   Dim oSheet As Object, oRange As Object
   oSheet = ThisComponent.Sheets.getByName("Sheet1")
   oRange = oSheet.getCellRangeByPosition(0, 2, 2, 7)
   Print CalcFunctions.VLookup("y", oRange, 2, 0)
   Print CalcFunctions.VLookup("z", oRange.DataArray, 2, 0)
End Sub

Тест вернёт по порядку: 1, 21 и для примера на скриншоте - 25, 26.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

eeigor

#2
Моё предположение: функцию INDIRECT нельзя вызвать через сервис com.sun.star.sheet.FunctionAccess.
Правда, я не знаю, зачем это нужно. Ведь можно напрямую обратиться к ячейке, на которую производится косвенная (indirect) адресация, и получить готовое значение. В свойстве Formula будет формула, а в свойстве String - готовое значение или имя диапазона, тогда этот диапазон можно инициализировать в коде.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

mikekaganski

Когда Вы вызываете Вашу Vlookup, Вы передаёте в неё все данные - включая Data. Функция VLOOKUP сервиса com.sun.star.sheet.FunctionAccess не оперирует никакими данными никакого документа.

В любом случае, создавая сервис "com.sun.star.sheet.FunctionAccess", Вы не привязываетесь ни к какому документу. Так с чем должны работать функции типа INDIRECT, которые, принимая от Вас некие ссылки, должны отдать значение, найденное по адресу в документе?
С уважением,
Михаил Каганский

eeigor

Михаил, добрый день! Значит, нельзя, как я и предположил. Но я и не стал разбираться, зачем автору это нужно. Сделал пояснение выше.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

sokol92

Цитата: mikekaganski от 16 марта 2022, 10:57Так с чем должны работать функции типа INDIRECT, которые, принимая от Вас некие ссылки, должны отдать значение, найденное по адресу в документе?
Интересный вопрос.
При задании некорректного адреса ячейки функция "Indirect" (вызванная через сервис FunctionAccess) вызывает ошибочную ситуацию.
При задании корректного адреса ячейки, кроме "A1", возвращает 0. Для A1 почему-то возникает ошибка.
Текст не смотрел.  :)
Владимир.

mikekaganski

Цитата: sokol92 от 16 марта 2022, 14:09Для A1 почему-то возникает ошибка
... циклической ссылки?

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

eeigor

#7
Цитата: sokol92 от 16 марта 2022, 14:09При задании корректного адреса ячейки, кроме "A1", возвращает 0. Для A1 почему-то возникает ошибка.
Если дополнить ссылку именем листа, то тоже вернёт 0. Но это ни о чём... Когда Михаил ответил, я как раз мысленно и безуспешно пытался привязать вызов функции к документу, но Михаил прервал мои размышления... :)

Edit:
Функция VLookup() в коде может быть весьма полезной.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

sokol92

Цитата: mikekaganski от 16 марта 2022, 14:12для работы сервиса используется внутренний временный документ
Михаил, спасибо за очередную ценную информацию.
Кстати, в Excel объект Excel.WorksheetFunction не имеет метода Indirect и ему подобных (по указанным выше причинам).
Владимир.

mikekaganski

#9
Цитата: sokol92 от 16 марта 2022, 15:58
Кстати, в Excel объект Excel.WorksheetFunction не имеет метода Indirect и ему подобных (по указанным выше причинам).

Но кажется, что было бы полезно использовать полное имя в INDIRECT, включая имя документа, и тогда это получает смысл?

Кстати, возможно будет интересно узнать, что некоторые функции Basic реализованы через вызов XFunctionAccess::callFunction.
С уважением,
Михаил Каганский

eeigor

Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

sokol92

Цитата: mikekaganski от 16 марта 2022, 16:05Но кажется, что было бы полезно использовать полное имя в INDIRECT, включая имя документа, и тогда это получает смысл?
Да, наверное.
Попробовал. Такая формула работает:
=INDIRECT("'file:///C:/Temp/test3.ods'#$Sheet1.B1")

Через сервис FunctionAccess - нет.
Владимир.

eeigor

Цитата: eeigor от 16 марта 2022, 10:28Правда, я не знаю, зачем это нужно. Ведь можно напрямую обратиться к ячейке, на которую производится косвенная (indirect) адресация, и получить готовое значение. В свойстве Formula будет формула, а в свойстве String - готовое значение или имя диапазона, тогда этот диапазон можно инициализировать в коде.
Цитата: sokol92 от 16 марта 2022, 16:39Через сервис FunctionAccess - нет.
Владимир, Вы можете привести пример когда это нужно?
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

sokol92

Цитата: eeigor от 16 марта 2022, 16:49Владимир, Вы можете привести пример когда это нужно?
Игорь, а как получить в коде Basic значение ячейки?
'file:///C:/Temp/test3.ods'#$Sheet1.B1
Корректировка документа имеет побочные эффекты, например, может измениться свойсво isModified. В Excel для подобных целей используется Application.Evaluate.
Владимир.

eeigor

#14
Очень просто: getCellRangeByName().Formula  'Value. & .String
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community