VBA Sheets - Най -доброто ръководство

Това е най -доброто ръководство за работа с таблици / работни листове в 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

Справочни листове в други работни книги

Също така е лесно да се позовавате на листове в други работни книги. За да направите това, трябва да използвате обекта на работните книги:

1 Работни книги ("VBA_Examples.xlsm"). Работни листове ("Лист1"). Активирайте

Важно: Работната книга трябва да е отворена, преди да можете да се позовавате на нейните таблици.

Активиране срещу Изберете лист

В друга статия обсъждаме всичко за активирането и избора на листове. Кратката версия е следната:

Когато активирате лист, той става ActiveSheet. Това е листът, който ще видите, ако погледнете програмата си Excel. Може да се активира само един лист наведнъж.

Активирайте лист

1 Листове („Лист1“). Активирайте

Когато изберете лист, той също става ActiveSheet. Можете обаче да изберете няколко листа едновременно. Когато са избрани няколко листа наведнъж, „горният“ лист е ActiveSheet. Можете обаче да превключвате ActiveSheet в рамките на избрани листове.

Изберете лист

1 Листове („Лист1“). Изберете

Изберете няколко листа

Използвайте масив, за да изберете няколко листа наведнъж:

1 Работни листове (масив ("лист2", "лист3")). Изберете

Променлива на работен лист

Присвояването на работен лист към променлива ви позволява да посочвате работния лист по името на променливата. Това може да спести много писане и да направи кода ви по -лесен за четене. Има и много други причини, поради които може да искате да използвате променливи.

За да декларирате променлива на работен лист:

1 Dim ws като работен лист

Присвояване на работен лист на променлива:

1 Задайте ws = Листове ("Sheet1")

Сега можете да се позовавате на променливата на работния лист във вашия код:

1 ws.Активирайте

Прегледайте всички листове в работната книга

Променливите на работния лист са от съществено значение, когато искате да прегледате всички работни листове в работна книга. Най -лесният начин да направите това е:

12345 Dim ws като работен листЗа всеки ws в работни листовеMsgBox ws.nameСледващата ws

Този код ще премине през всички работни листове в работната книга, показвайки името на всеки работен лист в поле за съобщение. Преглеждането на всички листове в работна книга е много полезно при заключване / отключване или скриване / разкриване на няколко работни листа едновременно.

Защита на работния лист

Защита на работната книга

Защитата на работната книга заключва работната книга от структурни промени като добавяне, изтриване, преместване или скриване на работни листове.

Можете да включите защитата на работната книга с помощта на VBA:

1 ActiveWorkbook.Protect Password: = "Парола"

или деактивирайте защитата на работната книга:

1 ActiveWorkbook.UnProtect Password: = "Парола"

Забележка: Можете също да защитите / премахнете защитата без парола, като пропуснете аргумента Password:

1 ActiveWorkbook.Protect

Защита на работния лист

Защитата на ниво работен лист предотвратява промени в отделни работни листове.

Защита на работен лист

1 Работни листове („Лист1“). Защитете „Парола“

Премахване на защитата на работния лист

1 Работни листове („Sheet1“). Премахнете защитата на „Password“

Има различни опции за защита на работни листове (разрешаване на промени във форматирането, разрешаване на потребителя да вмъква редове и т.н.) Препоръчваме да използвате макрорекордера за запис на желаните от вас настройки.

Тук обсъждаме защитата на работния лист по -подробно.

Видимо свойство на работен лист

Може би вече знаете, че работните листове могат да бъдат скрити:

Всъщност има три настройки за видимост на работния лист: Видима, Скрита и Много скрит.Скритите листове могат да бъдат скрити от всеки обикновен потребител на Excel - като щракнете с десния бутон в областта на раздела на работния лист (показан по -горе). Много скритите листове могат да бъдат скрити само с VBA код или от редактора на VBA. Използвайте следните примери за код, за да скриете / покажете работни листове:

Показване на работен лист

1 Работни листове ("Sheet1"). Visible = xlSheetVisible

Скриване на работен лист

1 Работни листове ("Sheet1"). Visible = xlSheetHidden

Много скриване на работен лист

1 Работни листове ("Sheet1"). Visible = xlSheetVeryHidden

Събития на ниво работен лист

Събитията са тригери, които могат да предизвикат изпълнение на „Процедури за събития“. Например, можете да накарате кода да се изпълнява всеки път, когато се промени някоя клетка на работен лист или когато се активира работен лист.

Процедурите за събития на работен лист трябва да бъдат поставени в модул на работен лист:

