Свойства и методы Worksheet

Автор Дмитрий Якушев На чтение16 мин. Просмотров70

Malcolm Gladwell

Мечтатель начинает с чистого листа бумаги и переосмысливает мир

Эта статья содержит полное руководство по использованию Excel
VBA Worksheet в Excel VBA. Если вы хотите узнать, как что-то сделать быстро, ознакомьтесь с кратким руководством к рабочему листу VBA ниже.

Если вы новичок в VBA, то эта статья — отличное место для начала. Мне нравится разбивать вещи на простые термины и объяснять их на простом языке.

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

Содержание

1. Краткое руководство к рабочему листу VBA
2. Вступление
3. Доступ к рабочему листу
4. Скрыть рабочий лист
5. Защитить рабочий лист
6. Индекс вне диапазона
7. Использование индекса для доступа к рабочему листу
8. Использование кодового имени рабочего листа
9. Кодовое имя в других книгах
10. Резюме кодового имени
11.  Активный лист
12. Объявление объекта листа
13. Доступ к рабочему листу в двух словах
14. Добавить рабочий лист
15. Удалить рабочий лист
16. Цикл по рабочим листам
17. Использование коллекции листов
18. Заключение

Краткое руководство к рабочему листу VBA

В следующей таблице приведен краткий обзор различных методов
Worksheet .

Примечание. Я использую Worksheet в таблице ниже, не указывая рабочую книгу, т.е. Worksheets, а не ThisWorkbook.Worksheets, wk.Worksheets и т.д. Это сделано для того, чтобы примеры были понятными и удобными для чтения. Вы должны всегда указывать рабочую книгу при использовании Worksheets . В противном случае активная рабочая книга будет использоваться по умолчанию.

Задача	Исполнение
Доступ к рабочему листу по имени	Worksheets(«Лист1»)
Доступ к рабочему листу по позиции слева	Worksheets(2) Worksheets(4)
Получите доступ к самому левому рабочему листу	Worksheets(1)
Получите доступ к самому правому листу	Worksheets(Worksheets.Count)
Доступ с использованием кодового имени листа (только текущая книга)	Смотри раздел статьи Использование кодового имени
Доступ по кодовому имени рабочего листа (другая рабочая книга)	Смотри раздел статьи Использование кодового имени
Доступ к активному листу	ActiveSheet
Объявить переменную листа	Dim sh As Worksheet
Назначить переменную листа	Set sh = Worksheets(«Лист1»)
Добавить лист	Worksheets.Add
Добавить рабочий лист и назначить переменную	Worksheets.Add Before:= Worksheets(1)
Добавить лист в первую позицию (слева)	Set sh =Worksheets.Add
Добавить лист в последнюю позицию (справа)	Worksheets.Add after:=Worksheets(Worksheets.Count)
Добавить несколько листов	Worksheets.Add Count:=3
Активировать рабочий лист	sh.Activate
Копировать лист	sh.Copy
Копировать после листа	sh1.Copy After:=Sh2
Скопировать перед листом	sh1.Copy Before:=Sh2
Удалить рабочий лист	sh.Delete
Удалить рабочий лист без предупреждения	Application.DisplayAlerts = False sh.Delete Application.DisplayAlerts = True
Изменить имя листа	sh.Name = «Data»
Показать/скрыть лист	sh.Visible = xlSheetHidden sh.Visible = xlSheetVisible sh.Name = «Data»
Перебрать все листы (For)	Dim i As Long For i = 1 To Worksheets.Count   Debug.Print Worksheets(i).Name Next i
Перебрать все листы (For Each)	Dim sh As Worksheet For Each sh In Worksheets     Debug.Print sh.Name Next

Вступление

Три наиболее важных элемента VBA — это Рабочая книга, Рабочий лист и Ячейки. Из всего кода, который вы пишете, 90% будут включать один или все из них.

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

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

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

Доступ к рабочему листу

В VBA каждая рабочая книга имеет коллекцию рабочих листов. В этой коллекции есть запись для каждого рабочего листа. Эта коллекция называется просто Worksheets и используется очень похоже на коллекцию Workbooks. Чтобы получить доступ к рабочему листу, достаточно указать имя.

