Работа с работни листове с помощта на Excel VBA (обяснено с примери)

Освен клетките и диапазоните, работата с работни листове е друга област, която трябва да знаете за ефективното използване на VBA в Excel.

Както всеки обект във VBA, работните листове имат различни свойства и методи, свързани с него, които можете да използвате, докато автоматизирате работата си с VBA в Excel.

В този урок ще разгледам подробно „Работни листове“ и ще ви покажа някои практически примери.

Така че нека започнем.

Всички кодове, които споменавам в този урок, трябва да бъдат поставени във VB Editor. Отидете в раздела „Къде да поставите VBA кода“, за да знаете как работи.

Ако се интересувате да научите VBA по лесния начин, вижте моя Онлайн обучение по Excel VBA.

Разлика между работни листове и листове във VBA

Във VBA имате две колекции, които понякога могат да бъдат малко объркващи.

В работна книга можете да имате работни листове, както и листове с диаграми. Примерът по -долу има три работни листа и един лист с диаграми.

В Excel VBA:

• Колекцията „Работни листове“ ще се отнася до колекцията от всички обекти на работния лист в работна книга. В горния пример колекцията Worksheets ще се състои от три работни листа.
• Колекцията „Листове“ ще се отнася за всички работни листове, както и за диаграми в работната книга. В горния пример той ще има четири елемента - 3 работни листа + 1 лист с диаграми.

Ако имате работна книга, която има само работни листове и без листове с диаграми, тогава колекцията „Работни листове“ и „Листове“ е една и съща.

Но когато имате един или повече листа с диаграми, колекцията „Листове“ ще бъде по -голяма от колекцията „Работни листове“

Листове = Работни листове + Графични листове

Сега с това разграничение препоръчвам да бъдете възможно най -конкретни, когато пишете VBA код.

Така че, ако трябва да се позовавате само на работни листове, използвайте колекцията „Работни листове“ и ако трябва да се позовавате на всички листове (включително листове с диаграми), използвайте колекцията „Листове“.

В този урок ще използвам само колекцията „Работни листове“.

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

Има много различни начини, по които можете да се обърнете към работен лист във VBA.

Разбирането как да се позовавате на работни листове би ви помогнало да напишете по -добър код, особено когато използвате цикли във вашия VBA код.

Използване на името на работния лист

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

Да предположим например, че имате работна книга с три работни листа - лист 1, лист 2, лист 3.

И искате да активирате лист 2.

Можете да направите това, като използвате следния код: Sub ActivateSheet () Работни листове ("Sheet2"). Активирайте End Sub

Горният код изисква VBA да се позове на Sheet2 в колекцията Worksheets и да го активира.

Тъй като използваме точното име на лист, тук можете да използвате и колекцията Sheets. Така че кодът по -долу също би направил същото.

Sub ActivateSheet () Sheets ("Sheet2"). Активирайте End Sub

Използване на индексния номер

Докато използването на името на листа е лесен начин да се обърнете към работен лист, понякога може да не знаете точното име на работния лист.

Например, ако използвате VBA код за добавяне на нов работен лист към работната книга и не знаете колко работни листа вече са там, няма да знаете името на новия работен лист.

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

Да предположим, че имате следните листове в работна книга:

Кодът по -долу ще активира Sheet2:

Sub ActivateSheet () Работни листове (2). Активирайте End Sub

Обърнете внимание, че сме използвали индекс номер 2 в Работни листове (2). Това ще се отнася до втория обект в колекцията от работни листове.

Какво се случва, когато използвате 3 като индексен номер?

Той ще избере Sheet3.

Ако се чудите защо е избрал Sheet3, тъй като очевидно е четвъртият обект.

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

Така че, когато използваме индексните номера в колекцията на работни листове, тя ще се отнася само за работните листове в работната книга (и ще игнорира листовете с диаграми).

Напротив, ако използвате Sheets, Sheets (1) ще се позовават на Sheets1, Sheets (2) ще се отнасят към Sheet2, Sheets (3) ще се позовават на Chart1 и Sheets (4) ще се отнасят към Sheet3.

Тази техника за използване на индексния номер е полезна, когато искате да прегледате всички работни листове в работна книга. Можете да преброите броя на работните листове и след това да ги прегледате, като използвате това броене (ще видим как да направите това по -късно в този урок).

Забележка: Индексният номер върви отляво надясно. Така че, ако изместите Sheet2 вляво от Sheet1, тогава работните листове (1) ще се отнасят до Sheet2.

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

Един от недостатъците на използването на името на листа (както видяхме в горния раздел) е, че потребителят може да го промени.

И ако името на листа е променено, вашият код няма да работи, докато не промените и името на работния лист във VBA кода.

За да се справите с този проблем, можете да използвате кодовото име на работния лист (вместо обикновеното име, което използвахме досега). Кодово име може да бъде присвоено в VB Editor и не се променя, когато промените името на листа от областта на работния лист.

За да дадете кодово име на работния си лист, следвайте стъпките по -долу:

1. Щракнете върху раздела Разработчик.
2. Щракнете върху бутона Visual Basic. Това ще отвори VB Editor.
3. Щракнете върху опцията Изглед в менюто и щракнете върху Прозорец на проекта. Това ще направи прозореца Свойства видим. Ако прозорецът Свойства вече е видим, пропуснете тази стъпка.
4. Щракнете върху името на листа в изследователя на проекта, който искате да преименувате.
5. В прозореца Свойства променете името в полето пред (Име). Обърнете внимание, че не можете да имате интервали в името.

Горните стъпки биха променили името на вашия работен лист във VBA бекенда. В изгледа на работен лист на Excel можете да дадете име на работния лист каквото искате, но в задната част той ще отговори и на двете имена - името на листа и кодовото име.

В горното изображение името на листа е „SheetName“, а кодовото име е „CodeName“. Дори ако промените името на листа в работния лист, кодовото име остава същото.

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

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

Работни листове („Име на лист“). Активирайте CodeName. Активирайте

Разликата в тези две е, че ако промените името на работния лист, първият няма да работи. Но вторият ред ще продължи да работи дори с промененото име. Вторият ред (използвайки CodeName) също е по -къс и по -лесен за използване.

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

Ако искате да се обърнете към работен лист в друга работна книга, тази работна книга трябва да бъде отворена, докато кодът работи, и трябва да посочите името на работната книга и работния лист, към който искате да се обърнете.

Например, ако имате работна книга с името Примери и искате да активирате Sheet1 в Примерната работна книга, трябва да използвате кода по -долу:

Sub SheetActivate () Работни книги ("Примери.xlsx"). Работни листове ("Sheet1"). Активирайте End Sub

Обърнете внимание, че ако работната книга е запазена, трябва да използвате името на файла заедно с разширението. Ако не сте сигурни какво име да използвате, потърсете помощ от Project Explorer.

В случай, че работната книга не е запазена, не е необходимо да използвате разширението на файла.

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

Кодът по -долу ще добави работен лист (като първия работен лист - т.е. като най -ляв лист в раздела на листа).

Sub AddSheet () Работни листове. Добавяне на End Sub

Той приема името по подразбиране Sheet2 (или друг номер въз основа на това колко листа вече има).

Ако искате да се добави работен лист преди конкретен работен лист (да речем Sheet2), тогава можете да използвате кода по -долу.

Sub AddSheet () Работни листове. Добавете преди: = Worksheets ("Sheet2") End Sub

Горният код казва на VBA да добави лист и след това използва израза „Преди“, за да посочи работния лист, преди който трябва да се вмъкне новият работен лист.

По същия начин можете също да добавите лист след работен лист (да речем Sheet2), като използвате кода по -долу:

Sub AddSheet () Worksheets.Add After: = Worksheets ("Sheet2") End Sub

Ако искате новият лист да бъде добавен в края на листовете, първо трябва да знаете колко листа има. Следният код първо отчита броя на листата и добавя новия лист след последния лист (към който се позоваваме, използвайки индексния номер).

Sub AddSheet () Dim SheetCount As Integer SheetCount = Работни листове. Брой работни листове. Добавяне след: = Работни листове (SheetCount) Краен под

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

Кодът по -долу ще изтрие активния лист от работната книга.

Sub DeleteSheet () ActiveSheet.Delete End Sub

Горният код ще покаже предупреждение, преди да изтриете работния лист.

Ако не искате да видите подканата за предупреждение, използвайте кода по -долу:

Sub DeleteSheet () Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Sub

Когато Application.DisplayAlerts е зададено на False, то няма да ви покаже подканата за предупреждение. Ако го използвате, не забравяйте да го върнете на True в края на кода.

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

Ако искате да изтриете определен лист, можете да направите това, като използвате следния код:

