Vba excel сообщение в строке состояния
Обновлено: 18.05.2024
Часто при создании кодов в VBA используется обращение к ячейкам, листам, книгам и т.д. и их обработка в циклах. Пара примеров подобных циклов:
Если операция в цикле выполняется за пару секунд - это вполне приемлемо и отражать графически подобные действия нет нужды. Но, если циклы "крутятся" по полчаса - вполне неплохо иметь возможность видеть на какой стадии цикл. Здесь есть один нюанс: циклы могут быть как с заранее известным кол-вом итераций, так и без этого понимания.
Цикл Do While из первого кода статьи Просмотреть все файлы в папке является циклом условия. Т.е. заранее неизвестно сколько файлов будет обработано и следовательно невозможно отразить прогресс выполнения задачи в процентах.
Циклы вроде For Each и For . Next как правило дают возможность определить общее кол-во элементов к обработке, т.к. применяются как правило к коллекциям и объектам, у которых есть свойство .Count. Углубляться в этой статье не стану - это лишь предисловие, чтобы было ясно, почему и зачем далее в статье продемонстрированы разные подходы отображения процесса выполнения.
Отобразить же процесс можно двумя способами:
Использование Application.StatusBar
Самый простой вариант отображения процесса выполнения кода. Он может быть без проблем использован на любом ПК.
Application.StatusBar - это специальный элемент интерфейса, расположенный в левой нижней части окна Excel и который может показывать дополнительную информацию в зависимости от действий пользователя. Все не раз видели его в работе. Например, после того как мы скопировали ячейки StatusBar покажет нам доп.информацию:
И из VBA есть доступ к этому элементу. Чтобы написать слово привет в StatusBar надо выполнить всего одну строку кода:
Чтобы сбросить значения StatusBar и передать управление им обратно самому Excel необходимо выполнить строку:
Sub Get_All_File_from_Folder() Dim sFolder As String, sFiles As String 'диалог запроса выбора папки с файлами With Application.FileDialog(msoFileDialogFolderPicker) If .Show = False Then Exit Sub sFolder = .SelectedItems(1) End With sFolder = sFolder & IIf(Right(sFolder, 1) = Application.PathSeparator, "", Application.PathSeparator) 'отключаем обновление экрана, чтобы наши действия не мелькали Application.ScreenUpdating = False sFiles = Dir(sFolder & "*.xls*") Do While sFiles <> "" 'показываем этап выполнения Application.StatusBar = "Обрабатывается файл '" & sFiles & "'" 'открываем книгу Workbooks.Open sFolder & sFiles 'действия с файлом 'Запишем на первый лист книги в ячейку А1 - www.excel-vba.ru ActiveWorkbook.Sheets(1).Range("A1").Value = "www.excel-vba.ru" 'Закрываем книгу с сохранением изменений ActiveWorkbook.Close True 'если поставить False - книга будет закрыта без сохранения sFiles = Dir Loop 'возвращаем ранее отключенное обновление экрана Application.ScreenUpdating = True 'сбрасываем значение статусной строки Application.StatusBar = False End Sub
Если запустить код, то перед открытием каждой книги в строке StatusBar будет показано какой именно файл отрывается и обрабатывается. И так с каждым файлом:
В случае же с циклами, количество итераций которых есть возможность определить, можно показывать этап выполнения в процентах. Например, цикл по всем выделенным ячейкам:
Sub ShowProgressBar() Dim lAllCnt As Long, lr as Long Dim rc As Range 'кол-во ячеек в выделенной области lAllCnt = Selection.Count 'цикл по всем ячейкам в выделенной области For Each rc In Selection 'прибавляем 1 при каждом шаге lr = lr + 1 Application.StatusBar = "Выполнено: " & Int(100 * lr / lAllCnt) & "%" DoEvents 'чтобы форма перерисовывалась Next 'сбрасываем значение статусной строки Application.StatusBar = False End Sub
В строке статуса это будет выглядеть так:
Но можно показывать информацию и в чуть более изощренных формах:
Вариант отображения % и блоками-цифрами от 1 до 10 (1 = 10% выполнения)
Sub StatusBar1() Dim lr As Long, lrr As Long, lp As Double Dim lAllCnt As Long 'кол-во итераций Dim s As String lAllCnt = 10000 'основной цикл For lr = 1 To lAllCnt lp = lr \ 100 'десятая часть всего массива s = "" 'формируем строку символов(от 1 до 10) For lrr = 10102 To 10102 + lp \ 10 s = s & ChrW(lrr) Next 'выводим текущее состояние выполнения Application.StatusBar = "Выполнено: " & lp & "% " & s: DoEvents DoEvents Next 'очищаем статус-бар от значений после выполнения Application.StatusBar = False End Sub
Вариант отображения % и стрелками -> (1 стрелка = 10% выполнения)
Sub StatusBar2() Dim lr As Long, lp As Double Dim lAllCnt As Long 'кол-во итераций Dim s As String lAllCnt = 10000 For lr = 1 To lAllCnt lp = lr \ 100 'десятая часть всего массива 'формируем строку символов(от 1 до 10) s = String(lp \ 10, ChrW(10152)) & String(11 - lp \ 10, ChrW(8700)) Application.StatusBar = "Выполнено: " & lp & "% " & s: DoEvents DoEvents Next 'очищаем статус-бар от значений после выполнения Application.StatusBar = False End Sub
Вариант отображения % и квадратами (кол-во квадратов можно изменять. Если lMaxQuad=20 - каждый квадрат одна 20-я часть всего массива)
Sub StatusBar3() Dim lr As Long Dim lAllCnt As Long 'кол-во итераций Const lMaxQuad As Long = 20 'сколько квадратов выводить lAllCnt = 10000 For lr = 1 To lAllCnt Application.StatusBar = "Выполнено: " & Int(100 * lr / lAllCnt) & "%" & String(CLng(lMaxQuad * lr / lAllCnt), ChrW(9632)) & String(lMaxQuad - CLng(lMaxQuad * lr / lAllCnt), ChrW(9633)) DoEvents Next 'очищаем статус-бар от значений после выполнения Application.StatusBar = False End Sub
Этот вариант мне нравится больше всего.
При желании можно сделать и иные варианты - главное найти символы, которые будут показываться и определить их числовой код.
Использование UserForm
Использование стандартного элемента ProgressBar
Для Userform можно использовать стандартный контрол ProgressBar, но я лично не люблю добавлять на формы элементы, которые надо подключать отдельно. Потому как впоследствии контрол может отказаться работать, т.к. нужной версии не окажется на конечном ПК пользователя. Например в моем офисе 2010 для 64-битных систем его нет.
Поэтому про него кратко и в файле примере его нет. Как его создать:
- создаем UserForm (в меню VBE -Insert -UserForm. Подробнее про вставку модулей и форм - Что такое модуль? Какие бывают модули?)
- отображаем окно конструктора(если не отображено): View -Toolbox
- далее в меню Tools -Additional Controls
- там ищем что-то имеющее в названии ProgressBar и отмечаем его. Жмем Ок.
Теперь в окне Toolbox появится элемент ProgressBar. Просто перетаскиваем его на форму. В свойствах можно задать цвет и стиль отображения полосы прогресса. Останется лишь при необходимости программно показывать форму и задавать для элемента ProgressBar значения минимума и максимума. Примерно это выглядеть будет так:
Практический код
Например, надо обработать все выделенные ячейки. Если форма называется UserForm1, а ProgressBar - ProgressBar1, то код будет примерно такой:
Sub ShowProgressBar() Dim lAllCnt As Long Dim rc As Range 'кол-во ячеек в выделенной области lAllCnt = Selection.Count 'показываем форму прогресс-бара UserForm1.Show UserForm1.ProgressBar1.Min = 1 UserForm1.ProgressBar1.Max = lAllCnt 'цикл по всем ячейкам в выделенной области For Each rc In Selection 'прибавляем 1 при каждом шаге UserForm1.ProgressBar1.Value = UserForm1.ProgressBar1.Value + 1 DoEvents 'чтобы форма перерисовывалась Next 'закрываем форму Unload UserForm1 End Sub
Использование своего собственного прогресс-бара
Я использую в своих приложениях свой прогресс-бар с процентами. Для этого я использую стандартную UserForm, на которой располагаю два элемента Caption. Первый отвечает за визуальную составляющую в виде синей полосы заполнения, а так же за отображение процентов белыми цифрами на синем фоне. Второй Caption прозрачный и на нем в том же месте, что и у первого, отображаются проценты цифрами, но уже черным шрифтом. В результате в работе это выглядит так:
Как использовать эту форму и коды
Первоначально надо скачать файл, приложенный к статье, и в свой проект перенести форму frmStatusBar и модуль mCustomProgressBarModule.
Далее просто внедряем нужные строки в свои коды с циклами:
- До начала цикла необходимо вызывать процедуру инициализации формы:
Call Show_PrBar_Or_No(lAllCnt, "Обрабатываю данные. ")
первым аргументом задается общее кол-во обрабатываемых элементов, а вторым заголовок формы. Если второй аргумент не указан, то по умолчанию будет показан заголовок "Выполнение. ". Так же внутри кодов есть кусок кода, отвечающий за минимальное кол-во элементов к обработке. По умолчанию задано 10. Это значит, что если обрабатывается менее 10 ячеек, то форма прогресс-бара показана не будет. Нужно для случаев, когда производятся разные действия над ячейками, но неизвестно сколько их будет. Но зато известно, что с ними будет делать код. Часто для кол-ва ячеек менее 100 нет смысла отображать прогресс выполнения, т.к. это и так секундное дело.
Чтобы изменить минимальное кол-во достаточно в строке bShowBar = (lCnt > 10) заменить 10 на нужное число. - Далее в каждом проходе цикла вызвать перерисовку формы под новое значение цикла:
If bShowBar Then Call MyProgresBar - и в конце не забыть закрыть форму, чтобы не висела:
If bShowBar Then Unload frmStatusBar
Пример применения формы:
Sub Test_ProgressForm() Dim lr As Long Dim lAllCnt As Long 'кол-во итераций lAllCnt = 10000 'инициализируем форму прогресс-бара Call Show_PrBar_Or_No(lAllCnt, "Обрабатываю данные. ") 'сам цикл For lr = 1 To lAllCnt If bShowBar Then Call MyProgresBar Next 'закрываем форму, если она была показана If bShowBar Then Unload frmStatusBar End Sub
Рис. 1. В окне UserForm отображается ход выполнения макроса
Мы рассмотрим три метода создания индикаторов текущего состояния:
- Макрос, который запускается за пределами диалогового окна UserForm (отдельный индикатор текущего состояния).
- Макрос, который запускается из диалогового окна UserForm. При этом в диалоговом окне UserForm используется элемент управления MultiPage для отображения индикатора текущего состояния, пока выполняется другой макрос.
- Макрос, который запускается из диалогового окна UserForm. При этом высота диалогового окна UserForm увеличивается, а индикатор текущего состояния отображается в нижней части окна.
Отображение индикатора текущего состояния в строке состояния окна
Простой способ отображения хода выполнения макроса — использование строки состояния Excel. Его преимущество — простота реализации. Недостатком же является то, что большинство пользователей не привыкли отслеживать информацию, которая отображается в строке состояния окна, поскольку предпочитают просматривать ее в отдельном окне.
Application.StatusBar = " Пожалуйста, подождите… "
Можно обновлять строку состояния в процессе выполнения макроса. Например, если в макросе используется переменная Pet, которая представляет состояние задачи, можно создать код, который будет периодически выполнять следующий оператор:
Application.StatusBar = " Выполнение… " &Pet& " % завершено "
После завершения макроса нужно вернуть строку состояния к прежнему виду. Для этого используется следующий оператор:
Создание отдельного индикатора текущего состояния
Такой индикатор не инициализируется путем отображения формы UserForm. Следующий макрос очищает рабочий лист, и записывает 20 тысяч случайных чисел в диапазон ячеек (см. также файл progress indicatorl.xlsm).
После небольшого изменения макроса (описанного в следующем разделе) диалоговое окно UserForm отображает индикатор процесса выполнения макроса (рис. 1).
Создание диалогового окна UserForm, включающего индикатор текущего состояния
Выполните следующие шаги:
- Вставьте новое диалоговое окно UserForm и измените значение свойства Caption на Ход выполнения процесса.
- Добавьте элемент управления Frame и присвойте ему имя FrameProgress.
- Добавьте элемент управления Label в состав элемента управления Frame и назначьте ему имя LabelProgress. Удалите заголовок этого элемента управления, а также сделайте его фон красным (посредством свойства BackColor). На данный момент размеры и расположение этого элемента управления не важны.
- Добавьте еще один элемент управления Label над элементом управления Frame, с помощью которого вы будете описывать происходящее (необязательно). В нашем примере с помощью этого элемента управления добавляется надпись Ход выполнения процесса.
- Настройте диалоговое окно UserForm и элементы управления таким образом, чтобы они выглядели, как на рис. 2.
Рис. 2. Окно формы UserForm может играть роль индикатора хода выполнения процесса
Создание процедур обработки событий. Важно, чтобы процедура автоматически запускалась при отображении диалогового окна UserForm. Один из вариантов подразумевает использование события Initialize. Но это событие возникает еще до того, как диалоговое окно отображается на экране, поэтому такой вариант не подходит. С другой стороны, событие Activate возникает в тот момент, когда диалоговое окно UserForm отображается на экране, поэтому в данном случае можно остановиться на его использовании.
Вставьте следующую процедуру в модуль кода диалогового окна UserForm. Эта процедура всего лишь вызывает процедуру GenerateRandomNumbers, когда диалоговое окно UserForm отображается на экране. Процедура GenerateRandomNumbers, которая хранится в модуле кода VBA, является фактическим макросом, который будет работать, пока на экране отображается индикатор текущего состояния.
В процедуре GenerateRandomNumber имеется дополнительный модуль, который отслеживает текущее состояние, сохраняя соответствующие сведения в переменной PctDone.
Создание процедуры запуска. Для отображения диалогового окна UserForm введите следующий код в модуль VBA.
Можно сделать так, чтобы строка состояния соответствовала текущей теме рабочей книги. Для этого в процедуру ShowUserForm добавьте следующий оператор.
.LabelProgress.BackColor = ActiveWorkbook.Theme. _
ThemeColorScheme.Colors(msoThemeAccentl)
При выполнении процедуры ShowUserForm ширина объекта Label устанавливается равной 0. После этого вызывается метод Show объекта UserForm1, что приводит к отображению диалогового окна UserForm (которое играет роль индикатора текущего состояния). Когда диалоговое окно UserForm отображается на экране, вызывается событие Activate, которое приводит к выполнению процедуры GenerateRandomNurabers. Процедура GenerateRandomNumbers включает код, который вызывает процедуру UpdateProgress при каждом изменении переменной счетчика цикла r. Обратите внимание, что процедура UpdateProgress использует метод Repaint объекта UserForm. Если бы этого оператора не было, изображение на экране не обновлялось бы. Перед завершением процедуры GenerateRandomNumbers ее последний оператор выгружает диалоговое окно UserForm из памяти.
Для того чтобы модифицировать эту методику, необходимо разобраться, как определяется процент завершения выполняемой задачи. После этого значение состояния задачи можно будет присваивать переменной PctDone. Определение этой величины осуществляется различными способами в зависимости от приложения. Если код выполняется в цикле (как в данном примере), вычисление процента выполнения сравнительно несложное. Если же код выполняется не в цикле, определение состояния выполнения происходит в различных точках кода.
Отображение сведений о текущем состоянии с помощью элемента управления MultiPage
- проект завершен и отлажен;
- в проекте используется диалоговое окно UserForm (без элемента управления MultiPage) для запуска долго выполняющегося макроса;
- существует метод оценки степени завершения выполняемой задачи.
Как и в предыдущем примере, в рабочий лист вводятся случайные числа. Отличие заключается в том, что в приложении содержится диалоговое окно UserForm, в котором пользователем определяется количество строк и столбцов для ввода случайных чисел (рис. 3; см. также файл progress indicator2.xlsm).
Рис. 3. Пользователь определяет количество строк и столбцов, в которые вводятся случайные числа
Изменение диалогового окна. На данном этапе предполагается, что диалоговое окно UserForm уже настроено. Нам осталось добавить элемент управления MultiPage. Первая страница элемента управления MultiPage будет содержать все первоначальные элементы управления. На второй странице располагаются элементы управления, которые используются для отображения индикатора текущего состояния. Когда макрос начнет выполняться, в коде VBA значение свойства Value элемента управления MultiPage изменится. В результате будут скрыты исходные элементы управления и отображены элементы управления, которые используются для создания индикатора текущего состояния.
Первым шагом будет добавление элемента управления MultiPage в диалоговое окно UserForm. После этого необходимо переместить все существующие в диалоговом окне UserForm элементы управления на первую страницу элемента управления MultiPage. Затем следует активизировать вторую страницу элемента управления MultiPage и настроить ее так, чтобы она выглядела, как показано на рис. 4. В данном случае используется та же комбинация элементов управления, что и в предыдущем примере.
- Добавьте элемент управления Frame, присвоив ему имя FrameProgress.
- Добавьте элемент управления Label в состав элемента управления Frame, присвоив ему имя LabelProgress. Удалите заголовок этого элемента управления, а также сделайте его фоновый цвет красным.
- Добавьте еще один элемент управления Label, описывающий суть происходящего (необязательно).
- Активизируйте элемент управления MultiPage в целом (а не отдельную вкладку), а свойству Style присвойте значение 2 – fmTabStyleNone. (Это приведет к сокрытию всех вкладок.) Возможно, придется изменить размер элемента управления MultiPage, чтобы учесть скрытые вкладки.
Рис. 4. Вторая вкладка элемента управления MultiPage, которая применяется для отображения индикатора текущего состояния
Простейший способ выделения элемента управления MultiPage, когда вкладки скрыты, — выбор его в раскрывающемся списке, который находится в окне Properties (подчеркнут красной линией на рис. 4). Для выбора определенной страницы укажите величину свойства Value для элемента MultiPage: 0 — для Page1, 1 — для Page2 и т.д.
Вставка процедуры UpdateProgress. Вставьте следующую процедуру в модуль кода диалогового окна UserForm.
Возвращает или задает текст в панели состояния. Для чтения и записи, String.
Синтаксис
выражения. StatusBar
выражение: переменная, представляющая объект Application.
Примечания
Это свойство возвращает False, если Microsoft Excel имеет контроль над баром состояния. Чтобы восстановить текст панели состояния по умолчанию, установите свойство False; это работает, даже если планка состояния скрыта.
Пример
В этом примере устанавливается текст панели состояния "Пожалуйста, будьте терпеливы. " перед открытием книги Large.xls, а затем восстанавливает текст по умолчанию.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Кому-как, а мне лично строка состояния бывает нужна только в 2-3 случаях:
- после фильтрации в ней отображается количество оставшихся после отбора значений
- при выделении диапазона в ней отображается сумма, среднее и количество выделенных ячеек
- в случае тяжелых файлов в ней можно увидеть прогресс по пересчету формул в книге.
Не так уж и много для строки, которая занимает почти всю ширину экрана и висит на нем постоянно. Давайте попробуем расширить этот скромный список и прикрутить к ней еще несколько полезных функций :)
Общие принципы управления строкой состояния
Управление строкой состояния с помощью Visual Basic очень несложно. Чтобы вывести в нее свой текст, можно использовать простой макрос:
После его запуска получим:
Чтобы восстановить исходное состояние строки статуса нужен будет такой же короткий "антимакрос":
В базовом варианте, как видите, все очень просто. Теперь давайте попробуем развить идею.
Адрес выделенного диапазона в строке состояния
В левом верхнем углу окна Excel в строке формул всегда можно увидеть адрес текущей ячейки. Но если выделен целый диапазон, то адреса выделения мы там, к сожалению, не увидим - отображается все та же одна единственная активная ячейка:
Для решения этой задачи можно использовать простой макрос, который будет выводить адрес выделенной области в строку состояния. Причем запускаться этот макрос должен автоматически, при любом изменении выделения на любом листе - для этого разместим его в обработчике события SelectionChange нашей книги.
Откройте редактор Visual Basic с помощью одноименной кнопки на вкладке Разработчик (Developer) или сочетания клавиш левый Alt+F11. Найдите в левом верхнем углу на панели Project свою книгу и откройте в ней двойным щелчком модуль ЭтаКнига (ThisWorkbook) :
В открывшееся окно скопируйте и вставьте следующий код макроса:
Теперь при выделении любого диапазона (в том числе и не одного!), в строке состояния будет отображаться его адрес:
Чтобы адреса нескольких выделенных с Ctrl диапазонов не сливались, можно добавить небольшое улучшение - заменить с помощью функции Replace запятую на запятую с пробелом:
Количество выделенных ячеек в строке состояния
При выделении любого диапазона в правой части строки состояния стандартно отображается количество непустых выделенных ячеек. Иногда же нужно знать количество выделенных. Эту задачу также можно решить с помощью простого макроса обработки события книги SelectionChange, как в предыдущем примере. Нужен будет макрос вида:
Этот макрос перебирает все выделенные с Ctrl области (если их несколько), сохраняет количество строк и столбцов в каждой области в переменных RowsCount и ColumnsCount и накапливает количество ячеек в переменной CellCount, которая затем и выводится в строку состояния. В работе это будет выглядеть следующим образом:
Само-собой, можно соединить этот и предыдущий макросы, чтобы выводить и адрес выделенного диапазона и количество ячеек одновременно. Понадобится изменить лишь одну предпоследнюю строку на:
Тогда картина будет совсем замечательная:
Ну, я думаю, вы поняли идею. Предлагайте в комментариях - что еще полезного можно было бы отобразить в строке состояния?
Читайте также: