Робота з аркушами за допомогою Excel VBA (пояснюється прикладами)

Окрім клітинок та діапазонів, робота з робочими аркушами - це ще одна область, яку ви повинні знати про ефективне використання VBA у Excel.

Як і будь -який об’єкт у VBA, аркуші мають різні властивості та методи, пов’язані з ним, які можна використовувати під час автоматизації роботи з VBA в Excel.

У цьому підручнику я детально розгляну «Робочі листи», а також покажу вам деякі практичні приклади.

Тож почнемо.

Усі коди, які я згадую у цьому підручнику, потрібно розмістити у редакторі VB. Перейдіть до розділу «Де розмістити код VBA», щоб дізнатися, як він працює.

Якщо ви зацікавлені у вивченні VBA простим способом, ознайомтеся з моїм Онлайн навчання Excel VBA.

Різниця між аркушами та аркушами у VBA

У VBA у вас є дві колекції, які часом можуть трохи заплутати.

У робочій книзі ви можете мати аркуші, а також аркуші діаграм. У наведеному нижче прикладі є три аркуші та один аркуш діаграми.

У Excel VBA:

• Колекція "Робочі аркуші" стосуватиметься колекції всіх об'єктів робочого аркуша у робочій книзі. У наведеному вище прикладі колекція Worksheets складатиметься з трьох аркушів.
• Колекція "Аркуші" стосуватиметься всіх аркушів, а також аркушів діаграми у робочій книзі. У наведеному вище прикладі він міститиме чотири елементи - 3 аркуші + 1 аркуш діаграми.

Якщо у вас є робоча книга, яка містить лише робочі аркуші, а не аркуші діаграми, то колекції «Робочі аркуші» та «Таблиці» однакові.

Але якщо у вас є один або кілька аркушів діаграми, колекція "Аркуші" буде більшою за колекцію "Робочі аркуші"

Аркуші = Робочі аркуші + Аркуші діаграм

З цією відмінністю я рекомендую бути максимально конкретним при написанні коду VBA.

Тому, якщо вам потрібно звертатися лише до робочих листів, використовуйте колекцію «Робочі листи», а якщо ви маєте посилатися на всі аркуші (включаючи аркуші діаграми), використовуйте колекцію «Таблиці».

У цьому підручнику я буду використовувати лише колекцію «Робочі листи».

Посилання на аркуш у VBA

Існує багато різних способів звернення до робочого аркуша у VBA.

Розуміння того, як звертатися до робочих листів, допоможе вам написати кращий код, особливо коли ви використовуєте цикли у своєму коді VBA.

Використання назви робочого аркуша

Найпростіший спосіб звернутися до аркуша - це використати його назву.

Наприклад, припустимо, що у вас є робочий зошит з трьома аркушами - аркуш 1, аркуш 2, аркуш 3.

І ви хочете активувати Аркуш 2.

Ви можете зробити це за допомогою такого коду: Sub ActivateSheet () Worksheets ("Sheet2"). Активувати End Sub

Наведений вище код пропонує VBA звернутися до Sheet2 у колекції Worksheets та активувати його.

Оскільки ми використовуємо точну назву аркуша, тут також можна скористатися колекцією «Таблиці». Отже, код нижче також буде робити те саме.

Sub ActivateSheet () Sheets ("Sheet2"). Активувати End Sub

Використання індексного номера

Хоча використання назви аркуша - це простий спосіб звернутися до аркуша, іноді ви можете не знати точної назви аркуша.

Наприклад, якщо ви використовуєте код VBA для додавання нового аркуша до робочої книги, і ви не знаєте, скільки аркушів вже є, ви б не знали назву нового аркуша.

У цьому випадку можна використовувати номер індексу аркушів.

Припустимо, у вас є такі аркуші в робочій книзі:

Код нижче активує Sheet2:

Sub ActivateSheet () Робочі аркуші (2). Активувати End Sub

Зауважте, що ми використовували індекс номер 2 в Робочі листи (2). Це стосуватиметься другого об’єкта в колекції аркушів.

Що відбувається, коли ви використовуєте 3 як індексний номер?

Він вибере аркуш 3.

Якщо вам цікаво, чому він вибрав Sheet3, оскільки це явно четвертий об’єкт.

Це відбувається тому, що аркуш діаграми не є частиною колекції аркушів.

