Excel тормозит на больших таблицах — как ускорить VBA-макрос в разы

Excel тормозит на больших таблицах — как ускорить VBA-макрос в разы и перестать ждать по полминуты каждое действие. Подробно разбираем, почему Excel начинает зависать на больших объёмах данных и как ускорить VBA-макросы: ScreenUpdating, Calculation, EnableEvents, массивы, правильная запись в диапазоны и типичные ошибки.

Есть у Excel одна черта, которую знают почти все, кто работает с ним не для галочки, а всерьёз: сначала он кажется бодрым и послушным, а потом вдруг начинает вести себя как старый автобус в горку. Таблица открывается дольше. Макрос мигает экраном. Формулы пересчитываются так, будто у них личная драма. А самая неприятная картина начинается, когда ты запускаешь VBA-процедуру на большом массиве данных и понимаешь, что быстрее, наверное, было бы часть действий сделать руками.

В такие моменты люди обычно грешат на всё подряд. На компьютер. На Windows. На “сломанный Excel”. На тяжёлый файл. На атмосферное давление, если день совсем плохой. Но правда в том, что очень часто Excel не виноват. Он просто честно делает лишнюю работу, которую ему навязал макрос.

И вот тут начинается важный поворот. Когда пользователь впервые понимает, почему именно тормозит VBA, его отношение к Excel меняется. Оказывается, скорость макроса — это не загадка и не лотерея. Это почти всегда следствие того, как именно написан код. Один и тот же сценарий можно реализовать так, что он будет мучительно ползти, а можно — так, что он пролетит через таблицу почти без шума и пыли.

Сегодня разберём как раз это. Не в стиле “вот волшебная строка, вставьте и молитесь”, а по-человечески: почему Excel тормозит, что сильнее всего убивает производительность, какие приёмы дают самый заметный эффект и как собрать нормальный быстрый макрос для реальной рабочей книги.

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

Почему один и тот же макрос может работать и быстро, и мучительно медленно

На первый взгляд кажется, что скорость VBA зависит от объёма данных. Частично это правда. Если в таблице двадцать строк, всё будет летать почти при любом раскладе. Если в таблице сто тысяч строк, чудес не будет. Но проблема не только в количестве данных. Гораздо важнее что именно макрос делает с Excel во время работы.

Представим простой сценарий. Макрос идёт по строкам и проверяет сумму в столбце F. Если сумма больше порога, он красит ячейку. На бумаге задача элементарная. Но внутри Excel в этот момент происходит не одно действие, а целая процессия:

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

И всё это — на каждой итерации.

То есть пользователь думает, что макрос просто “красит строки”, а Excel в это время устраивает маленький завод с конвейером. Поэтому тормоза чаще всего рождаются не из-за самих данных, а из-за того, что код заставляет Excel слишком много раз делать тяжёлые побочные действия.

Вот почему профессиональная оптимизация VBA почти всегда начинается не с хитрых алгоритмов, а с одного простого вопроса: как убрать из процесса всё лишнее, пока макрос работает.

Что именно сильнее всего замедляет VBA в Excel

Есть несколько классических причин, из-за которых макросы начинают ползти.

• Первая — перерисовка экрана. Excel любит показывать пользователю всё, что происходит. Если макрос выделяет, копирует, форматирует, листает диапазоны, активирует листы и двигает курсор, программа честно рисует всё это на экране. На маленьких задачах вы почти не замечаете нагрузку. На больших — получаете медленное мигание и ощущение, что Excel думает слишком долго.
• Вторая — автоматический пересчёт формул. Если в книге много формул, особенно тяжёлых, любое изменение в ячейке может инициировать цепочку пересчётов. А если макрос изменяет тысячи ячеек подряд, то и пересчёт может стартовать тысячи раз.
• Третья — события. В книге могут быть процедуры типа Worksheet_Change, Worksheet_Calculate, Workbook_SheetChange и другие обработчики. Макрос меняет данные — Excel может запускать дополнительные куски кода. И если вы не контролируете это, один макрос внезапно тащит за собой ещё два-три.
• Четвёртая — построчная работа с ячейками без необходимости. Это уже более глубокая тема. Когда VBA тысячу раз обращается к объектной модели Excel, это заметно медленнее, чем если он один раз забирает диапазон в память, обрабатывает его там и одним действием возвращает обратно.
• Пятая — Select, Activate и прочая экскурсия по листу. Макросы, написанные “как руками”, часто сначала выбирают лист, потом диапазон, потом ячейку, потом ещё что-то активируют. Excel терпит, но любит это не больше, чем человек любит лишнюю бюрократию.

