Что-то этот трюк не работает у меня.

Задача такая: в интерфейсе с настройками (msForms) дать пользователю возможность задать параметр с использованием привычных ему формул Excel на "человеческом" языке. К примеру, в поле Параметр1 пользователь может написать СУММ(Лист1!A1;Лист1!A2), а программа должна преобразовать это в нативный формат и вычислить с помощью Evaluate.

Использовать для этого временную ячейку на листе кажется не очень правильным, а с именами какая-то странность: то ошибка 1004 при добавлении имени, то имя добавляется, но RefersTo отдает то же самое, что хранит RefersToLocal, хотя при этом преобразование из A1 в R1C1 работает.

Кажется, при добавлении любой экселевской формулы в RefersToLocal эксель считает, что вся строка с формулой неправильная.

Поиск по форуму и гуглам открывает некоторые нюансы:

Microsoft сообщает о проблемах с RefersToLocal
Пользователи пишут, что иногда приходится пошаманить со свойством Comment

Все перепробовал, не помогает. Файл с примером во вложении.

P.S.
Офис 2021, версия Excel 2108, 64-разрядная

Alexcx, пару раз сталкивался с тем, что xlsb вдруг переставал открываться, но подробностей не помню.
А с другими своими файлами этот же трюк повторить не пробовали? Чтобы понять, насколько стабильный эффект)

До ваших результатов считал, что да.. Какой смысл задействовать буфер обмена, если известен пункт назначения? Это как если бы Васе нужно было переложить пачку денег из правой руки в левую, но он задействовал бы для этого еще Петю) Да и Майкрософт говорит про необязательный параметр Destination метода Copy вот что:
Что, конечно, можно интерпретировать по-разному, но как-бы намекает, что если destination не указан, то копирует в буфер, а если указан, то просто создает в нужном месте копию. И это косвенно подтверждает ваш антивирус тоже.

irabel, во-первых, там нет ничего такого, а во-вторых – вы результаты-то видели? через буфер обмена получается в десятки раз быстрее, чем при прямом копировании. Поэтому и антивирусы не на первом месте среди подозреваемых. Да и временно отключить я их сам не смогу, надо сначала гипотезу с версиями Excel опровергнуть.

Проверил на 2007.

test1: 0.16
test2: 0,31
test3: 0,30
test4: 0,01

То есть так, как оно по идее должно быть, и как у sokol92 на 2016.

Тем не менее у меня на разных машинах оно работает гораздо быстрее, чем прямое копирование. Это и странно. И сильно сомневаюсь, что это как-то может повлиять на стабильность. Следующая после Copy операция не начнет выполняться, пока не завершится копирование, а копирование не завершится, пока винда не скажет, что оно завершилось)

Закономерность такая, что по идее (и как пишет sokol92, ) на любом компе и на любой версии excel первый метод должен работать быстрее, чем второй и третий. Плюс по идее драматической разницы между ними быть не должно. Но пока что на разных компах 2019 и 2021 ведут себя с точностью до наоборот. Если у кого-то под такими же версиями будет работать так, как ожидается (особенно интересно посмотреть на 2019 32-разрядную), то гипотезу о зависимости от версий Excel можно будет отбросить, а она пока основная.

sokol92, так я бы тоже оставался при таких же рекомендациях, если бы не увидел то, что вижу на 19 и 21 версиях Excel:) На разных машинах. Кстати, и диапазоны копируются точно так же. Rows(n).Copy Rows(m) проворачивается от двух до двадцати раз медленнее, чем Rows(n).Copy: Rows(m).PasteSpecial. У вас нет возможности проверить на других версиях? Вечером попробую на 2007.

Намеренно находится там для чистоты эксперимента, потому что в конкретной задаче он может находиться только там. Кстати, а кнопка чем помешает?

Это просто тест, в реальном коде все на месте.

В общем, граждане! У кого есть возможность прогнать на своих компах, не проходите мимо. Любопытно же. Может хоть какая-то закономерность определится.

sokol92, такое впечатление, что вы вообще не вникали в то, что я писал выше и в результаты выложенного примера тоже.

Я знаю, как оптимизировать обработку данных, сводя к минимуму взаимодействие с ячейками напрямую (обработать таблицу с миллионом строк и парой сотен столбцов за секунды – не проблема). Но как вы сформируете любую итоговую таблицу с нужный форматом КАЖДОЙ отдельной ячейки в ней, не взаимодействуя с Excel?

100 – это как раз приблизительное число ячеек итогов в конкретной задаче, которые необходимо отформатировать в соответствии с данными и форматами ячеек на других листах. Думаете, я с диапазонами работать не умею?)

Вы не заметили, что результаты теста противоречат вашим рекомендациям (и моим ожиданиям, кстати, тоже)? Внезапно через буфер обмена получается раз в 10 быстрее))