Тому, коли ми використовуємо індексні номери у колекції робочих аркушів, він буде посилатися лише на аркуші робочої книги (і ігнорувати аркуші діаграм).

Навпаки, якщо ви використовуєте Аркуші, Аркуші (1) посилатимуться на Аркуші1, Аркуші (2) - на Аркуш2, Аркуші (3) - на Діаграму1, а Таблиці (4) - на Аркуш3.

Цей прийом використання індексного номера корисний, коли ви хочете переглянути всі робочі аркуші у книзі. Ви можете порахувати кількість робочих аркушів, а потім прокрутити їх за допомогою цього підрахунку (ми побачимо, як це зробити пізніше в цьому підручнику).

Примітка: Номер індексу йде зліва направо. Отже, якщо ви змістите аркуш 2 ліворуч від аркуша1, то робочі аркуші (1) будуть посилатися на аркуш2.

Використання кодової назви робочого аркуша

Одним з недоліків використання назви аркуша (як ми бачили у розділі вище) є те, що користувач може змінити його.

І якщо ім’я аркуша було змінено, ваш код не працюватиме, доки ви також не зміните ім’я аркуша у коді VBA.

Щоб вирішити цю проблему, ви можете використовувати кодове ім'я аркуша (замість звичайного імені, яке ми використовували досі). Кодове ім’я можна призначити в редакторі VB і не змінюється, коли ви змінюєте назву аркуша з області робочого аркуша.

Щоб надати своєму аркушу кодову назву, виконайте наведені нижче дії.

