Excel если в имени листа есть

Проверка существования листа в рабочей книге Excel из кода VBA с помощью присвоения его объектной переменной или перебора существующих листов циклом.

Присвоение листа объектной переменной

Пользовательская функция VBA Excel для проверки существования листа в рабочей книге путем определения результата присвоения ссылки на него объектной переменной. Присвоение состоялось (SheetExist = True) – искомый лист существует, произошла ошибка и присвоение не состоялось (SheetExist = False) – лист не существует.

Function SheetExist(WbName As String, ShName As String) As Boolean

Dim mySheet As Worksheet

    On Error Resume Next

        Set mySheet = Workbooks(WbName).Sheets(ShName)

    SheetExist = Not mySheet Is Nothing

End Function

Аргументы функции SheetExist:

  • WbName – имя открытой рабочей книги, в которой ищется лист.
  • ShName – имя искомого рабочего листа.

Перебор существующих листов циклом

Проверка существования рабочего листа в книге Excel с помощью перебора существующих листов циклом VBA и сравнения их имен с именем искомого листа. Совпадение найдено (SheetExist = True) – искомый лист существует, совпадение не найдено (SheetExist = False) – лист не существует.

Function SheetExist(WbName As String, ShName As String) As Boolean

Dim mySheet As Worksheet

    For Each mySheet In Workbooks(WbName).Sheets

        If mySheet.Name = ShName Then

            SheetExist = True

            Exit Function

        End If

    Next

End Function

Пример проверки существования листа

Пример проверки существования искомого листа в рабочей книге Excel с помощью пользовательской функции VBA SheetExist:

Sub Primer()

    If SheetExist(ThisWorkbook.Name, «Лист1») Then

        MsgBox «Лист существует»

    Else

        MsgBox «Лист не существует»

    End If

End Sub

Имя сторонней открытой книги должно быть указано вместе с расширением:

...

    If SheetExist(«Книга2.xlsm», «Лист2») Then

...

Обратите внимание, если книга, имя которой указано в параметре WbName закрыта или не существует, будет сгенерирована ошибка.

Чтобы функция проверки существования рабочего листа SheetExist была доступна из модуля любой книги Excel на вашем компьютере, разместите ее в Личной книге макросов.

Источник

Дабы не создавать новую тему,

В моей книге есть макросы, имеющие в своем составе формулы со ссылками на другие листы этой книги. При запуске макросов необходимо проверить книгу на соответствие имен листов (не изменил ли кто случайно). если все ОК, то запускаем другой макрос, если нет, то сообщение, какой лист нужно проверить.

воспользовался примером Busine2012, и Казанский, написал код:

Visual Basic 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

   Sub ttt()
    
    'Создаём переменную, в которую поместим имя листа.
    Dim sName As String
    Dim aName As String
    
    'Создаём переменную, с помощью которой будем обращаться к нужному листу.
    Dim oSheet As Excel.Worksheet
    'Помещаем в переменные имена листов.
    sName = "ДАННЫЕ"
    aName = "РЕЗУЛЬТАТЫ"
    bName = "ИТОГИ
 
    On Error Resume Next
    
        'Присваиваем листу имя oSheet.
Set oSheet = Worksheets(sName)
  
    
        If Worksheets(sName) Is Nothing Then
            MsgBox "Проверь имя листа ДАННЫЕ.", vbCritical
   Else
    
Set oSheet = Worksheets(aName)
    
    If Worksheets(aName) Is Nothing Then
            MsgBox "проверь имя листа РЕЗУЛЬТАТЫ.", vbCritical
    Else
Set oSheet = Worksheets(bName)
    
    If Worksheets(bName) Is Nothing Then
            MsgBox "проверь имя листа ИТОГИ.", vbCritical
               Else
            MsgBox "Работа разрешена.", vbCritical  ' работа макроса
    End If
 End If
 End If
    
End Sub

Макрос работает.

Вопрос: можно ли упростить код для проверки имен листов, если число проверяемых листов на правильность имени будет больше 10 (без ухудшения производительности)?

Было бы конечно лучше, если макрос сам переименовывал неправильные названия без участия пользователя..

Заранее спасибо.

Добавлено через 25 минут

Цитата
Сообщение от dmb2
Посмотреть сообщение

Было бы конечно лучше, если макрос сам переименовывал неправильные названия без участия пользователя..

Visual Basic 
1

Лист1.Name="Данные"

Добавлено через 5 минут
Но код проверяет (соответственно и переименовывает неправильные листы) по одному за работу всего цикла. Хотелось бы чтобы сразу все листы проверил, и все исправил.

Добавлено через 5 минут
Возникла мысль, хотелось бы узнать мнение о целесообразности,

Может обойтись без проверки листов и сразу все листы переименовать?

Источник

От Климова П.Ю.  
Вариант I. (без ‘генерации’ ошибки)    
Option Compare Text  ‘Если Вы не  
понимаете, зачем используется эта инструкция, то оставьте её в покое  

  Private Function WorksheetIsExist(iName$) As Boolean  
‘***********************************************’  
‘   Дата создания 01/01/2005                    ‘  
‘   Автор Климов Павел Юрьевич                  ‘  
‘  

http://www.msoffice.nm.ru

                  ‘  
‘***********************************************’    
   Dim iList As Worksheet    
   For Each iList In Worksheets    
       If iList.Name = iName$ Then    
          WorksheetIsExist = True    
          Exit Function    
       End If    
   Next    
   WorksheetIsExist = False    
End Function  

  Private Function WorksheetIsExist(iName$) As Boolean  
‘***********************************************’  
‘   Дата создания 01/01/2005                    ‘  
‘   Автор Климов Павел Юрьевич                  ‘  
‘  

http://www.msoffice.nm.ru

                  ‘  
‘***********************************************’    
   Dim iList As Worksheet    
   For Each iList In Worksheets    
       If StrComp(iList.Name, iName$, vbTextCompare) = 0 Then  
          WorksheetIsExist = True  
          Exit Function  
       End If  
   Next  
   WorksheetIsExist = False  
End Function  

  Вариант II.    
Private Function WorksheetIsExist(iName$) As Boolean  
‘***********************************************’  
‘   Дата создания 01/01/2005                    ‘  
‘   Автор Климов Павел Юрьевич                  ‘  
‘  

http://www.msoffice.nm.ru

                  ‘  
‘***********************************************’    
   On Error Resume Next  
   WorksheetIsExist = IsObject(Worksheets(iName$))  
End Function  

  Private Function WorksheetIsExist(iName$) As Boolean  
   On Error Resume Next  
   WorksheetIsExist = (TypeOf Worksheets(iName$) Is Worksheet)  
End Function  

  Private Function WorksheetIsExist(iName$) As Boolean  
   On Error Resume Next  
   WorksheetIsExist = (TypeName(Worksheets(iName$)) = «Worksheet»)  
End Function  

  Private Function WorksheetIsExist(iName$) As Boolean  
   On Error Resume Next  
   WorksheetIsExist = (VarType(Worksheets(iName$)) = vbObject)  
End Function  

  Private Function WorksheetIsExist(iName$) As Boolean  
   On Error Resume Next  
   WorksheetIsExist = Len(Worksheets(iName$).Name) > 0  
End Function  

  Private Function WorksheetIsExist(iName$) As Boolean  
   On Error Resume Next  
   WorksheetIsExist = Worksheets(iName$).Index > 0  
End Function  
Пример вызова любой из вышеопубликованных авторских функций :    
Private Sub Test()  
   MsgBox WorksheetIsExist(«Имя_Рабочего_Листа»)  
End Sub

Источник

Хитрости »

1 Май 2011              136614 просмотров

Как узнать существует ли лист в книге?

Довольно часто при добавлении листов в книгу кодом необходимо удостовериться существует ли уже лист с таким именем или же нет. Т.к. если уже существует, то попытка создать лист с таким же именем неизбежно приведет к ошибке. Можно, конечно, поставить обработчик ошибки 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 использовать, но это уже другая тема :)

Также см.:
Как проверить открыта ли книга?

Статья помогла? Поделись ссылкой с друзьями!

  Плейлист   Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

Источник

200?’200px’:»+(this.scrollHeight+5)+’px’);»>
Sub Macros()
Dim ws As String
Dim a As Integer

ws = InputBox(«ВВедите имя листа/часть имени», «Поиск листа по имени», ActiveSheet.Name)

If ws = «» Then Exit Sub
On Error Resume Next

For i = 1 To Worksheets.Count
If InStr(Worksheets(i).Name, ws) > 0 Then
Worksheets(i).Select
choice = MsgBox(«Нашли?», vbYesNo)
If choice = «Yes» Then Exit For ‘ Exit inner loop.
End If
Next i
If Err Then MsgBox «Листа с таким именем нет»
End Sub

200?’200px’:»+(this.scrollHeight+5)+’px’);»>
Sub Macros()
Dim ws As String
Dim a As Integer

ws = InputBox(«ВВедите имя листа/часть имени», «Поиск листа по имени», ActiveSheet.Name)

If ws = «» Then Exit Sub
On Error Resume Next

For i = 1 To Worksheets.Count
If InStr(Worksheets(i).Name, ws) > 0 Then
Worksheets(i).Select
choice = MsgBox(«Нашли?», vbYesNo)
If choice = «Yes» Then Exit For ‘ Exit inner loop.
End If
Next i
If Err Then MsgBox «Листа с таким именем нет»
End Sub

200?’200px’:»+(this.scrollHeight+5)+’px’);»>
Sub Macros()
Dim ws As String
Dim a As Integer

ws = InputBox(«ВВедите имя листа/часть имени», «Поиск листа по имени», ActiveSheet.Name)

