Створення макросів

Макрос – це набір інструкцій, які задають послідовність дій, які Microsoft Excel (або інший додаток) виконує замість вас Макроси, по суті, є компютерними програмами, але вони працюють не незалежно, а тільки всередині Excel Їх область застосування – автоматизація трудомістких або типових завдань

Макроси можуть виконувати послідовності операцій значно швидше вас Наприклад, один макрос при введенні ряду дат в рядок робочого аркуша буде вирівнювати кожну дату по центру комірки і одночасно застосовувати до рядка один з типів рамки Або інший приклад: макрос встановлює параметри друку в діалоговому вікні Параметри сторінки і потім друкує документ Макроси бувають і простими, і дуже складними Вони навіть можуть бути інтерактивними, тобто можуть працювати в режимі діалогу з людиною

Існує два способи створення макросів: автоматичний запис послідовності операцій або безпосереднє введення інструкцій в програмний модуль В останньому випадку потрібно трохи знати мову програмування Microsoft Visual Basic for Applications (VBA)

У цьому розділі ми покажемо, як записати простий макрос, і заодно познайомимо вас з VBA Потім ми пояснимо, як вивести на екран текст записаного макросу і зробити його більш корисним, внісши в нього кілька простих змін У главі 32 ви дізнаєтеся, як використовувати VBA для створення власних функцій У главі 33 буде розглянута техніка налагодження створюваних макросів і функцій користувача

Запис макросу

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

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

Процес запису макросу складається з трьох кроків Спочатку активізуйте режим запису і надайте макросу імя Потім виконайте дії, які підлягають записи, наприклад вибір команд меню, виділення осередків і введення даних Після цього зупиніть запис макросу

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

1 Виберіть у меню Сервіс (Tools) команду Макрос (Macro) і потім – Почати запис (Record New Macro) Excel виведе на екран діалогове вікно Запис макросу (Record Macro), показане на рис 311

Рис 311 Відкрийте діалогове вікно Запис макросу, щоб почати запис макросу

2 Призначте імя макросу Ви можете прийняти запропоноване Excel імя Макрос1 або ввести будь-яке інше, в даному випадку введіть Адрес_Компаніі

3 Звяжіть макрос з комбінацією клавіш, ввівши в поле Поєднання клавіш (Shortcut Key) букву, у нашому прикладі прописну А

4 Щоб зберегти макрос в поточній книзі, переконайтеся, що в списку, що розкривається

Зберегти в (Store Marco In) вибраний пункт Ця книга (This Workbook)

5 Введіть короткий коментар до макросу в полі Опис (Description) Збережіть запропонований стандартний текст (він допоможе вам орієнтуватися надалі серед безлічі макросів) і доповніть його словами Введення адреси фірми

6 Для того щоб почати запис, натисніть кнопку ОК Excel виведе в рядку стану повідомлення Запис (Recording), і на екрані зявиться панель інструментів Зупинка запису (Stop Recording) (Рис 312)

Рис 312 Панель інструментів Зупинка запису

7 Виділіть клітинку А6 і введіть текст Регіональна Торгова Компанія У осередок А7 занесіть адресу Морська вулиця, 15 У комірці А8 вкажіть інші його складові – 325611, Кузнецьк, Росія

8 Клацніть на кнопці Зупинити запис (Stop Recording Macro) на панелі інструментів Зупинка запису або виберіть у меню Сервіс команду Макрос і потім – Зупинити запис Цей крок необхідний, інакше Excel буде продовжувати фіксувати ваші дії нескінченно

Щоб перевірити роботу нового макросу, очистіть лист і натисніть клавіші Ctrt + Shift + A Excel запустить макрос і відтворить те, що ви робили на кроці 7, в тій же послідовності

ПРИМІТКА –

Сполучення клавіш у VBA задаються з урахуванням регістра букв

Запуск макросу з діалогового вікна Макрос

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

Найбільш загальний шлях запуску макросів в середовищі Excel – діалогове вікно Макрос

1 Виберіть у меню Сервіс команду Макрос і потім – Макроси (Macros), щоб відкрити діалогове вікно Макрос (Macro), показане на рис 313

Рис 313 Макроси можна запускати з діалогового вікна Макрос

2 Виберіть імя макросу і натисніть кнопку Виконати (Run)

Як ви побачите в наступному розділі, діалогове вікно Макрос також використовується для перегляду і редагування макросів

За сценою: середа VBA

Тепер давайте зясуємо, що ж відбувається при записі макросу Після натискання вами в перший раз кнопки ОК в діалоговому вікні Запис макросу Excel створює якусь сутність, звану програмниммодулем,і вставляє його в поточну книгу При введенні в активний аркуш назви і адреси фірми Excel фіксує ваші дії і додає в цей модуль відповідні інструкції VBA

Але новий модуль так просто не доступний, як інші аркуші книги Щоб його побачити, в меню Сервіс виберіть команду Макрос і потім – Макроси Після цього в діалоговому вікні Макрос виділіть щойно створений макрос Адрес_ Компанії та натисніть кнопку Змінити (Edit) Запуститься редактор VBA, у вікні якого буде відображений код макросу Адрес_Компаніі, як показано на рис 314

Як видно, вікно редактора VBA нагадує вікно текстового процесора Команди рядка меню дозволяють редагувати, налагоджувати і запускати програми VBА Тут є всі ті ж знайомі вам по текстових процесорам команди

копіювання, вставки і видалення, застосовні до інструкцій VBA і коментарям І звичайно, ви можете перемикатися з редактора VBA в Excel і назад за допомогою відповідних кнопок на Панелі завдань Windows

Рис 314Excel перетворює ваші дії в інструкції VBA і вставляє останні в окремий модуль

Середа Microsoft VBA цікава в багатьох відношеннях, але зараз ми зосередимо увагу тільки на записаному тексті програми У правій частині вікна редактора VBA знаходиться вікно, в якому відображається модуль, що містить інструкції

Перша і остання рядка вихідного коду є початковою і кінцевою точками макросу, тобто макрос починається інструкцією Sub, що містить його імя, а закінчується інструкцією End Sub Спеціальні терміни VBA називаються ключовими словамита синім кольором Ви можете змінити кольори, призначувані різних елементів макросів, якщо в меню Tools (Сервіс) виберете команду Options (Параметри) і потім в діалоговому вікні перейдете на вкладку Editor Format (Формат редактора)

Отримання довідки за ключовими словами VBA

Щоб отримати детальну інформацію про ключове слово, клацніть на ньому і натисніть клавішу F1 Якщо в коді, представленому на рис 314, встановити курсор на ключовому слові Sub і натиснути Fl, Excel виведе на екран довідку, яка містить опис інструкції Sub, як показано на рис 315

Рис 315 Довідка за інструкцією Sub

Багато розділів довідки за ключовими словами VBA містять посилання, після клацання на якій відкривається інший екран довідки з одним або декількома прикладами застосування ключового слова в процедурах VBA (Наприклад, у вікні на рис 315 це посилання Example прямо під заголовком Sub Statement) Ви можете скопіювати інструкції з прикладу і вставити їх в модуль, а потім змінити їх відповідно зі своїми завданнями

Обєкти, властивості і методи

У VBA всі елементи документів Excel розглядаються якобєкти,а не як абстрагований набір структур даних або впорядкованих рядів пікселів на екрані Кожен обєкт є контейнером для іншої безлічі обєктів На вершині ієрархічної обєктної моделі знаходиться сам додаток Excel, осяжний всі інші обєкти Другу щабель ієрархії утворюють робочі книги Вони, в свою чергу, містять робочі листи і листи діаграм, робочі листи містять діапазони (І також обєкти-діаграми) і т д

Першим пропозицією VBA в макросе Адрес_Компаніі після інструкції Sub (не рахуючи коментарів, які розпізнаються по початковому апострофа) є інструкція

Range(&quotA6&quot)Select