1. Перейдіть на вкладку Розробник.
2. Натисніть кнопку Visual Basic. Відкриється редактор VB.
3. Натисніть опцію Перегляд у меню та натисніть Вікно проекту. Це зробить панель властивостей видимою. Якщо область Властивості вже видно, пропустіть цей крок.
4. Натисніть на назву аркуша в досліднику проекту, який потрібно перейменувати.
5. На панелі властивостей змініть ім’я в полі перед (Ім'я). Зверніть увагу, що у назві не може бути пробілів.

Наведені вище кроки змінять назву вашого аркуша у бекенді VBA. У поданні робочого аркуша Excel можна назвати аркуш як завгодно, але в бекенді він буде відповідати на обидві назви - ім’я аркуша та кодове ім’я.

На зображенні вище назва аркуша - "SheetName", а кодове ім'я - "CodeName". Навіть якщо ви зміните назву аркуша на аркуші, кодове ім’я залишиться незмінним.

Тепер ви можете скористатися або колекцією Worksheets для посилання на аркуш, або скористатися кодовою назвою.

Наприклад, обидва рядки активують аркуш.

Робочі аркуші ("Назва аркуша"). Активуйте CodeName.Activate

Різниця в цих двох полягає в тому, що якщо змінити назву робочого аркуша, перший не працюватиме. Але другий рядок продовжує працювати навіть зі зміненою назвою. Другий рядок (з використанням CodeName) також коротший і простіший у використанні.

Посилання на аркуш в іншому робочому зошиті

Якщо ви хочете звернутись до робочого аркуша в іншій книзі, ця книга має бути відкритою під час виконання коду, і вам потрібно вказати ім’я робочої книги та аркуш, на який ви хочете посилатися.

Наприклад, якщо у вас є книга з назвою Приклади і ви хочете активувати Sheet1 у книзі Приклад, вам потрібно скористатися наведеним нижче кодом:

Sub SheetActivate () Робочі книги ("Приклади.xlsx"). Робочі аркуші ("Sheet1"). Активувати End Sub

Зауважте, що якщо книгу збережено, вам потрібно використовувати назву файлу разом із розширенням. Якщо ви не впевнені, яку назву використовувати, зверніться по допомогу до Провідника проектів.

Якщо книгу не збережено, не потрібно використовувати розширення файлу.

Додавання аркуша

Наведений нижче код додасть аркуш (як перший аркуш - тобто як крайній лівий аркуш на вкладці аркуша).

Додаткові аркуші AddSheet (). Додати кінець Sub

Він приймає назву за замовчуванням Sheet2 (або будь -який інший номер залежно від того, скільки аркушів вже є).

Якщо ви хочете, щоб аркуш був доданий перед конкретним аркушем (скажімо, Sheet2), то ви можете скористатися наведеним нижче кодом.

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

Наведений вище код повідомляє VBA додати аркуш, а потім використовує оператор "Перед", щоб вказати аркуш, перед яким слід вставити новий аркуш.

Аналогічно, ви також можете додати аркуш після робочого аркуша (скажімо, Sheet2), використовуючи код нижче:

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

Якщо ви хочете, щоб новий аркуш був доданий до кінця аркушів, вам потрібно спочатку знати, скільки там аркушів. Наступний код спочатку підраховує кількість аркушів і додає новий аркуш після останнього аркуша (на який ми посилаємося за допомогою номера індексу).

Sub AddSheet () Dim SheetCount As Integer SheetCount = Робочі аркуші. Підрахувати робочі аркуші. Додати після: = Робочі аркуші (SheetCount) Закінчити підряд

Видалення робочого аркуша

Наведений нижче код видалить активний аркуш із книги.

Sub Видалити таблицю () ActiveSheet.Delete End Sub

Наведений вище код покаже попередження перед видаленням аркуша.

Якщо ви не хочете бачити попередження, скористайтеся наведеним нижче кодом:

Sub DeleteSheet () Application.DisplayAlerts = Помилковий ActiveSheet.Delete Application.DisplayAlerts = True End Sub

Якщо для Application.DisplayAlerts встановлено значення False, воно не відображатиме запит попередження. Якщо ви його використовуєте, не забудьте встановити значення True в кінці коду.

Пам’ятайте, що ви не можете скасувати це видалення, тому скористайтеся наведеним вище кодом, коли будете абсолютно впевнені.

Якщо ви хочете видалити певний аркуш, ви можете зробити це за допомогою такого коду:

Sub DeleteSheet () Робочі аркуші ("Sheet2"). Видалити End Sub

Ви також можете використовувати кодову назву аркуша, щоб видалити його.

Sub Видалити аркуш () Аркуш 5. Видалити кінець 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 () Розмити таблиці як цілі лічильники = Worksheets.Count For i = 1 - 4 Worksheets.Додати після: = Worksheets (Countsheets + i - 1) Worksheets (Countsheets + i) .Name = "2018 Q" & i Next i End Sub

У наведеному вище коді ми спочатку підраховуємо кількість аркушів, а потім використовуємо цикл For Next, щоб вставити нові аркуші в кінці. Коли аркуш додається, код також перейменовує його.

Призначення змінної об’єкта робочого аркуша

Під час роботи з аркушами можна призначити аркуш змінній об’єкта, а потім використовувати змінну замість посилань на аркуш.

Наприклад, якщо ви хочете додати префікс року до всіх аркушів, замість того, щоб підраховувати аркуші та запускати цикл стільки разів, ви можете використовувати змінну об’єкта.

Ось код, який додасть 2021-2022 як префікс до всіх назв робочого аркуша.

Sub RenameSheet () Dim Ws як робочий аркуш для кожного Ws у робочих аркушах Ws.Name = "2018 -" & Ws.Name Наступний Ws Кінцевий суб

Наведений вище код оголошує змінну Ws типом робочого аркуша (використовуючи рядок «Dim Ws As Worksheet»).

Тепер нам не потрібно підраховувати кількість аркушів, щоб їх прокрутити. Замість цього ми можемо використовувати цикл "Для кожного Ws у робочих листах". Це дозволить нам переглянути всі аркуші у колекції аркушів. Не має значення, чи є 2 аркуші чи 20 аркушів.

Хоча вищезгаданий код дозволяє нам перебирати всі аркуші, ви також можете призначити змінному певний аркуш.

У наведеному нижче коді ми призначаємо змінну Ws Sheet2 і використовуємо її для доступу до всіх властивостей Sheet2.

Sub RenameSheet () Dim Ws As Worksheet Set Ws = Worksheets ("Sheet2") Ws.Name = "Summary" Ws.Захистити кінець

Після того, як ви встановите посилання на аркуш для змінної об’єкта (за допомогою оператора SET), цей об’єкт можна буде використовувати замість посилання на аркуш. Це може бути корисним, якщо у вас довгий складний код і ви хочете змінити посилання. Замість того, щоб вносити зміни всюди, ви можете просто внести зміни в оператор SET.

Зауважте, що код оголошує об’єкт Ws як змінну типу аркуша (використовуючи рядок Dim Ws як робочий аркуш).

Приховати робочі листи за допомогою VBA (приховані + дуже приховані)

Приховати та приховати робочі аркуші в Excel - це просте завдання.

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

Але що робити, якщо ви не хочете, щоб вони могли відображати робочі аркуші.

Це можна зробити за допомогою VBA.

Нижче наведений код приховає всі робочі аркуші у книзі (крім активного аркуша), так що ви не зможете приховати його, клацнувши правою кнопкою миші на назві аркуша.

Sub HideAllExcetActiveSheet () Dim Ws як робочий аркуш для кожного WS у цій робочій книзі.

У наведеному вище коді властивість Ws.Visible змінюється на xlSheetVeryHidden.

• Якщо для властивості Visible встановлено значення xlSheetVisible, аркуш буде видно в області робочого аркуша (як вкладки аркуша).
• Якщо для властивості Visible встановлено значення xlSheetHidden, аркуш прихований, але користувач може показати його, клацнувши правою кнопкою миші на будь-якій вкладці аркуша.
• Якщо для властивості Visible встановлено значення xlSheetVeryHidden, аркуш прихований і його неможливо приховати з області робочого аркуша. Вам потрібно використовувати код VBA або вікно властивостей, щоб показати його.

Якщо ви хочете просто приховати аркуші, які можна легко приховати, скористайтеся наведеним нижче кодом:

Sub HideAllExceptActiveSheet () Dim Ws як робочий аркуш для кожного WS у цій робочій книзі.

Наведений нижче код відкриває всі робочі аркуші (як приховані, так і дуже приховані).

Sub UnhideAllWoksheets () Dim Ws як робочий аркуш для кожного WS у цій робочій книзі.

Пов'язана стаття: Показати всі аркуші в Excel (за один раз)

Сховати аркуші на основі тексту в ньому

Припустимо, у вас є кілька аркушів з назвами різних відділів або років, і ви хочете приховати всі аркуші, крім тих, у яких є рік 2021-2022.

Це можна зробити за допомогою функції VBA INSTR.

Код, наведений нижче, приховає всі аркуші, крім тих, у яких є текст 2021-2022.

Sub HideWithMatchingText () Dim Ws як робочий аркуш для кожного Ws у робочих листах If 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.Захист пароля: = пароль Наступний ws Завершити Sub

Наступний код зніме захист усіх аркушів за один раз.

Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" 'Замініть Test123 на пароль, який ви використовували під час захисту Для кожного ws у робочих листах

Створення змісту всіх робочих аркушів (з гіперпосиланнями)

Якщо у вас є набір робочих листів у книзі, і ви хочете швидко вставити підсумковий аркуш із посиланнями на всі аркуші, ви можете скористатися наведеним нижче кодом.

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) .Name & "! A1", _ TextToDisplay: = Робочі аркуші (i). Назва Next i End Sub

