Это полное руководство по работе с таблицами / листами в Excel.
Внизу этого руководства мы создали памятку общих команд для работы с листами.
Листы Vs. Рабочие листы
Есть два способа ссылаться на Таблицы с помощью VBA. Первый связан с объектом Sheets:
| 1 | Таблицы ("Лист1"). Активировать |
Другой - с объектом Worksheets:
| 1 | Рабочие листы ("Лист1"). Активировать |
В 99% случаев эти два объекта идентичны. Фактически, если вы искали в Интернете примеры кода VBA, вы, вероятно, видели, что используются оба объекта. Вот разница:
Коллекция Таблиц содержит Рабочие Таблицы И Таблицы диаграмм.
Так что используйте Таблицы, если вы хотите включать обычные Рабочие Таблицы И Таблицы диаграмм. Используйте Рабочие листы, если вы хотите исключить Таблицы диаграмм. В оставшейся части этого руководства мы будем использовать Таблицы и Рабочие листы как взаимозаменяемые.
Ссылки на листы
Есть несколько способов ссылаться на Таблицы:
- ActiveSheet
- Имя вкладки листа
- Индексный номер листа
- Кодовое имя листа
ActiveSheet
ActiveSheet - это текущий активный лист. Другими словами, если вы приостановили код и посмотрели в Excel, то будет виден лист. В приведенном ниже примере кода будет отображаться окно сообщений с именем ActiveSheet.
| 1 | MsgBox ActiveSheet.Name |
Имя листа
Вы, вероятно, больше всего знакомы со ссылками на Таблицы по их имени вкладки:
| 1 | Таблицы ("TabName"). Активировать |
Индексный номер листа
Номер индекса листа - это позиция листа в книге. 1 - это первый лист. 2 - второй лист и т.д .:
| 1 | Таблицы (1) .Активировать |
Номер индекса листа - последний лист в книге
Чтобы ссылаться на последний лист в книге, используйте Sheets.Count, чтобы получить последний номер индекса:
| 1 | Таблицы (Sheets.Count). Активировать |
Лист «Кодовое имя»
Кодовое имя листа - это имя объекта в VBA:
| 1 | CodeName.Activate |
Ссылки на листы в других книгах
Также легко ссылаться на Таблицы в других книгах. Для этого вам нужно использовать объект Workbooks:
| 1 | Книги ("VBA_Examples.xlsm"). Таблицы ("Лист1"). Активировать |
Важный: Рабочая книга должна быть открыта, прежде чем вы сможете ссылаться на нее в Таблицах.
Активировать или выбрать лист
В другой статье мы обсудим все, что касается активации и выбора листов. Краткая версия такова:
Когда вы активируете лист, он становится ActiveSheet. Это тот лист, который вы бы увидели, если бы посмотрели в свою программу Excel. Одновременно можно активировать только один лист.
Активировать лист
| 1 | Таблицы ("Лист1"). Активировать |
Когда вы выбираете лист, он также становится ActiveSheet. Однако вы можете выбрать сразу несколько листов. Когда одновременно выбрано несколько листов, «верхним» листом является ActiveSheet. Однако вы можете переключать ActiveSheet на выбранных листах.
Выбрать лист
| 1 | Таблицы ("Лист1"). Выберите |
Выбрать несколько листов
Используйте массив для одновременного выбора нескольких листов:
| 1 | Листы (Массив ("Лист2", "Лист3")). Выберите |
Переменная рабочего листа
Назначение рабочего листа переменной позволяет ссылаться на рабочий лист по имени переменной. Это может сэкономить массу времени на вводе текста и упростить чтение кода. Есть также много других причин, по которым вы можете захотеть использовать переменные.
Чтобы объявить переменную рабочего листа:
| 1 | Dim ws как рабочий лист |
Назначьте рабочий лист переменной:
| 1 | Установить ws = Sheets ("Sheet1") |
Теперь вы можете ссылаться на переменную рабочего листа в своем коде:
| 1 | ws.Activate |
Перебирать все листы в книге
Переменные рабочего листа необходимы, когда вы хотите просмотреть все рабочие листы в книге. Самый простой способ сделать это:
| 12345 | Dim ws как рабочий листДля каждого ws в листахMsgBox ws.nameСледующий ws |
Этот код будет проходить через все рабочие листы в книге, отображая имя каждого рабочего листа в окне сообщения. Цикл по всем листам в книге очень полезен при блокировке / разблокировке или скрытии / отображении нескольких листов одновременно.
Защита рабочего листа
Защита книги
Защита книги блокирует ее от структурных изменений, таких как добавление, удаление, перемещение или скрытие листов.
Вы можете включить защиту книги с помощью VBA:
| 1 | ActiveWorkbook.Protect Пароль: = "Пароль" |
или отключить защиту книги:
| 1 | ActiveWorkbook.UnProtect Пароль: = "Пароль" |
Примечание: вы также можете защитить / снять защиту без пароля, опуская аргумент пароля:
| 1 | ActiveWorkbook.Protect |
Защита рабочего листа
Защита на уровне рабочего листа предотвращает внесение изменений в отдельные рабочие листы.
Защитить рабочий лист
| 1 | Рабочие листы («Лист1»). Защитить «Пароль» |
Снять защиту листа
| 1 | Рабочие листы ("Лист1"). Снять защиту "Пароль" |
Существует множество вариантов защиты рабочих листов (разрешить изменение форматирования, разрешить пользователю вставлять строки и т. Д.). Мы рекомендуем использовать средство записи макросов для записи желаемых параметров.
Мы обсуждаем защиту рабочего листа более подробно здесь.
Видимое свойство листа
Возможно, вы уже знаете, что рабочие листы можно скрыть:
На самом деле существует три параметра видимости рабочего листа: Видимый, Скрытый и Очень скрыто.Скрытые листы могут быть отображены любым обычным пользователем Excel, щелкнув правой кнопкой мыши в области вкладки листа (как показано выше). Листы VeryHidden можно отобразить только с помощью кода VBA или из редактора VBA. Используйте следующие примеры кода, чтобы скрыть / показать листы:
Показать рабочий лист
| 1 | Листы ("Лист1"). Visible = xlSheetVisible |
Скрыть рабочий лист
| 1 | Листы ("Лист1"). Visible = xlSheetHidden |
Очень скрыть рабочий лист
| 1 | Листы ("Лист1"). Visible = xlSheetVeryHidden |
События на уровне рабочего листа
События - это триггеры, которые могут вызывать запуск «процедур обработки событий». Например, вы можете запускать код каждый раз, когда изменяется какая-либо ячейка на листе или когда лист активируется.
Процедуры событий рабочего листа должны быть помещены в модуль рабочего листа:
Есть множество событий рабочего листа. Чтобы увидеть полный список, перейдите в модуль рабочего листа, выберите «Рабочий лист» в первом раскрывающемся списке. Затем выберите процедуру события из второго раскрывающегося списка, чтобы вставить ее в модуль.
Рабочий лист активировать событие
События активации рабочего листа запускаются каждый раз, когда рабочий лист открывается.
| 123 | Личный вспомогательный лист_активировать ()Диапазон ("A1"). ВыберитеКонец подписки |
Этот код будет выбирать ячейку A1 (сбрасывая область просмотра в левый верхний угол рабочего листа) каждый раз, когда рабочий лист открывается.
Событие изменения рабочего листа
События изменения рабочего листа запускаются всякий раз, когда изменяется значение ячейки на листе. Прочтите наше руководство о событиях изменения рабочего листа для получения дополнительной информации.
Лист шпаргалки
Ниже вы найдете шпаргалку, содержащую общие примеры кода для работы с таблицами в VBA.
Шпаргалка по VBA Worksheets
Шпаргалка по рабочим листам VBA| Описание | Пример кода |
|---|---|
| Ссылки и активация листов | |
| Имя вкладки | Таблицы («Ввод»). Активировать |
| Кодовое имя VBA | Sheet1.Activate |
| Позиция индекса | Таблицы (1) .Активировать |
| Выбрать лист | |
| Выбрать лист | Таблицы ("Ввод"). Выбрать |
| Установить переменную | Dim ws как рабочий лист Установить ws = ActiveSheet |
| Имя / Переименовать | ActiveSheet.Name = "NewName" |
| Следующий лист | ActiveSheet.Next.Activate |
| Цикл по всем листам | Dim ws как рабочий лист Для каждого ws в листах Msgbox ws.name Следующий ws |
| Перебирать выбранные листы | Dim ws как рабочий лист Для каждого ws в ActiveWindow.SelectedSheets MsgBox ws.Name Следующий ws |
| Получить ActiveSheet | MsgBox ActiveSheet.Name |
| Добавить лист | Таблицы.Добавить |
| Добавить лист и имя | Sheets.Add.Name = "NewSheet" |
| Добавить лист с именем из ячейки | Sheets.Add.Name = диапазон ("a3"). Значение |
| Добавить лист после другого | Sheets.Add After: = Sheets ("Ввод") |
| Добавить лист после и имени | Sheets.Add (After: = Sheets ("Input")). Name = "NewSheet" |
| Добавить лист перед и имя | Sheets.Add (Before: = Sheets ("Input")). Name = "NewSheet" |
| Добавить лист в конец книги | Sheets.Add After: = Sheets (Sheets.Count) |
| Добавить лист в начало книги | Sheets.Add (Before: = Sheets (1)). Name = "FirstSheet" |
| Добавить лист в переменную | Dim ws как рабочий лист Установить ws = Sheets.Add |
| Копировать рабочие листы | |
| Переместить лист в конец книги | Листы ("Sheet1"). Переместить после: = Sheets (Sheets.Count) |
| К новой книге | Листы ("Лист1"). Копировать |
| Выбранные листы в новую книгу | ActiveWindow.SelectedSheets.Copy |
| Перед другим листом | Листы ("Лист1"). Копировать до: = Листы ("Лист2") |
| Перед первым листом | Листы ("Лист1"). Копировать до: = Листы (1) |
| После последнего листа | Листы ("Sheet1"). Копировать после: = Sheets (Sheets.Count) |
| Копия и имя | Листы ("Sheet1"). Копировать после: = Sheets (Sheets.Count) ActiveSheet.Name = "LastSheet" |
| Копирование и имя из значения ячейки | Листы ("Sheet1"). Копировать после: = Sheets (Sheets.Count) ActiveSheet.Name = Диапазон ("A1"). Значение |
| К другой книге | Таблицы ("Sheet1"). Копировать перед: = Workbooks ("Example.xlsm"). Sheets (1) |
| Скрыть / показать листы | |
| Скрыть лист | Таблицы ("Лист1"). Visible = False или Таблицы ("Лист1"). Visible = xlSheetHidden |
| Показать лист | Таблицы ("Лист1"). Visible = True или Таблицы ("Лист1"). Visible = xlSheetVisible |
| Очень Скрытый Лист | Листы («Лист1»). Visible = xlSheetVeryHidden |
| Удалить или очистить таблицы | |
| Удалить лист | Таблицы ("Лист1"). Удалить |
| Удалить лист (обработка ошибок) | При ошибке Возобновить Далее Таблицы ("Лист1"). Удалить При ошибке GoTo 0 |
| Удалить лист (без запроса) | Application.DisplayAlerts = False Таблицы ("Лист1"). Удалить Application.DisplayAlerts = True |
| Чистый лист | Таблицы ("Sheet1"). Cells.Clear |
| Очистить только содержимое листа | Таблицы ("Sheet1"). Cells.ClearContents |
| Очистить используемый лист | Листы ("Лист1"). UsedRange.Clear |
| Защитить или снять защиту листов | |
| Снять защиту (без пароля) | Таблицы ("Лист1"). Снять защиту |
| Снять защиту (Пароль) | Таблицы ("Sheet1"). Снять защиту "Пароль" |
| Защитить (без пароля) | Листы ("Лист1"). Защитить |
| Защитить (пароль) | Таблицы ("Sheet1"). Защитить "Пароль" |
| Защитить, но разрешить доступ VBA | Таблицы ("Лист1"). Защитить UserInterfaceOnly: = True |
| Снять защиту со всех листов | Dim ws как рабочий лист Для каждого ws в листах ws.Unprotect "пароль" Следующий ws |
