Как посчитать листы в Excel - Сортировка листов книги Excel с помощью процедуры VBA

В настоящей заметке описана разработка утилиты VBA для Excel. Показан процесс анализа задачи и последующего ее решения. Пример рассмотрен в расчете на начинающих.[1] Цель — разработать утилиту, которая изменяет порядок следования листов рабочей книги, сортируя их названия по алфавиту (без кода VBA это сделать невозможно). Если вы часто создаете книги с большим количеством листов, то знаете, что иногда сложно найти интересующий вас лист. Если же их упорядочить по названиям, то любой рабочий лист найти будет значительно проще.

Рис. 1. Метод Move объекта Sheets

Скачать заметку в формате Word или pdf, примеры в архиве (архив содержит два файла Excel; политикой провайдера файлы с поддержкой макросов загрузить на сайт нельзя)

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

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

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

В Excel отсутствует команда сортировки листов. Следовательно, отпадает вариант записи макроса для упорядочивания листов в алфавитном порядке.
Лист можно легко переместить, перетащив его за ярлычок (включите функцию записи макросов и перетащите лист в другое место, чтобы узнать, какой код создается при таком действии).
В Excel можно открыть диалоговое окно Переместить или скопировать, щелкнув правой кнопкой мыши на ярлычке листа с последующим выбором команды контекстного меню (убедитесь, что код макроса будет таким же, как и при перемещении листа вручную).
Следует знать, сколько листов содержится в активной рабочей книге. Эту информацию можно получить с помощью VBA.
Узнайте названия листов (вновь воспользовавшись VBA).
В Excel существует команда, сортирующая данные в ячейках рабочего листа. Возможно, стоит перенести названия листов в диапазон ячеек и использовать эту функцию. Или, возможно, в VBA есть метод сортировки, которым можно будет воспользоваться в программе.
Благодаря диалоговому окну Параметры макроса можно назначить макросу комбинацию клавиш.
Если макрос сохранен в личной книге макросов, он всегда доступен.
Вам понадобится тестировать приложение по мере разработки. Естественно, нельзя тестировать приложение в той же рабочей книге, в которой оно разработано. Создайте рабочую книгу, предназначенную специально для тестирования.
Если разработать программу правильно, то VBA не будет отображать сообщения об ошибках. Не будем принимать желаемое за действительное…

Вот предварительный план, описывающий общие задачи:

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

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

Откройте новую рабочую книгу, содержащую три рабочих листа. Включите функцию записи макросов и перетащите третий рабочий лист на место первого. Остановите запись макроса. Изучите код:

Sub Макрос1() Sheets("Лист3").Select Sheets("Лист3").Move Before:=Sheets(1) End Sub

Найдите в справочной системе слово Move (это метод, перемещающий лист в рабочей книге на новое место). Для этого, например, находясь в окне VBE, нажмите F2 (рис. 1; см. также справку в Интернете). Данный метод имеет один аргумент, определяющий будущее положение листа.

Вам также необходимо узнать количество листов в активной рабочей книге. Активизируем окно отладки (Immediate) в VBE (нажав Ctrl+G) и введем такой оператор:

? ActiveWorkbook.Sheets.Count

VBA вернула значение 3 (рис. 2).

Рис. 2. Использование окна отладки в VBE для тестирования оператора

Теперь введем в окне отладки (Immediate) следующий оператор:

? ActiveWorkbook.Sheets(1).Name

В результате будет получено название первого листа — ЛистЗ.

Конструкция For Each-Next используется для циклического просмотра всех членов коллекции (см., например, Основы программирования на VBA, раздел Управление объектами и коллекциями):

Sub Test() For Each Sht In ActiveWorkbook.Sheets MsgBox Sht.Name Next Sht End Sub

Макрос отобразил три окна сообщения, в каждом из которых — новое название листа.

Что касается сортировки, справочная система подскажет, что метод Sort относится к объекту Range. Поэтому одним из решений задачи могло быть перенесение названия листов в диапазон ячеек и сортировка этого диапазона. Однако такая задача слишком сложна. Возможно, целесообразнее сформировать из названий листов массив строк, а затем отсортировать этот массив с использованием кода VBA.

Однако прежде следует задать первоначальные настройки:

Создайте пустую рабочую книгу с пятью рабочими листами: названия — Лист1, Лист2, ЛистЗ, Лист4 и Лист5.
Разместите листы произвольно, чтобы они следовали не по порядку.
Сохраните рабочую книгу как Test.xlsm.
Перейдите в VBE (меню Разработчик —> Visual Basic) и выберите проект Personal.xlsb в окне Project (Проект). Если Personal.xlsb не отображается в окне Project, значит вы никогда не использовали личную книгу макросов. Excel создаст для вас эту книгу, когда вы запишете макрос (любой) и определите, что он должен сохраняться в личной книге макросов (подробнее см. Создание личной книги макросов).
Добавьте новый модуль VBA (используя команду Inserts —> Module).
Создайте пустую процедуру с названием SortSheets (рис. 3).
Перейдите в Excel. Выберите команду Разработчик —> Код —> Макросы для отображения диалогового окна Макрос.
В диалоговом окне Макрос выберите процедуру SortSheets и щелкните на кнопке Параметры. В открывшемся окне Параметры макроса выберите Ctrl+Shift+S.

Рис. 3. Пустая процедура в модуле, находящемся в персональной книге макросов

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

Начинаем писать код процедуры

