Посчитать количество листов в книге excel vba

Обновлено: 03.07.2024

Довольно часто при добавлении листов в книгу кодом необходимо удостовериться существует ли уже лист с таким именем или же нет. Т.к. если уже существует, то попытка создать лист с таким же именем неизбежно приведет к ошибке. Можно, конечно, поставить обработчик ошибки On Error.

Sub Add_New_Sheet() On Error Resume Next Sheets.Add(, Sheets(Sheets.Count)).Name = "Новый лист" End Sub

Но тогда, если лист с таким именем уже существует, будет создан лист со следующим порядковым номером(типа Лист4). А этого в большинстве случаев не надо, т.к. обычно планируется все же либо создать лист с нужным именем, либо не создавать вовсе.
Я обычно проверяю так:

Sub Add_New_Sheet() Dim wsSh As Worksheet On Error Resume Next Set wsSh = Sheets("Новый лист") If wsSh Is Nothing Then Sheets.Add(, Sheets(Sheets.Count)).Name = "Новый лист" 'здесь можно либо активировать лист, либо производить еще какие действия 'wsSh.Activate End Sub

Если предполагается использовать такую проверку более одного раза в коде, то имеет смысл вынести проверку в отдельную функцию

Function Sh_Exist(wb As Workbook, sName As String) As Boolean Dim wsSh As Worksheet On Error Resume Next Set wsSh = wb.Sheets(sName) Sh_Exist = Not wsSh Is Nothing End Function

Функция проверяет наличие листа в указанной книге и возвращает True, если лист есть в книге и False, если листа нет.
wb - объект Workbook, наличие листа в которой надо проверить.
sName - имя листа, наличие которого необходимо проверить.
Код функции Sh_Exist необходимо поместить в стандартный модуль и тогда для проверки наличия листа достаточно будет одной строки кода:

Sub Add_New_Sheet() If Not Sh_Exist(ActiveWorkbook, "Новый лист") Then ActiveWorkbook.Sheets.Add(, ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)).Name = "Новый лист" End If End Sub

"Новый лист" - вместо этого текста указывается имя листа, наличие которого необходимо проверить.
Если проверять надо не в активной книге, а в какой-либо другой, то вместо ActiveWorkbook необходимо указать эту книгу. Например, если книга называется "Отчет.xlsx", то код будет таким:

Sub Add_New_Sheet() If Not Sh_Exist(Workbooks("Отчет.xlsx"), "Новый лист") Then Workbooks("Отчет.xlsx").Sheets.Add(, Workbooks("Отчет.xlsx").Sheets(Workbooks("Отчет.xlsx").Sheets.Count)).Name = "Новый лист" End If End Sub

Хотя в данном случае практичнее выделить отдельную переменную:

Sub Add_New_Sheet() Dim wbCheck As Workbook Set wbCheck = Workbooks("Отчет.xlsx") If Not Sh_Exist(wbCheck, "Новый лист") Then wbCheck.Sheets.Add(, wbCheck.Sheets(wbCheck.Sheets.Count)).Name = "Новый лист" End If End Sub

Можно еще упростить и конструкцию With использовать, но это уже другая тема :)

В статье "Иерархия(структура) рабочей книги Excel" была рассмотрена структура рабочей книги, листа и приложения в целом. Из этого возникает вопрос, каким образом можно получить, например кол-во страниц в книге Excel, или их имена. С количеством все просто, к примеру, для того чтобы получить кол-во открытых книг, достаточно воспользоваться следующей командой:
Application.Workbooks.Count

Чтобы получить кол-во страниц в активной книге:
ActiveWorkbook.Worksheets.Count

Если же требуется получить кол-во страниц в определенной книге для этого воспользуйтесь:
Workbooks(1).Worksheets.Count
или
Workbooks("имя книги.xls").Worksheets.Count

В первом случае обращение к нужной книге происходит по индексу. Индекс присваивается в порядке открытия книг. Этот вариант не совсем удобен если необходимо обратится к конкретной книге т.к. книги могут открываться в разном порядке. Поэтому второй вариант команды позволяет обратиться к книге по ее полному имени.

Теперь перейдем к рассмотрению работы с конструкцией For Each . Next.

Синтаксис конструкции For Each . Next

For Each Элемент In Коллекция(Группа)
[команды]
[Exit For]
[команды]
Next [Элемент]

Данная конструкция позволяет поочередно обратится к каждому элементу группы или коллекции. Коллекция это группа одинаковых (однотипных) объектов. Например, массивы, или коллекции WorkBooks, Worksheets .
For Each . Next по принципу работы похож на цикл For. Next, который был рассмотрен здесь. Схожесть в том что, перебирается каждый элемент коллекции или группы по порядку. Не буду томить скучной теорией перейдем к рассмотрению примеров.

Пример 1. Необходимо получить имена всех открытых книг Excel. Имена всех рабочих книг доступны в коллекции Workbooks.

Sub GetWorkBooksName()
Dim WBooks As Workbook
Dim Msg As String

For Each WBooks In Workbooks
Msg = Msg & WBooks.Name & Chr(13)
Next WBooks

MsgBox Msg
End Sub

Пример 2. Необходимо получить имена всех страниц в активной книге. Имена страниц содержатся в коллекции Worksheets.

Sub GetWorkSheetsName()
Dim Item As Worksheet

For Each Item In ActiveWorkbook.Worksheets
MsgBox "Отображаемое имя листа " & CStr(Item.Index) & " - " & Item.Name
Next Item

Пример 3. Необходимо посчитать кол-во страниц во всех открытых книгах Excel.

Sub GetAllCountSheets()
Dim WBooks As Workbook
Dim kolSheet As Long
Dim Msg As String

kolSheet = 0
For Each WBooks In Workbooks
kolSheet = kolSheet + Workbooks(WBooks.Name).Worksheets.Count
Next WBooks

MsgBox "Всего страниц в открытых книгах: " & CStr(kolSheet)

Пример 4. Необходимо преобразовать весь текст в выделенном диапазоне ячеек в верхний регистр т.е. сделаем буквы большими. Для этого воспользуемся коллекцией Selection (тип Range - хранит область выделенных ячеек) и функцией перевода символов в верхний регистр UCase (если необходим перевод в нижний регистр, то воспользуйтесь функцией LCase).

Sub RangeUpCase()
Dim Cell As Range

For Each Cell In Selection
Cell.Value = UCase(Cell.Value)
Next Cell

Выделяем нужные ячейки или диапазон ячеек с текстом и запускаем макрос.

Пример 5. И заключительный пример, закроем все рабочие книги Excel, кроме активной, без сохранения.

Sub CloseBooks()
Dim WBook As Workbook

For Each WBook In Workbooks
If WBook.Name <> ActiveWorkbook.Name Then WBook.Close False
Next WBook

За закрытие книги Excel отвечает команда WBook.Close. Параметр False указывает, что закрываем все книги без сохранения. Если этот параметр изменить на True, то все книги будут закрываться предварительно сохранившись. Если же этот параметр убрать, то при закрытии книг с внесенными изменениями выскочит диалоговое окно с вопросом о сохранении.

Листам в книгах Excel можно дать имена, соответствующие содержимому. Из них было бы удобно составить оглавление, но не все знают, как это сделать. Существуют несложные способы сформировать список листов и методы, требующие усилий, например установки сторонних дополнений. С помощью инструментов Excel пользователи также могут подсчитать количество листов в крупной книге. Выясним, как получить оглавление для чтения или перехода к каждому листу, какими формулами для этого нужно воспользоваться.

Список листов с помощью формулы

Этот способ основан на использовании функции, которую нельзя найти в Менеджере. Она связана с макросами Excel 4.0. Чтобы применить формулу на практике, необходимо пройти дополнительный шаг, редко встречающийся в работе с функциями – зайти в диспетчер имен и добавить туда выражение.

1. Переходим на вкладку «Формулы» и кликаем по кнопке «Диспетчер имен». Опция находится в разделе «Определенные имена».

1. Нажимаем «Создать» в открывшемся диалоговом окне.

