Excel vba отображение прогресса

Обновлено: 04.07.2024

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

Если во время кода все же нужно пересчитывать какие-то диапазоны, то можно пересчитывать только их:

Главное, что следует помнить - все эти свойства необходимо включить обратно после работы кода . Иначе могут быть проблемы с работой внутри 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

Для более опытных пользователей VBA я приведу несколько решений по оптимизации кодов в различных ситуациях:

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

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

В данной статье описано, как создать окно загрузки (или по-другому прогресс бар) для Ваших процедур без загромождения их кодом.

Состав прогресс бара

Строится окно загрузки на основе простой пользовательской формы UserForm, которая содержит следующие элементы:

Если вывести все элементы на форму, то она будет иметь такой вид:

В случае ненадобности тех или иных элементов, их можно не выводить. Контроль за выводом элементов осуществляет класс «ProgressBar», экземпляр которого для начала необходимо создать (Set var = New ProgressBar). Затем, используя созданный класс, можно программным образом заполнить форму элементами и задавать им конкретные значения.

Описание класса и способов создания окна загрузки

Для начала рассмотрим доступные методы данного класса, не концентрируясь на коде, а только на его функциональности:

Метод createLoadingBar – создает полосу загрузки на форме;
createString – создает сроку «Обработано: … %»;
createtimeDuration – создает сроку «Продолжительность обработки: …»;
createtimeFinish – создает строку «Оставшееся время обработки: …»;
createTextBox – создает элемент TextBox;
setParameters – задает параметры окна загрузки для предстоящего процесса. Принимает 3 аргумента:
- expProcess_INT – обязательный аргумент. Принимает целое число, сообщающее, из какого количества этапов состоит последующий процесс;
- UpdateInterval_INT – необязательный аргумент. С его помощью можно задать интервал обновления формы, т.е. через какое количество этапов все элементы окна загрузки необходимо обновить;
- UpdTimeInterval_INT_SEC – необязательный аргумент. Задает интервал обновления формы в секундах. Аргумент имеет смысл только в том случае, если не задан аргумент UpdateInterval_INT.
- curProcess – целое число. Номер текущего этапа процесса;
- stringTextBox – строка для элемента TextBox.
Это сделано по двум причинам.

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

Второй причиной является производительность. Не смотря на то, что идея вывода информации по загрузке является вполне обоснованной, само ее использование сильно замедляет процесс. Например, с использованием ProgressBar время обработки нижеприведенного примера составляет 1 минута 17 секунд при установленном интервале обновления в секунду. При обновлении формы на каждом этапе, за 2 минуты обработалось чуть больше 2 сотых процента. Без использования на все ушло 8 секунд. Поэтому старайтесь использовать прогресс бар только в случаях, когда это действительно важно и применяйте к нему оптимальный интервал – секунды достаточно, свыше данного значения особых изменений в производительности не наблюдается.

Также предусмотрена возможность остановить выполнение всех процессов, закрыв окно загрузки. Предварительно пользователю будет выведено окно с подтверждением.

Пример подключения прогресс бара к макросу

Далее приводиться простой пример перебора символов строки. Процедура сама по себе смысла не имеет, но хорошо демонстрирует возможности окна загрузки.

Многоуровневая полоса загрузки

Применение описанного в статье класса позволяет создавать независимые друг от друга окна загрузки для многоуровневых процессов.

Никаких дополнительных действий не требуется, достаточно создать новый экземпляр класса (New ProgressBar) и работать с ним независимо от родительского процесса.

Рекомендация: Для дочерних процессов добавляйте к формам загрузок уникальные заголовки (ProgressBar.Start Заголовок). Это уведомит пользователя программы о том, что сейчас выполняется подпроцесс.

Специальный элемент Microsoft ProgressBar Control

Выше было сказано о том, что саму полосу загрузки можно заменить дополнительным элементом управления формы, который специально предназначен для этого и называется Microsoft ProgressBar Control, version 6.0. Чтобы применить его, достаточно нажать правой кнопкой мыши на панели Tollbox и выбрать пункт "Additional Control. ".

