Vba excel обновление экрана

Принудительное обновление экрана в Excel VBA

мой инструмент Excel выполняет длинную задачу, и я пытаюсь быть добрым к пользователю, предоставляя отчет о ходе работы в строке состояния или в некоторой ячейке на листе, как показано ниже. Но экран не обновляется или перестает обновляться в какой-то момент (например, 33%). Задача в конечном итоге завершается, но индикатор выполнения бесполезен.

что я могу сделать, чтобы принудительно обновить экран?

Я использую Excel 2003.

6 ответов

добавить функция doevents функция внутри цикла, см. ниже.

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

текстовые поля в листах иногда не обновляются когда изменяется их текст или форматирование, и даже команда DoEvent не помогает.

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

следующие команды, кажется, сделать трюк:

вызовите DoEvents в петле.

это повлияет на производительность, поэтому вы можете вызвать его только на каждой, скажем, 10-й итерации.
Однако, если у вас есть только 30, это не проблема.

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

и положить этому конец

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

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

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

в коде UserForm это так же просто, как:

написать DoEvents непосредственно перед строкой, в которой вы обновляете пользовательский интерфейс, он должен работать.

How to dou

Простые хитрости 1 Формат числа

Table of Contents:

VBA для Excel 2016 работает быстро, но это не всегда достаточно быстро. (Компьютерные программы никогда не бывают достаточно быстрыми.) Продолжайте читать, чтобы открыть некоторые примеры программирования, которые вы можете использовать для ускорения макросов.

Отключение обновления экрана

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

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

Чтобы продемонстрировать разницу в скорости, выполните этот простой макрос, который заполняет диапазон цифрами:

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

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

Когда вы отлаживаете код, выполнение программы иногда заканчивается где-то посередине, если вы не включили обновление экрана. Это иногда приводит к тому, что окно приложения Excel становится полностью невосприимчивым. Выход из этого замороженного состояния прост: вернитесь к VBE и выполните следующее заявление в окне Immediate:

Отключение автоматического вычисления

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

Следующий оператор устанавливает режим вычисления Excel в руководство:

Выполните следующий оператор, чтобы настроить режим расчета на автоматический:

Если ваш код использует ячейки с результатами формулы, выключение вычисления означает, что ячейки не будут пересчитаны, если вы явно не указали Excel на это!

Устранение этих надвидных сообщений оповещения

Как вы знаете, макрос может автоматически выполнять ряд действий. Во многих случаях вы можете запустить макрос, а затем потусоваться в комнате перерыва, в то время как Excel делает свое дело. Однако в некоторых операциях Excel отображаются сообщения, требующие ответа человека. Эти типы сообщений означают, что вы не можете оставить Excel без присмотра, пока он выполняет ваш макрос, — если вы не знаете секретный трюк.

Вы можете указать Excel не отображать эти типы предупреждений при запуске макроса.

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

Excel выполняет операцию по умолчанию для этих типов сообщений. В случае удаления листа по умолчанию используется «Удалить». Если вы не знаете, что такое операция по умолчанию, выполните тест, чтобы узнать, что произойдет.

Когда процедура завершится, Excel автоматически сбрасывает свойство DisplayAlerts в значение True. Если вам нужно снова включить предупреждения, прежде чем процедура закончится, используйте это заявление:

Упрощение ссылок на объекты

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

Если ваш макрос часто использует этот диапазон, вам может понадобиться создать объектную переменную с помощью команды Set. Например, следующий оператор присваивает этому объекту Range объектной переменной Rate:

После определения этой объектной переменной вы можете использовать переменную Rate, а не длинную ссылку. Например, вы можете изменить значение ячейки с именем InterestRate:

Это гораздо проще ввести, чем следующее выражение:

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

Объявление типов переменных

Обычно вам не нужно беспокоиться о типе данных, которые вы назначаете переменной. Excel обрабатывает все детали за кулисами. Например, если у вас есть переменная с именем MyVar, вы можете присвоить ей любую переменную типа. Вы даже можете назначить ему текстовую строку позже в процедуре.

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

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

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

Использование With-End со структурой

Вам нужно установить ряд свойств для объекта? Ваш код работает быстрее, если вы используете структуру With-End With. Дополнительным преимуществом является то, что ваш код может быть легче читать.

Следующий код не использует With-End With:

Вот тот же код, переписанный для использования With-End With:

Когда вы используете With-End With, убедитесь, что каждое утверждение начинается с точки.

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

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

если печать производится внутри кода, то эту строку желательно вставить сразу после строки, выводящей лист на печать(при условии, что печать не происходит в цикле. В этом случае — по завершению цикла печати).
Я советую всегда отключать разбиение на страницы, т.к. это может тормозить весьма значительно, т.к. заставляет при любом изменении на листах обращаться к принтеру и переопределять кол-во и размер печатных страниц. А это порой очень не быстро.
На всякий случай можно отключить отображение информации в строке статуса 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-битных системах