Наведений вище код вставляє новий аркуш і називає його Index.

Потім він перебирає всі аркуші та створює гіперпосилання для всіх аркушів на аркуші індексу.

Де розмістити код VBA

Цікаво, де код VBA міститься у вашій книзі Excel?

Excel має бекенд VBA, який називається редактором VBA. Вам потрібно скопіювати та вставити код у вікно коду модуля VB Editor.

Нижче наведено кроки для цього.

1. Перейдіть на вкладку Розробник.
2. Натисніть на опцію Visual Basic. Це відкриє редактор VB у бекенді.
3. На панелі «Провідник проектів» у редакторі VB клацніть правою кнопкою миші будь-який об’єкт книги, до якого потрібно вставити код. Якщо ви не бачите Провідник проектів, перейдіть на вкладку Вигляд і натисніть Провідник проектів.
4. Перейдіть до пункту Вставити та натисніть Модуль. Це додасть об’єкт модуля до вашої книги.
5. Скопіюйте та вставте код у вікно модуля.

Вам також можуть сподобатися такі підручники Excel VBA:

• Робота з робочими книгами за допомогою VBA.
• Використання IF Тоді Інші заяви у VBA.
• Для наступного циклу у VBA.
• Створення користувальницької функції в Excel.
• Як записати макрос в Excel.
• Як запустити макрос в Excel.
• Події Excel VBA - простий (і повний) посібник.
• Як створити надбудову в Excel.
• Як зберегти та повторно використовувати макрос за допомогою особистої книги макросів Excel.