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


Мне нужно импортировать группу листов из другого файла и сохранить их формульные связи друг на друга. Кроме того, я не могу удалять старый лист и заменять его новым, т.к. это руинит формульные связи других листов к такому листу. Приходится действовать через Cells.Copy и Cells.PasteSpecial чтобы формулы ссылающиеся на лист выжили после того как он изменит данные. Я бы с радостью изменил код на что-то другое, раз тут есть проблемы с подменой связей, но озвученное выше должно соблюдаться. Надеюсь, не слишком субурно описал

Может не нужно было действовать через Cells.Copy и Cells.PasteSpecial и нужно было завозить данные как-то по другому чтобы не получать эти связи с донором, а потом бороться  с ней. Но ничего умнее в голову не пришло

МатросНаЗебре, разумеется второй вариант будет работать если связь локализуется в ячейках! Спасибо за ваши идеи, но может есть вариант проще чем обходить каждую ячейку. Если честно мне проще оставить проблему как есть и убедить пользователей избегать квадратных скобок в нейминге файлов чем чекать все ячейки импортируемых листов (что-то мне претит заниматься этим, наверное предрассудки что так не должно быть). Да, можно оптимизировать ваш код и чекать только формульные ячейки, но все равно выглядит подозрительно и с таким кодом иметь дел не хочется. В общем, я думал что человечество уже нашло элегантный способ решения этой проблемы, пойди на форум и забери его

К сожалению это не разрывает связь с книгой, вы можете это увидеть на вкладке "Данные" в разделе Workbook Links после того как отработал макрос. Вернее мне нужно не просто разорвать связь, а изменить ее на текущую книгу (из-за формул)

Здравствуйте! Возникает такая проблема, смотрите вложенный файл. Я не могу контролировать какие имена задают файлам пользователи поэтому совет "просто переименуй" не работает.




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

С обновлением эксель до последней версии проблема не возникает, мой файл примера не работает на обновленном эксель

nilske,  мой Anydesk 405580734

хорошо будет пока файл не посетит другой человек, с другим набором обновлений, если причина действительно в этом)
Попробую обновиться, действительно.

nilske,  попробуйте еще сделать так, пкм по файлу и в его свойствах разблокировать, вдруг у вас макросы блокируются для файлов из интернета. Я с трех компов вижу эту ошибку

nilske, возьмите файл sokol92 и проделайте действия, который я ему описал, у него не получалось, но следуя данным шагам он смог воспроизвести, вот:

Для этого я прямо в вашем файле переименовал функцию, стал в ячейку A2 и нажал enter для пересчета ячейки в локальную ошибку #ИМЯ?, после чего вернул старое имя функции и закрыл файл с сохранением изменений. При повторном открытии увидел "разрушительный сбой".

Переименовывать функцию нужно в модуле VBA, а не в ячейке.

Есть ли у вас что добавить касательно файлов .xlsb? Остаются ли они островком безопасности, как кажутся на первый взгляд, для тех, кто хочет работать с UDF и умными таблицами или бинарный файл тоже деградирует, но по тихому? (Я в курсе, что в .xlsb не возникает разрушительного сбоя, но это совсем не означать, отсутсвие деградации структуры на уровне бинарного файла).

Самое неприятное в этой истории, что люди из моего коллектива нашли другой неизвестный путь к разрушительному сбою в новом файле и он исключает редактирование имени макроса в VBA модуле (они его не посещали, доверяя коду разработчика, но тем не менее заруинили файл через разрушительный сбой). Я пробовал добавлять строку в таблицу в эксель с подавленым макросом чтобы в новой строке было #ИМЯ? и открывать файл с включеным макросом, но это не вызывает проблем и на первый взгляд проходит гладко. Я бы успокоился, но я знаю, что есть какой-то более короткий способ и меня это беспокоит так как у меня в проде xlsm+таблица+udf и потенциально у нас в зоне риска много форумчан. Желательно формализовать проблему с ракурса как без посещая модуль VBA, люди, ничего не знающие про VBA тоже могут прийти к аналогичной проблеме путем прямой работы с таблицей. Я видел пару таких случаев на слабой машине, когда было открыто много эксель-файлов. Открытие моего файла сопровождалось разрушительным сбоем, пока небыло сокращено число открытых файлов на машине. Потенциально это второй способ руинить новый файл, но мне тяжело его воспроизвести и указать конкретные шаги как прийти к проблеме. Могу только добавить, что тот файле также представлял связку xlsm+таблица+udf.

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