Приведенный ниже код записывает «Привет Мир» в ячейках A1 на листах: Лист1, Лист2 и Лист3 текущей рабочей книги.

1 2 3 4 5 6 7 8

Sub ZapisVYacheiku1()       ' Запись в ячейку А1 в листе 1, листе 2 и листе 3     ThisWorkbook.Worksheets("Лист1").Range("A1") = "Привет Мир"     ThisWorkbook.Worksheets("Лист2").Range("A1") = "Привет Мир"     ThisWorkbook.Worksheets("Лист3").Range("A1") = "Привет Мир"   End Sub

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

1 2 3 4 5 6 7 8

Sub ZapisVYacheiku1()       ' Worksheets относятся к рабочим листам в активной рабочей книге.     Worksheets("Лист1").Range("A1") = "Привет Мир"     Worksheets("Лист2").Range("A1") = "Привет Мир"     Worksheets("Лист3").Range("A1") = "Привет Мир"   End Sub

Скрыть рабочий лист

В следующих примерах показано, как скрыть и показать лист.

1 2 3

ThisWorkbook.Worksheets("Лист1").Visible = xlSheetHidden   ThisWorkbook.Worksheets("Лист1").Visible = xlSheetVisible

Если вы хотите запретить пользователю доступ к рабочему листу, вы можете сделать его «очень скрытым». Это означает, что это может быть сделано видимым только кодом.

1 2 3 4 5

' Скрыть от доступа пользователя ThisWorkbook.Worksheets("Лист1").Visible = xlVeryHidden   ' Это единственный способ сделать лист xlVeryHidden видимым ThisWorkbook.Worksheets("Лист1").Visible = xlSheetVisible

Защитить рабочий лист

Другой пример использования Worksheet — когда вы хотите защитить его.

1 2 3

ThisWorkbook.Worksheets("Лист1").Protect Password:="Мойпароль"   ThisWorkbook.Worksheets("Лист1").Unprotect Password:="Мойпароль"

Индекс вне диапазона

При использовании Worksheets вы можете получить сообщение об ошибке:

Run-time Error 9 Subscript out of Range

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

1. Имя Worksheet , присвоенное рабочим листам, написано неправильно.
2. Название листа изменилось.
3. Рабочий лист был удален.
4. Индекс был большим, например Вы использовали рабочие листы (5), но есть только четыре рабочих листа
5. Используется неправильная рабочая книга, например Workbooks(«book1.xlsx»).Worksheets(«Лист1») вместо
   Workbooks(«book3.xlsx»).Worksheets («Лист1»).

Если у вас остались проблемы, используйте один из циклов из раздела «Циклы по рабочим листам», чтобы напечатать имена всех рабочих листов коллекции.

Использование индекса для доступа к рабочему листу

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14

' Использование этого кода является плохой идеей, так как ' позиции листа все время меняются Sub IspIndList()       With ThisWorkbook         ' Самый левый лист         Debug.Print .Worksheets(1).Name         ' Третий лист слева         Debug.Print .Worksheets(3).Name         ' Самый правый лист         Debug.Print .Worksheets(.Worksheets.Count).Name     End With   End Sub

В приведенном выше примере я использовал Debug.Print для печати в Immediate Window. Для просмотра этого окна выберите «Вид» -> «Immediate Window » (Ctrl + G).

Использование кодового имени рабочего листа

Лучший способ получить доступ к рабочему листу — использовать кодовое имя. Каждый лист имеет имя листа и кодовое имя. Имя листа — это имя, которое отображается на вкладке листа в Excel.

Изменение имени листа не приводит к изменению кодового имени, что означает, что ссылка на лист по кодовому имени — отличная идея.

Если вы посмотрите в окне свойств VBE, вы увидите оба имени. На рисунке вы можете видеть, что кодовое имя — это имя вне скобок, а имя листа — в скобках.

Вы можете изменить как имя листа, так и кодовое имя в окне свойств листа (см. Изображение ниже).

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

