Презентація на тему:
Макроси та функції користувача

Розділ 11 Макроси та функції користувача Спецкурс «Microsoft Excel у профільному навчанні»

План вивчення теми У цьому розділі буде розглянуто: Призначення макросів; Створення, запуск, редагування та видалення макросів; Використання макросів для розв'язування прикладних задач. Функції користувача

Створення та використання макросів Табличний процесор Microsoft Excel містить засоби, які дають можливість автоматизувати виконання деякої послідовності дій. Наприклад, виконання рутинної роботи з форматування таблиці, будь-якої дії з великою кількістю записів тощо може бути замінено однією операцією, а саме викликом макросу — спеціальної програми, яка містить опис послідовності дій в Microsoft Excel мовою Visual Basic for Application (VBA). Макрос можна запускати кожного разу, коли потрібно виконати записані в ньому дії. Користувач сам вказує, які дії мають виконуватися автоматично і як саме буде викликатися макрос. Ця операція називається запис макросу. Коли записується макрос, Excel зберігає інформацію про кожний крок, виконуваний користувачем. Після запуску макросу всі вказані в ньому команди відтворюються. Зазначимо, що використання макросів пов'язано з певною загрозою безпеці комп'ютера, оскільки теоретично в них можуть зберігатися віруси. Тому перш ніж розпочинати запис, слід встановити низький рівень захисту від макросів. Для цього необхідно: Меню-Параметри Excel-Центр управління безпекою-Параметри центру управління безпекою-Параметри макросів-Дозволити всі макроси.

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

Створення макросів в редакторі Visual Basic Для введення команд і формування програми, тобто створення макросу необхідно відкрити спеціальне вікно - редактор програм на VBA, вбудований в Microsoft Excel. В старих версіях (Excel 2003 і старше) для цього йдемо в меню Сервіс - Макрос - Редактор Visual Basic (Toos - Macro - Visual Basic Editor). У нових версіях (Excel 2007 і новіше) для цього потрібно спочатку відобразити вкладку Розробник (Developer). Вибираємо Файл - Параметри - Налаштування стрічки (File - Options - Customize Ribbon) і включаємо в правій частині вікна прапорець Розробник (Developer). Тепер на вкладці, що з'явилася нам будуть доступні основні інструменти для роботи з макросами, в тому числі і потрібна нам кнопка Редактор Visual Basic (Visual Basic Editor)

Програмні модулі макросів Макроси (тобто набори команд на мові VBA) зберігаються в програмних модулях. У будь-якій книзі Excel ми можемо створити будь-яку кількість програмних модулів і розмістити там наші макроси. Один модуль може містити будь-яку кількість макросів. Доступ до всіх модулів здійснюється за допомогою вікна Project Explorer в лівому верхньому кутку редактора (якщо його не видно, натисніть CTRL + R). Програмні модулі бувають декількох типів для різних ситуацій: Звичайні модулі - використовуються в більшості випадків, коли мова йде про макроси. Для створення такого модуля виберіть у меню Insert - Module. У вікно, що з'явилося нового порожнього модуля можна вводити команди на VBA, набираючи їх з клавіатури або копіюючи їх з іншого модуля:

Програмні модулі макросів Модуль Ця книга - також видно в лівому верхньому кутку редактора Visual Basic у вікні, яке називається Project Explorer. В цей модуль зазвичай записуються макроси, які повинні виконуватися при настанні певних подій в книзі (відкриття або збереження книги, друк файлу і т.п.): Модуль аркуша - доступний через Project Explorer і через контекстне меню листа, тобто правою кнопкою миші по ярличку аркуша - команда Оригінальний текст (View Source). Сюди записують макроси, які повинні виконуватися при настанні певних подій на аркуші (зміна даних у комірках, перерахунок листа, копіювання або видалення аркуша і т.д.)

Звичайний макрос, введений в стандартний модуль виглядає приблизно так: Приклад: макрос Zamena: Будь-який макрос повинен починатися з оператора Sub, за яким йде ім'я макросу і список аргументів (вхідних значень) в дужках. Якщо аргументів немає, то дужки треба залишити порожніми. Будь макрос повинен закінчуватися оператором End Sub. Все, що знаходиться між Sub і End Sub - тіло макросу, тобто команди, які будуть виконуватися при запуску макросу. В даному випадку макрос виділяє комірку заливає виділений діапазон (Selection) жовтим кольором (код = 6) і потім проходить у циклі по всім коміркам, замінюючи формули на значення. В кінці виводиться вікно повідомлення (MsgBox).