p.s. Я не вижу для себя выхода в попытке связаться с разработчиками продукта Excel, так как в случае успеха, мой работодатель не сменит версию Excel в ближайшее время и мне нужно адаптироваться к существующей среде, а не ждать обновлений от MS. Кроме того, я не смогу зарегламентировать посещение публичного файла в сети и непускать в него пользователей эксель с неподходящей версией

sokol92, спасибо, за ваше участие. Я открыл ваш файл и проблема не возникла. Но сделать на основе этого файла разрушительный сбой удалось. Для этого я прямо в вашем файле переименовал функцию, стал в ячейку A2 и нажал enter для пересчета ячейки в локальную ошибку #ИМЯ?, после чего вернул старое имя функции и закрыл файл с сохранением изменений. При повторном открытии увидел "разрушительный сбой". Windows 10 Pro 64x разрядный, Excel-2019 (64 бит)

В исходном файле проблему вызывает функция с совершенно другим названием и оно не из зарезирвированных слов, даже если api объекта FSO причислить к таковым. Выше я указал, что упрощал файл ошибки и доупрощался до того, что от старой функции нет ничего, даже ее имени, т.к. старое имя не продолжало нести смысла в виду сильных упрощений. Я думаю, причина не в этом, хотя замечание верное и я мог бы подобрать имя для примера более аккуратно не создавая двусмысленности и ассоциаций с системными утилитами. Вероятно, вы смогли бы убедиться в том, что дело вовсе не в текущем имени функции на более старой версии MS Office. Уже двое отписались что на 365 ошибки не возникает. Естественно, не понимая природы данной ошибки я был вынужден экперементировать не только с именами функций, но и именами и типами аргументов и типами возвращаемого результата. Уверяю вас, дело не в текущем имени функции, я перебрал достаточно имен, чтобы быть уверенным в этом.

Приветствую!

Скачайте пример, зайти в единственный модуль макроса VBA, переименуйте функцию ‘GetText_’ убрав нижнее подчеркивание в конце имени функции. Закройте файл сохранив изменения и при повторном открытии у вас возникнет «Разрушительный сбой», если работа макросов не подавлена.
Файл-пример можете создать сами, если думаете, что дело в специфичных особенностях примера, новый файл будет работать с тем же эффектом в виде разрушительного сбоя, для этого в новом файле сначала создайте таблицу, в единственной строке которой укажите формулу ‘=GetText()’, затем вставьте модуль vba и код функции GetText, закройте файл сохранив его как .xlsm

Тестировалось на  пакетах MS Office 2016 и 2019, 64-бит, на разных машинах.

Теперь вопрос. Разумеется, обнаружил этот способ не от хорошей жизни. Часто ломается файл, где в умной таблице есть формулы с UDF, не зная как решить проблему, я упрощал и упрощал пример ошибки до минимально работающего набора, который ее провоцирует и он перед вами. Я не могу отказаться от умной таблицы и от UDF и от файла .xlsm,  но вместе эти три сущности, кажется, не уживаются, что делать, сталкивался ли кто?

БМВ, Как же вы измените порядок?  Если вы попытаетесь перезаписать значение, то получите ошибку.

А если вы будете удалять и добавлять элемент заново и таким образом добиваться сортировки (типо сортировки пузырьком, но снизу), то тоже самое можно делать и в Dictionary.

В смысле коллекция позволяет сортировать? Вы имеете в виду хранит порядок в котором элементы были добавлены? Dictionary тоже порядок внесения запоминает.

Если вы создали словарь так:
То это долгий старт для Dictionary и тогда понятно почему вам приходится говорить о разительной разнице.
Для себя решил отказаться от такого способа создания словарей.

testuser, это понятно, что выгрузку ключей лучше переиспользовать. Вопрос был другой - эффективность ColKeys, пример которой вы привели, в плане производительности. Сильно уступает методу keys(), который у Dictionary (при равном наборе ключей и значений)? Или призводительность сопоставима и переписав проект, избавив его от Dictionary, я не получу жесткой просадки производительности в части .keys() функционала? Вы не тестировали ее в плане перформанса?

Да это не имеет принципиального значения) Я нуждался в низкоуровневом образце кода с трюком, проверенным практикой, а потому устойчивый к ошибкам, чтобы утащить к себе в проект и видоизменить. Теперь, когда я получил такие образцы благодаря вам, мои мысли оставили это и меня интересует вопрос цены в плане производительности в сравнении с тем же Dictionary.

testuser, БМВ, спасибо огромное! То, что нужно!
Скажите, а в плане производительности это сопоставимо с Dictiory.keys()?
Без учета накладных на первоначальное создание самого объетка Dictiory или коллекции, ключи которого получаем.

Что скажете, подходит ли этот функционал для широкого использования, аналогично Dictiory.keys()?
Например. Задумал заменить в своем проекте все Dictionary на Коллекции. Мотивация - опасное поведение Dictionary при обращении к значению, он добавляет ключ, если его не было, даже тогда, когда я предпочел бы ошибку. Проект покрыт проверками Dictionary.Exists с функционалом выброса ошибки, если ключа нет, коллекция делала бы выброс ошибки по-умолчанию, что избавляет меня от нудных танцев с Dictionary и неочевидного Debugа.

Аналог .keys() для коллекций потребовался чтобы не городить дополнительный уровень вложености коллекции, хранящей Row таблицы (в отличии от Dictionary, Row в виде коллекции не может сообщить к каким столбцам она хранит значения из-за чего где-то в верхнеуровневом коде нужно выписывались названия столбцов или же хранить их в общей структуре DSetWithMeta с полями "rows", "columnNames". Т.е. образовался дополнительный уровень вложенности в сравнении с Dictionary).

Пример с Row не единственный, а лишь один частный случай, но очень часто Dictionary подкупает именно тем, что имеет метод .keys(), который в отличии от метода exists() сложно реализовать для коллекции (но благодаря вам, кажется, это теперь стало возможным).

Привет! Поделитесь, у кого есть, кодом Fn VBA для 64 битного MS Excel, которая принимает коллекцию как аргумент и возвращает все ее ключи (именно ключи, не значения).

New, спасибо! Я в итоге к тому же пришел, только кода в реальный проект чуток более пришлось написать - приходится обрабатывать возможную ошибку и возвращать итоги на место даже если .ListRows.Add провалился. А что там на уровне проблемной таблицы сломано так и не понял, вероятно, таблице переписали стиль, это можно сделать, например, если задать формат на уровне столбца, а затем попытаться вернуть формат таблицы обратно простым форматированием ячеек (это не удастся без таких вот последствий).

Привет! У двух умных таблиц разное поведение в части форматирования итогов при удалении макросом DataBodyRange.
Одна не сбрасывает формат итогов, другая сбрасывает. Внешне таблицы выглядят абсолютно одинаково и я хотел бы понять в чем дело и как сделать чтобы вторая таблица прекратила переустанавливать формат итогов.
При ручном удалении разницы между таблицами не возникает, не ясно почему разница возникает только при удалении макросом.

Смотрите Лист1 и Лист2 в файле-примере.

memo, магия! Спасибо!

memo, да, хотел это, спасибо большое!
Скорее ради удовлетворения любопытства спрошу, а через доработку моей "СУММПРОИЗВ" и не формулу массива эта проблема имеет не громоздкое решение?

memo, а как сделать чтобы числа введенные как текст не исчезали? Я так понял, "Ч" их обнуляет, что для меня опасно

