Ускорение макросов

Всем привет, меня зовут Андрей, и это снова я!

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

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

• Приведем пример. Допустим, что надо заполнить таблицу умножения размером 100 на 100. Допустим, что в первой строке у нас будут числа первого множителя (начиная со второго столбца, или столбца B). Числа второго множителя будут в первом столбце, начиная от строки 2.

Вначале заполним эти сами исходные цифры, о которых мы только что сказали, сделаем это с помощью одного макроса:

Sub Исходные_ТУ_100_100()

For i = 2 To 101

Cells(1, i).Value = i - 1

Cells(i, 1).Value = i - 1

Next i

End Sub

Затем заполним таблицу умножения 100 на 100 формулами, содержащими ссылку на те ячейки, что расположены в строке 1 и в столбце 1 (то есть те самые исходные данные, что мы недавно ввели). Переменная tim будет означать время выполнения макроса в секундах, мы это время поместим в ячейку A1. Вот текст нужного макроса:

Sub ТУ_100_скор_1()

tim = Timer()

For i = 2 To 101

For j = 2 To 101

Cells(i, j).Select

ActiveCell.FormulaR1C1 = "=RC1*R1C"

Next j

Next i

tim = Timer() - tim

Cells(1, 1).Value = tim

End Sub

Рис. 1. Выполнение макроса на "первой скорости".

Мы видим, что время выполнения макроса – больше, чем полторы минуты, оно видно в ячейке A1.

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

Cells(i, j).Select

ActiveCell.FormulaR1C1 = "=RC1*R1C"

Добавим одну строку:

Cells(i, j).FormulaR1C1 = "=RC1*R1C"

Вот текст нового макроса:

Sub ТУ_100_скор_2()
tim = Timer()
For i = 2 To 101
For j = 2 To 101
Cells(i, j).FormulaR1C1 = "=RC1*R1C"
Next j
Next i
tim = Timer() - tim
Cells(1, 1).Value = tim
End Sub
Смысл произведенной замены простой: мы всего лишь только избавились от оператора Select, что означает «выделить ячейку». Если есть возможность избавиться от операторов Select или Activate, это нужно сделать обязательно.

Получится следующий результат:

Рис. 2. Выполнение макроса на "второй скорости".

Как видим, скорость изменилась в несколько раз, но и это еще не предел, ведь мы так и не избавились от формул, содержащих ссылки на ячейки эксель. Если мы избавимся от формул внутри ячеек эксель, но формулы в редакторе VBA запишем таким образом, чтобы внутри ячеек эксель оказались не формулы, а конечные результаты, тогда скорость выполнения макроса увеличится еще больше.

• Вот текст нового макроса:

Sub ТУ_100_скор_3()

tim = Timer()

For i = 2 To 101

For j = 2 To 101

Cells(i, j).Value = Cells(i, 1) * Cells(1, j)

Next j

Next i

tim = Timer() - tim

Cells(1, 1).Value = tim

End Sub

Вот результат выполнения этого макроса (как и в предыдущих случаях, мы показываем только фрагмент листа эксель, содержащий время выполнения макроса в ячейке A1):

Рис. 3 Выполнение макроса на "третьей скорости".

Но и это еще не предел скорости. Если мы в качестве исходных данных для таблицы умножения будем брать не цифры из ячеек эксель, а данные из массива, то скорость выполнения макроса увеличится еще больше. В основном для всех случаев в качестве элементов массива нужно продублировать данные из ячеек эксель. Но иногда (а у нас именно тот самый случай) исходные данные можно ввести без привязки к числам эксель (просто у нас все исходные данные – это числа от 1 до 100). Но в любом другом случае замена исходных данных путем создания массива ускорит работу макроса.

• Вот текст модернизированного макроса:

Sub ТУ_100_скор_4()

tim = Timer()

For i = 2 To 101

For j = 2 To 101

Cells(i, j).Value = (i - 1) * (j - 1)

Next j

Next i

tim = Timer() - tim

Cells(1, 1).Value = tim

End Sub

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

