1. Створити книгу Excel
2. Додати до трьох аркушів дані,
наведені на малюнках
3. Створити макрос для перейменовування
аркушів, надаючи їм імена, наведені в комірках B1 відповідних аркушів.
Передбачте можливість того, що комірка B1 відповідного аркуша буде поржньою. У
цьому випадку Перейменовувати аркуш не потрібно
Теоретичні відомості
Теоретичні відомості
Навіщо може знадобитися VBA в
Excel 2010?
Microsoft Excel 2010 - це дуже
потужний інструмент, який можна використовувати для управління даними, їх
аналізу і представлення. Але іноді, незважаючи на багатий набір можливостей
стандартного інтерфейсу користувача Excel, може знадобитися знайти нескладний
спосіб виконувати повсякденні повторювані завдання або якісь завдання, які не
вдається вирішити за допомогою інтерфейсу користувача. На щастя в додатках
Office, таких як Excel, інтегрований Visual Basic для додатків (VBA), мова
програмування, що дозволяє розширювати можливості цих додатків.
Сприймання нового матеріалу та його усвідомлення
VBA працює, виконуючи макроси, покрокові
процедури, написані на мові Visual Basic. Навчання програмуванню може здаватися
страшним, але при певному терпінні і за допомогою декількох прикладів, багато
користувача виявлять, що оволодіння навіть невеликими навичками програмування
на VBA надає їм можливість виконувати в Office дії, які раніше представлялися
їм неможливими. Освоєння одних функцій VBA спрощує освоєння наступних - тому
надавані можливості необмежені.
Загальновизнано, що
найпоширенішою причиною використання VBA в Excel є автоматизація повторюваних
завдань. Наприклад, нехай є десяток книг, кожна з яких містить десяток листів,
у кожний з яких потрібно внести певні зміни. Ці зміни можуть бути простими,
такими як застосування нового форматування до деякого фіксованого діапазону
комірок, або складними, такими як перегляд певних статистичних даних для
кожного аркуша, вибір оптимальної діаграми для представлення даних з цими
характеристиками, і подальше створення і форматування потрібної діаграми.
У будь-якому випадку
користувачеві, можливо, не доведеться виконувати ці дії вручну, у меншій мірі,
не більше декількох разів. Замість цього можна автоматизувати завдання за
допомогою VBA, щоб записати явні інструкції, які повинні бути виконані додатком
Excel.
Але VBA призначений не тільки для
повторюваних завдань. VBA також можна використовувати для створення нових
можливостей в Excel (наприклад, можна розробити нові алгоритми аналізу даних, а
потім скористатися можливостями побудови діаграм в Excel для відображення
результатів) і для виконання завдань, що інтегрують Excel з іншими додатками
Office, такими як Microsoft Access 2010. Фактично, з усіх програм Office
програма Excel є однією з найчастіше використовувана для створення чогось,
схожого на загальну платформу розробки. Крім всіх очевидних завдань, що
стосуються списків і бухгалтерії, розробники використовують Excel в ряді
завдань, від візуалізації до створення прототипів програм.
Незважаючи на всі переваги
використання VBA в Excel 2010, важливо пам'ятати, що найкраще вирішення
проблеми може зовсім не затрогувати VBA. В Excel і без VBA вбудовано безліч
функцій, тому навіть досвідчені користувачі не завжди знайомі з усіма
можливостями програми. Перед зверненням до VBA-рішенням ретельно перегляньте
довідку та інтерактивні ресурси, щоб переконатися у відсутності більш простого
способу.
Основи програмування на VBA
Використання коду для
виконання операцій додатками
Може здатися, що написання коду -
складний або загадковий процес, але його базові принципи ґрунтуються на
застосуванні повсякденному логіки і цілком доступні. Додатки Office 2010
створені так, щоб надавати суті, звані об'єктами, які можуть приймати
інструкції. Користувач може взаємодіяти з додатками, відправляючи інструкції
різним об'єктам додатка. Ці об'єкти є численними, різноманітними і гнучкими,
але у них є свої обмеження. Вони можуть робити тільки те, для чого були
розроблені, і виконують тільки написані для них інструкції.
Об'єкти
Програмовані об'єкти пов'язані
один з одним в ієрархію, звану об'єктною
моделлю додатка. Грубо кажучи, об'єктна модель відображає те, що
показується в інтерфейсі користувача, наприклад, об'єктна модель Excel містить,
серед багатьох інших, об'єкти Application, Workbook, Sheet та Chart. Об'єктна
модель є загальною картою додатка і його можливостей.
Властивості і методи
Управляти об'єктами можна,
задаючи їх властивості і викликаючи їх методи. Завдання властивості змінює
деякий якість об'єкта. Виклик методу змушує об'єкт виконати деяку дію.
Наприклад, у об'єкта Workbook є метод Close, що закриває книгу, і властивість
ActiveSheet, що представляє лист, активний в даний момент в книзі.
Колекції
Багато об'єктів поставляються у
версіях однини і множини - Workbook і Workbooks, Worksheet і Worksheets і т. д.
Версії множини називаються колекціями. Об'єкти колекції використовуються для
виконання дії над декількома об'єктами колекції.
Макроси та редактор
Visual Basic
Тепер, познайомившись з наданням
об'єктної моделі додатка Microsoft Excel 2010, можна спробувати викликати
методи об'єкта і задати його властивості. Для цього необхідно написати свій код
таким чином, щоб він розпізнавався в Office. Зазвичай це робиться за допомогою
редактора Visual Basic. Незважаючи на те, що він встановлений за замовчуванням,
багато користувачів не знають про його наявність, поки цей редактор не буде
включений його на стрічці.
Вкладка
"Розробник"
Всі додатки Office 2010
використовують стрічку. Однією з вкладок на стрічці є вкладка Розробник, на
якій можна викликати редактор Visual Basic і інші інструменти розробника.
Оскільки в Office 2010 вкладка Розробник не показана за замовчуванням,
необхідно вивести її на екран, виконавши наступну процедуру.
Включення вкладки
"Розробник"
·
На вкладці Файл виберіть Параметри, щоб відкрити діалогове вікно Параметри
Excel.
·
Клацніть Настроювання стрічки у лівій частині діалогового вікна.
·
У розділі Вибрати команди з, розташованому зліва у вікні, виберіть
Популярні команди.
·
У розділі Настройка стрічки, що знаходиться праворуч в діалоговому вікні,
виберіть Основні вкладки, а потім встановіть прапорець Розробник.
·
Натисніть кнопку ОК.
Коли вкладка Розробник з'явиться
в інтерфейсі Excel, зверніть увагу на місцезнаходження на вкладці кнопок Visual
Basic, Макрос і Безпека макросів.
Проблеми безпеки
Натисніть кнопку Безпека
макросів, щоб визначити, які макроси можуть виконуватися і за яких умов. Хоча
неконтрольований код макросу може серйозно пошкодити комп'ютер, умови безпеки,
що забороняють виконувати корисні макроси, можуть серйозно погіршити
продуктивність роботи. Безпека макросів - це складний і важливе питання, в якому
слід розібратися при роботі з макросами Excel.
Для цілей цієї статті пам'ятайте,
що якщо при відкритті книги, яка містить макрос, між стрічкою і аркушем
з'являється рядок Попередження безпеки: запуск макросів відключений, можна
натиснути кнопку Включити вміст, щоб включити макрос.
Крім того, в якості заходів
безпеки, не можна зберегти макрос у форматі файлів Excel, використовуваному за
умовчанням (XLSX-файли), замість цього макрос повинен бути збережений в файл зі
спеціальним розширенням, XLSM-файл.
Редактор Visual Basic
Наступна процедура показує, як
створити нову порожню книгу, в якій будуть зберігатися макроси. Потім можна
зберегти цю книгу у форматі XLSM-файлу.
Створення нової порожньої книги
·
Натисніть кнопку Макрос на вкладці "Розробник"
·
У діалоговому вікні, Макрос введіть Hello в полі Ім'я макросу.
·
Натисніть кнопку Створити, щоб відкрити редактор Visual Basic з вже
введеної структурою нового макросу.
VBA - це повнофункціональна мова
програмування з відповідною повнофункціональної середовищем програмування.
Редактор Visual Basic міститиме
наступний код.
Sub Hello()
End Sub
Sub - це скорочення від Subroutine (підпрограма), яку в
даному випадку можна визначити як "макрос". Виклик макроса Hello
призведе до виконання будь-якого коду між рядками Sub Hello () і End Sub.
Тепер змінити макрос, щоб він був схожий на наступний
код.
Sub
Hello()
MsgBox
("Hello, world!")
End Sub
Поверніться на вкладку Розробник в Excel, а потім знову
натисніть кнопку Макрос.
Вибрати макрос Hello в з'являється списку, а потім натисніть
кнопку Виконати, щоб вивести невелике вікно повідомлення, що містить текст
"Hello, world!".
Тільки що був створений і запущений користувальницький
код VBA в Excel. Натисніть кнопку ОК у вікні повідомлення, щоб закрити його і
закінчити виконання макросу.
Якщо вікно повідомлення не з'являється, перевірте
налаштування безпеки макросів та перезапустіть Excel.
Надання доступу до макросів
Відкрити діалогове вікно Макрос також можна зі вкладки
Вигляд, але при частому використанні макросу може виявитися більш зручним
викликати його за допомогою поєднання клавіш або кнопки Панель
швидкого доступу.
Щоб створити кнопку для макросу Hello на Панелі швидкого
доступу, використовуйте наступну процедуру.
Наступна процедура описує, як створити кнопку для макросу
на панелі швидкого доступу.
Створення кнопки для макросу на панелі швидкого доступу
·
Перейдіть на вкладку Файл.
·
Натисніть кнопку Параметри, щоб відкрити діалогове вікно Параметри Excel, а
потім клацніть Панель швидкого доступу.
·
В списку під написом Вибрати команди з: виберіть Макрос. Знайдіть текст,
схожий на Книга1! Hello, в що з'являється списку і виберіть цей текст.
·
Натисніть кнопку Додати >>, щоб додати макрос в список з правого
боку, а потім натисніть кнопку Змінити ..., щоб вибрати зображення кнопки, яку
пов'язують з макросом.
·
Натисніть кнопку ОК. Нова кнопка повинна з'явитися на Панелі швидкого
доступу над вкладкою Файл.
Тепер можна швидко запустити макрос в будь-який момент,
не звертаючись до вкладці Розробник - спробуйте.
Практичний приклад
Припустимо, що є книга, що містить списки на безлічі
листів, і потрібно змінити ім'я кожного аркуша у відповідності із заголовком
списку на цьому аркуші. Не кожен аркуш містить список, але якщо список є, його
заголовок знаходиться в комірці B1, а якщо списку немає, осередок B1
виявляється порожньою. Імена аркушів без списків не повинні змінюватися.
Зазвичай це могло бути складним завданням, що складається
з перегляду кожного аркуша, перевірки наявності списку, копіювання його імені,
якщо воно є, клацання вкладки аркуша і вставки нового імені. Замість виконання
всіх дій вручну скористайтеся можливостями Excel VBA, щоб перейменувати листи
автоматично.
Використання запису
макросу
Іноді все, що потрібно - просто
записати макрос, в цьому випадку навіть не потрібно переглядати код. Але
частіше одного запису недостатньо. Але запис служить відправною точкою
подальшого процесу.
Використання запису макросу в
якості відправної точки рішення
·
Запишіть дії, які потрібно кодувати.
·
Перегляньте код і знайдіть рядки, які виконують дії.
·
Витріть залишки частина коду.
·
Змініть записаний код.
·
Додайте змінні, керуючі структури і інший код, який неможливо записати під
час запису макросу.
Почніть дослідження з запису
макросу, що змінює ім'я аркуша на Нове ім'я. Потім можна використовувати
записаний макрос для створення власного макросу, перейменовувати декілька
листів на основі їх вмісту.
Запис макросу, перейменовувати
лист
·
Натисніть кнопку Записати макрос на вкладці Розробник.
·
Назвіть макрос RenameWorksheets, перейменуйте Лист1 в Нове ім'я і натисніть
кнопку Зупинити запис.
·
На вкладці Розробник або Вид натисніть кнопку Макрос і виберіть Змінити,
щоб відкрити редактор Visual Basic.
Код в редакторі Visual Basic
повинен бути схожий на наступний код.
Sub RenameWorksheets()
'
'
RenameWorksheets Macro
'
'
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "New Name"
End Sub
Перші чотири рядки після рядка
Sub - це коментарі. Будь рядок, що починається з апострофа, є коментарем і не
впливає на дії макросу. В основному коментарі використовуються для наступних
цілей:
·
спростити розуміння коду, не тільки для автора, але й для всіх, кому згодом
може знадобитися змінити код.
·
Щоб тимчасово відключити рядок коду (закоментувати).
Чотири рядки коментарів в
записаному макросі не вирішують жодних завдань, тож видаліть їх.
Наступний рядок використовує
метод Select, щоб вибрати член Sheet1 (Лист1) об'єкта колекції Sheets. У коді
VBA зазвичай не потрібно вибирати об'єкти перед роботою з ними, навіть якщо це
робиться при записі макросів. Іншими словами, цей рядок коду є надлишкової,
тому її теж можна видалити.
Останній рядок записаного макросу
змінює властивість «Name» члена Sheet1 колекції Sheets. Цей рядок потрібно
зберегти.
Після внесення змін записаний код
повинен виглядати наступним чином.
Sub RenameWorksheets()
Sheets("Sheet1").Name = "New Name"
End Sub
Вручну змініть ім'я аркуша
"Нове ім'я" назад на "Лист1", потім виконайте макрос. Ім'я
повинно змінитися назад на "Нове ім'я".
Зміна записаного коду
Тепер саме час вивчити колекцію
Sheets, використовувану при записі макросу. Розділ "Листи" в
довідковому керівництві по об'єктної моделі містить наступний текст.
"Колекція Sheets може
містити об'єкти Chart або Worksheet. Якщо потрібно працювати тільки з листами
одного типу, перегляньте розділ про об'єкт цього типу аркуша".
Використовується тільки колекція
Worksheets, тому змінити код наступним чином.
Sub RenameWorksheets()
Worksheets("Sheet1").Name = "New Name"
End Sub
Цикли
До цього моменту цей код дозволяє
змінити ім'я лише одного листа. Можна додати по рядку для кожного
перейменовувати листа, але що робити, якщо невідомо ні число листів, ні їх
поточні імена? Знадобиться спосіб застосувати певне правило до кожного аркушу
книги.
У VBA є конструкція, звана циклом
For Each і ідеально підходить для цього випадку. Цикл For Each перевіряє всі
елементи в об'єкті колекції, такому як Worksheets, і може використовуватися для
виконання дії (наприклад, зміни імені) над деякими або над всіма цими
елементами.
Змініть макрос так, щоб він став
схожий на наступний код.
Sub RenameWorksheets()
For Each myWorksheet In Worksheets
myWorksheet.Name = "New
Name"
Next
End Sub
myWorksheet
є змінною, тобто її значення змінюється. У цьому випадку змінна myWorksheet
послідовно представляє кожен аркуш в колекції Worksheets. Необов'язково
використовувати myWorksheet, можна
використовувати "x", "ws",
"WorksheetToRenameAfterTheContentsOfCellB1" або (з невеликими
обмеженнями) практично будь бажане ім'я. Доброю практикою є використання імен
змінних, досить довгих, щоб нагадати про її сенсі, але не настільки довгих, щоб
захаращувати код.
Якщо виконати макрос в його
поточному стані, його виконання призведе до помилки, так як Excel вимагає, щоб
ім'я кожного аркуша у книзі було унікальним, але наступна рядок вказує Excel
привласнювати всім листам одне і те ж ім'я.
myWorksheet.Name = "New Name"
Щоб виправити рядок так, щоб
можна було перевірити роботу циклу For Each, змініть рядок наступним чином.
myWorksheet.Name = myWorksheet.Name & "-changed"
Замість спроби привласнити
кожному листу одне і те ж ім'я, цей рядок змінює поточне ім'я кожного аркуша
(myWorksheet.Name) на поточне ім'я з додаванням суфікса "-changed".
Корисне перейменування
Макрос поступово наближається до
того, що може дійсно вирішити проблему. Тепер потрібно знайти спосіб витягувати
дані з самих листів - конкретно з комірки B1 кожного аркуша - і поміщати ці
дані в імена аркушів.
На цей раз замість використання
запису макросу для вивчення посилання на комірку, зробимо припущення і
подивимося, чи спрацює використання об'єкта Cell (комірка).
' Set the interior of cell A1 to yellow.
Range("A1").Select
Передбачається, що об'єкт Range
(діапазон) використовується, щоб задати діапазон комірок або тільки одну окрему
комірку. І знову, частина. Select не знадобиться, але знадобиться знайти, як посилатися
на вміст об'єкта Range, а не на сам об'єкт Range. Якщо перейти до розділу
Range, можна прочитати, що в об'єкта Range є і Methods, і Properties. Вмістом
об'єкта Range є сутність, не дію, тому це, швидше за все, буде Property. Якщо
переглянути список, можна знайти властивість Value. Тому спробуйте наступний
код.
Sub RenameWorksheets()
For Each myWorksheet In Worksheets
myWorksheet.Name = myWorksheet.Range("B1").Value
Next
End Sub
Виконання цього коду для книги,
яка містить листи з порожньою коміркою B1, призведе до помилки, так як значення
властивості Value порожнього діапазону Range одно "" (порожній
текстовий рядок), яке не є допустимим ім'ям аркуша. Саме час створити будь-які
дані для прикладу. Зробіть три аркуші книги схожими на показані на наступному
малюнку, а потім виконайте макрос.
Імена аркушів зміняться
відповідним чином.
Перевірка порожніх
комірок
Як зазначалося раніше, макрос призводить до
помилки, якщо будь-яка з комірок B1 книги виявляється порожньою. Замість ручної
перевірки кожного аркуша можна запрограмувати макрос так, щоб ці дії
виконувалися в ньому. Перед рядком myWorksheet.Name додайте наступний рядок
коду.
If myWorksheet.Range("B1").Value <> "" Then
А після рядка myWorksheet.Name додайте наступний
текст.
End If
Такий код називається інструкцією If ... Then.
Інструкція If ... Then вказує Excel виконувати весь код між рядком If і рядком
End If, але тільки при виконанні умови, наведеного в рядку If. У прикладі
перевіряється умова задається наступним рядком.
myWorksheet.Range("B1").Value <> ""
Знаки <> означають "не дорівнює",
а знаки лапок, між якими нічого немає, позначають порожню текстову рядок, тобто
повна відсутність тексту. Отже, всі рядки коду між If і End If будуть виконані
тільки якщо значення у клітинці B1 не порожньо, тобто, коли осередок B1 містить
текст.
Оголошення змінних
Іншим поліпшенням, яке потрібно внести в макрос, є
приміщення в початок макросу оголошення змінної myWorksheet.
Dim myWorksheet As Worksheet
Dim є скороченням від "Dimension"
(розмірність), а Worksheet - це тип цієї конкретної змінної. Ця інструкція
повідомляє VBA, який тип сутності являє змінна myWorksheet. Зверніть увагу, що
після введення As, редактор Visual Basic виводить підказку, що містить перелік
усіх доступних типів змінних. Це приклад технології IntelliSense, тобто
редактор Visual Basic реагує на те, що, як він вважає, намагається зробити
користувач, і пропонує список відповідних варіантів. Можна вибрати варіант зі
списку або продовжити введення.
Хоча оголошення змінних в VBA не є обов'язковими,
їх використання настійно рекомендується! Оголошення змінних різко спрощує
відстеження змінних і виявлення помилок в коді. Крім того, пам'ятайте, що при
оголошенні змінної з типом об'єкта (наприклад, Worksheet) IntelliSense виводить
відповідний список властивостей і методів, пов'язаних з об'єктом, при
подальшому використанні цієї змінної об'єкта в макросі.
Коментарі
Тепер макрос став досить складним, щоб додати в
нього кілька коментарів, які нагадують, що робить код. Кількість
використовуваних коментарів - це питання особистого стилю, але в загальному
випадку занадто багато коментарів краще, ніж занадто мало коментарів. З часом
код зазвичай потрібно змінювати і оновлювати. Без коментарів може бути важко
зрозуміти, що робиться в коді, особливо коли особа, котра змінює код, не є
автором первісного коду. Додавання коментарів для умови If і для рядка,
перейменовувати листи, призводить до появи наступного коду.
Sub RenameWorksheets()
Dim myWorksheet As Worksheet
For Each myWorksheet In Worksheets
'make sure that cell B1 is not empty
If myWorksheet.Range("B1").Value <> "" Then
'rename the worksheet to the contents of cell B1
myWorksheet.Name = myWorksheet.Range("B1").Value
End If
Next
End Sub
Щоб перевірити макрос, перейменуйте
листи назад у Лист1, Лист2 і Ліст3 і видаліть вміст комірки B1 на одному або
кількох аркушах. Виконайте макрос, щоб перевірити, що він перейменовує листи з
текстом в комірці B1 і залишає недоторканими інші листи. Макрос працює для
будь-якого числа листів, з будь-якою комбінацією заповнених і порожніх клітинок
B1.
Немає коментарів:
Дописати коментар