Запис макросів макрорекордером Макрорекордер - це невелика програма, вбудована в Excel, яка переводить будь-які дії користувача на мову програмування VBA і записує отриману команду в програмний модуль. Якщо ми включимо макрорекордер на запис, а потім почнемо створювати свій щотижневий звіт, то макрорекордер почне записувати команди услід за кожною нашою дією і, в підсумку, ми отримаємо макрос, який створює звіт, так як якби він був написаний програмістом. Такий спосіб створення макросів не вимагає знань користувача про програмування і VBA і дозволяє користуватися макросами як певним аналогом відеозапису: включив запис, виконав операції, перемотав плівку і запустив виконання тих же дій ще раз. Природно у такого способу є свої плюси і мінуси: Макрорекордер записує тільки ті дії, які виконуються в межах вікна Microsoft Excel. Як тільки ви закриваєте Excel або переключаєтеся в іншу програму - запис зупиняється. Макрорекордер може записати тільки ті дії, для яких є команди меню або кнопки в Excel. Програміст ж може написати макрос, який робить те, що Excel ніколи не вмів (сортування за кольором, наприклад або щось подібне). Якщо під час запису макросу макрорекордером ви помилилися - помилка буде записана. Однак сміливо можете тиснути на кнопку скасування останньої дії (Undo) - під час запису макросу макрорекордером вона не просто повертає Вас в попередній стан, але і стирає останню записану команду на VBA.

Щоб включити запис необхідно: Натиснути кнопку Запис макросу (Record macro) на вкладці Розробник (Developer) Потім необхідно налаштувати параметри записуваного макросу у вікні Запис макросу:

Ім'я макросу - підійде будь-яке ім'я російською або англійською мовою. Ім'я повинно починатися з букви і не містити пропусків та розділових знаків. Поєднання клавіш - буде потім використовуватися для швидкого запуску макросу. Якщо забудете поєднання або взагалі його не введете, то макрос можна буде запустити через меню Сервіс - Макрос - Макроси - Виконати (Tools - Macro - Macros - Run) або за допомогою кнопки Макроси (Macros) на вкладці Розробник (Developer) або натиснувши ALT + F8. Зберегти в ... - Тут задається місце, куди буде збережений текст макросу, тобто набір команд на VBA з яких і складається макрос: Ця книга - макрос зберігається в модуль поточної книги і, як наслідок, буде виконаються тільки поки ця книга відкрита в Excel Нова книга - макрос зберігається в шаблон, на основі якого створюється будь-яка нова порожня книга в Excel, тобто макрос буде міститися в усіх нових книгах, створюваних на даному комп'ютері починаючи з поточного моменту Особиста книга макросів - це спеціальна книга Excel з ім'ям Personal.xls, яка використовується як сховище макросів. Всі макроси зі Personal.xls завантажуються в пам'ять при старті Excel і можуть бути запущені в будь-який момент і в будь-якій книзі. Після включення запису і виконання дій, які необхідно записати, запис можна зупинити командою Зупинити запис (Stop Recording).

Запуск і редагування макросів  Управління всіма доступними макросами проводиться у вікні, яке можна відкрити за допомогою кнопки Макроси (Macros) на вкладці Розробник (Developer) Будь-який виділений в списку макрос можна запустити кнопкою Виконати (Run). Кнопка Параметри (Options) дозволяє подивитися і відредагувати поєднання клавіш для швидкого запуску макросу. Кнопка Змінити (Edit) відкриває редактор Visual Basic (див. вище) і дозволяє переглянути і відредагувати текст макросу на VBA.

Створення кнопки для запуску макросів  Щоб не запам'ятовувати поєднання клавіш для запуску макросу, краще створити кнопку і призначити їй потрібний макрос. Кнопка може бути декількох типів: Кнопка на панелі інструментів в Excel 2003 і старше Кнопка на панелі швидкого доступу в Excel 2007 і новіше Кнопка на аркуші

Кнопка на панелі швидкого доступу в Excel 2007 і новіше  Клацніть правою кнопкою миші по панелі швидкого доступу в лівому верхньому куті вікна Excel і виберіть команду Настройка панелі швидкого доступу (Customise Quick Access Toolbar): Потім у вікні, виберіть категорію Макроси та за допомогою кнопки Додати (Add) перенесіть обраний макрос в праву половину вікна, тобто на панель швидкого доступу:

Кнопка на аркуші Цей спосіб підходить для будь-якої версії Excel. Ми додамо кнопку запуску макросу прямо на робочий лист, як графічний об'єкт. Для цього: У Excel 2007 і новіше - відкрийте список, що випадає Вставити (Insert) на вкладці Розробник (Developer) Виберіть об'єкт Кнопка (Button): Потім намалюйте кнопку на аркуші, утримуючи ліву кнопку миші. Автоматично з'явиться вікно, де потрібно вибрати макрос, який повинен запускатися при клацанні по намальованій кнопці.

Алгоритм запису макросу 1. Виконати команду Вигляд, Макрос , Запис макроса. У результаті відкриється вікно Запис макросу . 2. У полі Ім'я макросу ввести ім'я макросу. Можна залишити ім'я, задане за умовчанням (Макрос1, Макрос2,...), а можна ввести нове, яке відображатиме сутність дій, що їх виконує макрос. 3. Якщо макрос має запускатися натисканням певних клавіш, то потрібно ввести літеру в полі Сполучення клавіш. Можна використовувати сполучення Ctrl+літера (для малих літер) або Ctrl+Shift+літера (для великих літер), де літера — будь яка літера на клавіатурі. Саму клавішу Ctrl під час створення макросу натискати не потрібно. Вибрана для макросу комбінація клавіш замінює собою стандартні сполучення клавіш Microsoft Excel на той час, поки відкрито книгу з цим макросом.

