Vba excel существует ли лист

Обновлено: 07.01.2025

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

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

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

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

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

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

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

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

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

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

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

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

Function SheetList(N As Integer)

SheetList = ActiveWorkbook.Worksheets(N).Name

End Function

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как я могу проверить, существует ли этот лист? Что-то вроде:

Некоторым не нравится этот подход из-за «неуместного» использования обработки ошибок, но я думаю, что он считается приемлемым в VBA . Альтернативный подход - зацикливать все листы, пока не найдете совпадение.

Полностью одобряю использование IMO. Это ловушка для того, что постулируется как существующее, но не существует и имеет долгую историю - cf perl strict, STAE и т. Д. Проголосовали за Вероятно, следует использовать ActiveWorkbook вместо ThisWorkbook . Последнее относится к книге, содержащей код макроса, который может отличаться от книги, которую нужно тестировать. Думаю, это ActiveWorkbook было бы полезно в большинстве случаев (хотя надуманные ситуации всегда доступны). sht Is Nothing будет , True если нет листа с этим именем, но мы хотим , чтобы вернуться , True если есть лист с таким именем, следовательно, нет. Это немного проще (но не действует) , если переставить немного к SheetExists = sht Is Not Nothing Приятно отметить, что если вы запустите этот код в своей личной книге макросов, измените его с If wb Is Nothing Then Set wb = ThisWorkbook на If wb Is Nothing Then Set wb = ActiveWorkbook Это очень эффективный подход (см. Мои комментарии о контрольных точках под ответом Рори ниже), так что кого волнует, что думают недоброжелатели. Обратите внимание (на данный момент) у вас ноль голосов против.

Если вас особенно интересуют только рабочие листы, вы можете использовать простой вызов Evaluate:

@Rory Я провел несколько тестов по этому вопросу против ответа Тима Вильямса. Более 500000 циклов, у вас - 22 секунды, у Тима - <1. @roryap - если вам нужно запустить это 500000 раз, вам нужно полностью переосмыслить свой подход. ;) @roryap - однако при использовании нескольких медленных методов начнется накопление секунд. Я бы сказал, что это чрезвычайно ценная информация, так как «приложения» Excel начинают довольно легко @roryap - в чем ценность этой информации для разговора? Я просто утверждаю, что рассредоточение неэффективных методов по вашему коду замедлит работу приложения в целом. Вы тестируете это 500 тысяч раз - это здорово, и я благодарю вас за это, 22 секунды - это не очень хорошо. (Я согласен с вами) Даже если он медленнее, он выглядит намного более чистым решением, чем принятый ответ. +1 от меня.

Для этого вам не нужна обработка ошибок. Все, что вам нужно сделать, это перебрать все рабочие листы и проверить, существует ли указанное имя:

Поскольку проверка членов коллекции - это общая проблема, вот абстрактная версия ответа Тима:

Эта функция может быть использована с любой коллекцией как объект ( Shapes , Range , Names , Workbooks и т.д.).

Чтобы проверить наличие листа, используйте If Contains(Sheets, "SheetName") .

Это не отлавливает примитивные типы в коллекциях, так как Set ключевое слово вызывает ошибку . Я обнаружил, что вместо того, чтобы использовать Set , запрос TypeName члена коллекции работает для всех случаев, то есть TypeName objCollection(strName) @Peter: Лучше всего добавить что-нибудь, чтобы очистить ошибку, которая будет возникать в случае отсутствия до завершения функции - либо err.clear, либо On Error Resume Next. В противном случае обработка ошибок в вызывающей процедуре может быть непреднамеренно запущена в следующих случаях. Sub Test() On Error GoTo errhandler Debug.Print Contains(Workbooks, "SomeBookThatIsNotOpen") errhandler: If Err.Number <> 0 Then Stop End Sub

Исправлено: Без обработки ошибок:

В случае , если кто -то хочет , чтобы избежать VBA и тест , если таблица существует чисто в формуле ячейки, можно с помощью ISREF и INDIRECT функции:

Это вернется, TRUE если книга содержит лист с именем SheetName и в FALSE противном случае.

