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

Способ вывода таких сообщений предусмотрен приложением. Называется он Статус бар и вызывается он прямо из кода в редакторе Visual Basic следующей записью:

Application.StatusBar = “сообщение для пользователя”.

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

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

Скачать файл с классом можно в конце статьи.

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

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

1. Два элемента Label. Используются в совокупности для отображения полосы загрузки. Первый применяется как контейнер и имеет отличный от фона формы фон. Второй вкладывается в первый и имеет динамическую ширину, которая меняется вместе с процентом выполнения процесса. Ее фон отличный от фона формы и фона родительского элемента. Эти элементы можно заменить на один дополнительный, который так и называется - ProgressBar, но с его использованием могут быть связаны некоторые проблемы, речь о которых пойдет ниже.
2. Три элемента Label. Каждый из них не зависит от остальных и предназначается для вывода конкретной информации: продолжительность процесса, оставшееся время, количество пройденных этапов процесса.
3. ТехBox для вывода специальных сообщений пользователю.

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

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

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

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

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

Можно заметить, что прогресс бар обязательно устанавливает интервал обновления окна загрузки. Поэтому сообщения, заданные в аргументе stringTextBox метода Update, попадут в форму только в случае, если интервал это позволит. Но можно напрямую обратиться к форме и внести сообщение – ссылка_на_форму.Text = “сообщение”.

Это сделано по двум причинам.

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

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

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

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

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

Sub test()

' Объявляем переменную для загрузки строки

Dim strC As String

strC = Cells(1, 1).Value

' увеличиваем ее содержание

strC = strC & strC & strC & strC & strC

strC = strC & strC & strC & strC & strC

strC = strC & strC & strC & strC & strC

strC = strC & strC & strC & strC & strC

strC = strC & strC & strC & strC & strC

' создаем экземпляр класса прогресс бара

Dim bar As ProgressBar

Set bar = New ProgressBar

' создаем элементы формы прогресс бара

' последовательность создания элементов не имеет никакого значения, т.к.

' перед его создание проверяется наличие остальных. Если элементы найдены, то они сдвигаются

bar.createtimeFinish    ' вывод строки для оставшегося времени

bar.createLoadingBar    ' вывод полосы загрузки

bar.createString    ' вывод строки пройденных этапов из общего количества с указанием процента

bar.createtimeDuration  ' текущая время обработки процесса

bar.createTextBox   ' вывод пустого текстового поля

bar.setParameters Len(strC), 0, 5   ' Задание параметров для последующей обработки:

                                    ' 1 - указание числа этапов процесса;

                                    ' 2 - интервал обновления формы, в данном случае ноль, но можно вовсе опустить

                                    ' 3 - интервал обновления в секундах, применяется, только если предыдущий аргумент равен нулю или опущен

bar.Start   ' запускаем прогресс бар перед началом процесса

For i = 1 To Len(strC)

    ch = Mid(strC, i, 1)

    bar.Update i    ' обновляем прогресс бар и передаем ему номер текущего этапа процесса

                    ' вторым аргументом можно передать строку для текстового поля, но она попадет туда только в случае,

                    ' если интервал это позволяет, поэтому к элементу формы можно обратиться по его имени "Text"

Next i

bar.exitBar ' Закрываем прогресс бар

Set bar = Nothing ' удаляем экземпляр класса прогресс бара

End Sub

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

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

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

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

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

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

Но с применением этого элемента могут быть связаны проблемы работоспособности программы на разных версиях MS Office (в основном 2010 и 2013) и Windows, когда Вы попытаетесь добавить его в UserForm. Приложение выдаст ошибку "Библиотека не зарегистрирована".

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

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

Скачать пример полосы загрузки на VBA

Скачать пример progressbar VBA с применением элементов Label.

Скачать пример progressbar VBA c применением Microsoft ProgressBar Control.