Здравствуйте! Формула "СУММПРОИЗВ" не будет работать в принципе если в диапазоне суммирования есть текст? Я ввожу условия, которые должны были отсеять текст, но все равно не работает. И второй вопрос в рамках отправленного примера: как сделать чтобы умная таблица не переводила даты в заголовках таблицы в строки (хочу избавиться в моей формуле от ДАТАЗНАЧ, но хочу оставить умную таблицу)

Дополнено спустя время: желательно чтобы числа введеные как текст подсчитывались

RAN, собирается value ячеек в какую-то структуру данных, скажем, массив с листами и адресами ячеек, вот этот массив и нужно дополнить значениями по адресу ячеек. По этой причине двухмерный массив сразу имеет три столбца, заполнить нужно третий столбец - это value, в первых двух столбцах задан лист и ячейка. Порядок листов хаотичен и заранее не известен. Использовать собранные value никак не собираемся, просто кладем их в массив и довольно потираем руки.
Все, теперь есть полное описание задачи. Я расширил описание для вас, надеюсь теперь все понятно по исходным данным.
Я искренне не понимаю, правда, что дали эти дополнительные описания, что с их вводом появилось принципиально меняющее ход мыслей и как вам это поможет ответить на мои вопросы.

На всякий уточню, что вопрос не в полном коде которой должен иметь макрос, а максимум о паре строк в части цикла сбора.
Повторю свои прежние вопросы, но другими словами.
Имеет ли смысл циклу обходить ячейки на основании того, принадлежат ли они листу, или это ничего не дает с точки зрения скорости работы из-за условия, что ячейки не образуют общего диапазона на листе и каждое значение ячейки все равно придется собирать отдельной ссылкой на каждый адрес ячейки. Вы не можете получить доступ к диапазону, объединяющему несколько ячеек, потому что ячейки на листе не группируемы и поэтому какая бы то ни была группировка не может быть описаны (алгоритмом).
Есть ли смысл собирать значение, не заботясь о принадлежности к конкретному листу? Или лучше написать цикл так, чтобы он сначала собирал все ячейки одного листа и только потом переходил на другой лист, где снова обходил все его ячейки (это может быть быстрее, поскольку лист не прыгает туда-сюда и по этой причине может быть предустановлен одним действием сразу на несколько адресов одновременно, в противоположность тому, если об этом не позаботиться и разрешить листам прыгать туда-сюда в том хаотичном порядке в котором они встречаются по массиву).

Цикл можно составить по варианту 3 и лист поменяется 499 раза (так как у нас 10000 ячеек по 20 штук на листе, всего 500 листов), а можно не придавать этому значение и лист будет с отличием до 9999 раз в худшем сценарии варианта 1.

БМВ, большое спасибо! На всякий случай уточнюсь, меня смутила ваша настороженность по поводу читабельности кода. С краткостью решения, как мне кажется, полный порядок - не противоречит условию задачи адресам ячеек изначально находиться в двумерном массиве (лист, адрес). Простите меня за то, что я не упомянул об этом сразу. Вопросы выше можно свести и к таким:
1. Нужно ли разбивать данные в массиве по листам и последовательно запрашивать ячейки в пределах листа, или можно "не заморачиваться" и писать цикл полной адресации к случайному листу из массива.
2. Какой самый удачный синтаксис в пределах цикла в плане скорости. Есть ли разница между приведенными мной вариантами?
Если я правильно понял ваш ответ, то первое - да, желательно разбить. Второе - предпочтительный синтаксис на который нужно держать ориентир с "with", а не "Select" как было у меня в варианте 3. Могли бы вы пояснить в чем тонкость? Равнозначны ли вариант 1 и вариант 2? Спасибо!

New, не понял, а если данные листов не выстраиваются в линии чтобы их как в вашем примере макросом забирать целыми столбцами\строками?
Я именно такую ситуацию и обрисовывал в своем первом сообщении. Но, видимо, сделал это непрозрачно. Исправлюсь:
Есть 10000 адресов ячеек в несистемном виде. Скажем:
- по 20 ячеек на лист, таких листов 500. Итого 20яч. * на 500 листов = 10000 ячеек; Я не знаю может ли быть в одной книге столько листов. Для простоты предположим что все листы в одной книге.
- у ячеек нет общего столбца и строки в пределах одного листа, нужные данных по единственном экземпляре на строку и на столбец среди других ненужных значений.
- положение ячеек на одном листе ничего не говорит о их расположении на другом листе.