1 2 3 4 5 6 7 8

Sub IspKodImya2()       ' Используя кодовое имя листа     Debug.Print CodeName.Name     CodeName.Range("A1") = 45     CodeName.Visible = True   End Sub

Это делает код легким для чтения и безопасным от изменения пользователем имени листа.

Кодовое имя в других книгах

Есть один недостаток использования кодового имени. Он относится только к рабочим листам в рабочей книге, которая содержит код, т.е. ThisWorkbook.

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

Sub ИспЛист()       Dim sh As Worksheet     ' Получить рабочий лист под кодовым именем     Set sh = SheetFromCodeName("CodeName", ThisWorkbook)     ' Используйте рабочий лист     Debug.Print sh.Name   End Sub   ' Эта функция получает объект листа из кодового имени Public Function SheetFromCodeName(Name As String, bk As Workbook) As Worksheet       Dim sh As Worksheet     For Each sh In bk.Worksheets         If sh.CodeName = Name Then            Set SheetFromCodeName = sh            Exit For         End If     Next sh   End Function

Использование приведенного выше кода означает, что если пользователь изменит имя рабочего листа, то на ваш код это не повлияет.

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

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

1 2 3 4 5 6 7 8 9 10 11

Public Function SheetFromCodeName2(codeName As String _                              , bk As Workbook) As Worksheet       ' Получить имя листа из CodeName, используя VBProject     Dim sheetName As String     sheetName = bk.VBProject.VBComponents(codeName).Properties("Name")       ' Используйте имя листа, чтобы получить объект листа     Set SheetFromCodeName2 = bk.Worksheets(sheetName)   End Function

Резюме кодового имени

Ниже приведено краткое описание использования кодового имени:

1. Кодовое имя рабочего листа может быть использовано непосредственно в коде, например. Sheet1.Range
2. Кодовое имя будет по-прежнему работать, если имя рабочего листа будет изменено.
3. Кодовое имя может использоваться только для листов в той же книге, что и код.
4. Везде, где вы видите ThisWorkbook.Worksheets («имя листа»), вы можете заменить его кодовым именем рабочего листа.
5. Вы можете использовать функцию SheetFromCodeName сверху, чтобы получить кодовое имя рабочих листов в других рабочих книгах.

 Активный лист

Объект ActiveSheet ссылается на рабочий лист, который в данный момент активен. Вы должны использовать ActiveSheet только в том случае, если у вас есть особая необходимость ссылаться на активный лист.

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

Если вы используете метод листа, такой как Range, и не упоминаете лист, он по умолчанию будет использовать активный лист.

1 2 3 4 5

' Написать в ячейку A1 в активном листе ActiveSheet.Range("A1") = 99   ' Активный лист используется по умолчанию, если лист не используется Range("A1") = 99

Объявление объекта листа

Объявление объекта листа полезно для того, чтобы сделать ваш код более понятным и легким для чтения.

В следующем примере показан код для обновления диапазонов ячеек. Первый Sub не объявляет объект листа. Вторая подпрограмма объявляет объект листа, и поэтому код намного понятнее.

1 2 3 4 5 6 7 8

Sub NeObyavObektList()       Debug.Print ThisWorkbook.Worksheets("Лист1").Name     ThisWorkbook.Worksheets("Лист1").Range("A1") = 6     ThisWorkbook.Worksheets("Лист1").Range("B2:B9").Font.Italic = True     ThisWorkbook.Worksheets("Лист1").Range("B2:B9").Interior.Color = rgbRed   End Sub

1 2 3 4 5 6 7 8 9 10

Sub ObyavObektList()       Dim sht As Worksheet     Set sht = ThisWorkbook.Worksheets("Лист1")       sht.Range("A1") = 6     sht.Range("B2:B9").Font.Italic = True     sht.Range("B2:B9").Interior.Color = rgbRed   End Sub

Вы также можете использовать ключевое слово With с объектом листа, как показано в следующем примере.

1 2 3 4 5 6 7 8 9 10 11 12

Sub ObyavObektListWith()       Dim sht As Worksheet     Set sht = ThisWorkbook.Worksheets("Лист1")       With sht         .Range("A1") = 6         .Range("B2:B9").Font.Italic = True         .Range("B2:B9").Interior.Color = rgbRed     End With   End Sub

Доступ к рабочему листу в двух словах

Из-за множества различных способов доступа к рабочему листу вы можете быть сбитыми с толку. Так что в этом разделе я собираюсь разбить его на простые термины.

1. Если вы хотите использовать тот лист, который активен в данный момент, используйте ActiveSheet.

1	ActiveSheet.Range("A1") = 55

2. Если лист находится в той же книге, что и код, используйте кодовое имя.

1	Sheet1.Range("A1") = 55

3. Если рабочая таблица находится в другой рабочей книге, сначала получите рабочую книгу, а затем получите рабочую таблицу.

1 2 3 4 5 6

' Получить рабочую книгу Dim wk As Workbook Set wk = Workbooks.Open("C:\Документы\Счета.xlsx", ReadOnly:=True) ' Затем получите лист Dim sh As Worksheet Set sh = wk.Worksheets("Лист1")

Если вы хотите защитить пользователя от изменения имени листа, используйте функцию SheetFromCodeName из раздела «Имя кода».

1 2 3 4 5 6 7

' Получить рабочую книгу Dim wk As Workbook Set wk = Workbooks.Open("C:\Документы\Счета.xlsx", ReadOnly:=True)   ' Затем получите лист Dim sh As Worksheet Set sh = SheetFromCodeName("sheetcodename",wk)

Добавить рабочий лист

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

Когда вы добавляете рабочий лист, он создается с именем по умолчанию, например «Лист4». Если вы хотите изменить имя, вы можете легко сделать это, используя свойство Name.

В следующем примере добавляется новый рабочий лист и изменяется имя на «Счета». Если лист с именем «Счета» уже существует, вы получите сообщение об ошибке.

1 2 3 4 5 6 7 8 9 10 11 12 13

Sub DobavitList()       Dim sht As Worksheet       ' Добавляет новый лист перед активным листом     Set sht = ThisWorkbook.Worksheets.Add     ' Установите название листа     sht.Name = "Счета"       ' Добавляет 3 новых листа перед активным листом     ThisWorkbook.Worksheets.Add Count:=3   End Sub

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

Для этого вам нужно указать, какой лист новый лист должен быть вставлен до или после. Следующий код показывает вам, как это сделать.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

Sub DobavitListPervPosl()       Dim shtNew As Worksheet     Dim shtFirst As Worksheet, shtLast As Worksheet       With ThisWorkbook           Set shtFirst = .Worksheets(1)         Set shtLast = .Worksheets(.Worksheets.Count)           ' Добавляет новый лист на первую позицию в книге         Set shtNew = Worksheets.Add(Before:=shtFirst)         shtNew.Name = "FirstSheet"           ' Добавляет новый лист к последней позиции в книге         Set shtNew = Worksheets.Add(After:=shtLast)         shtNew.Name = "LastSheet"       End With   End Sub

Удалить рабочий лист

Чтобы удалить лист, просто вызовите Delete.

1 2 3

Dim sh As Worksheet Set sh = ThisWorkbook.Worksheets("Лист12") sh.Delete

Excel отобразит предупреждающее сообщение при удалении листа. Если вы хотите скрыть это сообщение, вы можете использовать код ниже:

1 2 3

Application.DisplayAlerts = False sh.Delete Application.DisplayAlerts = True

Есть два аспекта, которые нужно учитывать при удалении таблиц.

Если вы попытаетесь получить доступ к рабочему листу после его удаления, вы получите ошибку «Subscript out of Range», которую мы видели в разделе «Доступ к рабочему листу».

1 2 3 4 5 6

Dim sh As Worksheet Set sh = ThisWorkbook.Worksheets("Лист2") sh.Delete   ' Эта строка выдаст «Subscript out of Range», так как «Лист2» не существует Set sh = ThisWorkbook.Worksheets("Лист2")

