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