Как ускорить и оптимизировать код 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.DisplayPageBreaks code = False lr As Long От 1 до 10000 ячеек (lr, 1) .Value = lr ‘например, числовые строки Далее’ Возврат обновления экрана Application.ScreenUpdating = True ‘Возврат автоматического пересчета формул приложения .Calculation = xlCalculationAutomatic’ Включить события мониторинга приложений. EnableEvents = True End Sub
Слезы на распечатанных страницах не подлежат возврату: они в любом случае замедляют работу.
Вам следует избегать циклов типа Do While, чтобы найти последнюю ячейку. Новички часто совершают эту ошибку. Гораздо эффективнее и быстрее вычислить последнюю ячейку на всем листе или в конкретном столбце без этого торможения цикла Do While, который я обычно использую
я подробно описал другие варианты определения последней ячейки в статье: Как определить последнюю ячейку на листе с помощью VBA?
Для более опытных пользователей VBA я предоставлю несколько решений по оптимизации кода в различных ситуациях:
- Лучшая оптимизация кода, если вам нужно работать напрямую с ячейками листа, обрабатывать их и, возможно, изменять их значения, тогда быстрее выполнить всю обработку в массиве и сразу загрузить их на лист. Например, приведенный выше код для заполнения ячеек числами в этом случае будет выглядеть так:
Sub TestOptimize_Array () ‘Непосредственно код для заполнения ячеек Dim arr, lr As Long’ мы сохраняем все значения 10000 строк первого столбца arr = Cells (1, 1) .Resize (10000) .Value в массив за один раз столбцы ‘arr = Cells (1, 1) .Resize (10000, 2) .Value’ или ‘arr = Range (Cells (1, 1), Cells (10000, 2)). Value ‘o автоматически вычисляет последнюю ячейку и вставляет массив данных, начиная с ячейки 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 ‘fill массив с порядковыми номерами Далее ‘Перезагрузить обработанный на листе массив в те же ячейки 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), то она будет медленнее, чем
Если StrComp (s, s1, vbTextCompare) = 0 - For… Последующие циклы в большинстве случаев быстрее, чем циклы Do. Loor
- Избегайте присвоения переменных Variant. Хотя соблазн велик, этот тип занимает много памяти и еще больше замедляет код. Кроме того, для объектных переменных следует по возможности избегать безличного глобального типа Object и использовать определенный тип:
Как отключить обновление экрана для другого приложения (блокнот)
4 Бадан [2016-12-09 12:55:00]
Я написал макрос в vba, который открывает текстовый файл с помощью блокнота, выбирает весь текст и копирует его в Excel. Таким образом мне приходится обрабатывать около 100 файлов в день, и я хочу избавиться от мерцающих изображений, которые я вижу. Код работает, но проблема в том, что команда
Не работает с ноутбуком. Я могу использовать только нормальный фокус, иначе код не работает. Как я могу запустить следующий код, не замечая, что файл Блокнота открыт и обрабатывается?
Я не нашел рабочего решения.
блокнот Excel-VBA CSV
Вот быстрый ответ о том, как запустить Блокнот, но скрыть окно и использовать vbHide вместо vbNormalFocus в команде Shell :
Но я очень сомневаюсь, что SendKeys будет работать с невидимым окном.
Итак, это не отвечает на вопрос, но планируете ли вы использовать FileSystemObject и просто читать файл, не открывая Notepad.exe ?
Код будет работать, например, с диакритическими символами в моем тестовом текстовом файле:
Насколько мне известно, Application.ScreenUpdating применимо только к вашему сеансу Excel, а не к другим программам, работающим в Windows. Итак, чтобы фактически предотвратить появление этих окон, но вы все еще можете прочитать содержимое окна, вам нужно сделать что-то довольно сложное с Windows API. Использовать FileSystemObject намного проще.
0 Бадан [2016-12-12 11:37:00]
Наконец-то я нашел очень элегантное решение, которое может работать с внешними приложениями. Все, что вам нужно, это Роберт Шутт, чтобы написать этот шедевральный код. Это делает блокнот размером 1 пиксель, поэтому мерцающие изображения не наблюдаются. Для меня это звучит довольно сложно, но это спасло мне день:
How to dou
По мере того, как макросы Excel становятся более надежными и сложными, вы можете обнаружить, что они теряют производительность. Когда дело доходит до макросов, производительность обычно является синонимом скорости. Скорость — это скорость, с которой ваши подпрограммы VBA выполняют свои намеченные задачи. Вот десять способов сохранить макросы Excel на оптимальном уровне производительности.
Расчет листового листа
Знаете ли вы, что каждый раз, когда ячейка, которая влияет на формулу в вашей электронной таблице, изменяется или обрабатывается, Excel пересчитывает весь рабочий лист? В таблицах с множеством формул такое поведение может значительно замедлить работу макросов.
Вы можете использовать приложение. Вычислить свойства, чтобы Excel переключился в ручной режим вычислений. Когда книга находится в ручном режиме вычислений, она не будет пересчитана до тех пор, пока вы явно не выполните вычисление, нажав клавишу F9.
Переведите Excel в режим ручного расчета, запустите код, а затем вернитесь в режим автоматического расчета.
Установка режима расчета на xlCalculationAutomatic автоматически пересчитает рабочий лист, поэтому вам не нужно нажимать клавишу F9 после запуска макроса.
Отключение обновления экрана листа
Вы можете заметить, что во время работы макроса на экране появляется небольшое мерцание. Это мерцание означает, что Excel пытается перерисовать экран, чтобы показать текущий статус рабочего листа. К сожалению, каждый раз, когда Excel перерисовывает экран, он занимает ресурсы памяти.
Вы можете использовать приложение. ScreenUpdating, чтобы отключить обновление экрана до завершения макроса. Отключение обновления экрана экономит время и ресурсы, позволяя макросу работать немного быстрее. После завершения макроса вы можете повторно включить обновление экрана.
После того, как свойство 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 фиксирует любые действия копирования и вставки, выполняемые во время записи.
Вы можете немного расширить свои макросы, удалив посредника и сделав прямую копию из одной ячейки в целевую ячейку. Этот альтернативный код использует аргумент «Назначение» для обхода буфера обмена и копирования содержимого ячейки A1 непосредственно в ячейку B1.
Если вам нужно скопировать только значения (а не форматирование или формулы), вы можете еще больше повысить производительность, отказавшись от метода копирования. Просто установите для целевой ячейки то же значение, что и для исходной ячейки. Этот метод примерно в 25 раз быстрее, чем использование метода копирования:
Если вам просто нужно скопировать формулы из ячейки в ячейку (не значения или форматирование), вы можете установить формулу целевой ячейки на ту же формулу, которая содержится в исходной ячейке:
Использование оператора With
При записи макроса вы часто будете манипулировать одним и тем же объектом более одного раза. Вы можете сэкономить время и повысить производительность, используя оператор With для выполнения нескольких действий с данным объектом в одном снимке.
Оператор With, используемый в следующем примере, сообщает Excel применить все изменения форматирования сразу:
Привычка чередовать действия в операторах With не только ускоряет выполнение макросов, но и облегчает чтение кода макроса.
Избегание метода Select
Средство записи макросов предпочитает использовать метод Select для явного выбора объектов перед выполнением над ними действий. Как правило, перед работой с ними выделять объекты не требуется. Фактически, вы можете значительно улучшить производительность макросов без использования метода Select.
После записи макросов возьмите за привычку редактировать сгенерированный код, чтобы удалить методы Select. В этом случае оптимизированный код будет выглядеть так:
Обратите внимание, что ничего не выбрано; код просто использует иерархию объектов для выполнения действия.
Ограничение поездок на рабочий лист
Другой способ ускорить выполнение макросов — ограничить количество ссылок на данные рабочего листа в коде. Всегда менее эффективно получать данные с листа, чем из памяти. Другими словами, ваши макросы будут работать намного быстрее, если им не придется взаимодействовать с таблицей несколько раз.
Например, следующий простой код заставляет VBA постоянно возвращаться к листам («Sheet1»). Диапазон («A1»), чтобы получить число, необходимое для сравнения, выполняемого в операторе If:
Более эффективный способ — сохранить значение в листах («Sheet1»). Диапазон («A1») для переменной MyMonth. Таким образом, код ссылается на переменную MyMonth вместо рабочего листа:
Рассмотрите возможность использования переменных для работы с данными в памяти вместо того, чтобы напрямую обращаться к рабочим листам.
Excel vba: Почему обновление экрана не обновляется, прежде чем отключать обновление экрана.
У меня есть подпрограмма, которая начинается с вызова другой подпрограммы (которая редактирует ячейки в книге), а затем отключает обновление экрана и предупреждения. Дело в том, что первый юг, который редактирует книга, находится перед вторым югом, ячейка устарела.
Когда я выключаю второй элемент, ячейка обновляется. Как мне обновить ячейку и таким образом отключить обновление экрана?
РЕДАКТИРОВАТЬ: экран обновления уже правильный при запуске mainSub; Комментирование подпрограммы setupApp помогает, потому что она запускается явно до того, как ячейки будут изменены немного позже.
Ускоряем выполнение VBA кода, а так же убираем ненужные уведомления (всплывающие окна).
Чтобы ускорить выполнение кода VBA в Excel, помимо скрытия мерцания экрана, ненужных пересчетов формул, различных уведомлений, которые необходимо щелкнуть, чтобы продолжить выполнение кода и которые часто раздражают (подтверждение открытия файла , данные, сохраненные в виде текста и т д.), а также убрать ненужный пересчет формул, который постоянно возникает при обновлении данных, на которые они ссылаются, можно использовать следующие команды, которые необходимо добавлять в начале кода или перед конкретный желаемый раздел (если вас проинформировали).
Excel.Application.ScreenUpdating = False ‘удалить обновление экрана. Его можно использовать практически всегда — лишняя нагрузка на процессор.
Excel.Application.DisplayAlerts = False ‘убрать всплывающие окна. Вообще. Используйте с осторожностью.
Excel.Application.EnableEevents = False ‘Удалить обработку событий в Excel. События должны быть отключены, чтобы избежать незапланированных циклов или действий. Например, когда макрос добавляет значение на лист и ненужное (ненужное) выполнение другого макроса для обработки события добавления значения на лист, который, как вариант, предназначен для ввода вручную. Используйте только при необходимости.
Excel.Application.Calculation = xlCalculationManual ‘удаляет автоматический пересчет формул и их зависимых объектов. Его можно и нужно использовать практически всегда — дополнительная нагрузка на процессор, особенно если в формулах используются массивы или поиски.
Не забудьте ОБЯЗАТЕЛЬНО вернуть все, чтобы пользователи не ругались =) :
Excel.Application.ScreenUpdating = True
Excel.Application.DisplayAlerts = True
Excel.Application.EnableEvents = Истина
Excel.Application.Calculation = xlCalculationAutomatic