Если пример показался недостаточным - усложните, он же так скажем учебный, самый лютый сценарий примените из тех которые вам рисует воображение. Смысл в том что данных очень много, они не системны и стремятся образовывать максимально неудобные для считывания разом диапазоны ячеек, у части ячеек есть общий лист и это вся что у них общее. А мой вопрос про то нужно ли эти данные собирать ориентируясь на эту общность по листу.
(Исходя из этого я накидал варианты 1-3 в первом сообщении, но их у вас может быть больше, это те что мне пришли в голову).

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

Привет!
Меня интересует, увеличится ли скорость работы кода VBA, если он сначала опросит все ячейки одного листа и только потом перейдет на второй лист.
Допустим, нам нужно считать значение 10000 ячеек в массив, сами ячейки разбросаны в пределах одной книги (каждое значение нужно читать по индивидуально назначенному адресу ячейки, оптимизация по общему диапазону невозможна).

Будет ли зависимость скорости от последовательности адресации листов?
Будет ли зависимость скорости от синтаксиса адресации листов?

Второй вопрос можно сузить для краткости - какой синтаксис оптимален для описанной выше задачи, есть ли разница для цикла при задании итерации:


Вариант 1
итерация 1
ThisWorkbook.Sheets("Лист1").Range("A3")
итерация 2
ThisWorkbook.Sheets("Лист2").Range("A2")
итерация 3
ThisWorkbook.Sheets("Лист1").Range("A4")

Вариант 2
итерация 1
ThisWorkbook.Sheets("Лист1").Range("A3")
итерация 2
ThisWorkbook.Sheets("Лист1").Range("A4")
итерация 3
ThisWorkbook.Sheets("Лист2").Range("A2")

Вариант 3
несколько циклов (один в другом)
ThisWorkbook.Sheets("Лист1").Select
'код обращения к адресам нужных ячеек без указания листа
ThisWorkbook.Sheets("Лист2").Select
'код обращения к адресам нужных ячеек без указания листа


P.s. Еще вопрос про скорость который меня волнует, если можно)
Это нормальная практика объединять с разделителем несколько значений в String разной длины и засовывать в массив, делая String последним уровнем вложения в структуре данных? Извлекается от от туда только один раз, поиск мне не нужен. Переживаю за скорость. Или лучше заменить на коллекцию?)

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

Видел возможность сделать удаленный интерпретатор Python, но сам не делал. Я хочу чтобы вы знали, когда на сайте столкнетесь что это опция для PRO: такая возможность доступна и для обычной версии библиотеки, она не афишируется, возможно из коммерческих соображений, но в одном из видео демонстрации новых возможностей xlwings я видел как автор сайта объяснял что делать пользователям обычной библиотеки. Какое конкретно это было видео я уже не вспомню, вы сможете его найти на канале автора сайта в ютубе. На этом канале не так много видео длиной около одного часа которые посвящены демонстрации возможностей, демонстрация проходила в формате Live (через яндекс браузер сможете посмотреть на русском).

Оставлю тут перевод части текста по ссылке выше о возможностях:

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

Полностью управляемые сервисы : Heroku , render , Fly.io и др.

Интерактивные среды : PythonAnywhere , Anvil и др.

Бессерверные функции : AWS Lambda , Azure Functions , Google Cloud Functions , Vercel и т. д.

Виртуальные машины : DigitalOcean (реферальная ссылка), vultr (реферальная ссылка), Linode , AWS EC2 , Microsoft Azure VM , Google Cloud Compute Engine и т. д.

Корпоративные серверы : все будет работать (включая Kubernetes), если к соответствующим конечным точкам можно получить доступ из вашего приложения для работы с электронными таблицами."[