1. Открываем лист, где будет расположен список. Выбираем ячейку и записываем в ней формулу с только что созданным именем: =ИНДЕКС(Список_листов;СТРОКА()). Нажмите Enter, и в ячейке появится название первого листа.

1. Необходимо вывести все названия листов в столбец. Для этого зажимаем маркер заполнения, который находится в правом нижнем углу выбранной ячейки, и выделяем нужное количество ячеек. В таблице-примере 4 листа столько и было выделено ячеек.

Обратите внимание! Ячейка A1 прописывается в формуле, чтобы пользователи могли перейти на конкретную ячейку каждого листа. После нажатия Enter появится кликабельное название листа.

1. Полный список с гиперссылками можно создать так же, с помощью маркера заполнения.
2. Если название листа изменено, придется перезаполнить списки – для этого нужно просто удалить их, снова вставить те же формулы и выделить ячейки ниже.

Как составить список листов через VBA

Существует другой способ составления списка листов из книги – можно подключить пользовательскую функцию через редактор Visual Basic. Такой метод может показаться сложным, но это не так, если воспользоваться шаблоном для добавления функции в программу.

1. Открываем VBA с помощью комбинации клавиш «Alt+F11». Если при нажатии F11 снижается яркость или срабатывает другая функция, установленная на эту кнопку, зажмите клавишу Fn.
2. Нажмите «Вставить» (Insert) на верхней панели и выберите в открывшемся меню пункт «Модуль» (Module).

Function SheetList(N As Integer)

SheetList = ActiveWorkbook.Worksheets(N).Name

End Function

1. Далее можно закрыть окно Visual Basic, потому что этот инструмент больше не понадобится, а функция уже добавлена в программу.

1. Открываем лист для списка и вводим формулу в начальную ячейку. Теперь не нужно длинное выражение, чтобы создать список листов. Новая формула выглядит так: =SheetList(СТРОКА()).
2. Нажимаем Enter и получаем название листа в ячейке. Маркером заполнения создаем список.

Надстройки для составления списка листов

Надстройки – это дополнения для Microsoft Excel, которые создаются продвинутыми пользователями. Компания Microsoft рассказывает на официальном сайте о возможности подключить надстройки, но не предлагает скачать дополнения, поэтому обычно их загружают из других источников.

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

Существуют платные и бесплатные надстройки. Сегодня рассмотрим набор дополнений для Excel 2007-2019 под названием «Ёxcel». Разработчик распространяет файл на своем сайте за добровольное пожертвование. Установите надстройку по инструкции – после этого можно приступать к составлению списка.

1. Открываем лист, где будет размещен список, и нажимаем левой кнопкой мыши на начальную ячейку будущего перечисления.
2. На вкладке надстройки находим кнопку «Листы». Кликаем по ней, чтобы открылось меню, и выбираем пункт «Получить список листов книги». Скриншот создателя надстройки:

1. Выбираем, какие листы показать в списке. Для простейшего перечисления названий листов кликаем по пункту «Простой список» и жмем на кнопку с галочкой в левом нижнем углу диалогового окна.
2. На экране появится список листов. Если выставить настройки сложнее, то внешний вид списка немного изменится.

Как подсчитать количество листов в книге

Иногда в книгах Excel появляется много листов, например если документ относится к крупному проекту. Выяснить, сколько в файле страниц, можно с помощью функции ЛИСТЫ.

Обратите внимание! Функция работает только в версиях Microsoft Excel от 2013.

1. Выбираем пустую ячейку и записываем в ней формулу: =ЛИСТЫ(). Не обязательно заполнять аргумент «Ссылка», если нужно посчитать листы в одной книге.

1. Жмем Enter и получаем числовое значение.

Если все листы переименованы, и нужно узнать их номера, воспользуйтесь функцией ЛИСТ. Эта формула также доступна с 2013-й версии. У функции ЛИСТ один аргумент – «Значение». Если аргумент не заполнен, после нажатия клавиши Enter в ячейке появится номер того же листа, где была введена формула. Простое выражение с ЛИСТ выглядит так: =ЛИСТ().

Вы когда-нибудь пытались подсчитать количество листов, название которых начинается с определенного слова или содержит конкретное слово? Например, подсчитайте количество рабочих листов, имена которых начинаются с или содержат «KTE». Эта статья предоставит метод, который поможет вам выявить проблему.

Подсчет листов с определенным именем с кодом VBA

Следующие коды VBA помогают подсчитать количество листов с определенным именем в книге. Пожалуйста, сделайте следующее.

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

1. нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. в Microsoft Visual Basic для приложений окна, нажмите Вставить > Модуль, затем скопируйте ниже код VBA в окно кода.

Код VBA 1: Подсчет листов, имена которых начинаются с «KTE»

Если вы хотите подсчитать количество листов, содержащих «KTE», примените код VBA ниже 2.

Код VBA 2: Подсчет листов, имя которых содержит «KTE»

Внимание: Пожалуйста, замените слово «KTE» на то, на котором вам нужно подсчитать количество рабочих листов.

3. нажмите F5 ключ для запуска кода. Затем появится диалоговое окно, в котором сообщается, сколько листов в текущей книге начинается с «KTE» или содержит «KTE». Смотрите скриншоты:

Количество рабочих листов, название которых начинается с «KTE»:

Количество рабочих листов, в названии которых есть «KTE»:

Быстро найти все листы, содержащие определенное слово в названии листа.

В этом разделе вы узнаете, как быстро найти все листы, содержащие определенное слово в имени листа, с помощью Kutools for Excel.

1. После установки Kutools for Excel, Kutools ' Навигация панель откроется автоматически в левой части книги. Или вы можете включить панель, нажав Kutools > Навигация как показано ниже.

2. в Рабочая тетрадь и лист раздел панели навигации, проверьте Фильтр поле и введите конкретное имя рабочего листа, на котором вы хотите найти рабочие листы. Затем все листы, имя которых содержит определенное слово, фильтруются и отображаются в поле списка рабочего листа. Смотрите скриншот:

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

Странно, но в Excel невозможно напрямую сгенерировать список имен листов из книги. В этой статье будет рассказано, как создать список всех листов, содержащихся в книге. Как и в предыдущей статье, здесь мы будем использовать макрофункцию из Excel 4 XLM в именованной формуле.

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

1. Вставьте новый лист, на котором будет находиться список.
2. Выполните команду Формулы ► Присвоить имя, чтобы открыть диалоговое окно Создание имени.
3. Впишите в поле Имя название Список.
4. Введите следующую формулу в поле Диапазон (рис. 9.1): =ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"") .
5. Осталось нажать ОК, чтобы закрыть диалоговое окно Создание имени.

Рис. 9.1. Работа с окном Создание имени для создания именованной формулы

Обратите внимание: в формуле используется функция ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ, которая не относится к числу стандартных функций рабочего листа. Это макрофункция, написанная в сравнительно старом XLM-стиле и предназначенная для применения на специальном листе макросов. При использовании аргумента 1 она возвращает массив имен листов, причем перед именем каждого листа указано название книги, откуда он взят. Функции ЗАМЕНИТЬ и НАЙТИ удаляют имя книги, сопутствующее названию листа. Чтобы сгенерировать имена листов, введите в ячейку А1 следующую формулу, а затем скопируйте ее ниже по столбцу: =ИНДЕКС(Список;СТРОКА()) .

Рис. 9.2. Использование формулы для отображения списка имен листов

Список названий листов можно откорректировать, если вы будете добавлять, удалять или переименовывать листы, но это не происходит автоматически. Чтобы принудительно обновить формулы, нажмите Ctrl+Alt+F9. Если вы хотите, чтобы имена листов автоматически обновлялись при пересчете книги, измените именованную формулу следующим образом, чтобы сделать ее меняющейся: =ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"")&Т(ТДАТА()) .

Рис. 9.3. Создание списка гиперссылок

Если вы пользуетесь такими приемами, то книгу нужно сохранить как файл с поддержкой макросов (в формате *.xlsm или *.xls).

Читайте также:

  
• Vba excel combobox создать программно
  
• Программы для программирования роботов arduino
  
• You have to install atk0100 driver что это
  
• Что означает этот значок в браузере
  
• Верно ли что озу это быстрая память для хранения текущей программы и данных