Полезная информация в строке состояния
Кому-как, а мне лично строка состояния бывает нужна только в 2-3 случаях:
- после фильтрации в ней отображается количество оставшихся после отбора значений
- при выделении диапазона в ней отображается сумма, среднее и количество выделенных ячеек
- в случае тяжелых файлов в ней можно увидеть прогресс по пересчету формул в книге.
Не так уж и много для строки, которая занимает почти всю ширину экрана и висит на нем постоянно. Давайте попробуем расширить этот скромный список и прикрутить к ней еще несколько полезных функций :)
Общие принципы управления строкой состояния
Управление строкой состояния с помощью Visual Basic очень несложно. Чтобы вывести в нее свой текст, можно использовать простой макрос:
Sub MyStatus()
Application.StatusBar = "Привет!"
End Sub
После его запуска получим:
Чтобы восстановить исходное состояние строки статуса нужен будет такой же короткий "антимакрос":
Sub MyStatus_Off()
Application.StatusBar = False
End Sub
В базовом варианте, как видите, все очень просто. Теперь давайте попробуем развить идею...
Адрес выделенного диапазона в строке состояния
В левом верхнем углу окна Excel в строке формул всегда можно увидеть адрес текущей ячейки. Но если выделен целый диапазон, то адреса выделения мы там, к сожалению, не увидим - отображается все та же одна единственная активная ячейка:
Для решения этой задачи можно использовать простой макрос, который будет выводить адрес выделенной области в строку состояния. Причем запускаться этот макрос должен автоматически, при любом изменении выделения на любом листе - для этого разместим его в обработчике события SelectionChange нашей книги.
Откройте редактор Visual Basic с помощью одноименной кнопки на вкладке Разработчик (Developer) или сочетания клавиш левый Alt+F11. Найдите в левом верхнем углу на панели Project свою книгу и откройте в ней двойным щелчком модуль ЭтаКнига (ThisWorkbook):
В открывшееся окно скопируйте и вставьте следующий код макроса:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.StatusBar = "Выделено: " & Selection.Address(0, 0)
End Sub
Теперь при выделении любого диапазона (в том числе и не одного!), в строке состояния будет отображаться его адрес:
Чтобы адреса нескольких выделенных с Ctrl диапазонов не сливались, можно добавить небольшое улучшение - заменить с помощью функции Replace запятую на запятую с пробелом:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.StatusBar = "Выделено: " & Replace(Selection.Address(0, 0), ",", ", ")
End Sub
Количество выделенных ячеек в строке состояния
При выделении любого диапазона в правой части строки состояния стандартно отображается количество непустых выделенных ячеек. Иногда же нужно знать количество выделенных. Эту задачу также можно решить с помощью простого макроса обработки события книги SelectionChange, как в предыдущем примере. Нужен будет макрос вида:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim CellCount As Variant, rng As Range
For Each rng In Selection.Areas 'перебираем все выделенные области
RowsCount = rng.Rows.Count 'число строк
ColumnsCount = rng.Columns.Count 'число столбцов
CellCount = CellCount + RowsCount * ColumnsCount 'накапливаем общее число ячеек
Next
'выводим в строку состояния
Application.StatusBar = "Выделено: " & CellCount & " ячеек"
End Sub
Этот макрос перебирает все выделенные с Ctrl области (если их несколько), сохраняет количество строк и столбцов в каждой области в переменных RowsCount и ColumnsCount и накапливает количество ячеек в переменной CellCount, которая затем и выводится в строку состояния. В работе это будет выглядеть следующим образом:
Само-собой, можно соединить этот и предыдущий макросы, чтобы выводить и адрес выделенного диапазона и количество ячеек одновременно. Понадобится изменить лишь одну предпоследнюю строку на:
Application.StatusBar = "Выделено: " & Replace(Selection.Address(0, 0), ",", ", ") & " - итого " & CellCount & " ячеек"
Тогда картина будет совсем замечательная:
Ну, я думаю, вы поняли идею. Предлагайте в комментариях - что еще полезного можно было бы отобразить в строке состояния?
Ссылки по теме
- Что такое макросы, как они работают, как их использовать и создавать
- Удобное координатное выделение на листе Excel
- Как сделать сложные формулы более наглядными
1 - в строке статуса, соответственно информация о статусе листа, книги
Количество объектов на листе, типы объектов на листе, ... (перечисление через точку с запятой)
2 - вычисления для диаграммы Эйлера - Венна
3 - настраиваемую "Настройка строки статуса" с доп. возможностями которых там нет
Было бы очень удобно также в выделенном диапазоне отображать количество #N/A. Это очень актуально для больших файлов, так как фильтрация и антифильтрация по #N/A для их подсчёта иногда занимает достаточно много времени:(
1
1
2
3
4
4
3
количество уникальных: 4
В PLEX однозначно.
Можно указывать есть ли например проверки данных на листе и где.
Задался таким же вопросом. Как я понял, проблема в том, что событие это имеется на уровне листа или книги, а вот на уровне приложения я такого не нашел. Поэтому гуглим "vba events on application level" - у мелкомягких есть подробный мануал на эту тему. Я повторил действия, но суть понял не очень.
Получилось вот такое решение, у меня работает.
1. В персональную книгу макросов вставляем Class Module (Class1- так называется по умолчанию).
2. В этот модуль такой код:
3. в обычный модуль (тот где у вас Auto_open) персональной книги макросов, в объявления модуля
4. В тот же модуль добавляю Sub
5. В том же модуле вызываем подпрограмму из пункта 4 через Auto_open (чтоб запускалась при открытии Эксель)
PS пункты 3,4,5 хотелось поместить в Auto_open сразу, но что то не пошло.
Как то так.
Насколько я понимаю:
1. Все что я описал нужно поместить в одно место. У меня это персональная книга макросов, у вас может быть что-то другое.
2. Замечания по пункту 2 из моей схемы выше. Во-первых название этого самого Class module - такое же как и в пункте 3. Ну чтобы объявлилось что-то конкрентное. Во-вторых, я конечно забыл кусок кода (собственно событие). Его необходимо вставить первой сторокой (пункт 2 схемы)
Для Николая - нужно дообъявить переменные, возник вопрос на форуме по коду "Количество выделенных ячеек в строке состояния"
Dim RowsCount As Long, ColumnsCount As Long
Т.е. не видны цифры после запятой.
Понимаю, что могу воспользоваться автосуммированием и увижу число полностью, но, может быть, есть возможность менять размер служебного окна в строке состояния или размер шрифта в служебном окне?
Application.StatusBar = "Выделено: " & Selection.Address(0, 0)
End Sub
В Excel 2016 не работает
Подскажите, пожалуйста, как доработать макрос количества выделенных ячеек в строке состояния, чтобы результат отображался "на лету", т. е. при выделении диапазона или копировании перетаскиванием, но до отпускания левой кнопки мыши? Стандартно отображение количества непустых ячеек работает "на лету", а тут нет. Спасибо.