Как посчитать листы в Excel
![](http://officeassist.ru/wp-content/uploads/10912/tmp-74f557d9-58fb-41b0-bcd8-019a0cf0bc51-360x457.jpg)
В настоящей заметке описана разработка утилиты VBA для Excel. Показан процесс анализа задачи и последующего ее решения. Пример рассмотрен в расчете на начинающих.[1] Цель — разработать утилиту, которая изменяет порядок следования листов рабочей книги, сортируя их названия по алфавиту (без кода VBA это сделать невозможно). Если вы часто создаете книги с большим количеством листов, то знаете, что иногда сложно найти интересующий вас лист. Если же их упорядочить по названиям, то любой рабочий лист найти будет значительно проще.
![](http://officeassist.ru/wp-content/uploads/10912/tmp-74f557d9-58fb-41b0-bcd8-019a0cf0bc51.jpg)
Рис. 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).
![](http://officeassist.ru/wp-content/uploads/10912/tmp-e5fb5a2c-fee6-4b3b-840a-ef413f6419bb.jpg)
Рис. 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.
![](http://officeassist.ru/wp-content/uploads/10912/tmp-55bfda5f-c3e3-4af6-bbed-c42938e08262.jpg)
Рис. 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. Не забудьте только удалить оператор по завершении тестирования.
![](http://officeassist.ru/wp-content/uploads/10912/tmp-e8ac4611-d756-435d-a90e-b23ec0e2df9c.jpg)
Рис. 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 в качестве аргумента. Начиная с этого момента, модуль приобретает следующий вид.
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 Next i Call BubbleSort(SheetNames) End Sub
По окончании работы процедуры 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