Обновление функции Excel VBA только при изменении

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

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

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

  • Определите текущее значение ячейки, которую нужно изменить, из которой я могу определить, требуется ли обновление. Но ActiveCell.Value пуст, и попытки передать саму ячейку в качестве второго параметра выполняются с циклическим справочным предупреждением.
  • Создал второй столбец для хранения старого входного значения, которое затем передается в функцию и сравнивается с новым значением. Тем не менее, функции не могут обновить это старое значение при успешном выполнении, и попытки обновления только с помощью формул всегда удовлетворяются циклическими справочными предупреждениями. Кроме того, не кажется, что функция может возвращать «без изменений», поэтому мне нужно будет хранить и использовать старый результат.

Есть ли способ предотвратить некоторые действия по обновлению каждой формулы на листе?

Другие требования:

  • Может быть способ отключить автоматический расчет формулы, но я определенно хочу, чтобы каждая строка обновлялась, как только вводилась ячейка параметра. Я также не хочу заставить пользователя нажать кнопку, чтобы обновить функцию.
  • Я попытался сделать пользовательскую электронную таблицу в Visual Studio, но обнаружил, что развертывание слишком сложно поддерживать для моей пользовательской базы. Решение должно быть полностью включено в книгу с поддержкой макросов.
  • Версия Excel для использования листа будет в 2007 и 2010 годах.

я бы

  1. систематически блокировать пересчет, установив «Файл» / «Параметры» / «Формулы / Расчет рабочей книги» в «Ручной» и выключив «Пересчет по сохранению»

  2. создайте Private Sub Worksheet_Change(ByVal Target As Range) и определите, в каких условиях должен произойти пересчет ( Target.Worksheet.Calculate ) – например, определить Target.Column цели (используя Target.Column и Target.Row ) и вызовите запрос из триггера Worksheet_Change.

  3. В конце концов я бы поместил инструкцию перерасчета в свой собственный Sub и запустил кнопку в лист, чтобы пользователь мог – по своему желанию – вручную вызвать пересчет (на всякий случай, если ключ F9 не известен пользователям)

  4. Также возможно использовать глобальную логическую переменную need_Query которая задается триггером Worksheet_Change, If Target.Row = X And Target.Column = Y (= параметр), и функция запроса проверяет (и возвращает), что Boolean, и идет в запросе только If need_Query Then , иначе выйти без действия. Убедитесь, что need_Query установлен FALSE на время загрузки рабочего листа.

  5. Наконец, вы можете преобразовать функцию запроса в Sub и сформулировать еще более сложные условия в триггере Worksheet_Change для ее запуска (например, если какой-то столбец меняется и т. Д.).

  • Как я могу вернуть диапазон информации на основе даты и номера счета в excel?
  • Таблица в Excel из хранимой процедуры SQL Server с полем параметров в книге
  • Как отсортировать несколько сводных таблиц так, чтобы строки с похожими именами отображались вместе? `
  • Как игнорировать ссылки на ячейки, содержащие текст при выполнении вычислений?
  • Функция для получения SheetName в ячейке
  • Вложенные vlookups и соответствие индексов для нескольких условий внутри ячейки
  • Как удалить лист и вставить один и тот же лист с тем же именем, но не допускать ссылки на ошибки в формуле, если это так?
  • Как запустить различные макросы VBA на выбранных рабочих листах
  • Макрос Excel - запись данных из формулы в ячейки при нажатии кнопки
  • excel: сделать работу SUM.IF с формулами
  • Как определить, является ли числовая строка все повторяющиеся цифры
  • Interesting Posts
    Давайте будем гением компьютера.