Переписать код VBA под Libreoffice Basic по поиску в массиве

Автор eugenefoxx, 30 сентября 2018, 10:27

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

eugenefoxx

economist - такого рода макрос [B1:B10000].FormulaLocal="=ВПР(ЧТО;ГДЕ;СТОЛБЕЦ;ЛОЖЬ)" я на раз два пилю, в цикле, но именно дело в том, что его применение ограничено, на больших объемах он не эффективен. Будет смотреть дальше, попробую массивы в  LOBasic раскусить. Частично мне этот метод известен, но еще не настолько, как VBA.

eugenefoxx

#16
economist - FormulaLocal не сразу увидел что Вы такую форму указали (я использовал просто Formula=). Проверил - да, результат на лицо. Я ее "разглядел" в вашем комментарии уже после того, как смог парсить в массиве в LOBasic. Скорость FormulaLocal  впечатляет. И по времени парсинг в массиве сильно уступает FormulaLocal. Беру на вооружение.

mikekaganski

Не могли бы Вы выложить пример таблицы с макросами с использованием Formula и FormulaLocal, чтобы можно было потестировать время выполнения?
С уважением,
Михаил Каганский

economist

Кхм, возможно я тут немного воды намутил. Я постоянно кодю в Excel и Calc одно-/попеременно, и многие вещи пишу по памяти. Благо "миры" обоих табличных редакторов, в т.ч. благодаря форумчанам, теперь сблизились до степени подобия (одинаковыми русскими функциями и поддержкой выполнения диалекта VBA в ODS и даже в XLS-файлах).    

FormulaLocal - специфический метод VBA в MS Excel для создания в ячейках "кириллических" формул. Т.е. он нужен в Excel чтобы не писать =VLOOKUP(), а просто копировать готовую отрепетированную формулу в код прямо из книги: =ВПР(). Просто Local - в коде с ="=ВПР()" - даст в Excel ошибку.

Теперь то же самое в Calc:  
"просто" Formula, FormulaR1C1 - в LO 5.4.5.1-6.01-6.1.0.3 прекрасно работает и с английскими, и русскими формулами. Т.е. FormulaLocal в LO - не нужна, её просто нет, и у меня она в Calc (в формате ODS) - вообще даёт ошибку !

eugenefoxx - а вы уверены что такой код у вас работает именно в Calc, а не в Excel (и какое расширение у файла)? -

option vbasupport 1
option Compatible

sub test
[A1:A3]=1
[A4].FormulaLocal="=СУММ(A1:A3)"
[A4].font.bold=true
end sub  


В Calc у меня дает ошибку на [A4].FormulaLocal="=СУММ(A1:A3)"

И ускорения (даже теоретически) от замены одной команды на другую - ни в Calc, ни в Excel быть не должно.  Это ведь просто в VBA, на мой взгляд, способ упростить синтаксическому анализатору задачу "понимания" и пре-компиляции кода.

Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

eugenefoxx

#19
mikekaganski - Прилагаю файл с примером кода с двумя методами - Formula и FormulaLocal. Запуск через Tools - Macros - Run Macro... Как я понял, FormulaLocal использует Local системы и тем быстрее обрабатывает данные).

eugenefoxx

economist - Правда, хочу заметить. Первоначально про быстроту кода я говорил в отношении Linux. FormulaLocal выполняется здесь за несколько секунд. В отношении этого кода на Windows реализованную через эмулятор Qemu на машине, где и Linux, к таким быстрым исполнениям не приводит в обоих методах.

mikekaganski

#21
Хм... ну что ж.

А теперь можно сравнить скорость следующих двух реализаций:


Sub testFormulaLocal
Dim t as integer
n = Now()
oSheet = ThisComponent.Sheets(0)
For t = 5 to 15000 'SAP склад
   oSheet.getCellByPosition (11, t).FormulaLocal = "=IFERROR(VLOOKUP(B" & t+1 & ";Sheet2.$O$1:$O$1048576;1; );"")"
Next t

MsgBox (Format(Now() - n, "Данные обработаны! [s] с")
End Sub



Sub testRangeFormula
Dim t as integer
n = Now()
oSheet = ThisComponent.Sheets(0)
r = oSheet.getCellRangeByName("L6:L15001") ' то же самое, что oSheet.getCellByPosition (11, (5..15000)+1)
dim f(0 to r.Rows.Count-1, 0 to 0) as String
For t = 0 to r.Rows.Count-1 'SAP склад
   f(t, 0) = "=IFERROR(VLOOKUP(B" & r.Rows(t).RangeAddress.StartRow + 1 & ";Sheet2.$O$1:$O$1048576;1; );"")"
Next t
r.setFormulaArray(f)

MsgBox (Format(Now() - n, "Данные обработаны! [s] с")
End Sub


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

rami

Когда вы формируете строку для формулы (или запроса для базы) в макросе, нужно вместо пары кавычек (пустая строка) писать четыре:
";Sheet2.$O$1:$O$1048576;1; );"""")"   ' <— это часть формулы

mikekaganski

Баг насчёт неоправданно медленного задания Formula.
С уважением,
Михаил Каганский

rami

mikekaganski, нужно было поправить формулу в образце документа для бага, как я писал выше. А то макрос пишет в ячейки всякий бред, который нарастает с каждым просмотром формулы в мастере формул. Офис каждый раз пытается сам поправить формулу, но не получается.

mikekaganski

Это да, но это не имеет значения в данном случае, поскольку от этого не меняется картина
С уважением,
Михаил Каганский

rami

На скорость это почти не влияет, но при удалении всего диапазона с неправильными формулами комп подвисает секунд на 20, да и вид у поломанных формул печальный, "что о нас подумает Марья Ивановна ???"

Кстати, у меня макрос testformulalocal отрабатывает за ~17 сек.

mikekaganski

Цитата: rami от  9 октября 2018, 13:23
"что о нас подумает Марья Ивановна ???"

Ну, не "о нас", только обо мне ;)
С уважением,
Михаил Каганский

economist

#28
Цитата: eugenefoxx от  9 октября 2018, 04:28про быстроту кода я говорил в отношении Linux. FormulaLocal выполняется здесь за несколько секунд.

Прочел и понял что .FormulaLocal - это у ТС команда StarBasic (LO Basic), а не VBA. Ох уж эти "однофамильцы"...
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

eugenefoxx

rami,
Цитата: rami от  9 октября 2018, 07:19Когда вы формируете строку для формулы (или запроса для базы) в макросе, нужно вместо пары кавычек (пустая строка) писать четыре:
Код:
";Sheet2.$O$1:$O$1048576;1; );"""")"   ' <— это часть формулы
спасибо за подсказку, видел "проблему" с этим, но не знал как исправить.