Как ускорить работу макроса vba excel

Ускоряем работу макросов

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

Ускорить работу макроса в сотни раз можно, отключив на время его действия различные функции Excel:

'Ускоряем Excel путём отключения всего "тормозящего" Public Sub AccelerateExcel() 'Больше не обновляем страницы после каждого действия Application.ScreenUpdating = False 'Расчёты переводим в ручной режим Application.Calculation = xlCalculationManual 'Отключаем события Application.EnableEvents = False 'Не отображаем границы ячеек If Workbooks.Count Then ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False End If 'Отключаем статусную строку Application.DisplayStatusBar = False 'Отключаем сообщения Excel Application.DisplayAlerts = False End Sub

Разумеется, необходимо по окончании работы восстановить функциональность Excel, иначе пользоваться программой будет, мягко говоря, неудобно:

'Включаем всё то что выключили процедурой AccelerateExcel Public Sub disAccelerateExcel() 'Включаем обновление экрана после каждого события Application.ScreenUpdating = True 'Расчёты формул - снова в автоматическом режиме Application.Calculation = xlCalculationAutomatic 'Включаем события Application.EnableEvents = True 'Показываем границы ячеек If Workbooks.Count Then ActiveWorkbook.ActiveSheet.DisplayPageBreaks = True End If 'Возвращаем статусную строку Application.DisplayStatusBar = True 'Разрешаем сообшения Excel Application.DisplayAlerts = True End Sub

Функции AccelerateExcel и disAccelerateExcel легли в основу надстройки Excelerate It!.

Нюанс

Бывает что макросы аварийно завершают работу. Если перед ними вызывалась процедура AccelerateExcel, то те изменения которые она внесла в программу Excel не будут отменены – потому что очередь до disAccelerateExcel просто не дойдёт.

Как в этом случае «починить» Excel?

Во-первых, можно запустить disAccelerateExcel из списка макросов.

Другой способ – назначить эту процедуру для события Open книги с макросами. Тогда работоспособность будет восстанавливаться автоматически при открытии книги.

И, наконец, лучше всего установить надстройку Excelerate It!, которая позволит применять AccelerateExcel и disAccelerateExcel в любой момент.

Как ускорить выполнение VBA

Для большого количества преобразований в Excel-ких таблицах очень актуальным становится скорость выполнения макроса. Кроме оптимизации кода, я так же нашел для себя способ, как значительно ускорить выполнение кода на VBA. Для этого можно использовать 2 процедуры. Одна отключает «лишние» функции, замедляющие Excel, а вторая обратно всё включает, когда все готово.

Ускорение кода на VBA путем выключения ненужных функций

Public Sub AccelerateBegin() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False ActiveSheet.DisplayPageBreaks = False Application.DisplayStatusBar = False Application.DisplayAlerts = False End Sub

Что в этой процедуре делается:

1. Отключается обновление экрана при выполнении программы
2. Отключается автоматическое вычисление формул
3. Отключение событий
4. Отключение отображение разрывов страниц
5. Отключение строки статуса
6. Отключение сообщений Excel-я

Включение функций, когда все необходимые действия выполнены

Public Sub AccelerateEnd() Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True ActiveSheet.DisplayPageBreaks = True Application.DisplayStatusBar = True Application.DisplayAlerts = True End Sub

Теперь, если есть долго выполняющийся код, можно сделать так:

AccelerateBegin ' какие-то действия с таблицами, занимающие много времени AccelerateEnd

Обратите внимание: нужно иметь в виду, что если в коде между AccelerateBegin и AccelerateEnd произойдет ошибка, все отключенные функции Excel-я так и останутся отключенными, т.к. выполнение кода не дойдет до AccelerateEnd. В случае, если такое произошло, тогда чтобы всё восстановить, можно выполнить AccelerateEnd отдельно от остального, поставив курсор в текст этой процедуры в редакторе VBA и нажав «Выполнить». Либо, можно назначить этот макрос на какую-нибудь кнопку.

Как ускорить работу макроса vba excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Ускоряем работу VBA в Excel

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

Перед началом работы

Перед тем, как перейти прямо к сути, я хотел бы обратить внимание на пост: Несколько советов по работе с VBA в Excel. В частности, в блоке “Ускорение работы макросов” есть полезные примеры кода, которые стоит использовать вместе с моими советами по ускорению работы, для достижения максимального результата.

Ускоряем работу макроса

Итак, к сути… Для того что бы реально ускорить работу VBA в Ecxel нужно понимать, что обращение к ячейке на листе — занимает значительно время. Если Вы хотите записать в ячейку одно значение, то это не займет значительного времени, но если Вам потребуется записать(прочитать, обратиться) к тысячам ячеек, то это потребует гораздо большего времени. Что же делать в таких случаях? На помощь приходят массивы. Массивы хранятся в памяти, а операции в памяти VBA выполняет в сотни, а то и в тысячи раз быстрее. Поэтому, если у Вас в данных тысячи, сотни тысяч значений, то время выполнения макроса может занимать от нескольких минут до нескольких часов, а если эти данные перенести в массив, то выполнение макроса может сократиться до нескольких секунд (минут).

Я наведу пример кода и в комментариях объясню что к чему, так будет яснее. К тому же, могут пригодиться некоторые строки кода, не относящееся прямо к процессу ускорения.

Пример

Предположим, что у нас есть данные на “Лист1” (“Sheet1”). Данные содержаться в 50 колонках (колонки содержат названия) и 10 000 строк. К примеру, нам нужно в последнюю колонку внести значение, которое равно значению во второй колонке, деленное на значение в третьей колонке (начиная со 2-й строки, так как первая содержит заглавие). Потом мы возьмем первые 10 колонок и скопируем их на “Лист2” (“Sheet2”), для дальнейшей обработки (для других потребностей). Пусть пример и банальный, но, как мне кажется, он может отобразить всю суть данного поста.

'Для явной инициализации переменных, включаем эту опцию 'Это поможет избежать многих ошибок Option Explicit Sub Test() 'К листам будем обращаться через переменные Dim Sheet1_WS, Sheet2_WS As Worksheet 'Переменная для прохождения срок на листе (в массиве) Dim i As Long 'Массив, в котором будут храниться наши данные Dim R_data As Variant 'Переменные последней строки и колонки Dim FinalRow, FinalColumn As Long 'Можно инициализировать лист не по названию, а по порядковому номеру 'Set Sheet1_WS = Application.ThisWorkbook.Worksheet("Sheet1") Set Sheet1_WS = Application.ThisWorkbook.Sheets(1) Set Sheet2_WS = Application.ThisWorkbook.Sheets(2) 'Поиск последней не пустой строки в первой колонке 'Нужно, что бы данные не были отфильтрованы, иначе последняя строка будет последней строкой в фильтре 'Также в последней строке, в первой колонке, не должно быть пустой ячейки. Конечно, если в этой строке вообще есть данные. Иначе последней строкой будет последняя не пустая ячейка. FinalRow = Sheet1_WS.Cells(Rows.Count, 1).End(xlUp).Row '=10 000 'Поиск последней не пустой колонки в первой строке FinalColumn = Sheet1_WS.Cells(1, Columns.Count).End(xlToLeft).Column '=50 'Присваиваем массиву диапазон данных на Листе 1 R_data = Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, FinalColumn)) For i = 2 To FinalRow 'Выполняем нужные нам операции с данными. 'Проверяем, что бы не было деления на ноль. 'Предполагается, что в колонке 2 и 3 стоят числовые данные 'Иначе потребуется обработка ошибок If R_data(i, 3) <> 0 Then R_data(i, FinalColumn) = R_data(i, 2) / R_data(i, 3) End If Next i 'Копируем данные из массива обратно на Лист1 'Перед этим очищаем данные на листе (если есть форматирование или формулы, то лучше Sheet1_WS.Cells.ClearContents) Sheet1_WS.Cells.Delete Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, FinalColumn)) = R_data 'Копируем данные на Лист2, копируем первые 10 колонок. Sheet2_WS.Range(Sheet2_WS.Cells(1, 1), Sheet2_WS.Cells(FinalRow, 10)) = R_data 'Закрываем книгу и сохраняем её Workbooks(Application.ThisWorkbook.Name).Close SaveChanges:=True End Sub 

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

Dim R_new() As Variant . ' Явно указываем размер массива ReDim R_new(1 To FinalRow, 1 To 50) As Variant . Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, 50)) = R_new() 

Заключение

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

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

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

Спасибо за внимание. Удачных разработок.