Excel vba отключить обновление экрана

Как ускорить и оптимизировать код VBA

Если в коде есть много всяких Activate и Select , тем более в циклах — следует немедленно от них избавиться. Как это сделать я писал в статье: Select и Activate — зачем нужны и нужны ли?
Обязательно на время выполнения кода отключить:
- автоматический пересчет формул . Чтобы формулы не пересчитывались при каждой манипуляции на листе во время выполнения кода — это может дико тормозить код, если формул много:

если печать производится внутри кода, то эту строку желательно вставить сразу после строки, выводящей лист на печать(при условии, что печать не происходит в цикле. В этом случае — по завершению цикла печати).
На всякий случай можно отключить отображение информации в строке статуса 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 = False ‘Непосредственно код заполнения ячеек Dim lr As Long For lr = 1 To 10000 Cells(lr, 1).Value = lr ‘для примера просто пронумеруем строки Next ‘Возвращаем обновление экрана Application.ScreenUpdating = True ‘Возвращаем автопересчет формул Application.Calculation = xlCalculationAutomatic ‘Включаем отслеживание событий Application.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 ‘или автоматически вычисляем последнюю ячейку и заносим в массив данные, начиная с ячейки А3 ‘llastr = Cells(Rows.Count, 1).End(xlUp).Row ‘последняя ячейка столбца А ‘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-битных системах

Если используете быстрый ЕСЛИ — IIF , то замените его на IF . Then . Else

Так же лучше вместо Switch() и Choose() применить тот же IF . Then . Else

В большинстве случаев проверять строку на «не пусто» лучше через Len() , чем прямое сравнение с пустотой: Len(s)=0 вместо s = «» . Связано с тем, что работа со строками значительно медленнее, чем с числовыми данными

Не применять объединение строк без необходимости. Например, s = «АВ» , будет быстрее, чем: s =»А» & «В»

Не применять сравнение текстовых величин напрямую. Лучше применить встроенную функцию StrComp:
If s <> s1 Then будет медленнее, чем
If StrComp(s, s1, vbBinaryCompare) = 0
и тем более, если при сравнении необходимо не учитывать регистр:
If LCase(s) <> LCase(s1) Then будет медленнее, чем
If StrComp(s, s1, vbTextCompare) = 0

Циклы For … Next в большинстве случаев работает быстрее, чем цикл Do . Lоор

Избегать присвоения переменным типа Variant . Хоть соблазн и велик — этот тип забирает много памяти и в дальнейшем замедляет работу кода. Так же для объектных переменных следует избегать по возможности безликого глобального типа Object и применять конкретный тип:

Как отключить обновление экрана для другого приложения (блокнот)

4 Badan [2016-12-09 12:55:00]

Я написал макрос в vba, который открывает текстовый файл с помощью блокнота, выбирает все txt и копирует его в Excel. Я должен обрабатывать около 100 файлов ежедневно таким образом, и я хочу избавиться от мигающих изображений, которые я наблюдаю. Код работает, но проблема в том, что команда

Не работает с записной книжкой. Я могу использовать только обычный фокус, иначе код не работает. Как я могу выполнить код ниже, не заметив, что файл блокнота открыт и обработан?

Я не мог найти никакого рабочего решения.

excel-vba csv notepad

Вот быстрый ответ, как запустить Notepad, но скрыть окно и использовать vbHide вместо vbNormalFocus в вашей команде Shell :

Но я очень сомневаюсь, что SendKeys будет работать над невидимым окном.

Итак, это не ответ на вопрос, но вы считаете, что используете FileSystemObject и просто читаете файл без фактического открытия Notepad.exe ?

Код будет работать с акцентированными символами, например. мой тестовый текстовый файл:

Насколько я знаю, Application.ScreenUpdating применим только к вашему сеансу Excel, а не к другим программам, запущенным в Windows. Таким образом, чтобы фактически остановить появление этих окон — , но все еще можно прочитать содержимое окна, вам придется сделать что-то достаточно сложное с Windows API. Использовать FileSystemObject много, проще.

0 Badan [2016-12-12 11:37:00]

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

How to dou

Поскольку ваши макросы Excel становятся все более надежными и сложными, вы можете обнаружить, что они теряют производительность. При обсуждении макросов слово производительность обычно является синонимом speed . Скорость — это то, как быстро ваши процедуры VBA выполняют свои намеченные задачи. Ниже приведены десять способов помочь сохранить макросы Excel с оптимальным уровнем производительности.

Расчет листового листа

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

Вы можете использовать Приложение. Свойство Calculate, чтобы сообщить 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.

Приостановка обновлений сводной таблицы

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

Вы можете улучшить производительность своего макроса, приостановив перерасчет сводной таблицы до тех пор, пока не будут сделаны все изменения поля поворота. Просто установите PivotTable. Свойство ManualUpdate для True, чтобы отложить пересчет, запустите свой макрокоманд и затем установите сводную таблицу. Свойство ManualUpdate вернется к False, чтобы вызвать пересчет.

Очистка от копирования и вставки

Важно помнить, что хотя Macro Recorder экономит время, написав код VBA для вас, он не всегда записывает наиболее эффективный код. Ярким примером является то, как Macro Recorder захватывает любое действие копирования и вставки, которое вы выполняете во время записи.

