MyTetra Share
Делитесь знаниями!
Быстродействие выполнения процедур на VBA.
29.07.2019
23:56
Текстовые метки: VBA быстродействие
Раздел: !Закладки - VBA

Очень часто мы сталкиваемся с ситуацией, когда макросы выполняются очень медленно, и требуется повысить быстродействие выполнения процедур на 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

Так же в этом разделе:
 
MyTetra Share v.0.52
Яндекс индекс цитирования