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

Можно отметить два способа повышения производительности: ленивый и трудоемкий.

Ленивый способ заключается в добавлении в начале кода процедуры двух строчек и в конце кода еще двух строчек. Для этого мы будем манипулировать двумя свойствами объекта Application: ScreenUpdating и Calculation. Сначала мы отключим прорисовку экрана и автовычисление, а в конце включим обратно.

Sub Test

   Application.ScreenUpdating = False

   Application.Calculation = xlCalculationManual

      '<код процедуры>

   Application.Calculation = xlCalculationAutomatic

   Application.ScreenUpdating = True

End Sub

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

Трудоемкий способ. Но иногда требуется повысить быстродействие выполнения макросов на несколько порядков. Это ситуации, когда требуется выполнять код за милисекунды (тысячные доли секунд) или обрабатывать большие объемы данных – миллион строк за пару минут. В таком случае придется полностью переписывать код процедуры. Чтобы не заниматься переделками надо изначально построить код процедуры следуя советам.

Совет 1. Объекты – самое медленное звено

VBA очень медленно обрабатывает объекты. Поэтому стараемся использовать как можно меньше объектов. А если мы используем объект в коде, то минимизируем количество обращений к нему.

Наиболее типичная ситуация обращение к ячейкам. Если код обращается к сотне ячеек, то это работает быстро, но когда счет идет на сотни тысяч ячеек, то быстродействие падает катастрофически.

Пример кода

For i = 1 To 1000000

   Cells(i, 1).Value = 1

Next i

Оптимизируем код

Range(Cells(1,1), Cells(1000000,1)).Value = 1

Данный код сработает значительно быстрее, т.к. в первом случае мы миллион раз обращались к объектам – ячейкам, в последнем случае, обращаемся только к трем объектам (диапазон – range и две ячейки cells).

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

tmpValue = Cells(1, 1).Value

и в дальнейшем в коде процедуры обращаться к переменной tmpValue.

Совет 2. Ипсользование переменных и массивов

Собственно второй совет, является следствие первого совета. Поэтому работу кода процедуры надо разделить на три этапа.

Первый – считываем данные из ячеек в массивы или переменные.

Второй – производим необходимую обработку с переменными и массивами.

Третий – записываем полученные значения в ячейки.

Обработка единичных значений ячеек с помощью переменной

Dim TmpValue 'Объявляем переменную

tmpValue = Cells(1, 1).Value 'Считываем значение

'<Код обработки переменных>

Cells(2, 2).Value = tmpValue 'Записываем значение

Обработка диапазона ячеек с помощью массива. По сути диапазон ячеек представляет собой двумерный массив.

Dim tmpDynamicArray() 'Динамический массив

Set ngTable = Range(Cells(1, 1), Cells(10, 5))

tmpDynamicArray() = ngTable1.Value

Если мы заранее не знаем размеры исходного диапазона, то считать значения можно в динамический массив.

Если мы будем считывать значения в статичный массив, то надо предусмотреть, чтобы размеры массива и диапазона соответствовали друг другу.

Обращение к переменным или перебор значений массива происходит значительно быстрее, чем ячейкам. Особенно это заметно на больших объемах.

Чтобы записать значения в диапазон из двумерного массива, надо указать диапазон, соответствующий исходному массиву.

RowsCount = Ubound(tmpDynamicArray,1) - Ubound(tmpDynamicArray,1)

ColumnsColumns = Ubound(tmpDynamicArray,2) - Ubound(tmpDynamicArray,2)

FirstRow=1

FirstColumn=1

LastRow = FirstRow + RowsCount

LastColumn = First Column+ ColumnsCount

Range(Cells(FirstRow, FirstColumn), Cells(LastRow, LastColumn)).Value = tmpDynamicArray

Совет 3. Циклы

Перебор объектов коллекций объектов с помощью цикла

For each ... in ...

Next

Работает значительно быстрее цикла

For i = ... To ...

Next i

Совет 4. Быстрый объект Dictionary

Все таки среди объектов VBA есть одно исключение – объект Dictionary.

Обращение к данному объекту происходит значительно быстро.

Совет 5. Подключение к внешним источникам данных

Использование запросов к внешним базам данных также занимает значительное время. Поэтому старайтесь по возможности, реже считывать и подключаться к внешним базам данных. Желательно, чтобы SQL-запрос сразу возвращал нужные данные.

Совет 6. Логика алгоритма

Конечно, старайтесь оптимизировать логику алгоритма.

© Тимур Пустогачев 2016