Обновлять UDF Excel при применении сортировки

Question

Как показывает вопрос, я вижу побочные эффекты (медлительность) пользовательских функций, которые я создал, когда применяю сортировку к своей электронной таблице. Я создал несколько пользовательских функций в .NET и развернул их для тестирования. Функция за кулисами выполняет вызов API, а затем возвращает данные обратно в ячейку. Поэтому я ожидаю некоторой задержки при создании функции для запуска на начальном этапе. НО я вижу некоторую медлительность в приложении, когда сортирую столбцы, как будто UDF пересчитывают и вызывают API. Это нормальное поведение электронной таблицы?

Моя функция имеет ссылки на ячейки и выглядит следующим образом:

=wsStateMiles("ALKCUR",G45,K45,1,J45,$G$2)

Когда я удаляю эту функцию, электронная таблица сортируется очень быстро, так как первое, что я попытался сделать, это фактическая медлительность Microsoft Excel.

(Дополнительная информация)

Это не имеет ничего общего с сортировкой, но когда я добавляю дополнительные UDF, кажется, что Excel работает так медленно, что он снова пересчитывает UDF всей электронной таблицы. Опять же, это нормальное поведение Excel?

Answer 1

Для сортировки, к сожалению, это ожидаемое поведение.

При сортировке таблицы Excel загрязняет все ячейки таблицы. UDF с любым аргументом, ссылающимся на ячейку в таблице, будет пересчитан.

В случае добавления дополнительных UDF я не уверен, почему он пересчитывает все UDF. Нормальная энергонезависимая UBA-функция VBA вызывает вычисление только той ячейки, в которую она была введена. Может быть, ваши .NET UDF всегда изменчивы или есть параметр / атрибут, который установлен как "изменчивый"? Я знаю, что UDF с управляемым кодом имеют атрибут для включения / выключения волатильности.

К счастью, есть и хорошие новости. Хотя на самом деле невозможно остановить перерасчет UDF-файлов, их можно ускорить.

Требуется статический массив в UDF для хранения текущего состояния всех возвращаемых значений (т. Е. Столбца "Miles", L:L , в вашем примере) и ячейка "Suspend", используемая в качестве аргумента для UDF. Перед сортировкой установите для ячейки Suspend значение TRUE , а затем верните значение FALSE .

После приостановки UDF вернет результат, соответствующий ячейке вызывающего, из сохраненных значений. После перезапуска он будет работать как обычно, но также обновит сохраненное значение.

Хитрость, чтобы получить эту работу, заключается также в реализации гистерезиса. Первый вызов UDF на ячейку после перезапуска также должен возвращать сохраненное значение.

В качестве альтернативы, вместо использования ячейки "Suspend", сохраняя все переданные аргументы в статическом массиве, UDF всегда может вернуть сохраненное значение, если аргументы не изменятся. Как только они изменятся, будет вызван API и результат сохранен перед возвратом.

Этот второй метод также автоматически ускоряет пересчет при добавлении дополнительных UDF. (Первый метод также можно использовать при добавлении пользовательских функций путем "приостановки" перед добавлением их и "возобновления" после этого.)