Remkomplekty.ru

IT Новости из мира ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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 не отображать эти типы предупреждений при запуске макроса.

Секретный трюк, чтобы избежать этих предупреждающих сообщений, вставляет в ваш макрос следующий оператор 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-битных системах

  • Если используете быстрый ЕСЛИ — IIF , то замените его на IF . Then . Else
  • Так же лучше вместо Switch() и Choose() применить тот же IF . Then . Else
  • В большинстве случаев проверять строку на «не пусто» лучше через Len() , чем прямое сравнение с пустотой: Len(s)=0 вместо s = «» . Связано с тем, что работа со строками значительно медленнее, чем с числовыми данными и Len по сути не подсчитывает длину переменной, а берет это число непосредственно уже готовое из памяти. При сравнении же текста с пустой строкой(«»), VBA сначала создает в памяти переменную нулевой длинны, а уже потом сравнивает с ней наш текст. Поэтому в некоторых случаях так же ускоряет сравнение и в таком виде: s = vbNullString
  • Не применять объединение строк без необходимости. Например, 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 и применять конкретный тип:

    Заставить обновление экрана в Excel VBA

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

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

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

    6 ответов

    30 Решение Robert Mearns [2010-09-17 19:04:00]

    Добавьте функцию DoEvents внутри цикла, см. ниже.

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

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

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

    Следующие команды, похоже, делают трюк:

    6 GSerg [2010-09-17 16:05:00]

    Поместите вызов DoEvents в цикл.

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

    0 Michael [2010-09-17 19:05:00]

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

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

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

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

    UserForm не будет обновляться в некоторых случаях, потому что DoEvents будет запускать события; однако Repaint обновит UserForm, и пользователь увидит изменения на экране, даже если другое событие сразу же следует за предыдущим событием.

    В коде UserForm это просто:

    -3 Hari Das [2015-12-20 16:00:00]

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

    ScreenUpdating — обновление экрана

    Позволяет включать (присвоением свойству True) и отключать (присвоением False) обновление экрана. Имеет смысл отключить обновление экрана перед теми частями программы, которые интенсивно пользуются данными на листе. Благодаря тому, что системные ресурсы не будут тратиться на обновление экрана, программа будет работать быстрее. Этот метод весьма актуален, так как MS Excel часто используют для проведения ресурсоемких расчетов.

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

    Например, ниже приведен код, который два раза повторяет процедуру 100-кратного вывода на экран 400 целых случайных чисел и выводит время, требующееся для выполнения этих действий с обновлением экрана и без него.

    ‘Массив для значений времени

    ‘Время начала теста

    ‘Время окончания теста

    ‘Во втором проходе цикла

    ‘Запишем текущее время

    ‘Перейдем на лист для теста

    ‘Выведем 100 раз целые случайные

    ‘числа в область 20х20

    For y = 1 To 100

    ActiveSheet.Cells(p, j) = _

    ‘Запишем время окончания

    ‘Для корректного представления

    (StopTime — StartTime) * 24 * 60 * 60

    MsgBox «Время выполнения программы.» & Chr(13) + _

    «При включенном обновлении: » & _

    WorkTime(1) & » сек.» & Chr(13) & _

    «При выключенном обновлении: » & _

    Selection — ссылка на выделенный объект

    Это очень важное свойство возвращает ссылку на выделенный объект. Чаще всего это — ячейка или группа ячеек. Например, это свойство удобно использовать при работе с выделенным диапазоном ячеек (или отдельной выделенной ячейкой). Ниже мы коснемся его подробнее.

    WorksheetFunction — формулы Excel в коде VBA

    Возвращает объект WorksheetFunction, методы которого представляют собой формулы Excel, которые можно использовать в коде VBA. Использование этого свойства позволяет облегчить выполнение сложных расчетов.

    События Application

    Объект Excel.Application поддерживает множество событий. Работа с ними аналогична работе с событиями Word.Application, которыми мы занимались в соответствующем разделе предыдущей главы.

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

    Создайте новый модуль класса. Добавьте в него объявление объекта типа Excel.Application с событиями

    Public WithEvents obj_ExApp As Excel.Application

    После этого в списке объектов редактора кода модуля появится объект obj_ExApp, а в списке событий — соответствующие ему события. Выберите нужное вам событие — автоматически будет создан обработчик для него. В частности, Excel.Application поддерживает следующие события:

    • NewWorkbook — происходит при создании новой книги
    • SheetActivate — при активации любого листа
    • SheetBeforeDoubleClick — происходит при двойном щелчке по листу, то есть позволяет перехватить щелчок и выполнить собственную процедуру до того, как будет выполнено стандартное действие.
    • SheetBeforeRightClick — позволяет перехватить нажатие правой кнопки мыши по листу.
    • SheetCalculate — после пересчета листа или после изменения данных, которые отображаются на диаграмме.
    • SheetChange — при изменении содержимого ячеек на любом листе.
    • SheetFollowHyperlink — происходит при переходе по гиперссылке, которая может быть включена в лист Microsoft Excel.
    • SheetSelectionChange — при изменении выделения на листе
    • WindowActivate — при активации окна книги.
    • WindowDeactivate — при деактивации окна книги.
    • WindowResize — при изменении размера окна книги.
    • WorkbookActivate — при активации книги.
    • WorkbookBeforeClose — перед закрытием книги.
    • WorkbookBeforePrint — перед печатью книги.
    • WorkbookBeforeSave — перед сохранением книги.
    • WorkbookDeactivate — при деактивации книги.
    • WorkbookNewSheet — при добавлении нового листа в любую из открытых книг.
    • WorkbookOpen — при открытии книги.

    После того, как создан обработчик, написан его код, работа еще не окончена. Следующий шаг — это связывание объекта obj_ExApp с реально работающим приложением. Ниже приведен полный код модуля с одним обработчиком события, а также — процедура, служащая для связывания объекта obj_ExApp с работающим приложением. Эта процедура может существовать в виде отдельного макроса или в виде кода обработчика нажатия на кнопку. Ее выполнение можно назначить событию открывающейся книги, которая содержит данный модуль класса и т.д.

    Итак, вот код процедуры, который связывает объект созданного нами класса AppEvents с приложением:

    Dim obj_ExcelAppEv As New AppEvents

    Set obj_ExcelAppEv.obj_ExApp = Excel.Application

    А вот полный код модуля класса AppEvents с объявлением объектной переменной и обработчиком события.

    Public WithEvents obj_ExApp As Excel.Application

    Private Sub obj_ExApp_NewWorkbook(ByVal Wb As Workbook)

  • Ссылка на основную публикацию
    ВсеИнструменты
    Adblock
    detector
    ×
    ×