Белое окно и пометка «не отвечает» ещё не доказывают бесконечный цикл: длинный VBA-код выполняется в основном потоке Excel и может не отдавать интерфейсу время на перерисовку. Но ждать наугад тоже не нужно. Работайте с копией книги, уменьшите набор строк и найдите этап, на котором резко растёт время.

Перед оптимизацией сохраните исходный файл и запишите версию Excel, объём данных, длительность до зависания и последнее видимое действие. Не завершайте процесс во время записи рабочей книги, если нет резервной копии: проблема производительности легко превращается в потерю результата.

Симптом подсказывает направление проверки

НаблюдениеЧастая причинаБыстрый эксперимент
Время растёт вместе с числом строкЦикл читает и пишет ячейки по однойОбработать тот же диапазон массивом
Задержка после каждой записиАвтопересчёт или событияНа копии временно отключить и затем восстановить
Зависает на одном файлеПовреждённый диапазон, ссылки или объектыСравнить чистую копию листа и UsedRange
Интерфейс белеет, но CPU работаетДлинный цикл не возвращает управлениеРедко вызывать DoEvents и вести счётчик прогресса

Измеряйте этапы, а не отдельные догадки

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

Сравните 100, 1 000 и 10 000 строк. Если увеличение данных в десять раз даёт рост в сотни раз, ищите вложенный цикл, повторный поиск по всему диапазону или формулу, которую Excel пересчитывает после каждой записи.

Состояние Excel нужно вернуть даже после ошибки

Sub BuildReport()
    Dim oldCalc As XlCalculation
    Dim oldEvents As Boolean
    Dim oldScreen As Boolean

    oldCalc = Application.Calculation
    oldEvents = Application.EnableEvents
    oldScreen = Application.ScreenUpdating
    On Error GoTo CleanUp

    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    ' Основная работа макроса

CleanUp:
    Application.Calculation = oldCalc
    Application.EnableEvents = oldEvents
    Application.ScreenUpdating = oldScreen
    If Err.Number <> 0 Then Err.Raise Err.Number, Err.Source, Err.Description
End Sub

Отключение ScreenUpdating, Calculation и EnableEvents ускоряет многие сценарии, но оставлять их выключенными нельзя. Сохраняйте прежние значения и восстанавливайте их в единой секции очистки. Иначе следующий пользователь получит книгу, которая «не считает» или перестала запускать события, хотя сам макрос уже завершился.

Самый большой выигрыш обычно дают массивы

Каждое обращение VBA к отдельной ячейке пересекает границу между кодом и сеткой Excel. Считайте диапазон в двумерный Variant одним обращением, обработайте значения в памяти и верните результат одной операцией. Это надёжнее бесконечной настройки задержек и индикаторов.

  • Не используйте Select и Activate там, где можно обратиться к Workbook и Range явно.
  • Не пересчитывайте всю книгу внутри цикла; считайте нужный диапазон в определённой точке.
  • Ограничьте диапазон реальными данными вместо целых колонок, если операция тяжёлая.
  • Проверьте обработчики Worksheet_Change: запись макроса может запускать их повторно.

DoEvents помогает интерфейсу, но не ускоряет алгоритм

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

Как доказать, что исправление работает

Запустите старый и новый вариант на одной копии данных и сравните итоговые значения, время каждого этапа и состояние Application после намеренно вызванной ошибки. Результат считается готовым, если данные совпадают, настройки восстановлены, а рост времени остаётся объяснимым при увеличении объёма.

Для внешнего разбора достаточно обезличенной книги с несколькими строками, модуля VBA, замеров этапов и сведений о разрядности Excel. Рабочие отчёты с персональными или финансовыми данными передавать не требуется.

Проверенные источники