Има множество събития на работен лист. За да видите пълен списък, отидете на модул на работен лист, изберете „Работен лист“ от първото падащо меню. След това изберете процедура за събитие от второто падащо меню, за да я вмъкнете в модула.

Работен лист Активиране на събитие

Събитията за активиране на работен лист се изпълняват всеки път, когато работният лист се отваря.

123 Частен под работен лист_Activate ()Диапазон ("A1"). ИзберетеEnd Sub

Този код ще избира клетка A1 (нулиране на областта за изглед в горния ляв ъгъл на работния лист) всеки път, когато работният лист се отвори.

Събитие за смяна на работен лист

Събитията за смяна на работен лист се изпълняват винаги, когато се промени стойност на клетка в работния лист. Прочетете нашия урок за събития за смяна на работен лист за повече информация.

Лист за кодове на работен лист

По -долу ще намерите лист за мами, съдържащ общи примери за код за работа с листове във VBA

VBA работни листове Cheatsheet

VBA работни листове Cheatsheet
ОписаниеПример за код
Справочни и активиращи листове
Име на разделаТаблици („Въвеждане“). Активирайте
Кодово име на VBAЛист 1. Активирайте
Позиция на индексаЛистове (1). Активирайте
Изберете лист
Изберете листТаблици („Въвеждане“). Изберете
Задайте променливаDim ws като работен лист
Задайте ws = ActiveSheet
Име / ПреименуванеActiveSheet.Name = "Ново име"
Следващ листActiveSheet.Next.Activate
Прегледайте всички листовеDim ws като работен лист
За всеки ws в работни листове
Msgbox ws.name
Следващата ws
Превъртете избрани листовеЗатъмнете като работен лист
За всеки ws в ActiveWindow.SelectedSheets
MsgBox ws.Name
Следващата ws
Вземете ActiveSheetMsgBox ActiveSheet.Name
Добавяне на листЛистове. Добавяне
Добавете лист и имеSheets.Add.Name = "Нов лист"
Добавете лист с име от клеткаSheets.Add.Name = диапазон ("a3"). Стойност
Добавяне на лист след другSheets.Add After: = Sheets ("Input")
Добавяне на лист след и имеSheets.Add (After: = Sheets ("Input")). Name = "NewSheet"
Добавете лист преди и имеSheets.Add (Преди: = Sheets ("Въвеждане")). Name = "NewSheet"
Добавяне на лист към края на работната книгаSheets.Add After: = Sheets (Sheets.Count)
Добавяне на лист към началото на работната книгаSheets.Add (Преди: = Sheets (1)). Name = "FirstSheet"
Добавяне на лист към променливаЗатъмнете като работен лист
Задайте 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 = "Последен лист"
Копиране и име от стойността на клеткатаЛистове ("Sheet1"). Копиране след: = Sheets (Sheets.Count)
ActiveSheet.Name = Обхват ("A1"). Стойност
Към друга работна книгаЛистове ("Лист1"). Копиране преди: = Работни книги ("Пример.xlsm"). Листове (1)
Скриване / показване на листове
Скриване на листаЛистове ("Sheet1"). Visible = False
или
Листове ("Sheet1"). Visible = xlSheetHidden
Показване на листаЛистове („Лист1“). Видимо = Вярно
или
Листове ("Sheet1"). Visible = xlSheetVisible
Много скрит листЛистове („Лист1“). Видимо = xlSheetVeryHidden
Изтриване или изчистване на таблици
Изтриване на листЛистове („Лист1“). Изтриване
Изтриване на лист (обработка на грешки)На грешка възобновяване следващо
Листове („Лист1“). Изтриване
При грешка GoTo 0
Изтриване на лист (без подкана)Application.DisplayAlerts = False
Листове („Лист1“). Изтриване
Application.DisplayAlerts = Вярно
Чист листЛистове ("Лист1"). Клетки. Изчистване
Само съдържанието на ясен листЛистове ("Sheet1"). Cells.ClearContents
Изчистен лист Използвана гамаЛистове ("Sheet1"). UsedRange.Clear
Защитете или премахнете защитата на листове
Премахване на защитата (без парола)Листове („Лист1“). Премахнете защитата
Премахване на защитата (парола)Листове („Лист1“). Премахнете защитата от „Парола“
Защита (без парола)Листове („Лист1“). Защитете
Защита (парола)Листове („Лист1“). Защитете „Парола“
Защитете, но разрешете VBA достъпЛистове („Лист1“). Защита на UserInterfaceOnly: = Вярно
Премахнете защитата на всички листовеЗатъмнете като работен лист
За всеки ws в работни листове
ws. Премахнете защитата на „паролата“
Следващата ws
wave wave wave wave wave