Sub DeleteSheet () Работни листове ("Sheet2"). Изтриване на End Sub

Можете също да използвате кодовото име на листа, за да го изтриете.

Sub DeleteSheet () Sheet 5. Изтрий End Sub

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

Можете да промените свойството name на работния лист, за да промените името му.

Следният код ще промени името на Sheet1 на „Summary“.

Sub RenameSheet () Работни листове ("Sheet1"). Name = "Summary" End Sub

Можете да комбинирате това с метода за добавяне на лист, за да имате набор от листове с конкретни имена.

Например, ако искате да вмъкнете четири листа с име2021-2022 Q1,2021-2022 Q2,2021-2022 Q3 и 2021-2022 Q4, можете да използвате кода по-долу.

Sub RenameSheet () Затъмнете броячи като цяло число End Sub

В горния код първо преброяваме броя листове и след това използваме цикъл For Next, за да вмъкнем нови листове в края. С добавянето на листа кодът също го преименува.

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

Когато работите с работни листове, можете да присвоите работен лист на променлива на обект и след това да използвате променливата вместо препратките към работния лист.

Например, ако искате да добавите префикс за година към всички работни листове, вместо да броите листовете и да изпълнявате цикъла толкова пъти, можете да използвате променливата на обекта.

Ето кода, който ще добави 2021-2022 като префикс към всички имена на работния лист.

Sub RenameSheet () Dim Ws като работен лист за всеки Ws в работни листове Ws.Name = "2018 -" & Ws.Name Следващ Ws End Sub

Горният код декларира променлива Ws като тип работен лист (използвайки реда „Dim Ws като работен лист“).

Сега не е нужно да броим броя на листовете, за да ги прегледаме. Вместо това можем да използваме цикъла „За всеки Ws в работни листове“. Това ще ни позволи да преминем през всички листове в колекцията от работни листове. Няма значение дали има 2 листа или 20 листа.

Докато горният код ни позволява да преминем през всички листове, можете също да присвоите определен лист към променлива.

В кода по -долу ние присвояваме променливата Ws на Sheet2 и я използваме за достъп до всички свойства на Sheet2.

Sub RenameSheet () Dim Ws As Set of Worksheet Ws = Работни листове ("Sheet2") Ws.Name = "Обобщение" Ws. Защита на края на Sub

След като зададете препратка към работен лист към променлива на обект (използвайки инструкцията SET), този обект може да се използва вместо препратката към работния лист. Това може да бъде полезно, когато имате дълъг сложен код и искате да промените препратката. Вместо да правите промяната навсякъде, можете просто да направите промяната в инструкцията SET.

Обърнете внимание, че кодът декларира обекта Ws като променлива тип работен лист (използвайки реда Dim Ws като работен лист).

Скриване на работни листове с помощта на VBA (скрито + много скрито)

Скриването и разкриването на работни листове в Excel е проста задача.

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

Но какво, ако не искате те да могат да разкриват работния лист (и).

Можете да направите това с помощта на VBA.

Кодът по-долу ще скрие всички работни листове в работната книга (с изключение на активния лист), така че не можете да го скриете, като щракнете с десния бутон върху името на листа.

Sub HideAllExcetActiveSheet () Dim Ws като работен лист за всеки Ws в ThisWorkbook.Worksheets If Ws.Name ActiveSheet.Name Тогава Ws.Visible = xlSheetVeryHidden Следващ Ws End Sub

В горния код свойството Ws.Visible се променя на xlSheetVeryHidden.

• Когато свойството Visible е зададено на xlSheetVisible, листът се вижда в областта на работния лист (като раздели на работния лист).
• Когато свойството Visible е зададено на xlSheetHidden, листът е скрит, но потребителят може да го скрие, като щракнете с десния бутон върху всеки раздел на листа.
• Когато свойството Visible е зададено на xlSheetVeryHidden, листът е скрит и не може да бъде скрит от областта на работния лист. Трябва да използвате VBA код или прозорец със свойства, за да го скриете.

Ако искате просто да скриете листове, които могат лесно да се скрият, използвайте кода по -долу:

Sub HideAllExceptActiveSheet () Dim Ws като работен лист за всеки Ws в ThisWorkbook.Worksheets If Ws.Name ActiveSheet.Name Тогава Ws.Visible = xlSheetHidden Next Ws End Sub

Кодът по -долу ще разкрие всички работни листове (скрити и много скрити).