Короче, вопрос остается: в чем прикол? Почему на практически одинаковых машинах с плюс-минус одинаковыми процессорами, одинаковым объемом оперативы и соседними версиями Excel время выполнения операции копирования ячеек может отличаться в десятки раз? И как это можно обойти?
Пока что нашлось одно странное решение: вопреки интуиции, здравому смыслу и рекомендациям sokol92,  копировать через copy-pastespecial (на одних машинах получается быстрее раза в два, на других – в десять).

Может быть все-таки есть другие способы?

sokol92
Во вложении простой пример.
Отличия по времени выполнения соответственно примерно в 20, в 4, в 3 и в 5 раз.
Способы 2 и 3 периодически меняются местами по скорости, но общее соотношение сохраняется. Особенно впечатляет отличия для самого очевидного (казалось бы) и короткого по коду способа копирования. Вместо этого, оказывается, гораздо быстрее получается перенести сначала значение, а потом скопировать формат.

Пример выложить не могу, к сожалению. Но я описал все предельно точно. Именно операция копирования ячейки в ячейку (обе находятся на одном листе) и присвоение ячейки числового формата (cells(r,c).numberformat = str) на одном файле с одними и теми же данными на одних компах выполняется в десятки раз медленнее, чем на других. А в какой-нибудь день может пролететь за доли секунды, но потом опять тупит. А на других компах всегда работает быстро.

И это тестовый файл, в котором ничего не меняется. Но сталкивался с этим и на других файлах тоже, в основном именно при копировании ячейки в ячейку. Завтра поэкспериментирую на чистых файлах с чистым кодом, может быть прояснится что-нибудь.

Периодически напарываюсь на такое, но универсального решения не нашел.
Может кто-то уже сталкивался и нашел причины и методы обхода.

Проблема: один и тот же файл с одним и тем же кодом на одних компах обрабатывается быстро (1-2 секунды), на других -- медленно (30-40 секунд). Тормоза начинаются на копировании одной(!) ячейки в другую (cells(r1,c1).copy cells(r2,c2)) и на присвоении формата одной ячейке или диапазону из нескольких ячеек. На каждую такую операцию уходит в десятки раз больше времени на условно "медленных" компах, чем на "быстрых".

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

ScreenUpdating, Calculation и все такое прочее установлены правильно, да и на данном конкретном коде не сильно влияют на скорость (размер таблицы небольшой).

Загадочности добавляет тот факт, что иногда и на "медленных" компах оно вдруг начинает проворачиваться быстро, но потом снова тупит. Есть незначительные отличия в железе, плюс на некоторых компах под 64-разрядной windows установлен 32-разрядный Excel. Есть подозрение, что именно на 32-разрядных Excel 2019 происходит такой тупняк, но статистику еще не собрал.

Jul_25, приложение писал на VBA, принцип работы похож на тот, что описан Михаилом выше: у каждого пользователя свой excel файл, синхронизация данных осуществляется через промежуточные xml файлы, которые находятся в сетевой папке. Есть главный пользователь, который создает таблицу, других пользователей и группы, настраивает поля, которые доступны для редактирования и т.д. Каждая строка имеет свой id и набор статусов с информацией об изменениях. Также предусмотрен механизм защиты целостности данных при работе с копиями файлов пользователей и много чего еще.
Но прежде чем все это начинать делать, стоит хорошенько подумать над другими способами реализации вашей задачи)

asesja, я понимаю, что причин может быть много. В описанных случаях несколько из них явно присутствуют (вылеты при работе с макросами на тяжелых и кривых файлах, или при работе с sap, например), но они малоустранимы: таких файлов и макросов дофига и далеко не все из них даже можно поправить, так как они могут поступать извне.  

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

Периодически сталкиваюсь с ситуацией, когда после закрытия Excel файлов (иногда штатного, иногда после подвисаний тяжелых файлов), в фоновых процессах остается Microsoft Excel или просто какая-нибудь excel книга. То есть среди работающих приложений екселя нет, в панели задач, соответственно, его тоже нет, а в фоновых процессх висит excel.exe.

Если при этом запустить Excel (из меню пуск, например, или просто двойным кликом по книге), слетают все надстройки.
Лечится только снятием задачи в диспетчере задач.

Выявить однозначную закономерность не удается (разные компы, разные версии excel, разные файлы), но есть подозрение, что такое происходит, когда текущий процесс не успевает завершиться, а пользователь уже снова пытается открыть экселевский файл двойным кликом.

Вопрос: есть ли способ каким-то образом предотвратить такие зависания процессов в фоне?

Пока сделал костыль в виде vbs-скрипта, который убивает все фоновые ексели, чтобы не тратить время на их поиск в диспетчере, но хотелось бы найти более правильно решение:)

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

Отвлекся на другие дела, вернулся, чтобы завершить тему)

Hugo, посмотрел в xml и да, где-то так: <v>14986.39</v>, а где-то так: <v>17234.349999999999</v>.
При этом все значения, которые можно увидеть на листе или в отладчике имеют два знака после запятой. И похоже, что арифметические операции работают с реальными значениями, поэтому в моем случае 35180668,48 - 35180668,48  =  1,49011611938477E-08 (что не очень похоже на нуль). И мы бы этого даже не заметили, если бы subtotal на низком уровне работал ровно так же, как обычное сложение. Тогда совпадали бы не только "видимые" значения, но и реальные.

В общем, нужно повесить плакат над рабочим местом: "Не забывай про точность вычислений!" с суровым токарем со стальной болванкой в руке.
И, как советует testuser, в сомнительных случаях сравнивать преобразованные в строку значения (потому что такое преобразование работает именно с "видимыми" значениями числовых переменных).

Косвенно.
Значения в массиве получаются суммированием данных из других массивов, которые скопированы из диапазонов.
А checksum получается из функции subtotal 9.
Проблему обошел радикально, избавившись от subtotal. Но любопытство спать не дает)
XML  гляну завтра, но что там может быть не так?  Допустим, лежит что-то, что больше, чем double, при суммировании считается не как double, а потом впихивается в double c потерей точности. А subtotal честно считает даблы. Или наоборот. Но тогда эти даблы в итоге отлчались бы на самом деле. А они вроде как равны)

Как такое может быть?

Да, я помню, что исходный тип числа, которое хранится в variant, после арифметических операций может повыситься до следующего уровня точности, но VarType в обоих случаях выдает код double, да и в Immediate оба значения вроде как совпадают.

EShikh, да, если они открыты (или пытаются быть открытыми) в одном экземпляре excel. У меня для подобных случаев на рабочем столе лежит ярлык со ссылкой на excel с ключом /x. Например, у вас открыта модальная форма в какой-то книге. Если вы захотите открыть другой файл, он не откроется, пока вы не закроете форму. Но если кинете этот файл на такой ярлык, он спокойно откроется в новом экземпляре.

EShikh, еще бы посмотреть батник, конечно, но если я правильно понял проблему, ее решит запуск excel отдельным процессом.

А переделать в умные таблицы? Как часто файл обновляется?
Файл на рабочем компе посмотреть не могу, к сожалению.

sokol92, да, уже сам допер, вроде как очевидно же) Спасибо!

nilske, я и задался вопросом: есть ли другой способ?

Sanja, мне казалось, что максимально конкретно объяснил: при нажатии на значки фильтров в столбцах значения из последней строки не должны попадать в список фильтруемых значений. Файл с примером вложил.

Sanja, решение макросом и подразумевалось. Только что именно сделать нужно? Сформулирую точнее: в самом файле макросов быть не должно, т.е. разнообразные манипуляции с расширенным фильтром и т.д. не подходят. Но генерит его макрос, на этом этапе можно делать все, что угодно, но я так и не придумал, что именно.

Видимо, кроме вставки пустой строки, решения нет. Я, во всяком случае, ничего другого не вспомнил и не нашел)

Всем привет.

На картинке – пример таблицы с автофильтром, в которой нужно фильтровать строки со второй по третью, а в четвертой строке могут быть какие-то итоговые данные, неважно. Автофильтр, как водится, сам продлевает фильтруемый диапазон до последней строки, даже если использовать метод Autofilter для диапазона A1:C3. То есть в список фильтра первого столбца попадает пустая строка, в список второго столбца – "zzz" и т.д.

Может мне приснилось, но вроде как можно было каким-то образом обходить эту особенность автофильтра. И не только вставкой пустой строки перед последней, итоговой. Или других способов нет?

Автор темы хочет защитить файл от копирования. Привязать его к конкретному устройству. Что мешает скопировать файл вместе с библиотекой? В любом случае потребуется мудрить с привязкой.

DimoN233, еще был способ попроще и бесплатный)

Раньше WIN API позволяла (может и сейчас так можно) писать и читать данные на диске сразу за  концом файла. Если размер файла не кратен размеру сектора, то эти данные будут защищены от затирания (т.к. запись любого другого файла начинается с начала сектора).

Соответственно, схема такая: часть функций тоже выносим в dll, создаем какой-нибудь очень нужный конфиг-файл, который должен лежать рядом с вашим excel-файлом, при инсталляции всего этого добра пишем за концом этого файла какую-нибудь метку и периодически проверяем из dll наличие этой метки.

И да, устанавливать ваш файл на конкретный комп должны вы сами из вашего суперсекретного инсталлятора)