Отличная функция! Он не только быстрый, но и самый лаконичный. Я полагаю, что это ответ, который больше всего соответствует вопросу Мне нравится этот. Обратите внимание, что он основан на том факте, что значение по умолчанию для sheetExist будет False, поскольку это логическая функция. Оператор присваивания фактически не присваивает значение False для sheetExist, если лист не существует, он просто выдает ошибку и оставляет значение по умолчанию на месте. Если вы хотите, вы можете полагаться на тот факт, что любое ненулевое значение, присвоенное логической переменной, даст результат True и исключит сравнение> 0, например: sheetExist = ActiveWorkbook.Sheets(sSheet).Index

Мое решение очень похоже на Tims, но также работает в случае листов, отличных от рабочих листов - диаграмм

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

НЕ используйте команду «Возобновить после ошибки», поскольку она может конфликтовать с другой частью вашего кода.

Спустя много лет, но мне просто нужно было это сделать, и мне не понравилось ни одно из опубликованных решений . Так что я придумал одно, и все благодаря магии (жест радужной руки Губки Боба) "Evaluate ()"!

Вот короткая и простая функция, которая не полагается на обработку ошибок, чтобы определить, существует ли рабочий лист ( и правильно объявлено, что оно работает в любой ситуации!)

Пример использования:

В следующем примере добавляется новый рабочий лист с именем myNewSheet , если он еще не существует:

Когда у вас есть десятки рабочих листов или даже сотни рабочих листов в книге, и вы хотите найти или проверить, существует ли лист в этой книге, в общем, это может быть огромная работа. Теперь я представляю вам код VBA, чтобы вы могли быстро проверить, существует ли лист в книге.

Проверьте, существует ли лист и переключитесь на него с помощью Kutools for Excel

Проверьте, существует ли лист в книге

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

1. Нажмите Alt + F11 для отображения Microsoft Visual Basic для приложений окно.

2. В окне нажмите Вставить > Модуль чтобы отобразить новое окно модуля, затем скопируйте следующий код VBA в окно модуля.

VBA: проверьте, существует ли лист в книге.

3. Сохраните этот код, вернитесь на лист и выберите пустую ячейку, чтобы ввести эту формулу. = CheckSheet («Shee1») (Sheet1 указывает имя листа, которое вы хотите проверить, существует ли он) в него, нажмите Enter кнопка, НЕПРАВДА указывает, что этот лист не существует, и TURE указывает, что он существует в текущей книге.

Проверьте, существует ли лист и переключитесь на него с помощью Kutools for Excel

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

После бесплатная установка Kutools for Excel, сделайте следующее:

1. включите книгу, в которой вы хотите проверить имя листа, если оно существует, а затем щелкните Kutools > Навигация. Смотрите скриншот:

2. Затем на всплывающей панели щелкните Рабочая тетрадь и лист кнопку, чтобы развернуть панель, вы можете увидеть весь список имен листов на панели, вы можете прокрутить, чтобы найти имена листов.

В Word ниже объекта Application и Document начинались уже объекты непосредственно для работы с текстом — Selection, Range и т.п. В Excel между объектом рабочей книги и ячейками есть еще один промежуточный объект — объект Worksheet (лист). Объекты Worksheet в книге объединены в коллекцию Sheets.

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

Процесс создания выглядит очень просто:

Dim oExcel As New Excel.Application 'Запускаем Excel

oExcel.Visible = True 'Делаем его видимым

Dim oWbk As Excel.Workbook

Set oWbk = oExcel.Workbooks.Add () 'Создаем новую книгу

Dim oSheet As Excel.Worksheet

Set oSheet = oWbk.Worksheets.Add() ' Создаем новый лист

oSheet.Name = "Новый лист" 'Присваиваем ему имя "Новый лист"

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

Часто встречается и другая задача — просто найти нужный лист среди листов книги, например, если мы открыли существующую книгу. Сделать это очень просто, поскольку коллекция Worksheets умеет работать с именами листов. Ниже приведен пример, в котором мы так же запускаем Excel и создаем новую книгу, но при этом находим лист с именем "Лист1" и переименовываем его в "Новый лист":

Dim oExcel As New Excel.Application 'Запускаем Excel

oExcel.Visible = True 'Делаем его видимым

Dim oWbk As Excel.Workbook