Из этих причин самая первая и самая дешёвая в исправлении — отключить на время работы всё, что не обязано происходить прямо сейчас.

Главный базовый приём: отключить лишние процессы на время макроса

Это фундамент. Если вы делаете серьёзный макрос и не отключаете хотя бы ScreenUpdating, Calculation и EnableEvents, вы почти наверняка оставляете на столе бесплатную прибавку к скорости.

Вот как выглядит базовый шаблон:

Sub БыстраяОбработка()
Application.ScreenUpdating = False*
Application.Calculation = xlCalculationManual*
Application.EnableEvents = False*

Dim LastRow As Long*
Dim i As Long*

LastRow = Cells(Rows.Count, 1).End(xlUp).Row*

For i = 2 To LastRow*
If Cells(i, 6).Value > 100000 Then*

Cells(i, 6).Interior.Color = RGB(255, 200, 200)*

End If*

Next i*

Application.ScreenUpdating = True*
Application.Calculation = xlCalculationAutomatic*
Application.EnableEvents = True*
End Sub

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

Что делает ScreenUpdating и почему он даёт такой эффект

Когда вы пишете:

Application.ScreenUpdating = False

вы говорите Excel: не надо прямо сейчас показывать пользователю каждое промежуточное изменение.

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

Особенно заметен эффект в макросах, где:

• меняется формат ячеек
• есть переходы по листам
• много копирования и вставки
• идёт подсветка или очистка диапазонов
• выполняются операции с фильтрами, сводными, формами

Когда ScreenUpdating выключен, Excel работает “в тишине”. Пользователь видит уже итог, а не весь процесс. Это как раз тот случай, когда скромная строка кода даёт результат, который приятно ощущается руками.

Что делает Calculation и почему от него зависит половина боли

Строка:

Application.Calculation = xlCalculationManual

временно переводит Excel в ручной режим пересчёта формул.

Это особенно важно в книгах, где:

• много ссылок между листами
• есть сложные формулы
• используются ВПР, XLOOKUP, СУММЕСЛИМН, ИНДЕКС, ПОИСКПОЗ
• есть массивные вычисления
• сводные данные завязаны на большие таблицы

Если макрос вносит много изменений в такие книги, Excel в автоматическом режиме может пытаться пересчитываться снова и снова. Пользователь видит “думающую” программу и часто не понимает, что реальная работа макроса уже закончилась — просто файл продолжает расчёты.

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

Возвращать нужно так:

Application.Calculation = xlCalculationAutomatic

И делать это всегда. Без надежды “ладно, не забуду”.

Что делает EnableEvents и почему он спасает от скрытых тормозов

Теперь третья строка:

Application.EnableEvents = False

Она отключает обработку событий Excel. Это означает, что пока макрос работает, не будут автоматически запускаться другие процедуры, завязанные на изменение ячеек, пересчёт, активацию листа и так далее.

Если в книге нет никаких событий, разница может быть не такой заметной. Но в рабочих файлах события встречаются часто. Например:

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

И вот представьте: основной макрос меняет тысячу ячеек, а Workbook или Worksheet на каждое изменение запускает ещё один код. Это не просто замедление, это уже целый ансамбль, играющий без дирижёра.

Поэтому временное отключение событий — вещь очень разумная. И точно такая же обязательная по возврату:

Application.EnableEvents = True

Почему просто вставить эти три строки — ещё не вся оптимизация

Потому что они лечат не всё, а только самый первый слой проблемы.

Если у вас макрос медленный именно из-за бесконечных обновлений экрана, пересчётов и событий — эффект будет сильным. Но если код сам по себе написан тяжело, тормоза могут остаться.

Например, вот типичный медленный стиль:

• цикл по строкам
• обращение к каждой ячейке отдельно
• копирование по одной строке
• форматирование по одной ячейке
• выбор листов через Select
• постоянные переходы по диапазонам

Excel умеет пережить и это, но охотно не делает. Поэтому следующая большая тема — не мучить объектную модель Excel лишними обращениями.

Почему обращение к ячейкам в цикле — это медленнее, чем кажется

Когда VBA пишет:

Cells(i, 6).Value

это выглядит как мелочь. Но на самом деле он каждый раз обращается к объектной модели Excel. Один раз — не беда. Десять тысяч раз — уже ощутимо. Сто тысяч раз — здравствуйте, тормоза.

Особенно если таких обращений много в одном цикле:

If Cells(i, 1).Value <> "" Then
If Cells(i, 6).Value > 100000 Then*
Cells(i, 7).Value = "Риск"*

Cells(i, 6).Interior.Color = RGB(255, 200, 200)*

End If*
End If

Здесь на одной строке цикла уже несколько обращений к листу. Если строк десятки тысяч, Excel каждый раз ходит туда-сюда между VBA и листом. Это и создаёт вязкость.

Поэтому более быстрый подход — сначала забрать значения в массив, потом обработать их в памяти, а потом вернуть результат на лист.

Следующий уровень: работа с массивом вместо построчного чтения ячеек

Вот тут Excel начинает дышать свободнее.

Представим, что нам нужно проверить столбец F и пометить строки, где сумма больше 100000. Вместо того чтобы читать ячейки по одной, можно загрузить диапазон в массив:

Sub БыстраяПроверкаЧерезМассив()
Application.ScreenUpdating = False*
Application.Calculation = xlCalculationManual*
Application.EnableEvents = False*

Dim LastRow As Long*
Dim Данные As Variant*
Dim Результат As Variant*
Dim i As Long*

LastRow = Cells(Rows.Count, 1).End(xlUp).Row*
Данные = Range("A2:F" & LastRow).Value*
ReDim Результат(1 To UBound(Данные, 1), 1 To 1)*

For i = 1 To UBound(Данные, 1)*
If Данные(i, 6) > 100000 Then*

Результат(i, 1) = "Риск"*

Else*

Результат(i, 1) = ""*

End If*

Next i*

Range("G2:G" & LastRow).Value = Результат*

Application.ScreenUpdating = True*
Application.Calculation = xlCalculationAutomatic*
Application.EnableEvents = True*
End Sub

Этот код уже работает заметно быстрее на больших таблицах. Почему? Потому что Excel один раз отдаёт данные в память, потом VBA крутит их без постоянных походов к листу, а в конце один раз возвращает результат.

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

Когда массивы особенно полезны

Есть задачи, где переход к массивам даёт почти лучший эффект из всех простых оптимизаций. Например:

• массовая проверка значений
• нормализация текста
• проставление статусов
• поиск пороговых сумм
• сравнение соседних столбцов
• подготовка выгрузок
• создание служебных пометок

любые сценарии, где нужно пройти по большому диапазону и вычислить результат

Если же задача состоит именно в массовом форматировании ячеек, массивы помогают меньше, потому что формат всё равно приходится возвращать на лист. Но даже там можно сначала вычислить, что именно нужно форматировать, а уже потом красить только нужные диапазоны, а не всё подряд.

Ещё одна частая причина тормозов: Select и Activate

Это старая классика. Макросы, записанные через встроенный рекордер Excel, часто выглядят примерно так:

Sheets("Данные").Select
Range("A1").Select
Selection.Copy
Sheets("Отчет").Select
Range("B2").Select
ActiveSheet.Paste

Такой код работает, но медленно и неаккуратно. Excel приходится не только выполнять действие, но и постоянно переключать активные объекты, двигать выделение, менять фокус. Это почти всегда можно переписать гораздо лучше.

Например, так:

Worksheets("Данные").Range("A1").Copy Destination:=Worksheets("Отчет").Range("B2")

Одна строка вместо целого парада Select. И гораздо меньше лишней возни.

Если в вашем макросе много Select и Activate, это почти всегда повод переписать его без них. Ускорение часто бывает очень заметным.

Правильный шаблон быстрого макроса

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

Sub ШаблонБыстрогоМакроса()
On Error GoTo Завершение*

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

Dim ws As Worksheet*
Dim LastRow As Long*
Dim i As Long*

Set ws = Worksheets("Данные")*
LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row*

For i = 2 To LastRow*
If ws.Cells(i, 5).Value = "Просрочено" Then*

ws.Cells(i, 5).Interior.Color = RGB(255, 199, 206)*

End If*

Next i*
Завершение:
Application.ScreenUpdating = True*
Application.Calculation = xlCalculationAutomatic*
Application.EnableEvents = True*
End Sub

Здесь важна не только оптимизация, но и строка:

On Error GoTo Завершение

Она нужна для того, чтобы в случае ошибки макрос всё равно дошёл до блока восстановления настроек. Это очень полезная привычка. Потому что забыть вернуть Calculation или EnableEvents после аварийного сбоя — дело обычное, а потом пользователь начинает жить в книге с “тихо сломанными” настройками.

Почему обработка ошибок здесь важнее, чем кажется

Многие пропускают эту часть, потому что “код же и так работает”. Но хорошие макросы нужно писать не под идеальный день, а под реальную работу.

А реальная работа любит сюрпризы:

• не тот лист
• пустой диапазон
• удалённый столбец
• неожиданный тип данных
• изменённое имя вкладки
• ошибка в формуле

Если макрос упадёт в середине и не дойдёт до включения параметров обратно, Excel останется с отключённым ScreenUpdating, Calculation или EnableEvents. И вот тут начинается очень противное состояние: книга как будто работает, но уже не совсем нормально. Формулы пересчитываются странно, события молчат, пользователь нервничает.

Поэтому блок завершения — это не украшение, а нормальная страховка.

Что ещё помогает ускорить работу на больших таблицах

Есть ещё несколько практических приёмов, которые часто помогают.

• Первый — не красить каждую ячейку по одной, если можно красить диапазон целиком. Если нужно оформить блок данных, делайте это одним действием по диапазону, а не тысячей мелких операций.
• Второй — не обращаться к UsedRange без необходимости. Он не всегда ведёт себя так, как ожидают, и может захватывать мусор из прошлого.
• Третий — ограничивать объём обработки. Если у вас рабочая таблица начинается не с первой строки и не с первого столбца, не гоняйте макрос по всему листу просто “на всякий случай”.
• Четвёртый — использовать Long, а не Integer для строковых счётчиков. На больших объёмах это не про скорость как таковую, а про надёжность. Integer быстро упрётся в пределы, а Long живёт спокойно.
• Пятый — не хранить лишнюю логику внутри глубоко вложенных If, если можно упростить проверку заранее. Это уже вопрос читаемости и поддержки. Быстрый код — это ещё и код, который не превращается в болото через месяц.

Практический пример: почему один макрос работает 40 секунд, а другой 4

Представим две версии одной и той же задачи. Обе ищут строки, где в столбце F сумма больше 100000, и ставят пометку в столбце G.

Медленный вариант:

• экран обновляется
• формулы пересчитываются
• события включены
• каждая ячейка читается отдельно
• каждая запись делается отдельно

Быстрый вариант:

• экран отключён
• пересчёт переведён в ручной режим
• события отключены
• данные считаны в массив
• результат записан одним действием

Логика одинакова. Результат одинаков. А ощущение от работы файла — совершенно разное. В одном случае пользователь ждёт и злится. В другом — нажал кнопку и почти не успел отвлечься.

Вот в этом и состоит настоящая ценность оптимизации. Не в “красоте кода”, а в том, что Excel перестаёт быть тормозом в прямом и переносном смысле.

Когда оптимизация особенно нужна, а когда можно не усердствовать

Если у вас таблица на 150 строк и макрос выполняется за секунду, превращать код в суперскоростной болид не обязательно. Там важнее простота и читаемость.

Но если у вас:

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

То есть вопрос не в том, “надо ли всегда ускорять”. Вопрос в том, насколько объём задачи уже вырос из режима учебного примера в режим реальной работы. А у большинства рабочих книг этот переход случается очень быстро.

Самая частая ошибка новичков после такой статьи

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

Это не трагедия, но подход не самый зрелый.

Правильнее думать так:

если макрос короткий, простой и почти ничего не меняет — возможно, оптимизация ему не нужна

если макрос долгий, циклический, много пишет на лист, форматирует, пересчитывает, затрагивает события — оптимизация нужна почти наверняка

То есть сначала понимаем задачу, потом применяем приём. Excel любит не только скорость, но и здравый смысл.

Что в итоге нужно запомнить

Если собрать всё в короткий набор правил, получится так.

• Первое: Excel тормозит не только из-за объёма данных, но и из-за лишних процессов вокруг макроса.
• Второе: три базовых рычага ускорения — это:

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

• Третье: после завершения макроса всё обязательно нужно вернуть обратно.
• Четвёртое: работа через массивы на больших таблицах почти всегда быстрее, чем постоянное чтение и запись по ячейке.
• Пятое: Select и Activate — это чаще всего лишний балласт.
• Шестое: хороший быстрый макрос — это не только скорость, но и аккуратное восстановление настроек при ошибке.

Вот и всё. На словах не выглядит революцией. Но на практике именно эти вещи часто дают тот самый эффект, после которого человек впервые говорит: “О, так Excel всё-таки умеет работать нормально”.

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

Если хотите получать такие разборы дальше — подписывайтесь на канал и забирайте файл с примером в Telegram.

Там удобно смотреть готовые заготовки и тестировать приёмы на своих таблицах без лишней возни.