Вначале необходимо поместить названия листов в массив строк. Так как пока неизвестно, сколько листов содержит активная рабочая книга, для объявления массива используем оператор Dim с пустыми скобками. Помните, что затем нужно применить оператор ReDim и изменить размерность массива на требуемое число элементов (подробнее см. Основы программирования на VBA, раздел Массивы). В цикл добавим функцию MsgBox, чтобы убедиться, что названия листов на самом деле вводятся в массив.

Sub SortSheets() ' Сортировка листов в активной рабочей книге Dim SheetNames() as String Dim i as Long Dim SheetCount as Long SheetCount = ActiveWorkbook.Sheets.Count ReDim SheetNames(1 To SheetCount) For i = 1 To SheetCount SheetNames(i) = ActiveWorkbook.Sheets(i).Name MsgBox SheetNames(i) Next i End Sub

Поместим код в модуль VBA, перейдем в Excel, активизируем книгу Test.xlsm и нажмем клавиши Ctrl+Shift+S. Появится пять окон сообщений с названиями листов активной рабочей книги. Рекомендуем вам тестировать код по мере его создания. Когда вы убедитесь, что программа работает правильно, удалите операторы MsgBox. Вместо того чтобы использовать функцию MsgBox в целях тестирования, можно обратиться к методу Print объекта Debug, который отображает сведения в окне отладки. Для этого замените MsgBox следующим оператором:

Debug.Print SheetNames(i)

Этот прием не столь навязчив по сравнению с использованием операторов MsgBox. Не забудьте только удалить оператор по завершении тестирования.

Рис. 4. Использование метода Print объекта Debug в целях тестирования. Разместите курсор внутри текста процедуры Sub SortSheets(), откройте окно Immediate (Ctrl+G), нажмите Run. В окне Immediate отразятся номера листов книги; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Пока процедура SortSheets всего лишь создает массив названий листов в соответствии с порядком их следования в активной рабочей книге. Теперь нужно отсортировать значения в массиве SheetNames и изменить порядок следования листов в книге согласно отсортированному массиву.

Создание процедуры сортировки

Можно вставить программу сортировки в процедуру SortSheets, но лучше написать общую процедуру сортировки, которую можно будет использовать и в других проектах (сортировка массивов — довольно популярная операция).

Существует несколько способов сортировки массивов. Мы выбрали пузырьковый метод (хотя это не очень быстрый прием, но его легко запрограммировать). В данном конкретном приложении высокая скорость выполнения операций не так уж важна. В пузырьковом методе используется вложенный цикл For-Next, в котором оценивается каждый элемент массива. Если элемент массива больше, чем следующий, то эти два элемента меняются местами. Такое сравнение повторяется для каждой пары элементов (т.е. n — 1 раз).

Sub BubbleSort(List() As String) ' Сортировка массива List по возрастанию Dim First As Long, Last As Long Dim i As Long, j As Long Dim Temp As String First = LBound(List) Last = UBound(List) For i = First To Last — 1 For j = i + 1 To Last If List(i) > List(j) Then Temp = List(j) List (j) = List(i) List(i) = Temp End If Next j Next i End Sub

Эта процедура имеет один аргумент: одномерный массив с названием List. Массив, который передается в процедуру, может быть любой длины. Для присвоения нижней и верхней границ массива переменным First и Last использовались функции Lbound и UBound соответственно.

Ниже приведен код для тестирования процедуры BubbleSort:

Sub SortTester() Dim x(1 To 5) As String Dim i As Long x(1) = "собака" x(2) = "кот" x(3) = "слон" x(4) = "трубкозуб" x(5) = "птица" Call BubbleSort(x) For i = 1 To 5 Debug.Print i, x(i) Next i End Sub

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

Убедившись в том, что код работает надежно, я изменил процедуру SortSheets путем добавления вызова в процедуру BubbleSort, передачи массива SheetNames в качестве аргумента. Начиная с этого момента, модуль приобретает следующий вид.

По окончании работы процедуры SortSheets образуется массив, состоящий из отсортированных названий листов активной рабочей книги. Чтобы проверить это, можно отобразить содержимое массива в окне отладки, добавив в конец процедуры перед оператором End Sub такой код:

For i = 1 То SheetCount Debug.Print SheetNames(i) Next i

Осталось написать программу для изменения порядка следования листов в книге в соответствии с отсортированными элементами массива SheetNames. Нам пригодится фрагмент макроса, записанного в начале заметки: Sheets("ЛистЗ").Move Before:=Sheets(1)

Напишем цикл For-Next, который просматривает каждый лист и перемещает его в соответствующее место, указанное в массиве SheetNames.

For i = 1 То SheetCount Sheets(SheetNames(i)).Move Before:=Sheets(i) Next i

Например, в первой итерации цикла счетчик i = 1. Первый элемент массива SheetNames — Лист1. Следовательно, выражение для метода Move в цикле будет таким:

Sheets("Лист1").Move Before:= Sheets(1)

Вторая итерация цикла:

Sheets("Лист2").Move Before:= Sheets(2)

В конец процедуры SortSheets добавим новый код:

Sub SortSheets () Dim SheetNames() As String Dim SheetCount as Long Dim i as Long SheetCount = ActiveWorkbook.Sheets.Count ReDim SheetNames(1 To SheetCount) For i = 1 To SheetCount SheetNames(i) = ActiveWorkbook.Sheets(i).Name Next i Call BubbleSort(SheetNames) For i = 1 To SheetCount ActiveWorkbook.Sheets(SheetNames(i)).Move _ Before:=ActiveWorkbook.Sheets(i) Next i End Sub