Set oWbk = oExcel.Workbooks.Add() 'Создаем новую книгу

Dim oSheet As Excel.Worksheet

Set oSheet = oWbk.Worksheets.Item("Лист 1") ' Находим Лист1

oSheet.Name = "Новый лист" 'Присваиваем ему имя "Новый лист"

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

У коллекции Sheets, помимо привычных нам свойств и методов ( Count, Item, Add(), Delete()) и свойств и методов, которые удобнее применять для объекта Worksheet ( Visible(), Copy(), Move(), PrintOut(), PrintPreview(), Select()) — поскольку все равно указывать конкретный лист — есть и один специфический метод FillAcrossSheets() — скопировать объект диапазона Range (варианты: полностью, только содержимое, только оформление) во все листы данной книги.

У объекта Worksheet — множество важных свойств и методов:

Cells — одно из наиболее часто используемых свойств объекта Worksheet. Работает точно так же, как и рассмотренное выше одноименное свойство объекта Application — за исключением того, что вам больше не нужно ограничиваться только активным листом. Аналогично работают свойства Columns и Rows.
EnableCalculation — возможность отключить автоматический пересчет значений ячеек в книге.
EnableSelection — возможность запретить выделять на листе: все, ничего не запрещать, или разрешить выделять только незаблокированные ячейки.
Next — возможность получить ссылку на следующий лист в книге. Previous — то же самое для предыдущего листа.
PageSetup — как и в Word, возможность получить объект PageSetup, при помощи которого можно настроить те же параметры, что и через меню Файл -> Параметры страницы.
свойство Protection позволяет получить объект Protection, при помощи которого можно запретить пользователю вносить изменения в лист Excel. Настройке параметров защиты также служат и другие свойства, названия которых начинаются на Protection.
QueryTables — исключительно важное свойство. Оно возвращает коллекцию QueryTables — набор объектов QueryTable, которые, в свою очередь, представляют данные, полученные из внешних источников (как правило, из баз данных).
Range — самое важное свойство объекта Worksheet. Возвращает объект Range (диапазон ячеек), который в объектной модели Excel занимает примерно такое же место, что и одноименный объект в объектной модели Word. Этот объект будет рассматриваться ниже.
Type — возможность определить тип данного листа. Обычно используются два типа: xlWorksheet (обычный лист) и xlChart (диаграмма).
UsedRange — возвращает объект Range, представляющий собой прямоугольную область, включающую все непустые ячейки. Удобно для целей копирования или форматирования.
Visible — возможность спрятать лист с глаз пользователя (например, если он используется для служебных целей).

Некоторые важные методы объекта Worksheet:

методы Activate() , Calculate(), Copy(), Paste(), Delete(), Move(), Evaluate(), Select(), SaveAs(), PrintOut(), PrintPreview(), Protect(), Unprotect()нам уже знакомы . Отличие заключается только в том, что теперь эти методы могут применяться для выбранного вами листа.
метод PivotTables() возвращает коллекцию очень интересных объектов PivotTable (сводная таблица), которые будут рассматриваться ниже;
метод Scenarios() возвращает коллекцию Scenarios, состоящую из объектов Scenario (сценарии). Сценарии — это именованные наборы вводных данных, которые можно использовать для проверки различных сценариев (разные суммы продаж, уровни налогов, расходов и т.п.)
SetBackgroundPicture() — возможность назначить листу фоновое изображение (естественно, желательно, чтобы оно было полупрозрачное — "водяной знак", иначе на его фоне будет трудно читать текст в ячейках).
ShowAllData() — показать все скрытые и отфильтрованные данные на листе.

Самое важное событие объекта Worksheet — это, конечно, Change. Существует множество практических задач, когда изменение пользователем значения ячейки должно приводить к изменению значения в ячейке другого листа/рабочей книги Excel или даже в базе данных. Другая ситуация, в которой используется это события — сложная проверка вводимого пользователем значения (например, опять-таки через обращение к базе данных). Эта событийная процедура работает со специальным параметром Target — то есть объектом Range, представляющим изменившуюся ячейку. При помощи свойств и методов объекта Range вы можете получить информацию об изменившемся значении, столбце и строке, в котором произошло изменение и т.п.

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