Вторая проблема — когда вы назначаете переменную листа. Если вы попытаетесь использовать эту переменную после удаления листа, вы получите ошибку автоматизации, подобную этой:

Run-Time error -21147221080 (800401a8′) Automation Error

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

В следующем примере показано, как происходят ошибки автоматизации.

1 2 3 4

sh.Delete   ' Эта строка выдаст ошибку автоматизации Debug.Assert sh.Name

Если вы назначите переменную Worksheet действительному рабочему листу, он будет работать нормально.

1 2 3 4 5 6 7

sh.Delete   ' Назначить sh на другой лист Set sh = Worksheets("Лист3")   ' Эта строка будет работать нормально Debug.Assert sh.Name

Цикл по рабочим листам

Элемент «Worksheets» — это набор рабочих листов, принадлежащих рабочей книге. Вы можете просмотреть каждый лист в коллекции рабочих листов, используя циклы «For Each» или «For».

В следующем примере используется цикл For Each.

1 2 3 4 5 6 7 8 9

Sub CiklForEach()       ' Записывает «Привет Мир» в ячейку A1 для каждого листа     Dim sht As Worksheet     For Each sht In ThisWorkbook.Worksheets          sht.Range("A1") = "Привет Мир"     Next sht   End Sub

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

1 2 3 4 5 6 7 8 9

Sub CiklFor()           ' Записывает «Привет Мир» в ячейку A1 для каждого листа     Dim i As Long     For i = 1 To ThisWorkbook.Worksheets.Count          ThisWorkbook.Worksheets(i).Range("A1") = "Привет Мир"     Next sht   End Sub

Вы видели, как получить доступ ко всем открытым рабочим книгам и как получить доступ ко всем рабочим листам в ThisWorkbook. Давайте сделаем еще один шаг вперед — узнаем, как получить доступ ко всем рабочим листам во всех открытых рабочих книгах.

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

1 2 3 4 5 6 7 8 9 10 11 12 13

Sub NazvVsehStr()       ' Печатает рабочую книгу и названия листов для     ' всех листов в открытых рабочих книгах     Dim wrk As Workbook     Dim sht As Worksheet     For Each wrk In Workbooks         For Each sht In wrk.Worksheets             Debug.Print wrk.Name + ":" + sht.Name         Next sht     Next wrk   End Sub

Использование коллекции листов

Рабочая книга имеет еще одну коллекцию, похожую на Worksheets под названием Sheets. Это иногда путает пользователей. Чтобы понять, в первую очередь, вам нужно знать о типе листа, который является диаграммой.

В Excel есть возможность создать лист, который является диаграммой. Для этого нужно:

1. Создать диаграмму на любом листе.
2. Щелкнуть правой кнопкой мыши на графике и выбрать «Переместить».
3. Выбрать первый вариант «Новый лист» и нажмите «ОК».

Теперь у вас есть рабочая книга, в которой есть типовые листы и лист-диаграмма.

• Коллекция «Worksheets » относится ко всем рабочим листам в рабочей книге. Не включает в себя листы типа диаграммы.
• Коллекция Sheets относится ко всем листам, принадлежащим книге, включая листы типовой диаграммы.

Ниже приведены два примера кода. Первый проходит через все листы в рабочей книге и печатает название листа и тип листа. Второй пример делает то же самое с коллекцией Worksheets.

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

Sub KollSheets()       Dim sht As Variant     ' Показать название и тип каждого листа     For Each sht In ThisWorkbook.Sheets         Debug.Print sht.Name & " is type " & TypeName(sht)     Next sht   End Sub   Sub KollWorkSheets()       Dim sht As Variant     ' Показать название и тип каждого листа     For Each sht In ThisWorkbook.Worksheets         Debug.Print sht.Name & " is type " & TypeName(sht)     Next sht   End Sub

Если у вас нет листов диаграмм, то использование коллекции Sheets — то же самое, что использование коллекции WorkSheets.

Заключение

На этом мы завершаем статью о Worksheet VBA. Я надеюсь, что было полезным.

Три наиболее важных элемента Excel VBA — это рабочие книги, рабочие таблицы, диапазоны и ячейки.

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

Источник