Скорость: почему макрос тормозит

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

Главное правило скорости в VBA: дорого стоит не вычисление, а обращение к листу. Каждое Cells(i, 3).Value — это отдельный запрос из VBA в Excel. Считайте их, а не арифметические операции.

Зачем это нужно

Пользователи прощают макросу многое, но не «зависший» Excel. Когда окно белеет и в заголовке появляется «Не отвечает», человек делает единственное, что умеет, — снимает задачу через диспетчер. Работа потеряна, доверие тоже.

Хорошая новость: типичный «тормозящий» макрос ускоряется в 20–100 раз практически без переписывания логики. Плохая: пока вы не поймёте почему он тормозит, вы будете лечить симптомы.

Сначала измерьте

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

Sub Медленно()
    Dim i As Long
    Dim t As Double
    t = Timer

    For i = 2 To 50001
        Cells(i, 3).Value = Cells(i, 1).Value * Cells(i, 2).Value
    Next i

    MsgBox "Заняло " & Format(Timer - t, "0.00") & " с"
End Sub

На моём ноутбуке этот макрос обрабатывает 50 000 строк примерно за 28 секунд. Заметьте: арифметики здесь на копейки — одно умножение на строку. Всё время съедают 150 000 обращений к ячейкам (по три на строку: два чтения и одна запись).

Если макрос длинный, замеряйте по кускам и выводите в окно Immediate (окно немедленного выполнения; открывается в редакторе VBA по Ctrl+G) — так видно, какой участок действительно виноват:

Sub ЗамерУчастков()
    Dim t As Double

    t = Timer
    ЧитаемДанные
    Debug.Print "Чтение:  " & Format(Timer - t, "0.000") & " с"

    t = Timer
    Считаем
    Debug.Print "Расчёт:  " & Format(Timer - t, "0.000") & " с"

    t = Timer
    ПишемРезультат
    Debug.Print "Запись:  " & Format(Timer - t, "0.000") & " с"
End Sub

Шаг 1. Выключить всё, что Excel делает «за компанию»

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

Sub РежимСкорости(ByVal включить As Boolean)
    With Application
        .ScreenUpdating = Not включить                ' перерисовка экрана
        .EnableEvents = Not включить                  ' обработчики событий листа
        .DisplayStatusBar = Not включить              ' строка состояния
        If включить Then
            .Calculation = xlCalculationManual        ' пересчёт формул — вручную
        Else
            .Calculation = xlCalculationAutomatic
        End If
    End With
End Sub

Настройка	Что отключаем	Когда особенно важно
`ScreenUpdating = False`	перерисовку листа	всегда; обычно даёт наибольший выигрыш
`Calculation = xlCalculationManual`	пересчёт формул после каждой записи	если на листе есть формулы, особенно `ВПР`/`СУММЕСЛИ`
`EnableEvents = False`	события листа (`Worksheet_Change` и др.)	если в книге есть макросы-обработчики событий
`DisplayStatusBar = False`	обновление строки состояния	небольшой, но бесплатный выигрыш

Одно только это ускоряет наш пример примерно до 6 секунд — в 4–5 раз, ценой двух строк. Но есть жёсткое условие: всё выключенное обязано быть включено обратно — в том числе если макрос упал. Именно поэтому вызовы РежимСкорости True и РежимСкорости False в следующем примере обёрнуты в шаблон с меткой Завершение из прошлого урока. Забудете — пользователь останется с книгой, где формулы не пересчитываются, и решит, что макрос «сломал файл».

Шаг 2. Читать диапазон в массив

Настройки лечат симптом. Настоящая причина — в том, что мы ходим в Excel за каждой ячейкой по отдельности. VBA и Excel — две разные программы, и каждое .Value пересекает границу между ними. Один такой переход — доли миллисекунды; сто пятьдесят тысяч переходов — полминуты.

Решение: сходить один раз за всем диапазоном сразу. Если присвоить .Value многоячеечного диапазона переменной типа Variant, VBA получит двумерный массив со всеми значениями — и дальше вы работаете с памятью, где Excel не участвует вообще.

Sub Быстро()
    Dim лист As Worksheet
    Dim исходные As Variant, результат() As Variant
    Dim i As Long, последняя As Long
    Dim t As Double

    t = Timer
    On Error GoTo Ошибка
    РежимСкорости True

    Set лист = ThisWorkbook.Worksheets("Данные")
    последняя = лист.Cells(лист.Rows.Count, 1).End(xlUp).Row

    ' 1. Одним движением забираем весь диапазон в память
    исходные = лист.Range("A2:B" & последняя).Value

    ' 2. Считаем в памяти — Excel в этом даже не участвует
    ReDim результат(1 To UBound(исходные, 1), 1 To 1)
    For i = 1 To UBound(исходные, 1)
        результат(i, 1) = исходные(i, 1) * исходные(i, 2)
    Next i

    ' 3. Одним движением возвращаем результат на лист
    лист.Range("C2:C" & последняя).Value = результат

Завершение:
    РежимСкорости False
    MsgBox "Заняло " & Format(Timer - t, "0.00") & " с", vbInformation
    Exit Sub

Ошибка:
    MsgBox "Ошибка " & Err.Number & ": " & Err.Description, vbExclamation
    Resume Завершение
End Sub

Разберём ключевые места:

исходные = лист.Range("A2:B" & последняя).Value — одно обращение к Excel вместо ста тысяч. В исходные теперь двумерный массив: исходные(строка, столбец).
Нумерация в таком массиве начинается с единицы, а не с нуля, и всегда двумерная — даже если столбец один. То есть первая ячейка — исходные(1, 1).
ReDim результат(1 To UBound(исходные, 1), 1 To 1) — готовим массив под ответ: столько же строк, один столбец. UBound(массив, 1) — номер последней строки.
Цикл считает в оперативной памяти. Здесь можно хоть миллион операций — это быстро.
лист.Range("C2:C" & последняя).Value = результат — одно обращение на запись. Размер диапазона должен совпадать с размером массива, иначе Excel растянет или обрежет данные.
лист.Cells(лист.Rows.Count, 1).End(xlUp).Row — стандартный приём «найти последнюю заполненную строку»: встаём на самый низ столбца A и «нажимаем Ctrl+↑».

Результат: те же 50 000 строк обрабатываются примерно за 0,2 секунды. Итог по всем шагам:

Вариант	Время (50 000 строк)
цикл по ячейкам, всё включено	≈ 28 с
цикл по ячейкам + `ScreenUpdating`/`Calculation`	≈ 6 с
массив + настройки	≈ 0,2 с

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

Что ещё тормозит

Select и Activate. Их щедро вставляет макрорекордер, а стоят они дорого: Excel реально двигает выделение. Работайте с объектами напрямую.

' Так писать не надо
Sheets("Данные").Select
Range("A2").Select
Selection.Copy
Sheets("Итог").Select
Range("A2").Select
ActiveSheet.Paste

' Так — надо
ThisWorkbook.Worksheets("Итог").Range("A2").Value = _
    ThisWorkbook.Worksheets("Данные").Range("A2").Value

Copy/Paste ради значений. Через буфер обмена — медленно и капризно. Прямое присваивание .Value быстрее в разы.
UsedRange или End(xlUp) внутри цикла. Вычислите границы один раз до цикла и запомните в переменную.
Работа с ячейками по одной ради форматирования. Форматируйте диапазоном: Range("C2:C50001").NumberFormat = "0.00".
Волатильные формулы на листе (СМЕЩ/OFFSET, ДВССЫЛ/INDIRECT, СЕГОДНЯ/TODAY) — пересчитываются при любом изменении. Тут виноват не макрос, а книга.

Как это работает

VBA — отдельная среда, встроенная в Excel, и общается она с ним через слой объектной модели (это тот самый Application, Workbook, Range). Каждое обращение к свойству вроде .Value — не «чтение переменной», а полноценный вызов внутрь Excel: с проверкой прав, поиском объекта, упаковкой значения. Стоимость одного вызова почти не зависит от объёма данных — поэтому один вызов на 50 000 ячеек обходится примерно как один вызов на одну ячейку. Отсюда и весь фокус массива.

Пересчёт формул устроен так же безжалостно: в режиме xlCalculationAutomatic запись в ячейку заставляет Excel обойти граф зависимостей и обновить всё, что от неё зависит. Если рядом стоит столбец ВПР на 50 000 строк, вы пересчитываете его 50 000 раз вместо одного. xlCalculationManual просто откладывает пересчёт до конца макроса.

Что не ускоряет: замена Integer на Long, «оптимизация» арифметики, DoEvents. Всё это шум на фоне обращений к листу.

Частые ошибки

Не вернули Calculation = xlCalculationAutomatic. Формулы в книге замирают. Пользователь уверен, что макрос испортил файл, — и он почти прав.
Восстановление настроек только в конце, без обработчика ошибок. Макрос упал на середине — Excel остался «мёртвым». Всегда используйте блок Завершение.
Забыли, что массив 1-based и двумерный. исходные(0, 0) даст ошибку «Subscript out of range».
Диапазон из одной ячейки. Range("A2").Value вернёт не массив, а одно значение, и UBound упадёт. Если строк может быть ровно одна — проверяйте это отдельно.
Несовпадение размеров при выгрузке. Массив на 100 строк, а диапазон на 99 — данные обрежутся молча.
EnableEvents = False без возврата. Все события листа в книге перестают работать до перезапуска Excel.
Оптимизация без замера. Полдня переписывали цикл, а тормозило подключение к внешней базе.

Итоги

Тормозит не расчёт, а обращения к листу. Считайте их, а не операции.
Timer до и после — обязательный первый шаг. Без замера оптимизация превращается в гадание.
ScreenUpdating = False и Calculation = xlCalculationManual — две строки, которые дают кратный выигрыш почти в любом макросе.
Чтение диапазона в Variant-массив, расчёт в памяти и выгрузка одним присваиванием ускоряют работу в десятки раз.
Массив из диапазона всегда двумерный и нумеруется с единицы.
Всё, что выключили, обязано включиться обратно — в том числе после ошибки. Это работа блока уборки из прошлого урока.