If ws = «» Then Exit Sub
On Error Resume Next

For i = 1 To Worksheets.Count
If InStr(Worksheets(i).Name, ws) > 0 Then
Worksheets(i).Select
choice = MsgBox(«Нашли?», vbYesNo)
If choice = «Yes» Then Exit For ‘ Exit inner loop.
End If
Next i
If Err Then MsgBox «Листа с таким именем нет»
End Sub

SkyPro Дата: Четверг, 05.06.2014, 13:19 | Сообщение № 2
Karbofox Дата: Четверг, 05.06.2014, 14:25 | Сообщение № 3
_Boroda_ Дата: Четверг, 05.06.2014, 14:36 | Сообщение № 4

Ответить

DJ_Marker_MC Дата: Четверг, 05.06.2014, 14:40 | Сообщение № 5
Alex_ST Дата: Четверг, 05.06.2014, 14:50 | Сообщение № 6


С уважением,
Алексей
MS Excel 2003 — the best.

_Boroda_ Дата: Четверг, 05.06.2014, 14:55 | Сообщение № 7

Ответить

Alex_ST Дата: Четверг, 05.06.2014, 14:58 | Сообщение № 8

Ответить

_Boroda_ Дата: Четверг, 05.06.2014, 15:01 | Сообщение № 9

Ответить

Alex_ST Дата: Четверг, 05.06.2014, 15:05 | Сообщение № 10

Ответить

Alex_ST Дата: Четверг, 05.06.2014, 15:08 | Сообщение № 11


С уважением,
Алексей
MS Excel 2003 — the best.

SkyPro Дата: Четверг, 05.06.2014, 15:09 | Сообщение № 12

200?’200px’:»+(this.scrollHeight+5)+’px’);»> Private Sub ListBox1_Click()
Sheets(ListBox1.Text).Activate
End Sub

Private Sub TextBox1_Change()
Dim sh As Worksheet
ListBox1.Clear
For Each sh In ThisWorkbook.Worksheets
If Not TextBox1.Text = «» Then
If LCase(sh.Name) Like «*» & LCase(TextBox1.Text) & «*» Then
ListBox1.AddItem sh.Name
End If
End If
Next
End Sub

200?’200px’:»+(this.scrollHeight+5)+’px’);»> Private Sub ListBox1_Click()
Sheets(ListBox1.Text).Activate
End Sub

Private Sub TextBox1_Change()
Dim sh As Worksheet
ListBox1.Clear
For Each sh In ThisWorkbook.Worksheets
If Not TextBox1.Text = «» Then
If LCase(sh.Name) Like «*» & LCase(TextBox1.Text) & «*» Then
ListBox1.AddItem sh.Name
End If
End If
Next
End Sub

200?’200px’:»+(this.scrollHeight+5)+’px’);»> Private Sub ListBox1_Click()
Sheets(ListBox1.Text).Activate
End Sub

Private Sub TextBox1_Change()
Dim sh As Worksheet
ListBox1.Clear
For Each sh In ThisWorkbook.Worksheets
If Not TextBox1.Text = «» Then
If LCase(sh.Name) Like «*» & LCase(TextBox1.Text) & «*» Then
ListBox1.AddItem sh.Name
End If
End If
Next
End Sub
Karbofox Дата: Четверг, 05.06.2014, 15:12 | Сообщение № 13

Уважаемый Борода,
Вам Спасибо, как всегда!

Уважаемый Борода,
Вам Спасибо, как всегда!

Уважаемый Борода,
Вам Спасибо, как всегда!

Автор — Karbofox
Дата добавления — 05.06.2014 в 15:12

Источник

VBA в Excel Объект Excel.Worksheet и программная работа с листами Excel средствами VBA

10.5 Коллекция Sheets и объект Worksheet, их свойства и методы

Объект Excel.Worksheet, программное создание, обнаружение, удаление листа Excel средствами VBA, свойства, методы и события объекта Excel.Worksheet

В 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 вы можете получить информацию об изменившемся значении, столбце и строке, в котором произошло изменение и т.п.

У объекта Worksheet есть еще два очень удобных события (их сильно не хватает объекту Document в Word). Это — события BeforeRightClick() и BeforeDoubleClick(). Как понятно из названия, первое событие позволяет перехватывать щелчок правой кнопкой мыши по любому месту в листе, а второе — двойной щелчок мышью. При помощи этих событий вы можете назначить свою реакцию (открытие контекстных меню, выдачу предупреждающих сообщений, переход в другой режим работы и т.п.) на действия пользователя.

Источник

Adblock
detector

Источник

Понравилась статья? Поделить с друзьями:

А вот еще интересные статьи:

  • Excel если в диапазоне ячеек есть значение то
  • Excel если в диапазоне месяца
  • Excel если в диапазоне есть число
  • Excel если в диапазоне есть значение то возвращает
  • Excel если было изменение то обновить таблицу

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии