Vba excel существует ли лист
Часто при разработке различных проектов в VBA требуется где-то хранить различные служебные данные: списки, константы(вроде путей к файлам, email-ов и т.п.). Обычно это делается на отдельных листах, которые потом делают очень скрытыми. А на листах делает еще и потому, что списки удобнее хранить именно на них, да и порой необходимо дать конечному пользователю возможность самому изменять некие данные. Например, программа должна собирать информацию с различных файлов. Но файлы могут располагаться в различных папках - зависит от того, на каком компьютере программа запускается. Или лист содержит списки артикулов, на основании которых программа отбирает некоторые файлы. И эти артикулы могут изменяться со временем. В таких случаях лист не скрывают, а оставляют на виду, чтобы пользователь сам мог прописать путь к нужным папкам и файлам, вписать нужные артикулы и т.п.
В коде к этому листу можно обращаться по имени. Предположим, лист называется "SETS". Тогда код получения данных из ячейки A2 будет выглядеть так:
Dim v v = ThisWorkbook.Sheets("SETS").Range("A2").Value
Подробнее про обращение к диапазонам можно узнать из статьи: Как обратиться к диапазону из VBA
Все бы ничего, но есть одна ложка дегтя: пользователь может случайно этот лист переименовать. Тогда обращение к листу по заранее заданному имени вызовет ошибку( 9 - Subscript out of range ). Можно пойти другим путем - обратиться к листу по его порядковому номеру. Например, наш лист "SETS" расположен вторым по порядку в книге:
Тогда код получения данных из ячейки A2 будет таким:
Dim v v = ThisWorkbook.Sheets(2).Range("A2").Value
Но и здесь проблема: пользователь может удалить первый лист или просто переместить наш лист "SETS" в другое место. Что опять либо вызовет ошибку 9 - Subscript out of range (если количество листов в книге меньше указанного числа. В нашем примере если в книге будет только один лист) либо запишет нам в переменную v значение из совершенно другого листа и как следствие - это будет не то значение, которое нам нужно.
Как же быть? Есть такое понятие - кодовое имя листа. Т.е. у листа есть два имени: одно отображаемое - это то, которое мы видим на ярлыке(на рисунке выше это Лист1, SETS, Лист2). И есть кодовое имя, которое не видно напрямую. Чтобы увидеть кодовое имя листа необходимо перейти в редактор Visual Basic for Application( Alt + F11 ), отобразить окно проводника проекта -View -Project explorer(или Ctrl + R ), раскрыть папку Microsoft Excel Objects. Там мы увидим все объекты проекта VBA и их имена. Само название листов в этом окне состоит из двух частей: сначала идет кодовое имя листа, а в скобках - имя, отображаемое на ярлыке:
Где искать разобрались. Что нам это дает? Т.к. это имя просто так не найти - пользователь не сможет уже изменить его имя. А обратиться к такому листу из кода VBA проще простого - как и к любому из компонентов проекта - просто по его кодовому имени(для нашего листа "SETS" это Лист2)
Dim v v = Лист2.Range("A2").Value
Теперь ни переименование ярлыка, ни перемещение не вызовет ошибку. Ошибку теперь может вызвать удаление этого листа. Но это другая история.
Удаление листов можно запретить, защитив структуру книги(Рецензирование (Review) -Защитить книгу (Protect workbook) )
И еще важно помнить: обращение к листу по его кодовому имени приведенным выше способом возможно исключительно внутри той книги, в которой этот лист расположен. Обратиться к нему по привычке с указанием книги уже не получится: ActiveWorkbook.Лист2.Range("A2").Value . Т.е. по факту нельзя обратиться к листу по кодовому имени из другой книги. Хотя в большинстве случаев это и не надо. Если уж реальная необходимость возникнет - ниже приведена функция поиска листа по его кодовому имени, которую легко можно переделать под получение отображаемого имени листа, найдя его по кодовому имени.
Чтобы самостоятельно изменить имя кодового листа, необходимо выделить этот лист в проводнике проекта -отобразить окно свойств, если оно еще не отображено(View -Properties Window или F4 ) и просто изменить свойство Name:
Для чего переименовывать? Во-первых, не на всех ПК кириллица воспринимается нормально. Поэтому лучше в кодах использовать латиницу. Во-вторых - код будет более читаемый и наглядный, если обращение будет к объектам вроде wsSets и wsData , чем к объектам вида Лист1 , Лист2 и т.п.
Но при переименовании необходимо помнить, что кодовое имя листа не должно содержать пробелов и иных знаков препинания(кроме нижнего подчеркивания), не должно начинаться с цифр. В общем такие же требования, как и к именам макросов.
Порой необходимо проверить - есть ли лист с указанным кодовым именем. Это к слову о том, как избежать ситуации обращения к несуществующему листу, если обращение идет по кодовому имени(и при этом в проектах Вы почему-то не используете Option Explicit). Можно применить такую простенькую функцию:
'--------------------------------------------------------------------------------------- ' Procedure : RenameSheetCodeName ' Purpose : Ищет в указанной книге лист с указанным кодовым именем ' wb - Книга, лист в которой необходимо найти ' sName - Кодовое имя листа, которое надо проверить на наличие в книге '--------------------------------------------------------------------------------------- Function FindSheetByCodeName(wb As Workbook, sName As String) Dim ws As Worksheet 'цикл по всем листам For Each ws In wb.Worksheets If StrComp(ws.CodeName, sName, 1) = 0 Then 'сравнение имени без учета регистра 'назначаем функции возвращаемое значение FindSheetByCodeName = True Exit Function End If Next End Function
Проверить при этом наличие листа с кодовым именем wsSets можно так:
Sub IsShhetCodeName_Exist() If FindSheetByCodeName(ActiveWorkbook, "wsSets") = False Then MsgBox "Нет такого листа", vbCritical, "www.excel-vba.ru" End If End Sub
И вдогонку код, который поможет быстро переименовать кодовое имя листа по его отображаемому имени. Т.е. все что надо - знать имя листа, отображаемое на ярлыке. И указать новое кодовое имя:
'--------------------------------------------------------------------------------------- ' Procedure : RenameSheetCodeName ' Purpose : Ищет в указанной книге лист с указанным именем ' и переименовывает кодовое имя листа на указанное ' wb - Объект. Книга, лист в которой необходимо переименовать ' sOldName - Текст. Имя листа для переименования ' (может быть как отображаемым на ярлыке, так и кодовым) ' sNewName - Новое кодовое имя листа ' SearchByCodeName - Если True, то лист для переименования ищется ' по кодовому имени листа ' Если False - лист ищется по отображаемому на ярлыке имени '--------------------------------------------------------------------------------------- Function RenameSheetCodeName(wb As Workbook, sOldName As String, sNewName As String, _ Optional SearchByCodeName As Boolean = True) Dim vbc As Object, ws As Worksheet Dim sn As String 'проверяем, нет ли уже в книге компонета с таким именем(sNewName) On Error Resume Next Set vbc = wb.VBProject.VBComponents(sNewName) If Not vbc Is Nothing Then MsgBox "The worksheet '" & sNewName & "' is already exist", vbCritical, "www.excel-vba.ru" 'MsgBox "Компонент с именем '" & sNewName & "' уже есть в проекте", vbCritical, "www.excel-vba.ru" Exit Function End If 'цикл по всем листам и проверка имени For Each ws In wb.Worksheets If SearchByCodeName Then 'если ищем по кодовому имени sn = ws.CodeName Else sn = ws.Name 'если ищем по отображаемому имени End If If StrComp(sn, sOldName, 1) = 0 Then 'сравнение имени без учета регистра 'переименовываем, если имя совпадает Set vbc = wb.VBProject.VBComponents(ws.CodeName) vbc.Name = sNewName 'назначаем функции возвращаемое значение RenameSheetCodeName = True 'выходим из функции(нет смысла продолжать - переименовали) Exit Function End If Next End Function
Вызвать переименование кодового имени листа можно будет так:
Sub TestRename() 'RenameSheetCodeName(ActiveWorkbook, "Sheet1", "Лист1") 'RenameSheetCodeName(ActiveWorkbook, "Лист1", "Sheet1") If RenameSheetCodeName(ActiveWorkbook, "Sheet1", "Лист1") Then MsgBox "Кодовое имя листа переименовано", vbCritical, "www.excel-vba.ru" End If End Sub
Для кодового имени листа программно необходимо, чтобы было проставлено доверие к объектной модели проекта VBA и изменяемый проект не должен быть защищен. Подробнее читайте в статье: Что необходимо для внесения изменений в проект VBA(макросы) программно
Без этого будет невозможно программное вмешательство в проект VBA.
Рабочий лист (Worksheet) принадлежит коллекции всех рабочих листов (Worksheets) книги Excel. Обратиться к листу можно как к элементу коллекции и, напрямую, по его уникальному имени.
Откройте редактор VBA и обратите внимание на вашу книгу в проводнике, где уникальные имена листов указаны без скобок, а в скобках — имена листов, отображаемые на ярлычках в открытой книге Excel. Уникальные имена листов отсортированы по алфавиту и их расположение по порядку не будет соответствовать их индексам (номерам), если листы перемещались по отношению друг к другу. Индексы листов смотрите по порядку расположения ярлычков в открытой книге. Переместили листы — изменились их индексы.
Обращение к рабочему листу в коде VBA Excel:
- УникИмяЛиста — уникальное имя листа, отображаемое в проводнике редактора VBA без скобок, с помощью кода VBA изменить его невозможно.
- N — индекс листа от 1 до количества всех листов в книге, соответствует порядковому номеру ярлычка этого листа в открытой книге Excel.
- Имя листа — имя листа, отображаемое в проводнике редактора VBA в скобках, с помощью кода VBA изменить его можно.
Количество листов в рабочей книге Excel определяется так:
Переименование листов
В VBA Excel есть некоторые особенности в наименовании листов, так как у рабочего листа есть два свойства, связанных с именем: (Name) и Name. Откройте окно «Properties» в редакторе VBA, нажав клавишу «F4», и выделите любой лист в проводнике. Вы увидите, что в окне «Properties» свойству (Name) в скобках соответствует в проводнике уникальное имя листа без скобок, а свойству Name без скобок соответствует изменяемое имя листа в скобках. Оба имени в окне «Properties» можно редактировать.
С помощью кода VBA Excel можно редактировать только имя листа Name, отображаемое на ярлычке листа и в проводнике без скобок. Для этого используется свойство рабочего листа Worksheets.Name со следующим синтаксисом:
expression.Name
где expression — переменная, представляющая собой объект Worksheet. Смена имени осуществляется путем присвоения нового значения свойству Worksheets.Name.
Допустим, у нас есть лист с уникальным именем (Name) — Лист1, индексом — 1 и именем Name — МойЛист, которое необходимо заменить на имя — Реестр.
Скрытие и отображение листов
Для скрытия и отображения рабочих листов в VBA Excel используется свойство Worksheet.Visible со следующим синтаксисом:
expression.Visible
где expression — переменная, представляющая собой объект Worksheet. Свойству Worksheet.Visible могут присваиваться следующие значения:
- False — лист становится невидимым, но он будет присутствовать в списке скрытых листов, и пользователь сможет его отобразить с помощью инструментов рабочей книги Excel.
- xlVeryHidden — лист становится супер невидимым и его не будет в списке скрытых листов, пользователь не сможет его отобразить. Актуально для Excel 2003-2016.
- True — лист становится видимым.
Аналоги присваиваемых значений:
- False = xlHidden = xlSheetHidden = 1
- xlVeryHidden = xlSheetVeryHidden = 2
- True = xlSheetVisible = -1 (константа xlVisible вызывает ошибку)
Как создать, скопировать, переместить или удалить рабочий лист с помощью кода VBA Excel, смотрите в этой статье.
5 комментариев для “VBA Excel. Рабочий лист (обращение, переименование, скрытие)”
Уникальное имя листа, отображаемое в проводнике редактора VBA без скобок, с помощью кода VBA изменить невозможно. В статье «Кодовое имя листа — что это и зачем нужно? Какие плюсы и минусы?» приведён код, позволяющий это сделать.
Отличная статья, спасибо!
Здравствуйте!
Хотел поделиться моментом, заслуживающим внимания, на мой взгляд:
обратите внимание, что обращаться к листу можно и через переменную
Worksheets(i).Activate
но допустим, что мы пишем
i = 2 ,
а у нас в книге присутствуют несколько листов, в т.ч. лист с именем 2
Каким же образом будет происходить обращение?
Опытным путём я установил, что это зависит от типа переменной
т.е. i будет использоваться в качестве имени листа, только если оно предварительно обозначено, как текстовая строка
Dim i as String
в других же случаях будет происходить переключение на второй лист (т.е. переменная считается индексом) если переменная никак не обозначена (при отключенном Option Explicit) или обозначена:
Dim i или Dim i as Variant
Dim i as Byte
Думаю, и с другими числовыми форматами будет вести себя также.
Добрый день!
Нигде не могу найти информацию о максимально возможном количестве листов в Excel
Спасибо за внимание
Добрый день!
На сайте разработчиков написано: «Количество листов в книге ограничено объемом доступной оперативной памяти». В скобках указано количество листов в новой книге по умолчанию.
Создание новых рабочих листов осуществляется с помощью метода Sheets.Add.
Синтаксис метода Sheets.Add
expression.Add [Before, After, Count, Type]
где expression — переменная, представляющая собой объект Sheet.
Компоненты метода Sheets.Add
- Before* — необязательный параметр типа данных Variant, указывающий на лист, перед которым будет добавлен новый.
- After* — необязательный параметр типа данных Variant, указывающий на лист, после которого будет добавлен новый.
- Count — необязательный параметр типа данных Variant, указывающий, сколько листов будет добавлено (по умолчанию — 1).
- Type — необязательный параметр типа данных Variant, указывающий тип листа: xlWorksheet** (рабочий лист) или xlChart (диаграмма), по умолчанию — xlWorksheet.
*Если Before и After не указаны, новый лист, по умолчанию, будет добавлен перед активным листом.
**Для создания рабочего листа (xlWorksheet) можно использовать метод Worksheets.Add, который для создания диаграмм уже не подойдет.
Примеры создания листов
- Лист1 в After:=Лист1 — это уникальное имя листа, указанное в проводнике редактора VBA без скобок.
- Лист1 в After:=Worksheets(«Лист1») — это имя на ярлыке листа, указанное в проводнике редактора VBA в скобках.
Создаваемый лист можно присвоить объектной переменной:
Если создаваемый лист присваивается объектной переменной, он будет помещен перед активным листом. Указать дополнительные параметры невозможно.
Копирование листов
Копирование рабочих листов осуществляется с помощью метода Worksheet.Copy.
Синтаксис метода Worksheet.Copy
expression.Copy [Before, After]
где expression — переменная, представляющая собой объект Worksheet.
Компоненты метода Worksheet.Copy
- Before* — необязательный параметр типа данных Variant, указывающий на лист, перед которым будет добавлена копия.
- After* — необязательный параметр типа данных Variant, указывающий на лист, после которого будет добавлена копия.
*Если Before и After не указаны, Excel создаст новую книгу и поместит копию листа в нее. Если скопированный лист содержит код в проекте VBA (в модуле листа), он тоже будет перенесен в новую книгу.
Примеры копирования листов
Если рабочие книги указаны как элементы коллекции Workbooks, в том числе ActiveWorkbook и ThisWorkbook, листы нужно указывать как элементы коллекции Worksheets, использование уникальных имен вызовет ошибку.
Перемещение листов
Перемещение рабочих листов осуществляется с помощью метода Worksheet.Move.
Синтаксис метода Worksheet.Move
expression.Move [Before, After]
где expression — переменная, представляющая собой объект Worksheet.
Компоненты метода Worksheet.Move
- Before* — необязательный параметр типа данных Variant, указывающий на лист, перед которым будет размещен перемещаемый лист.
- After* — необязательный параметр типа данных Variant, указывающий на лист, после которого будет размещен перемещаемый лист.
*Если Before и After не указаны, Excel создаст новую книгу и переместит лист в нее.
Примеры перемещения листов
Простые примеры перемещения листов:
Если рабочие книги указаны как элементы коллекции Workbooks, в том числе ActiveWorkbook и ThisWorkbook, листы нужно указывать как элементы коллекции Worksheets, использование уникальных имен вызовет ошибку.
Перемещение листа «Лист4» в позицию перед листом, указанным как по порядковому номеру, так и по имени ярлыка:
Удаление листов
Удаление рабочих листов осуществляется с помощью метода Worksheet.Delete
Синтаксис метода Worksheet.Delete
где expression — переменная, представляющая собой объект Worksheet.
Примеры удаления листов
Если рабочие книги указаны как элементы коллекции Workbooks, в том числе ActiveWorkbook и ThisWorkbook, листы нужно указывать как элементы коллекции Worksheets, использование уникальных имен вызовет ошибку.
Как обратиться к рабочему листу, переименовать, скрыть или отобразить его с помощью кода VBA Excel, смотрите в этой статье.
57 комментариев для “VBA Excel. Рабочий лист (создание, копирование, удаление)”
А как удалить листы не указывая постоянно их название?:
Привет, Мон!
Можно удалить все листы, кроме указанных в условии, с помощью цикла For Each … Next:
Если по крупному
нет Проверки наличия Листа в книге
нет Переименования Листа
Добрый день! не могу сделать в макросе — чтобы сделанному скопированному листу присваивалось свое имя, которое при новом запуске макроса новому листу будет присваивать новое имя из этой книги (переменное)
Добрый день, Эдуард!
Я правильно понял вопрос: нужно, чтобы макрос при каждом запуске создавал копию листа и присваивал ему новое имя, отличное от имени по умолчанию? Если так, то по каким правилам должно создаваться новое имя?
Здравствуйте. Не могу найти макрос может кто поможет.
Задача состоит в том что бы макрос читал все листы книги после чего делал копию этих листов и вставлял в эти же листы данные как значения. Т.е. фактически разрывал связи методом копи паст но на всех листах книги (колличество листов в книге может быть каждый раз разное.
Добрый день!
У меня вопрос.
Как перемещать лист перед другим листом, номер или название которого будет вводить пользователь?
Привет, Артур!
Перемещение листа «Лист2» на позицию перед листом, имя ярлыка которого вводится в текстовое поле информационного окна InputBox:
Спасибо!
У меня ещё вопрос. А если указывать номер листа, перед которым будет другой лист, какое изменение будет в коде?
А так можно вводить и номер, и имя:
Бладораю.
У меня последний вопрос.
Нужно, чтобы при отмене операции по перемещению не было ошибок. Я пробовал с If… Then GoTo, но не выходит.
Добрый день! Возможно ли полное копирование листа на нужный мне лист? В январе заполняются данные каждый день и надо чтобы за каждый день создавалась копия на заранее созданные 31 лист по дням?
Добрый день, Эдуард!
Это можно сделать двумя строками, но перед копированием следует проверить, не был ли исходный лист сегодня уже сохранен под новым именем:
Спасибо за помощь! Нашел другое решение более мне подходящее но не знаю как сделать чтобы созданые листы вставлялись по порядку нумерации 1,2,3 и т.д. после активного листа? Не поможете вот код
Спасибо все работает!
Еще вопрос, можно ли скопировать лист не в конец книги а между листами, есть 12 месяцев в книге ексель чтобы копировалось после января, февраля?
И может еще чтобы кнопка с макросом не копировалась а создавалась гипперссылка обратно на лист?
Я добавил код перемещения нового листа относительно исходного листа с именем текущего месяца и удаления ненужного нового листа, который создается при повторном вводе одного и того же числа:
Евгений огромное спасибо это то что и требовалось весьма признателен за Вашу помощь.
Эдуард, представленный выше код будет работать только с одним месяцем. Excel не даст создать в одной книге несколько листов с одним именем (одно и то же число, но разных месяцев). Вам придется как-то изменить наименование листов, например: 1.01-31.01, 1.02-28.02. Тогда для перемещения новых листов придется использовать другой алгоритм или по окончании одного месяца, все его листы переименовать, например так:
Я понял этого и не требуется по окончанию месяца листы будут сохранены отдельно.
А вот по предедущему коду нельзя ввести произвольное число т.е не по порядку 1,2,3 а сразу например 17 пропустив с 1 по 16?
Можно и так, если указать имя листа, перед которым вставлять перемещаемый (вместо всего блока If … End If):
В таком виде все работает как задумывалось — может кому то пригодится. Спасибо огромное Евгению за помощь!
Помогите с мелочью никак не пойму куда вставить умножить на 2,1 и на 1,9 чтобы не считалось +_ 20% в этой формуле?
=ДВССЫЛ(АДРЕС(СТРОКА();СТОЛБЕЦ()-1;1;1))
Эдуард, может быть у вас для ячейки с формулой установлен формат «Процентный»?
Нет числовой. условное форматирование со стрелками вверх вниз и вправо
=ДВССЫЛ(АДРЕС(СТРОКА();СТОЛБЕЦ()-1;1;1*2,6)) для стрелки вверх
=ДВССЫЛ(АДРЕС(СТРОКА();СТОЛБЕЦ()-1;1;1*2,4)) для стрелки вправо
но не работает может я не туда вставляю *2,6
Синтаксис функции АДРЕС:
АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[A1];[имя_листа])
Там, где вы умножаете, должно быть логическое значение [A1]: ИСТИНА или ЛОЖЬ (1 или 0).
Ребята привет! Подскажите как решить (буду очень признателен за готовый код) проблему.
Есть Гугл таблица с 10 листами. Один лист требуется скачивать на комп в формате CSV — это легко делается самой таблицей — есть возможность сохранить текущий лист. А вот второй лист нужно скачивать в формате ексель. И гугл уже не позволяет скачать текущий лист. Он скачивает в формате ексель всю книгу целиком. Это не удобно. Приходится удалять вручную ненужные листы. Либо сначала копировать весь лист в гугле, а затем вставлять в лист екселя.
В общем нужен код, который в дальнейшем привяжу к кнопочке, для скачивания определенного листа из книги с данными без формул и в формате ексель.
удаляю Лист по имени на ярлычке
машина вступает в диалог:
» Выбранные Листы будут удалены безвозвратно…»
Как сделать чтобы машина удаляла и не спрашивала
Евгений, помогите пожалуйста со следующей задачей.
В папке несколько документов эксель, в каждом из которых несколько листов.
Необходимо, чтобы макрос открывал по порядку каждый документ, проходил по каждому листу и брал оттуда нужную информацию. Затем эта информация переносится в новый документ эксель в заданном порядке.
В целом программу я реализовал, но столкнулся с проблемой, что debug упирается в последний лист первого открытого документа и не может дальше перейти к следующему документу. Т.е. необходимо прописать условие, чтобы vba понимал, что данный лист в документе последний.
Подскажите пожалуйста, как это сделать?
Александр, для обхода листов каждой книги используйте цикл For Each… Next:
Цикл будет завершен после обработки последнего листа.
Здравствуйте!
Возможно ли при копировании листа из другого файла перенести и пользовательский стиль таблицы?
Добрый день!
Если не ошибаюсь, пользовательский стиль таблицы всегда копируется вместе с листом, но внешний вид таблиц будет одинаков, если оба файла созданы и открыты в одной версии Excel.
Спасибо за ответ.
Увы, не так, и даже всё не так.
Стиль «пытается» копироваться, цвета отличаются оттенками, но это полбеды. Главное, что нужно мне, чтобы копировался цвет шрифта таблицы, а этого не происходит. Почему-то на листе-цели устанавливается на весь лист чёрный шрифт, который Эксель считает главнее табличного.
И даже больше. После ручной замены на всё листе-цели шрифта с чёрного на авто замены цвета шрифта в импортированной таблице не происходит. Что всегда есть при ручной работе с таблицами.
Ну, и совсем глюк. Пользовательский стиль переезжает и доступен в новой книге. Но даже при ручном его выборе на листе-цели для таблицы не происходит смены цвета шрифта.
Цвет можно поменять только руками (макросом) без учёта таблицы.
Всё это происходит на одном компьютере в одной ОС В10.
Я провел тест: создал умную таблицу, создал пользовательский стиль и применил его к таблице. Далее изменил у разных блоков строк цвет шрифта и начертание (полужирное, курсив). Программно скопировал лист с таблицей в другую книгу. Все форматирование сохранилось и пользовательский стиль тоже появился в разделе со стилями таблиц. У меня версия Excel — 16.0 (Excel 2016). Оба файла должны быть созданы в одной версии Excel, так как более старые версии Excel могут не поддерживать возможности новых версий.
Хм. Эксель 2016, всё выше описанное по-прежнему.
Если не затруднит, скопируйте умную таблицу макросом на новый лист, перед этим созданный тем же макросом.
Довольно часто при добавлении листов в книгу кодом необходимо удостовериться существует ли уже лист с таким именем или же нет. Т.к. если уже существует, то попытка создать лист с таким же именем неизбежно приведет к ошибке. Можно, конечно, поставить обработчик ошибки 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 (питание в университетской столовой).
Есть ли на панели лейбл с определённым именем
Здравствуйте. Есть панель на которой есть несколько Label`ов. Надо определить, есть ли на ней.
Узнать, есть ли на компьютере с указанным IP-адресом папка с определенным именем
Необходимо написать батник который по ip проверяет есть ли на компах определенная папка (в моем.
On Error Resume Next '- игнорируем ошибки
'.
'Err.Clear '- очищаем объект Err (применяем, если между On Error Resume Next и Err.Clear есть какой-либо код)
Workbooks("ИмяКниги").Worksheets("ИскомыйЛист").Activate'- какое-либо обращение к листу. Можно заменить на что-то другое.
If Err.Number <> 0 Then MsgBox "Листа с таким именем в книге нет."
On Error GoTo 0'- отключаем действие On Error Resume Next
'или On Error goto metka - безусловный переход на строку, содержащую слово с двоеточием, в данном случае - "metka:" Используется, как правило, для обработки ошибок.
'On Error goto metka
'.
Ext Sub' - выход из процедуры
'metka:'()
'Оработка ошибок
'End Sub
'или Err.Clear - очищаем объект Err для проверки в дальнейшем чего-либо подобного
Mne tak ponravilas' vasha funkciya, chto ya ne uderzhlasya i dal'she uprostil ee; udalil If:
Kstati, esli ona fnCheckWSheet, to lucshe strWSheetName, ili tak: fnCheckSheet i strSheetName
2R1001
1. Ваш код работать не будет: правильно - if ActiveWorkbook.Sheets(i).name="List" then .
2. Вариант перебора листов очевиден, но. для ускорения выполнения программ, по-возможности, следует отказываться от циклов.
2R1001
1. Ваш код работать не будет: правильно - if ActiveWorkbook.Sheets(i).name="List" then .
2. Вариант перебора листов очевиден, но. для ускорения выполнения программ, по-возможности, следует отказываться от циклов.[/quote]
Ну что Вы, он работает :-)
Я имел в виду переменную List, а не конкретное значение "List"
А насчет скорости выполнения могу поспорить, дело в том, что при использовании конструкции On error VBA выполняет гораздо больше инструкций и занимает гораздо больше тактов процессора, для этого надо попробовать выйти на уровень ассемблера, с другой стороны согласен, если искомый лист будет последним в списке, то выигрыш будет небольшим. Наверное, чтобы найти истину, надо сделать тест с проверкой ну хотя бы несколько десятков файлов. Но вообще-то все это фигня, т.к. я занимаюсь именно этим, в смысле большим кол-вом файло и основное время - 95% уходит в основном, на открытие файла :-(
Читайте также: