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

Я заметил, что мои пользовательские функции пересчитываются всякий раз, когда я удаляю ячейки. Это приводит к значительным задержкам при удалении целых столбцов, потому что UDF вызывается для каждой ячейки, в которой он используется. Таким образом, если вы используете 1000 UDFS, то удаление столбца или ячейки вызовет его 1000 раз.

Например, поместите следующий UDF в модуль, а затем несколько раз вызовите его из рабочего листа с помощью =HelloWorld().

Function HelloWorld()
HelloWorld = "HelloWorld"
Debug.Print Now()
End Function

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

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

Изменить: после экспериментов я нашел больше действий, которые запускают UDFS:

1. Any change to the number of columns that a ListObject (i.e. Excel Table) spans through resizing (but not rows). Even if the UDFs themselves aren't in the ListObject concerned, or in fact in any ListObject at all.
   2. Adding new cells or columns anywhere in the sheet (but not rows).

Обратите внимание, что режим ручного расчета недоступен по нескольким направлениям.

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

Во-вторых, я на самом деле не разрабатываю конкретную электронную таблицу, а скорее пишу книгу о том, как не являющиеся разработчиками могут использовать хорошо написанный готовый код, такой как UDF, для выполнения задач, которые в противном случае были бы им недоступны. Примеры включают динамическое объединение или разделение текста или пользовательскую функцию точного совпадения бинарного поиска, которую Чарльз Уильямс описывает по адресу https://fastexcel.wordpress.com/2011/07/22/developing-faster-lookups-part-2.-how-to-build-a-faster-vba-lookup/ (И да, я предупреждаю их, что обычно собственное решение на основе формул превосходит UDF. Но, как вы увидите из темы, которую я упоминалось выше, тщательно написанные функции могут работать хорошо).

Я не знаю, как пользователи будут их использовать.

В отсутствие решения для программирования, похоже, мне просто придется указать в книге, что пользователи могут испытывать значительные задержки при добавлении или удалении ячеек или изменении размера ListObjects, если они используют ресурсоемкую UDFS. Даже если эти UDF написаны эффективно.