Для ее устранения сначала проверьте наличие на Вашем компьютере файла MSCOMTCL.ocx. Это библиотека содержащая общие элементы управления Windows 6.0. Он должен располагаться в папке \Windows\SysWOW64 для 64-разрядных ОС либо \Windows\Sistem32 для 32-разрядных. В случае необходимости скачайте его и разместите в требуемую папку.

После того, как Вы убедились в наличии библиотеки, следует ее зарегистрировать. Запустите командную строку от имени администратора (Пуск -> Все программы -> Стандартные -> Командная строка) и выполните команду regsvr32 MSCOMTCL.ocx.

Данный прогресс-бар позволяет отображать ход выполнения любого макроса.

Для использования этого индикатора перетащите из файла-примера в свой файл модуль класса ProgressIndicator и форму F_Progress

Использовать прогресс бар сравнительно просто - достаточно добавить в макрос несколько строк кода:

Давайте рассмотрим подробнее работу с индикатором.

Прогресс-бар выполнен в виде модуля класса, поэтому, для начала работы с ним,
надо прежде всего создать экземпляр этого класса:

Итак, прогресс-бар создан, и теперь надо его отобразить.

Для этого мы используем метод Show объекта типа ProgressIndicator:

При использовании метода Show мы сразу задаём заголовок индикатора (можно здесь указать название вашего макроса)

Индикатор появился на экране - но полоса не отображается, ибо процент выполнения по-умолчанию равен нулю.

Для каждого действия мы будем задавать начальный и конечный процент выполнения задачи

К примеру, если первое действие вашего макроса занимает по времени примерно пятую часть от времени выполнения всего макроса,
то мы укажем интервал для индикатора от 0% до 20%:

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

При вызове этого метода можно сразу задать текст для каждой из 3 текстовых строк индикатора:

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

(например, основное действие - это форматирование ячеек (от 0% до 20% индикатора), а поддействия - это окраска отдельных ячеек (первая строка - от 0% до 1% индикатора, вторая строка - от 1% до 2%, и т.д.))

Чтобы нам не мучиться с расчётами этих процентов, мы просто задаём количество действий (например, количество форматируемых ячеек, равное 3000),
и индикатор сам разделит диапазон от 0% до 20% на 3000 равных частей, плавно увеличивая длину полосы индикатора по мере форматирования отдельных ячеек.

Чтобы уведомить индикатор об очередном "поддействии" внутри цикла, мы используем метод SubAction объекта ProgressIndicator

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

В этом случае (если значения некоторых из 3 строк индикатора не заданы), эти строки не изменяются
(в первой строке индикатора останется текст, заданный ранее при использовании метода StartNewAction)

Кроме того, в тексте для строк индикатора можно использовать следующие ключевые слова:
- $index и $count - для вывода строк типа "Обрабатывается ячейка 515 из 3000",
- $time - для вывода ожидаемого времени до окончания макроса
(макрос анализирует текущий процент выполнения и затраченное время, и предсказывает, сколько времени осталось до окончания всех действий)

Если же необходимо просто увеличить длину полоски индикатора - можете использовать метод SubAction без параметров:

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

Вполне допустим следующий код:

По окончании макроса желательно закрыть прогресс бар:

У объекта ProgressIndicator имеется много различных свойств и методов.

Вкратце расскажу о некоторых свойствах:
- свойство Caption позволяет задать новый заголовок индикатора
- свойство FP позволяет получить доступ к отображаемой форме (и всем её элементам управления)
  (например, код pi.FP.PrintForm выведет индикатор на печать)
- свойства Line1, Line2 и Line3 позволяют в любом месте кода задать текст конкретной строки индикатора
- свойства ShowPercents и ShowTime включают или выключают отображение процента выполнения и времени в заголовке индикатора
  (по умолчанию оба свойства имеют значение TRUE, т.е. в заголовке отображается и время, и процент выполнения макроса)
Из функций объекта мы рассмотрим только одну: AddChildIndicator

Эта функция создаёт дочерний прогресс бар, и отображает его выше или ниже родительского:

При изменении процента выполнения в дочернем индикаторе пропорционально меняется и процент выполнения главного (родительского) прогресс-бара.

В прикреплённом файле, помимо модуля класса и формы индикатора,
присутствует также стандартный модуль с несколькими примерами использования прогресс-бара.

На индикаторе присутствует кнопка «Отмена» - её нажатие вызывает останов всех запущенных макросов
(выполняется команда End, останавливающая все макросы, и обнуляющая все переменные)

Поскольку у этой кнопки свойство Cancel установлено в TRUE, нажатие на клавиатуре клавиши ESC равносильно нажатию кнопки «Отмена»
(при нажатии Esc макрос останавливается)

Новая версия прогресс-бара - с поддержкой отображения лога на индикаторе, и возможностью отображения лога в виде текстового файла.

Высоту текстового поля с логом можно изменять:

Новая версия индикатора, и примеры его использования - во втором прикреплённом файле.

Комментарии

Огромное спасибо разработчику.
Несколько лет применял ваш ProgressIndicator (выдрал из какого-то примера), просто, эффектно. И вот наконец обнаружил автора.

Большое спасибо Автору! Великолепный макрос! Думал что не нужный, пока не пришлось обрабатывать однотипные файлы тысячами! А так сразу понятно, на сколько можно идти пить чай!

Еще раз добрый день!
Заменил строку LogString = LogString & vbNewLine & currtime$ & txt на LogString = LogString & vbLf & currtime$ & txt в вашей процедуре Log, все стало нормально.
Спасибо
С уважением Александр

Доброго дня!
Попробовал Ваш код с поддержкой отображения лога на индикаторе
Очередная строка лога лепится к предыдущей, а не пишется на индикаторе с новой строки
Что посоветуете посмотреть?
Спасибо
С уважением Александр

Надо внутрь кода вычислений добавить строку DoEvents
чтобы комп не подвисал на выполнении вычислений.
Тогда прогрессбар будет отрисовываться постоянно, и ничего пропадать не будет
PS: все ваши 6 строк кода, к этой проблеме никакого отношения не имеют.

Добрый день!
Использую прогресс бар для индикации во время объемных вычислений (парсинг большого объема JSON, общее время около 8 - 10 минут). Когда тестировал на меньших объемах (1-2 минуты) все работало отлично, но когда загрузил реальные данные, то прогресс бар стал периодически пропадать на несколько секунд, а потом снова появляться уже с обновленными данными. Причем количество таких появлений и пропаданий прогресс бара плавающее. Подскажите, может кто-то сталкивался с такой же проблемой?

Все параметры обновления перед запуском сбрасываю:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
Application.DisplayStatusBar = False
Application.DisplayAlerts = False

Подскажите, если при использовании дочернего индикатора обращаться к нему напрямую (например, если брать Ваш пример, так: pi.AddChildIndicator("ИМЯ_ИНДИКАТОРА").SubAction "Действие $index из $count"), без конструкции With, то на стадии обновления дочернего индикатора (добавления действия) возникает ошибка деления на ноль: почему так происходит?

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

Добрый день,
я не понял как и где вставлять сам код между моим макросом. Форму и модуль я скопировал.
У меня следующий процесс.
1. Отключаю экранчик.
2. Открываю файл 1 и проверяю его на наличие в указанной папке.
3. Если файл1 есть то в самом файле я снимаю объединения ячеек.
4. Открываю файл2 повторяю пункт 2-3.
5. В файле1 делаю автофильтр и удаляю все не нужное и копирую данные в свой "материнский" файл через который запустил макрос. Закрываю файл 1 без сохранения(поскольку не знаю как макросом вернуть удаленное).
6. опять открываю файл1 п.2-3
7. Тяну ВПР с файла 2, фильтрую не нужные данные в файле1 и удаляю их, копирую в другую таблицу "материнского" файла.
8. закрываю файл 1,2 не сохраняя, и включаю экран.

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

Игорь, в настройках парсера: дополнительно-разное, есть галочка "отображать второй прогресс-бар" уже не актуальна) , т.к. первый после апа проги вмещает 6 строк. Было бы очень здорово если бы на следующий ап галочку изменили на "отображать прогресс-бар"))) Спасибо!

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

Здравствуйте, вопрос в следующем. Как отключить прогресс-бар в парсере? Очень не удобно когда парсер запускается каждую минуту и всплывает поверх окон. Спасибо!

Александр, надо задать количество действий - selection.cells.count
и при обработке каждой ячейки вызывать медом .SubAction прогрессбара
Тогда время и проценты будут считаться

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

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

Нажатие на красный крестик не останавливает макрос, а просто скрывает форму прогресс-бара.

Чтобы крестиком форма не закрывалась, добавьте код в ФОРМУ F_Progress

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

Вышел из положения так. Сначала запускается маленький легкий макрос, он показывает юзеру чистый лист с предупреждающей надписью и стоящим на 20% прогресс-баром, пока что фальшивым, статичным.
Ведь главное, чтобы юзер спокойно ждал и не дергался!
Далее из этого макроса запускается основной большой макрос и прогресс-бар начинает ехать обычным порядком.
Прогресс-бар использовал свой, простейший, см. ранее, повтором буквы g шрифта Webdings.

У меня сложный макрос долго работал.
Но пока налаживал этот прогресс-бар, выяснилась интересная штука.
Собственно, и необходимость в нем отпала.
Выяснилось, что основное замедление (до 15-20 секунд) происходит еще ДО НАЧАЛА выполнения операций!
Получается, что на этапе выделения памяти под переменные! Больше быть нечему.
У меня там реально много: по 20 переменных типа Long и Single, 8 String, 4 динамических массива Range, 8 массивов String тоже динамических и т.д. Сократить бы можно, но - за счет понятности кода.
За эту гипотезу говорит то, что замедление на компе с 8G памяти практически не чувствуется, а с 2G достигает 15 сек. Та же Win-7 и тот же Office-2010.

Это правда может такое быть?

Я пытался запускать прогресс-бар еще до определения переменных. Однако ничего не меняется.
Да и кстати - по ходу придумал наипростейший прогресс-бар, он работает всегда!
На любой винде есть шрифт Webdings, в нем буква "g" с номером 103 - это просто закрашенный квадратик. Создаем прогресс-бар повтором этих квадратиков функцией String(), на отдельном листе с заранее отформатированной ячейкой. Размер, цвет надо будет подобрать.
Допустим, в ячейку помещается 23 квадратика.
Прогресс-бар в одну строчку:

Sheets("ProgressBar").Range("D7").Value = String(Round(23 * i / n), 103)

где i и n - ваши параметры. Например, выполнено i действий из n.

Алексей, форма должна быть немодальной, чтобы прогресс-бар отображался сверху
В свойствах формы поставьте ShowModal = FALSE

Прогресс-бар работает быстро, - за секунду может сменить десятки состояний.
Если у вас что-то медленно работает, - проблема в вашем коде (или неправильном использовании прогресс-бара)
Смотрите пример файл из статьи, - там всё быстро работает.

Добрый день
Почему-то тогда я пытаюсь показать прогресс-бар перед своей формой(окно) она отображается за ней, и я прогресс-бар не вижу, а вижу только свою форму. Также остается проблема, когда я свою форму скрываю на время. При этом чтобы прогресс-бар работал быстрее надо мышкой на него левой кнопкой нажать (а так ждать около 1 мин против 5 сек).

Чтобы все работало - надо скопировать в ваш файл модуль класса и форму из прикреплённого файла

Здравствуйте выдает ошибку pi As New ProgressIndicator

У меня такое же было из-за того, что я скопировал в свой проект классмодуль ProgressIndicator, но забыл скопировать форму F_Progress - нужно копировать и то и другое, как скопировал ошибку перестало вываливать.
П.С. Автору индекатора/сайта уклон до земли, респект, уважуха .

Игорь, добрый день.
Будет ли компонент работать в MS Access 2007 и выше?

Все получилось!
Огромное спасибо за помощь!