Алгоритм запису макросу 4. У полі Зберегти в вибрати книгу, у якій потрібно зберегти макрос. Якщо потрібно, щоб цей макрос був постійно доступний під час роботи в Excel, слід вибрати варіант Особиста книга макросів. За потреби додати до макросу опис його слід увести в полі Опис. 5. Клацнути кнопку ОК. На робочому аркуші з'явиться панель Зупинити запис (стрічка Розробник або стрічка Вигляд, макрос), і це означатиме, що ви перейшли в режим запису макросу і відомості про дії, які ви зараз виконаєте, будуть збережені в програмі макросу. 6. Виконати всі дії, які має виконувати макрос. 7. Клацнути кнопку (Зупинити запис) на однойменній панелі інструментів. Запис макросу буде завершено. 8. Перевірити дію макросу, натиснувши призначені йому клавіші.

Відносні та абсолютні посилання Буває, що макрос потрібно виконувати відносно позиції активної клітинки, наприклад, якщо дія цього макросу полягає в обчисленні суми значень у п'яти клітинках, розташованих над поточною клітинкою, або в обведенні поточної клітинки рамкою. В інших випадках адреси клітинок, над якими макрос виконує дії, є абсолютними, наприклад, якщо він обчислює суму значень у клітинках А1:А5 або обводить рамкою діапазон А1:D10. Для запису операцій відносно активної клітинки потрібно на панелі Зупинити запис натиснути кнопку (Відносне посилання). Запис макросу буде продовжено з використанням відносних посилань, поки ви не вийдете з Microsoft Excel або не відтиснете цю кнопку.

Способи запуску макросів Макрос можна запустити, вибравши його зі списку в діалоговому вікні Макрос, яке відкривається командою Вигляд, Макрос, Макроси, або Розробник, Макроси, або за допомогою клавіш (якщо вони були задані під час створення цього макросу). Крім того, є можливість для запуску макросу призначити кнопку на панелі інструментів, графічний об'єкт або спеціальну кнопку на аркуші електронної книги. Щоб зупинити виконання макросу, потрібно натиснути клавішу Esc, а потім у вікні Microsoft Visual Basic клацнути кнопку End.

Вправа 11.1.Створення макросу Запишіть макрос, який встановлюватиме для виділених клітинок сірий колір заливки, напівжирне написання шрифту та вирівнювання тексту зліва. 1. Створіть нову електронну книгу та збережіть її під іменем Bправа_11_l.xls. Виділіть будь-яку порожню клітинку і виконайте команду Сервіс ► Макрос ► Почати запис. 2. У вікні Запис макросу введіть назву макросу, наприклад СірийЖирнийЗліва. Клацніть поле Сполучення клавіш та натисніть клавішу, за допомогою якої, у сполученні з клавішею Ctrl, цей макрос запускатиметься (рис.7). 3. Клацніть кнопку OK, і ви перейдете до режиму запису макросу (про це свідчитиме наявність панелі інструментів Зупинити запис). Не переміщуючи курсор в іншу клітинку, задайте за допомогою панелі інструментів Форматування сіре тло для клітинки, напівжирне написання шрифту та вирівнювання тексту за лівим краєм. ПРИМІТКА. Якщо під час запису макросу ви перемістите курсор в іншу клітинку, буде активовано режим абсолютних посилань і після виклику макросу завжди форматуватиметься саме та клітинка, у яку переміщено курсор. 4. Таблицю з файлу BnpaBa_ll_l.xls відформатуйте за допомогою макросу так, як показано на рис. 8. Для цього виділяйте клітинки, які мають бути відформатовані описаним вище чином, та натискайте ключові клавіші макросу.  Рис. 11.8. Відформатована таблиця

Створення користувальницьких функцій на VBA  Створення користувальницьких функцій або, як їх іноді ще називають, UDF-функцій (User Defined Functions) принципово не відрізняється від створення макросу в звичайному програмному модулі. Різниця тільки в тому, що макрос виконує послідовність дій з об'єктами книги (комірками, формулами та значеннями, листами, діаграмами і т.д.), а користувальницька функція - тільки з тими значеннями, які ми передамо їй як аргументи (вихідні дані для розрахунку ).  Щоб створити для користувача функцію для розрахунку, наприклад, податку на додану вартість (ПДВ) відкриємо редактор VBA, додамо новий модуль через меню Insert - Module і введемо туди текст нашої функції:

Створення користувальницьких функцій на VBA На відміну від макросів функції мають заголовок Function замість Sub і непорожній список аргументів (в нашому випадку це Summa). Після введення коду наша функція стає доступна в звичайному вікні Майстра функцій (Вставка - Функція) в категорії Визначені користувачем (User Defined):

Створення користувальницьких функцій на VBA Після вибору функції виділяємо комірки з аргументами (з сумою, для якої треба порахувати ПДВ) як у випадку із звичайною функцією: