Це найкращий посібник з роботи з аркушами / аркушами в Excel.
Внизу цього посібника ми створили шпаргалку загальних команд для роботи з аркушами.
Аркуші проти Робочі листи
Існує два способи посилання на Аркуші за допомогою VBA. По -перше, з об'єктом "Таблиці":
| 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 | Затемнити ws як робочий аркуш |
Призначте аркуш змінній:
| 1 | Встановити ws = Аркуші ("Аркуш1") |
Тепер ви можете посилатися на змінну аркуша у своєму коді:
| 1 | ws.Activate |
Перегляньте всі аркуші робочого зошита
Змінні робочого аркуша мають істотне значення, якщо ви хочете прокрутити всі аркуші у книзі. Найпростіший спосіб це зробити:
| 12345 | Затемнити як робочий аркушДля кожного ws у робочих аркушахMsgBox ws.nameНаступний ws |
Цей код прокручуватиме всі робочі аркуші у книзі, відображаючи ім’я кожного аркуша у вікні повідомлення. Перегляд усіх аркушів у книзі дуже корисний при блокуванні / розблокуванні або приховуванні / приховуванні кількох аркушів одночасно.
Захист робочого аркуша
Захист робочої книжки
Захист робочої книги блокує книгу від структурних змін, таких як додавання, видалення, переміщення або приховування аркушів.
Ви можете ввімкнути захист книг за допомогою VBA:
| 1 | ActiveWorkbook.Protect Password: = "Пароль" |
або вимкніть захист книги:
| 1 | ActiveWorkbook.UnProtect Password: = "Пароль" |
Примітка: Ви також можете захистити / зняти захист без пароля, опустивши аргумент Пароль:
| 1 | ActiveWorkbook.Protect |
Захист робочого аркуша
Захист на рівні аркуша запобігає змінам окремих аркушів.
Захистити аркуш
| 1 | Робочі аркуші ("Аркуш1"). Захистіть "Пароль" |
Зняти захист робочого аркуша
| 1 | Робочі аркуші ("Аркуш1"). Зняти захист "Паролю" |
Існує безліч варіантів захисту робочих аркушів (дозволяють зміни форматування, дозволяють користувачеві вставляти рядки тощо). Для запису бажаних налаштувань ми рекомендуємо використовувати Макрореєстратор.
Тут ми детальніше обговорюємо захист аркуша.
Робочий лист Видима властивість
Можливо, ви вже знаєте, що робочі листи можна приховати:
Насправді існує три налаштування видимості аркуша: Видимий, Прихований та Дуже прихований.Будь -який звичайний користувач Excel може приховати приховані аркуші, клацнувши правою кнопкою миші в області вкладки робочого аркуша (показано вище). Дуже приховані аркуші можна приховати лише за допомогою коду VBA або з редактора VBA. Використовуйте такі приклади коду, щоб приховати / показати робочі аркуші:
Показати робочий аркуш
| 1 | Робочі аркуші ("Аркуш1"). Видимий = xlSheetVisible |
Сховати робочий аркуш
| 1 | Робочі аркуші ("Аркуш1"). Visible = xlSheetHidden |
Дуже приховати аркуш
| 1 | Робочі аркуші ("Аркуш1"). Видимий = xlSheetVeryHidden |
Події на рівні аркуша
Події - це тригери, які можуть викликати запуск “Процедур подій”. Наприклад, ви можете викликати запуск коду щоразу, коли змінюється будь -яка клітинка на аркуші або коли активується робочий аркуш.
Процедури події робочого аркуша повинні бути розміщені в модулі робочого аркуша:
Існує чимало подій на робочому аркуші. Щоб побачити повний список, перейдіть до модуля робочого аркуша, виберіть «Робочий лист» у першому спадному меню. Потім виберіть процедуру події з другого розкривного меню, щоб вставити її в модуль.
Робочий лист Активувати подію
Події активації робочого аркуша запускаються кожного разу, коли відкривається аркуш.
| 123 | Приватний робочий аркуш_активувати ()Діапазон ("A1"). ВиберітьEnd Sub |
Цей код буде вибирати клітинку A1 (скидання області перегляду у верхній лівий кут аркуша) кожного разу, коли відкривається робочий аркуш.
Подія зміни робочого аркуша
Події зміни робочого аркуша запускаються щоразу, коли змінюється значення комірки на аркуші. Прочитайте наш підручник про події зміни аркуша для отримання додаткової інформації.
Шпаргалка для робочого аркуша
Нижче ви знайдете шпаргалку, що містить загальні приклади коду для роботи з аркушами у VBA
Таблиця робочих аркушів VBA
Таблиці робочих аркушів VBA| Опис | Приклад коду |
|---|---|
| Посилання та активація аркушів | |
| Назва вкладки | Аркуші ("Введення"). Активуйте |
| Кодове ім'я VBA | Аркуш 1. Активуйте |
| Положення індексу | Аркуші (1). Активуйте |
| Виберіть Аркуш | |
| Виберіть Аркуш | Аркуші ("Введення"). Виберіть |
| Установіть значення Змінна | Затемнити як робочий аркуш Встановіть ws = ActiveSheet |
| Ім'я / Перейменувати | ActiveSheet.Name = "Нове ім'я" |
| Наступний аркуш | ActiveSheet.Next.Activate |
| Перегляньте всі аркуші | Затемнити як робочий аркуш Для кожного 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")). Name = "NewSheet" |
| Додайте аркуш до і назву | Sheets.Add (Before: = Sheets ("Input")). Name = "NewSheet" |
| Додати аркуш до кінця робочої книги | Таблиці. Додати після: = Аркуші (Таблиць. Кількість) |
| Додати аркуш до початку книги | Sheets.Add (Before: = Sheets (1)). Name = "FirstSheet" |
| Додати аркуш до змінної | Затемнити як робочий аркуш Встановити ws = Таблиці. Додати |
| Копіювати робочі аркуші | |
| Перемістити аркуш до кінця робочої книги | Аркуші ("Аркуш1"). Перемістити після: = Аркуші (Таблиці.Кількість) |
| До нового робочого зошита | Аркуші ("Аркуш1"). Копіювати |
| Вибрані аркуші до нової книги | ActiveWindow.SelectedSheets.Copy |
| Перед іншим аркушем | Аркуші ("Аркуш1"). Скопіювати до: = Аркуші ("Аркуш2") |
| Перед першим аркушем | Аркуші ("Аркуш1"). Скопіювати до: = Аркуші (1) |
| Після останнього аркуша | Аркуші ("Аркуш1"). Скопіювати після: = Аркуші (Таблиць. Кількість) |
| Копія та ім’я | Аркуші ("Аркуш1"). Скопіювати після: = Аркуші (Таблиць. Кількість) ActiveSheet.Name = "Остання таблиця" |
| Копіювати та назвати зі значення клітинки | Аркуші ("Аркуш1"). Скопіювати після: = Аркуші (Таблиць. Кількість) ActiveSheet.Name = Діапазон ("A1"). Значення |
| До іншого робочого зошита | Аркуші ("Аркуш1"). Копіювати до: = Робочі зошити ("Приклад.xlsm"). Аркуші (1) |
| Сховати / показати аркуші | |
| Сховати аркуш | Аркуші ("Аркуш1"). Видимий = Неправда або Аркуші ("Аркуш1"). Visible = xlSheetHidden |
| Показати аркуш | Аркуші ("Аркуш1"). Видимий = Правда або Аркуші ("Аркуш1"). Видимий = xlSheetVisible |
| Дуже приховати аркуш | Аркуші ("Аркуш1"). Видимий = xlSheetVeryHidden |
| Видалити або очистити аркуші | |
| Видалити аркуш | Аркуші ("Аркуш1"). Видалити |
| Видалити аркуш (обробка помилок) | Увімкнути Помилка Відновити Далі Аркуші ("Аркуш1"). Видалити Помилка Перейти до 0 |
| Видалити аркуш (без запиту) | Application.DisplayAlerts = Неправда Аркуші ("Аркуш1"). Видалити Application.DisplayAlerts = Правда |
| Очистити аркуш | Аркуші ("Аркуш1"). Клітинки. Очистити |
| Лише зміст чистого аркуша | Аркуші ("Аркуш1"). Осередки.ЧистотаВміст |
| Очистити аркуш Використовуваний діапазон | Аркуші ("Лист1"). UsedRange.Clear |
| Захист або зняття захисту аркушів | |
| Зняти захист (без пароля) | Аркуші ("Аркуш1"). Зняти захист |
| Зняти захист (пароль) | Аркуші ("Аркуш1"). Зняти захист "Пароль" |
| Захист (без пароля) | Аркуші ("Аркуш1"). Захист |
| Захистити (пароль) | Аркуші ("Аркуш1"). Захистіть "Пароль" |
| Захистіть, але дозвольте доступ VBA | Аркуші ("Аркуш1"). Захистіть UserInterfaceOnly: = Істина |
| Зняти захист з усіх аркушів | Затемнити як робочий аркуш Для кожного ws у робочих аркушах ws.Зняти захист "пароля" Наступний ws |