Вставьте этот код в новый модуль, проверьте, - все должно заработать.

Здравствуйте!
Добавил на прогресс-бар кнопку "Лог", которая появляется в конце работы.
Подскажите, пожалуйста, как привязать к ее нажатию запуск метода класса ShowLog? Т.е. я хочу чтобы при нажатии этой кнопки лог "открывался" в блокноте.
Заранее спасибо!

I'm doing an Excel app that needs a lot data updating from a database, so it takes time. I want to make a progress bar in a userform and it pops up when the data is updating. The bar I want is just a little blue bar moves right and left and repeats till the update is done, no percentage needed.

I know I should use the progressbar control, but I tried for sometime, but can't make it.

My problem is with the progressbar control, I can't see the bar 'progress'. It just completes when the form pops up. I use a loop and DoEvent but that isn't working. Plus, I want the process to run repeatedly, not just one time.

19.9k 55 55 gold badges 68 68 silver badges 89 89 bronze badges 2,712 7 7 gold badges 32 32 silver badges 43 43 bronze badges
13 Answers 13

Sometimes a simple message in the status bar is enough:

24.4k 17 17 gold badges 78 78 silver badges 109 109 bronze badges Glad I saw this. Was a much better idea for me than actually faking a progress bar. This works great! and very simple. But is there a way to make it work when you turn off screenupdating? Right now I am just turning it on right before the status bar, then back off right after but I believe this might be slowing it down a little bit. I am also running this on 3 separate for loops.
Here's another example using the StatusBar as a progress bar.

By using some Unicode Characters, you can mimic a progress bar. 9608 - 9615 are the codes I tried for the bars. Just select one according to how much space you want to show between the bars. You can set the length of the bar by changing NUM_BARS. Also by using a class, you can set it up to handle initializing and releasing the StatusBar automatically. Once the object goes out of scope it will automatically clean up and release the StatusBar back to Excel.

2,377 1 1 gold badge 29 29 silver badges 37 37 bronze badges Looks very similar to the one that Microsoft uses for e.g. opening workbooks. This works surprisingly well. Using a class made this simpler since it automatically resets the status bar when the calling sub terminates (assuming you use a locally dimmed variable as in your sample usage). Thanks for sharing!
In the past, with VBA projects, I've used a label control with the background colored and adjust the size based on the progress. Some examples with similar approaches can be found in the following links:

Here is one that uses Excel's Autoshapes:

8,585 11 11 gold badges 49 49 silver badges 76 76 bronze badges 13.7k 5 5 gold badges 53 53 silver badges 63 63 bronze badges
I'm loving all the solutions posted here, but I solved this using Conditional Formatting as a percentage-based Data Bar.

This is applied to a row of cells as shown below. The cells that include 0% and 100% are normally hidden, because they're just there to give the "ScanProgress" named range (Left) context.

In the code I'm looping through a table doing some stuff.

Minimal code, looks decent.
535 1 1 gold badge 9 9 silver badges 24 24 bronze badges I would like to get this working, but am having trouble with the code provided. Any chance of providing a Sub, or more instruction? UPDATE -> Walking through link instructions.
Create a Button on a Worksheet; map button to "ShowProgress" macro

Create a UserForm1 with 2 buttons, progress bar, bar box, text box:

I liked the Status Bar from this page:

I updated it so it could be used as a called procedure. No credit to me.

24.2k 20 20 gold badges 64 64 silver badges 113 113 bronze badges

You can create a form in VBA, with code to increase the width of a label control as your code progresses. You can use the width property of a label control to resize it. You can set the background colour property of the label to any colour you choose. This will let you create your own progress bar.

The label control that resizes is a quick solution. However, most people end up creating individual forms for each of their macros. I use the DoEvents function and a modeless form to use a single form for all your macros.

Читайте также:

Excel vba отображение прогресса

Состав прогресс бара

Описание класса и способов создания окна загрузки

Пример подключения прогресс бара к макросу

Многоуровневая полоса загрузки

Специальный элемент Microsoft ProgressBar Control

Комментарии

13 Answers 13