Sub UnhideAllWoksheets () Dim Ws като работен лист за всеки Ws в ThisWorkbook.Worksheets Ws.Visible = xlSheetVisible Следващ Ws End Sub

Свързана статия: Покажете всички листове в Excel (наведнъж)

Скриване на листове въз основа на текста в него

Да предположим, че имате няколко листа с имената на различни отдели или години и искате да скриете всички листове с изключение на тези, в които има година 2021-2022.

Можете да направите това с помощта на VBA INSTR функция.

Кодът по-долу ще скрие всички листове с изключение на тези с текст 2021-2022 в него.

Sub HideWithMatchingText () Dim Ws като работен лист за всеки Ws в работни листове Ако InStr (1, Ws.Name, "2018", vbBinaryCompare) = 0 Тогава Ws.Visible = xlSheetHidden End Ако следващият Ws End Sub

В горния код функцията INSTR връща позицията на знака, където намира съответстващия низ. Ако не намери съответстващия низ, връща 0.

Горният код проверява дали името съдържа текст 2021-2022 в него. Ако това стане, нищо не се случва, иначе работният лист е скрит.

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

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

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

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

Sub SortSheetsTabName () Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount If Sheets (j) .Name < Листове (i). Име След това Листове (j). Преместване преди: = Листове (i) Край, ако следващ j Следващ i Приложение. ScreenUpdating = True End Sub

Имайте предвид, че този код работи добре с текстови имена и в повечето случаи с години и числа. Но може да ви даде грешни резултати, в случай че имената на листа са 1,2,11. Той ще сортира и ще ви даде последователността 1, 11, 2. Това е така, защото прави сравнението като текст и счита 2 по -големи от 11.

Защитете/премахнете защитата на всички листове наведнъж

Ако имате много работни листове в работна книга и искате да защитите всички листове, можете да използвате VBA кода по -долу.

Тя ви позволява да посочите паролата в кода. Тази парола ще ви е необходима, за да премахнете защитата на работния лист.

Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" 'заменете Test123 с паролата, която искате За всеки ws В работни листове ws.Protect парола: = парола Следваща ws Крайна под

Следният код ще премахне защитата на всички листове наведнъж.

Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" 'заменете Test123 с паролата, която сте използвали, като защитавате За всеки ws В работни листове ws.Unprotect парола: = парола Следваща ws End Sub

Създаване на съдържание на всички работни листове (с хипервръзки)

Ако имате набор от работни листове в работната книга и искате бързо да вмъкнете обобщен лист, който има връзки към всички листове, можете да използвате кода по -долу.

Sub AddIndexSheet () Worksheets.Add ActiveSheet.Name = "Index" For i = 2 To Worksheets.Count ActiveSheet.Hyperlinks.Add Anchor: = Cells (i - 1, 1), _ Address: = "", SubAddress: = Worksheets (i). Име & "! A1", _ TextToDisplay: = Работни листове (i). Име Следващ и Краен под

Горният код вмъква нов работен лист и го нарича Index.

След това той преминава през всички работни листове и създава хипервръзка за всички работни листове в индексния лист.

Къде да поставите VBA кода

Чудите се къде отива кодът VBA във вашата работна книга на Excel?

Excel има VBA бекенд, наречен VBA редактор. Трябва да копирате и поставите кода в кодовия прозорец на модула VB Editor.

Ето стъпките за това:

1. Отидете в раздела Разработчик.
2. Щракнете върху опцията Visual Basic. Това ще отвори VB редактора в задната част.
3. В прозореца Project Explorer в редактора на VB щракнете с десния бутон върху всеки обект за работната книга, в който искате да вмъкнете кода. Ако не виждате Project Explorer, отидете в раздела View и щракнете върху Project Explorer.
4. Отидете на Вмъкване и щракнете върху Модул. Това ще вмъкне обект на модул за вашата работна книга.
5. Копирайте и поставете кода в прозореца на модула.

Може да харесате и следните уроци по Excel VBA:

• Работа с работни книги с помощта на VBA.
• Използване на IF след това Други изявления във VBA.
• За следващия цикъл във VBA.
• Създаване на дефинирана от потребителя функция в Excel.
• Как да запишете макрос в Excel.
• Как да стартирате макрос в Excel.
• Събития на Excel VBA - Лесно (и пълно) ръководство.
• Как да създадете добавка в Excel.
• Как да запазвате и използвате повторно макроса с помощта на Excel Personal Macro Workbook.