Цей рядок ілюструє важливу особливість VBA: синтаксис багатьох інструкцій увазі спочатку опис обєкта, а потім дії з цим обєктом

Обєктом може бути діапазон, робочий лист, графічний елемент, книга – будь-який з 100 з гаком типів обєктів Excel У даній інструкції обєкт-діапазон Range – це посилання на клітинку А6, а виконувану дію – Виділення діапазону

Обєкти VBA мають методи, тобто набір дій, які вони вміють виконувати Образно кажучи, якщо обєкт – це іменник, то методи подібні до дієсловам Давайте створимо в VBA дресированого кіберпса Щоб змусити його гавкати, подамо якусь команду

DogBark

Але кіберпес може (або повинен) не тільки подавати голос Наприклад, він зобовязаний розуміти команди «Сидіти» (Sit), «Барєр» (RollOver), «Принести» (Fetch):

DogSit DogRollOver DogFetch

Наш кіберпес вміє гавкати, перестрибувати через перешкоду, приносити предмети і т п Перераховані дії є його методами Кожен обєкт має свій список методів, які він здатний виконувати Так, обєкт Range підтримує майже 80 різних методів, що дозволяють копіювати і вставляти комірки, виконувати сортування, форматування та ін

Подібно предметів реального світу, обєкти VBA мають також властивості Якщо вважати, що обєкти – це іменники, а методи – дієслова, то властивості є прикметниками Властивість – це якість або характеристика обєкта, наприклад його колір або візерунок Такі характеристики нашого кіберпса, як окрас, число плям, довжина хвоста і гучність гавкоту, є властивостями цього обєкта

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

DogTailLength = 10

Тут TailLength є властивістю обєкта Dog

Тепер, зясувавши аналогії, знову поглянемо на третю виконувану інструкцію в макросе

Адрес_Компаніі:

ActiveCellFormulaR1C1 = Регіональна Торгова Компанія

Вона змінює одна з властивостей активної комірки Властивість FormulaRlCl дозволяє задати вміст поточної комірки, в яку в даному випадку вводиться назва фірми – Регіональна Торгова Компанія

Решта інструкції в макросе Адрес_Компаніі забезпечують почергове виділення і введення тексту в ще два осередки листа: А7 і А8

Перегляд обєктів

Ви можете переглянути наявні в Excel типи обєктів, їх методи і властивості,

переключившись в редактор VBA Для цього скористайтеся командою Макрос в меню

Сервіс і потім – командою Редактор Visual Basic (Visual Basic Editor) Потім в меню View виберіть команду Object Browser (Перегляд обєктів) або натисніть клавішу F2 Зявиться вікно, зображене в правій частині рис 316 У списку бібліотек, розташованому у верхній частині вікна перегляду обєктів, виберіть пункт Excel

Рис 316 Список класів обєктів, що належать додатком Excel

Список в лівій частині цього вікна містить класи обєктів, наявні в Excel Ви можете розглядати клас як шаблон або опис типу обєкта Наприклад, конкретна діаграма є обєктом, що належать до класу Chart У VBA класи належать проекту або бібліотеці

Так, якщо прокрутити лівий список і вибрати в ньому клас Range, то в списку справа відобразяться назви властивостей і методів (разом званих членами класу), властивих будь-якому діапазону аркуша, як показано на рис 317

Сімейства обєктів

Одночасно можуть співіснувати кілька примірників одного і того ж обєкта VBA У сукупності ці екземпляри утворюютьсімействоКонкретний екземпляр в сімействі обєктів ідентифікується своїм імям або за індексом Наприклад, сімейством всіх аркушів у книзі є

Sheets()

Рис 317 Обєкт Range і декілька його методів і властивостей

Конкретний екземпляр листа, третій в сімействі, вказується таким чином:

Sheets(3)

Третій лист з імям Підсумки допустимо ідентифікувати як

Sheets(&quotMTorn&quot)

У VBA кожен елемент сімейства має власний індекс, але інтервал значень індексів не обовязково безперервний, і тому при видаленні одного примірника обєкта індекси інших екземплярів можуть залишитися незмінними Так, якщо ви видалите Sheets (3) з родини дванадцяти листів книги, немає гарантії, що Excel перенумеруйте Sheets (4) і т д до Sheets (12), щоб усунути розрив

Для багаторазового повторення операції часто застосовується конструкція For .. Next,

наприклад, послідовно активувати аркуші книги дозволяє такий цикл:

For n 1 to 12 Активізація кожного аркуша

Sheets(n)Activate Next n

Але якщо ви виконаєте цей код після видалення аркуша Sheets (3), VBA виведе повідомлення про помилку і зупинить макрос, оскільки обєкта Sheets (3) більше не існує Для таких ситуацій VBA пропонує конструкцію For Each .. Next, яка дозволяє перебирати екземпляри сімейства без урахування їх індексів Припустимо, що ми хочемо додатково

помітити кожен лист активної книги, вводячи в комірку А1 значення Лист1, Лист2 і т д Так як в загальному випадку кількість аркушів у книзі заздалегідь не відомо, скористаємося наступним макросом VBA:

Sub EnterSheetNum() n = 0

For Each Sheet In Sheets( ) n = n + 1

SheetActivate Range (A1) Select ActiveCellFormulaR1C1 = Лист + Str (n)

Next End Sub

Зміна властивостей обєкта без його виділення

У наведеному раніше макросе кожен з листів спочатку активується, потім на ньому виділяється осередок А1, і після цього встановлюється властивість FormulaRlCl цього осередку У VBA, як правило, перші два з перерахованих дій є надлишковими і лише збільшують час виконання макросу Іншими словами, послідовність з трьох інструкцій

SheetActivate Range (A1) Select ActiveCellFormulaR1C1 = Лист + Str (n)

можна замінити однією

SheetRange(“A1&quot)FormulaR1C1  =   &quotSheet&quot  + Str(n)

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

Імена аргументів методу

У VBA багато методи мають аргументи, що дозволяють задати параметри виконуваних дій Якщо ми хочемо змусити нашого кіберпса виляти хвостом (обєкт Tail) за допомогою методу Wag, то будуть потрібні такі властивості, як wagRate – кількість помахів в секунду, wagTime – тривалість одного помаху в секундах і wagArc

– розмах в градусах Їх можна задати в двох варіантах синтаксису

У першому варіанті, який часто називаєтьсясинтаксисом по імені,аргументи вказуються по імені і в довільному порядку Наприклад, така інструкція жорстоко примушує Тузика подрагивать хвостом три рази на секунду протягом години з амплітудою 5 °:

Robodogs (Тузик) TailWag _ wagRate: = 3, _

wagTime:=  3600,_ wagArc:=  5

Для привласнення значення аргументу використовується двокрапка і знак рівності,

аргументи відокремлюються один від одного комою

ПРИМІТКА

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

У другому випадку, який зазвичай називаєтьсясинтаксисом по позиції,аргументи вводяться в установленому порядку Відповідно попередня інструкція, записана в позиційному синтаксисі, придбає наступний вигляд:

Robodogs (Tyзік) TailWag (3,3600,5)

Зверніть увагу, що список аргументів укладений у круглі дужки Наскільки зручний той чи інший варіант, залежить від ваших уподобань: синтаксис по позиції вимагає запамятовування порядку проходження аргументів, а синтаксис за назвою – імен цих аргументів Але врахуйте, що програма з іменами аргументів набагато більше говорить про застосовані параметрах

ПРИМІТКА

При запису макросів Excel перераховує аргументи по позиції, а не по імені Тому записані макроси важче зрозуміти, ніж створені вручну, в яких аргументи задані по імені І ще: коли ви виділяєте зарезервоване імя аргументу VBA і натискаєте клавішу F1, Excel виводить тему довідки, яка надає контекстну інформацію, як для будь-якого ключового слова Погодьтеся, що це зручно

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*