Вы можете немного увеличить свои макросы, вырезав посредника и выполнив прямую копию из одной ячейки в ячейку назначения. Этот альтернативный код использует аргумент Destination для обхода буфера обмена и копирования содержимого ячейки A1 непосредственно в ячейку B1.

Если вам нужно скопировать только значения (не форматирование или формулы), вы можете повысить производительность еще больше, избегая при этом метода копирования. Просто установите значение ячейки назначения на то же значение, которое находится в исходной ячейке. Этот метод примерно в 25 раз быстрее, чем при использовании метода Copy:

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

Использование оператора With

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

Оператор With, используемый в следующем примере, сообщает Excel применять все изменения форматирования за один раз:

Привычка к чередованию действий в операторы With не только ускорит выполнение макросов, но и упростит чтение вашего макрокода.

Избегание метода Select

Macro Recorder предпочитает использовать метод Select для явного выбора объектов, прежде чем предпринимать действия над ними. Как правило, нет необходимости выбирать объекты перед их работой. Фактически, вы можете значительно улучшить производительность макросов, не используя метод Select.

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

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

Ограничение поездок на рабочий лист

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

Например, следующий простой код заставляет VBA непрерывно возвращаться к листам («Лист1»). Range («A1»), чтобы получить номер, необходимый для сравнения, выполняемого в инструкции If:

Более эффективным способом является сохранение значения в листах («Лист1»). Range («A1») к переменной MyMonth. Таким образом, код ссылается на переменную MyMonth вместо рабочего листа:

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

Excel vba: Почему обновление экрана не обновляется, прежде чем отключать обновление экрана.

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

Когда я выключаю второй элемент, ячейка обновляется. Как я могу обновить ячейку, а затем отключить обновление экрана?

EDIT: обновление Экран уже верно, когда mainSub начинается; Комментируя setupApp sub out , он помогает, потому что он явно запускается раньше, чем ячейки редактируются несколько позже.

Создан 30 авг. 17 2017-08-30 13:36:04 Ans

Есть ли приложение.ScreenUpdating = False в getStateNames? – Moosli 30 авг. 17 2017-08-30 14:27:07

@Moosli Нет, нет – Ans 30 авг. 17 2017-08-30 14:32:24

Хорошо, давайте отправимся в Кахт. Вы можете увидеть это в комментариях в моем ответе. – Moosli 30 авг. 17 2017-08-30 14:36:50

Я действительно не понимаю, почему это не работает. Но вы можете попытаться вызвать SetupApp с помощью инструкции If. Для этого вам нужно привязать editSub к функции и в конце просто вернуть истинное значение. Что-то вроде этого:

Второе решение

Вы можете попытаться форсировать события с DoEvents для обновления экрана.

Или Помещенный DoEvents сразу после того, как вы написаны в клетках.

Если это не работает, вы можете попробовать метод Wait:

Первое решение

ли судимое вам повернуть ScreenUpdating на Перед выполнением Sub?

Создан 30 авг. 17 2017-08-30 13:50:20 Moosli

Не помогает. Экран updatin ** уже ** истинен при запуске суб. Комментирование второй суб ** помогает **, потому что она, очевидно, выполняется раньше, чем нужно. – Ans 30 авг. 17 2017-08-30 13:51:44

Сколько кода в editSub? Becuase, если есть только несколько строк, может быть, что UpdateEvent должен замедляться, и он будет отключен до обновления экрана. – Moosli 30 авг. 17 2017-08-30 13:54:33

Довольно много строк. Он берет некоторые данные, проверяет их, добавляет в массив и только затем редактирует ячейки со значениями из массива. – Ans 30 авг. 17 2017-08-30 13:56:28

@Ans Хорошо, я добавил мой Awnser – Moosli 30 авг. 17 2017-08-30 14:05:06

К сожалению, ни то, ни другое не помогает . Ожидание просто приводит к более длительному времени выполнения, в то время как ячейки все еще пусты, «DoEvents» ничего не влияет. – Ans 30 авг. 17 2017-08-30 14:14:20

Можете ли вы опубликовать код из editSup? – Moosli 30 авг. 17 2017-08-30 14:19:49

Я редактировал вопрос. – Ans 30 авг. 17 2017-08-30 14:22:53

Копилка знаний…..

Ускоряем выполнение VBA кода, а так же убираем ненужные уведомления (всплывающие окна).

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

Excel.Application.ScreenUpdating = False ‘убирает обновление экрана. Можно использовать почти всегда — лишняя нагрузка на процессор.

Excel.Application.DisplayAlerts = False ‘убирает всплывающие окна. Совсем. Использовать аккуратно.

Excel.Application.EnablEevents = False ‘убирает обработку событий в Excel. События необходимо отключать для исключения зацикливания или выполнения незапланированных действий. Например при внесении одним макросом значения на лист и лишнего (не нужного) выполнения другого макроса по обработки события добавления значения на лист — который, как вариант, рассчитан на ручной ввод. Использовать только при необходимости.

Excel.Application.Calculation = xlCalculationManual ‘убирает автопересчет формул и зависимых от них объектов. Можно и нужно использовать почти всегда — лишняя нагрузка на процессор, особенно если формулы использующие массивы или поиск.

Не забываем ОБЯЗАТЕЛЬНО все вернуть обратно, чтобы пользователи не ругались = ) :

Excel.Application.ScreenUpdating = True
Excel.Application.DisplayAlerts = True
Excel.Application.EnableEvents = True
Excel.Application.Calculation = xlCalculationAutomatic