В описании нет противоречий. Ознакомьтесь с примером. Протяните диапазон таблицы вниз. У новых строк будет назначена формула, которая отличается от всех имеющихся строк. Требуется решение, способное выяснить эту дефолтную формулу у объекта таблицы.

В одном году 31 536 000 секунд или 31 622 400 секунд если год високосный. Решение задачи зависит от того какой год брать, какой период считать месяцем, есть ли смещение часового пояса. Ваша цифра сама по себе имеет мало смысла без базовой даты от которой идет отсчет. Вероятно, у вас есть еще одна дата от которой идет отсчет, например Unix-дата (01.01.1970 в UTC)?

Здравствуйте!
При удалении таблице DataBodyRange и создании новой строки, таблица откуда-то восстанавливает формулу столбца. Найти бы где она хранит эти формулы. Прикладная VBA-задача, например, может быть такой - выяснить соответствует ли формула в строке таблицы, общей формуле для всего столбца (при этом у всех ячеек столбца формула может быть не верна (при создании новой строки она была бы иная), а значит сверять формулы строк относительно друг-друга тупик. Хочется прямолинейно решить задачу, а не, например, через костыль, где я создаю дополнительную строку, смотрю ее формулы, а затем сношу)

Sanja, великолепно!

Добавлю только что можно обернуть в Индекс чтобы работало без формулы массива:

Alex, Спасибо, очень познавательно! В целом решение, но не гибкое

Здравствуйте!
Как задать массив как литерал прямо в ячейке чтобы потом это значение ячейки можно было скормить какой-либо формуле, принимающей массив?
Я хочу где-то задавать массив как литерал (допустим, строковым типом, так как тип значения A1 это "строка"). Нужен механизм конвертации на уровне формул. Нужен какой-то способ сказать "Excel, тут в A1 не строка, а массив", пример. Решить нужно на уровне формул, доступных в Excel-2019 или ниже. Спасибо!

Решил проблему так:

Оборачивание в ИНДЕКС позволило делать ПОИСКПОЗ без формулы массива и когда в умной таблице одна строка тоже корректно обрабатывается все.

Sanja, спасибо добрый человек!

Странно, у меня требует, без формулы массива выводит "1" в обоих случаях Вашего образца

Здравствуйте!
Мне нужно типо ВПР по двум искомым значениям, но не сам результат, а номер позиции, удовлетворяющий критериям (не важно первый он или последний).
Также в примере какой-то косяк в работе ПОИСКПОЗ, когда умная таблица из одной строки.

Я знаю, что формула =ПРОСМОТР(1;1/(B18=B24:B40);D24:D40) работает как впр и ее не нужно вводить как формулу массива, но мне нужен номер по массиву. Есть ли более удачный вариант формулы, чем моя ПОИСКПОЗ, желательно работающий как обычная формула (не массива), как это демонстрирует ПРОСМОТР?

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


Мне нужно импортировать группу листов из другого файла и сохранить их формульные связи друг на друга. Кроме того, я не могу удалять старый лист и заменять его новым, т.к. это руинит формульные связи других листов к такому листу. Приходится действовать через 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 в файле-примере.