Белое окно и пометка «не отвечает» ещё не доказывают бесконечный цикл: длинный 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. Рабочие отчёты с персональными или финансовыми данными передавать не требуется.