Как ускорить и оптимизировать код VBA
- Если в коде много Activate и Select, особенно в циклах, от них нужно сразу избавляться. Как это сделать я писал в статье: Выбрать и активировать - зачем они нужны и нужны ли они?
- Обязательно отключите во время выполнения кода:
- автоматический пересчет формул. Чтобы формулы не пересчитывались при каждой манипуляции с листом во время выполнения кода — это может сильно тормозить код, если формул много:
если печать производится внутри кода, то эту строку целесообразно вставлять сразу после строки, печатающей лист (при условии, что печать происходит не в цикле. В данном случае по завершении цикла печати).
На всякий случай можно отключить отображение информации в строке состояния Excel (в каких случаях информация там отображается вообще и почему вы можете узнать в статье: Показать прогресс). Хотя это не использует много ресурсов, иногда это может ускорить код:
Самое главное помнить, что все эти функции должны быть снова включены после . В противном случае могут возникнуть проблемы с работой внутри Excel. Например, если вы забудете включить автоматический пересчет формул, то большинство формул будут пересчитываться исключительно принудительным методом — Shift+F9. А если вы забудете отключить обновление экрана, есть шанс заблокировать себе работу с листами и книгами. Хотя свойство ScreenUpdating должно возвращаться в True по умолчанию, если оно было отключено в процедуре, лучше не полагаться на это и привыкнуть принудительно возвращать все свойства на свои места. На самом деле все сводится к нескольким строчкам:
'Вернуть обновление экрана Application.ScreenUpdating = True 'Вернуть автоматический пересчет формул Application.Calculation = xlCalculationAutomatic 'Включить отслеживание событий Application.EnableEvents = True
Как этот код выглядит на практике? Предположим, вам нужно написать в цикле 10 000 строк значений:
Sub TestOptimize() 'Отключить обновление экрана Application.ScreenUpdating = False 'Отключить автоматический пересчет формул Application.Calculation = xlCalculationManual 'Отключить отслеживание событий Application.EnableEvents = False 'Отключить разбиение на страницы ActiveWorkbook.ActiveSheet.Display cell fillsePageDreaks code lr As Long For lr = от 1 до 10000 клеток(lr, 1). Value = lr 'например, просто пронумеровать строки Next 'Вернуть обновление экрана Application.ScreenUpdating = True 'Вернуть автоматический пересчет формул Application.Calculation = xlCalculationAutomatic 'Включить отслеживание событий приложения. EnableEvents = True EndSub
Разрывы на распечатанных страницах вернуть нельзя — они в любом случае замедляют работу.
Следует избегать циклов типа Do While для поиска последней ячейки. Новички часто допускают эту ошибку. Гораздо эффективнее и быстрее вычислить последнюю ячейку всего листа или в определенном столбце без этого замедляющего цикла Do While я обычно использую
другие варианты определения последней ячейки я подробно описал в статье: Как через VBA определить последнюю ячейку листа?
Для более опытных пользователей VBA я предоставлю несколько решений для оптимизации кода в разных ситуациях:
- Лучшая оптимизация кода, если нужно работать с ячейками листа напрямую, обрабатывать их и возможно менять значения, то быстрее всю обработку делать в массиве и сразу выводить на лист. Например, приведенный выше код для заполнения ячеек числами в этом случае будет выглядеть так:
Sub TestOptimize_Array() 'Прямой код для заполнения ячеек Dim arr, lr As Long 'сохранить все значения 10 000 строк в первом столбце массива одним штрихом arr = Cells(1, 1).Resize(10000) . Значение 'если вам нужно заполнить два или более столбца' arr = Cells(1, 1).Resize(10000, 2).Value 'или 'arr = Range(Cells(1, 1),Cells(10000) , 2)). Value 'или автоматически вычислить последнюю ячейку и заполнить матрицу данных из ячейки A3 'llastr = Cells(Rows.Count, 1).End(xlUp).Row 'последняя ячейка в столбце A 'arr = Range(Cells(3, 1),Cells(llastr, 2)) .Value For lr = 1 To 10000 arr(lr,1) = lr 'Заполнить массив порядковыми номерами Next 'Загрузить обработанный массив обратно на лист в те же ячейки Cells(1 , 1) .Resize(10000).Value = arr End Sub
Но здесь также следует учитывать тот факт, что большие массивы могут просто вызвать переполнение памяти. Это наиболее актуально для 32-битных систем, где для VBA и Excel выделяется меньше памяти, чем в 64-битных системах
- Если вы используете быстрый IF-IIF, замените его на IF. Затем. Еще
- Также лучше использовать один и тот же IF вместо Switch() и Choose() . Затем. Еще
- В большинстве случаев проверка строки на «не пустость» с помощью Len() лучше, чем прямое сравнение с пустой строкой: Len(s)=0 вместо s = "" . Это связано с тем, что работа со строками намного медленнее, чем работа с числовыми данными
- Не используйте конкатенацию строк без необходимости. Например, s = «AB», будет быстрее, чем: s = «A» & «B»
- Не сравнивайте текстовые значения напрямую. Лучше использовать встроенную функцию StrComp:
Если s s1 Тогда будет медленнее, чем
Если StrComp(s, s1, vbBinaryCompare) = 0
и тем более, если сравнение должно быть чувствительным к регистру:
Если LCase(s) LCase(s1) Then будет медленнее, чем
Если StrComp(s, s1, vbTextCompare) = 0 - Циклы For... Next в большинстве случаев выполняются быстрее, чем циклы Do. Лор
- Избегайте присвоения переменных типа Variant. Несмотря на заманчивость, этот тип занимает много памяти и еще больше замедляет код. Для объектных переменных следует по возможности избегать безликого глобального типа Object и использовать конкретный тип:
По мере того как ваши макросы Excel становятся более надежными и сложными, вы можете обнаружить, что они теряют производительность. При обсуждении макросов слово «производительность» обычно является синонимом скорости. Скорость — это то, насколько быстро ваши процедуры VBA выполняют поставленные перед ними задачи. Ниже приведены десять способов, которые помогут вам поддерживать оптимальные уровни производительности макросов Excel.
Расчет листового листа
Знаете ли вы, что каждый раз, когда ячейка, влияющая на формулу на листе, изменяется или обрабатывается, Excel пересчитывает весь лист? В таблицах с большим количеством формул такое поведение может значительно замедлить работу ваших макросов.
Вы можете использовать приложение. Свойство Calculate, чтобы указать Excel переключиться в режим ручного расчета. Когда рабочая книга находится в ручном режиме вычисления, рабочая книга не будет пересчитана, пока вы явно не выполните вычисление, нажав клавишу F9.
Переведите Excel в режим ручного расчета, запустите код, а затем снова переключитесь в режим автоматического расчета.
Возврат к режиму вычислений xlCalculationAutomatic приведет к автоматическому пересчету рабочего листа, поэтому нет необходимости нажимать клавишу F9 после запуска макроса.
Отключение обновления экрана листа
Вы можете заметить, что во время работы макросов на экране появляется небольшое мерцание. Это мерцание означает, что Excel пытается перерисовать экран, чтобы показать текущее состояние рабочего листа. К сожалению, каждый раз, когда Excel перерисовывает экран, он использует ресурсы памяти.
Вы можете использовать приложение. Свойство обновления экрана для отключения обновления экрана до завершения макроса. Отключение обновления экрана экономит время и ресурсы, позволяя макросу работать немного быстрее. После завершения макроса вы можете снова включить обновление экрана.
После того, как свойство ScreenUpdating вернется в значение True, Excel автоматически запустит новое рисование экрана.
Отключение обновлений строки состояния
Строка состояния Excel, которая появляется в нижней части окна Excel, обычно показывает ход выполнения определенных действий в Excel.Если ваш макрос работает с большим количеством данных, строка состояния будет занимать больше ресурсов.
Важно отметить, что отключение обновления экрана отличается от отключения отображения строки состояния. Строка состояния будет продолжать обновляться, даже если вы отключите обновление экрана. Вы можете использовать приложение. DisplayStatusBar, чтобы временно отключить любые обновления в строке состояния, что еще больше повысит производительность вашего макроса:
Указание Excel на игнорирование событий
Вы можете реализовать макросы как процедуры обработки событий, указывая Excel на запуск определенного кода при изменении листа или книги.
Иногда стандартные макросы вносят изменения, которые вызывают процедуру обработки события. Например, если у вас есть стандартный макрос, который манипулирует несколькими ячейками на листе Sheet1, каждый раз, когда изменяется ячейка на этом листе, ваш макрос следует приостанавливать, пока срабатывает событие Worksheet_Change.
Вы можете добавить еще один уровень повышения производительности, используя свойство EnableEvents, чтобы заставить Excel игнорировать события во время выполнения макроса.
Перед запуском макроса задайте для свойства EnableEvents значение False. После завершения макроса вы можете вернуть свойству EnableEvents значение True.
Скрытие разрывов страниц
Каждый раз, когда макрос изменяет количество строк, количество столбцов или макет страницы рабочего листа, Excel вынужден тратить время на перерасчет разрыва страницы на листе.
Этого можно избежать, скрыв разрывы страниц перед запуском макроса.
Задайте для свойства DisplayPageBreaks листа значение False, чтобы скрыть разрывы страниц. Чтобы продолжить отображение разрывов страниц после запуска макроса, установите для свойства листа DisplayPageBreaks значение True.
Приостановка обновлений сводной таблицы
Если ваш макрос управляет сводными таблицами, которые содержат большие источники данных, вы можете столкнуться с низкой производительностью при выполнении таких операций, как динамическое добавление или перемещение полей сводки.
Вы можете повысить производительность своего макроса, приостановив пересчет сводной таблицы до тех пор, пока не будут внесены все изменения в поле сводной таблицы. Просто установите сводную таблицу. Задайте для свойства ManualUpdate значение True, чтобы отложить перерасчет, запустить макрос, а затем настроить сводную таблицу. Свойство ManualUpdate вернется к значению False, чтобы запустить новый расчет.
Очистка от копирования и вставки
Важно помнить, что, хотя Macro Recorder экономит время, записывая код VBA за вас, он не всегда записывает наиболее эффективный код. Хорошим примером является то, как Macro Recorder фиксирует все действия копирования и вставки, которые вы выполняете во время записи.
Вы можете немного улучшить свои макросы, вырезав посредника и выполнив прямую копию из одной ячейки в целевую ячейку. Этот альтернативный код использует аргумент Destination для обхода буфера обмена и копирования содержимого ячейки A1 непосредственно в ячейку B1.
Если вам нужно копировать только значения (а не форматирование или формулы), вы можете еще больше повысить производительность, избегая метода копирования. Просто установите значение целевой ячейки на то же значение, что и в исходной ячейке. Этот метод примерно в 25 раз быстрее, чем метод копирования:
Если вам нужно только скопировать формулы из одной ячейки в другую (а не значения или форматирование), вы можете установить формулу целевой ячейки на ту же формулу, что и в исходной ячейке:
Использование оператора With
При записи макросов вам часто потребуется манипулировать одним и тем же объектом более одного раза. Вы можете сэкономить время и повысить производительность, используя оператор With для выполнения нескольких действий над данным объектом в одном снимке.
Оператор With, используемый в следующем примере, указывает Excel применить все изменения форматирования сразу:
Привычка чередовать действия в операторах With не только ускорит выполнение макросов, но и облегчит чтение кода макроса.
Избегание метода Select
Средство записи макросов предпочитает использовать метод Select для явного выбора объектов перед выполнением каких-либо действий с ними. Как правило, нет необходимости выбирать объекты перед работой с ними. Фактически, вы можете значительно повысить производительность макросов, не используя метод Select.
После регистрации макросов сделайте привычкой изменять сгенерированный код, чтобы удалить методы Select. В этом случае оптимизированный код будет выглядеть так:
Обратите внимание, что ничего не выбрано. Код просто использует иерархию объектов для выполнения необходимых действий.
Ограничение ссылок на рабочий лист
Еще один способ ускорить работу макросов — ограничить количество ссылок на данные электронных таблиц в вашем коде. Извлекать данные из электронной таблицы всегда менее эффективно, чем из памяти. Другими словами, ваши макросы будут работать намного быстрее, если им не придется постоянно взаимодействовать с электронной таблицей.
Например, следующий простой код заставляет VBA постоянно возвращаться к листу ("Sheet1"). Range("A1"), чтобы получить число, необходимое для сравнения, выполняемого в выражении If:
Более эффективным способом является сохранение значения на листе ("Sheet1"). Range("A1") в переменную MyMonth. Таким образом, код ссылается на переменную MyMonth вместо рабочего листа:
Рассмотрите возможность использования переменных для работы с данными в памяти вместо прямого обращения к электронным таблицам.
Ускоряем выполнение VBA кода, а так же убираем ненужные уведомления (всплывающие окна).
Для ускорения выполнения кода VBA в Excel, а также скрыть мерцание экрана, ненужные пересчеты формул, различные уведомления, на которые приходится нажимать для продолжения выполнения кода и которые часто раздражают (подтверждение открытия файлов, сохранение данных в виде текст и т.д.), а также можно убрать ненужный пересчет формул, происходящий постоянно при обновлении данных, на которые они ссылаются, можно использовать следующие команды, которые нужно добавить либо в начало кода, либо перед конкретная часть, которая вам нужна (если вы знаете).
Excel.Application.ScreenUpdating = False 'отключает обновление экрана. Пользоваться можно почти всегда - дополнительная нагрузка на процессор.
Excel.Application.DisplayAlerts = False 'отключает всплывающие окна. Совсем. Используйте осторожно.
Excel.Application.EnableEevents = False 'отключает обработку событий в Excel. События должны быть отключены, чтобы избежать зацикливания или выполнения незапланированных действий. Например, при вводе значения на лист макросом и ненужном (ненужном) выполнении другого макроса для обработки события добавления значения на лист - который, как вариант, предназначен для ручного ввода. Используйте только при необходимости.
Excel.Application.Calculation = xlCalculationManual 'удалить автовычисление формул и их зависимых объектов. Его можно и нужно использовать практически всегда — дополнительная нагрузка на процессор, особенно если в формулах используются массивы или поиски.