Вот результат:

Рис. 4 Выполнение макроса на "четвертой скорости".

Но есть и еще несколько причин, которые замедляют работу как эксель в целом, так и работу каждого макроса:

1. Обновление экрана.

2. Автоматический пересчет формул.

3. Отслеживание событий.

4. Разбивка на печатные страницы.

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

• Первый способ: сначала нужно выполнить отдельный макрос, который нейтрализует (отключает) все то, что замедляет работу эксель, потом выполнить основной макрос (тот самый, который нам нужно ускорить), и затем выполнить еще один макрос, который снова включает все то, что было отключено первым макросом. При этом разбивку на печатные страницы можно не возвращать совсем, потому что она в любом случает будет замедлять работу эксель, а на достоверность и правильность информации в ячейках разбивка на страницы никак не влияет.
• Второй способ – все делать в одном макросе. Просто поместить в начало того макроса, который нужно ускорить, те строки, которые ускоряют работу эксель, а в конец этого же макроса – те строки, которые снова ее замедляют (в нашем конкретном случае мы выберем именно этот способ).

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

Вот текст того макроса, который включает ускорение:

Sub Ускорение_включить()

'отключаем обновление экрана

Application.ScreenUpdating = False

'Отключаем автоматический пересчет формул

Application.Calculation = xlCalculationManual

'Отключаем отслеживание событий

Application.EnableEvents = False

'Отключаем разбивку на печатные страницы

ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False

End Sub

А вот текст того макроса, который выключает ускорение:

Sub Ускорение_выключить()

'Возвращаем обновление экрана

Application.ScreenUpdating = True

'Возвращаем автоматический пересчет формул

Application.Calculation = xlCalculationAutomatic

'Включаем отслеживание событий

Application.EnableEvents = True

End Sub

Если строки с ремарками (строки, перед которыми стоят одинарные кавычки) мешают, эти строки можно удалить, работа макроса от этого не изменится.

• Вот текст того макроса, который позволит получить таблицу умножения 100 на 100 на максимальной скорости:

Sub ТУ_100_скор_5()

tim = Timer()

'Ускорение_включить()

'отключаем обновление экрана

Application.ScreenUpdating = False

'Отключаем автоматический пересчет формул

Application.Calculation = xlCalculationManual

'Отключаем отслеживание событий

Application.EnableEvents = False

'Отключаем разбиение на печатные страницы

ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False

For i = 2 To 101

For j = 2 To 101

Cells(i, j).Value = (i - 1) * (j - 1)

Next j

Next i

'Ускорение_выключить()

'Возвращаем обновление экрана

Application.ScreenUpdating = True

'Возвращаем автоматический пересчет формул

Application.Calculation = xlCalculationAutomatic

'Включаем отслеживание событий

Application.EnableEvents = True

tim = Timer() - tim

Cells(1, 1).Value = tim

End Sub

А вот и конечный результат:

Рис. 5 Выполнение макроса на "пятой скорости".

Итак, с помощью нескольких нехитрых уловок полторы минуты превращаются в отрезок времени короче секунды. Это даже несмотря на то, что во время включения/выключения дополнительного ускорения мы не выключали счетчик времени.

В принципе, почти вся работа эксель заключается в преобразовании исходных данных и получении на их основе какого-то конечного результата.

Но нужно иметь ввиду: если взаимосвязь между исходными данными и конечным результатам происходит не от ячейки к ячейке, а от массива-дублера тех исходных данных, что находятся в ячейках эксель, тогда не будет происходить автоматического изменения в конечных результатах сразу при изменении исходных данных. В этом есть свои и плюсы, и минусы. Главный плюс – в увеличении скорости. Но если сразу после ввода всех измененных исходных данных снова запустить тот макрос, который устанавливает зависимость между исходными данными и конечным результатом, тогда произойдет обновление всех конечных результатов. Это и есть главный минус ускорения – придется несколько раз запускать «быстрый» макрос. Но этот минус с лихвой окупается слишком большой скоростью.

На этом пока все, и до новых встреч на дзене!