Что делает макрос: Создать оглавление книги — наиболее запрашиваемый макрос Excel.
Нам часто приходится работать с файлами, которые имеют много вкладок. Таблица содержания определенно помогает.
Следующий макрос не только создает список имен рабочих таблиц в книге, но он также показывает гиперссылки, так что вы можете легко перейти к листу с простым нажатием кнопки.
Как макрос работает
На первый взгляд данный макрос выглядит сложным. Тем не менее, рассмотрим несколько простых действий, как это происходит
- Удаляет любую предыдущую таблицу Содержание листа
- Создает новую таблицу Содержание листа
- Захватывает имя каждого листа и вставляет его в оглавлении
- Добавляет гиперссылку к каждой записи в оглавлении
Код макроса
Sub SozdatOglavlenie()
'Шаг 1: Объявить переменные
Dim i As Long
'Шаг 2: Удалить предыдущий лист с названием Оглавление и перейти к 'следующему
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Table Of Contents").Delete
Application.DisplayAlerts = True
On Error GoTo 0
'Шаг 3: Добавить новый лист в качестве первого
ThisWorkbook.Sheets.Add _
Before:=ThisWorkbook.Worksheets(1)
ActiveSheet.Name = "Оглавление"
'Шаг 4: Запустить счетчик
For i = 1 To Sheets.Count
'Шаг 5: Выбрать следующую доступную строку
ActiveSheet.Cells(i, 1).Select
'Шаг 6: Добавить имя листа и гиперссылку
ActiveSheet.Hyperlinks.Add _
Anchor:=ActiveSheet.Cells(i, 1), _
Address:="", _
SubAddress:="'" & Sheets(i).Name & "'!A1", _
TextToDisplay:=Sheets(i).Name
'Шаг 7: Цикл назад приращение I
Next i
End Sub
Как этот код работает
- Шаг 1 объявляет целую переменную с именем I, чтобы служить в качестве счетчика, который перебирает листы.
- В предыдущих макросах переменная выбирала каждый рабочий лист. В этой процедуре мы используем счетчик (наша переменная I). Основная причина в том, что мы не только должны следить за листами, но мы также должны суметь ввести имя каждого листа на новую строку в таблицу оглавления. Идея заключается в том, что, как счетчик проходит через листы, он также служит для перемещения курсора вниз в таблице содержания, каждая новая запись идет на новую строку.
- Шаг 2, по существу пытается удалить любой предыдущий лист под названием «Оглавление». Шаг 2 должен начаться с On Error Resume Next — обработчика ошибок. Если возникает ошибка — происходит удаление оглавления листа с использованием метода DisplayAlerts. Наконец, мы сбрасываем обработчик ошибок, снова введя On Error GoTo 0.
- На шаге 3 добавляем новый лист в книгу, используя аргумент, чтобы поместить новый лист в качестве первого листа. Затем именуется лист как «Оглавление». Как мы уже упоминали ранее в этой части, при добавлении нового рабочего листа, то он автоматически становится активным. Поскольку этот новый лист имеет фокус на протяжении всей процедуры, любые ссылки на ActiveSheet в этом коде, обращаются к таблице содержания листа.
- Шаг 4 запускает счетчик I и происходит подсчет всех листов в книге. Когда будет достигнуто максимальное количество листов, конец макрокоманды.
- Шаг 5 выбирает соответствующую строку в таблице содержания листа. То есть, если счетчик на 1, он выбирает первую строку в таблице содержания листа. Если счетчик I находится в 2, она выбирает вторую строку, и так далее.
- Мы можем сделать это с помощью пункта меню Cells. Элемент
Cells обеспечивает чрезвычайно удобный способ выбора диапазонов с помощью кода. Для этого требуется только относительные строки и столбца позиции в качестве параметров. Таким образом, клетки (1,1) приводит к строке 1, столбец 1 (или ячейку A1). Ячейка (5, 3) приводит к 5-й строке, столбце 3 (или ячейка C5). Числовые параметры в пункте ячейки особенно удобны, когда вы хотите, чтобы цикл шел через серию строк или столбцов, используя увеличивающееся номер индекса. - Шаг 6 использует метод Hyperlinks.Add, чтобы добавить имя листа и гиперссылки к выбранной ячейке. Параметры методом Hyperlinks.Add необходимы для построения гиперссылок.
- Последний шаг в макросе возвращает к началу цикла приращения счетчика I к следующему. Когда счетчик достигает числа I конец макрокоманд.
Как использовать
Для реализации этого макроса, вы можете скопировать и вставить его в стандартный модуль:
- Активируйте редактор Visual Basic, нажав ALT + F11.
- Щелкните правой кнопкой мыши имя проекта / рабочей книги в окне проекта.
- Выберите Insert➜Module.
- Введите или вставьте код во вновь созданном модуле.