Створення макросів в середовищі Excel Х

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

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

Таким чином, існує в MS Excel існує два способи створення макросів.

Заданням відповідних дій за допомогою миші та клавіатури (за допомогою “Записувача макросів”).

Написанням відповідної процедури мовою VBA.

Для створення макросів першим способом необхідно виконати наступні дії.

Розпочати запис макросу вибравши пункт меню Tools\Macro\Record New Macro….

Рис. 4. Діалогове вікно запису нового макросу.

У діалоговому вікні що з’явилось, задати ім’я макросу (Macro name), також можна задати гарячі клавіші, за якими в подальшому викликатиметься цей макрос (Shortcut key).

Після натиску кнопки ОК, виконати необхідні маніпуляції над елементами MS Excel за допомогою миші та клавіатури. При цьому з’явиться інструментальне меню Stop Recording (Зупинити запис).

Рис. 5. Інструментальне меню Stop Recording (Зупинити запис).

Завершити запис макросу з допомогою пункту меню Tools\Macro\Stop Recording або натиском відповідної (першої зліва) кнопки інструментального меню Stop Recording (Зупинити запис).

Створений макрос буде мати вказане вище ім’я.

Для створення макросів другим способом необхідно виконати наступні дії.

Завантажити редактор Visual Basic за допомогою пункту меню Tools\Macro\Visual Basic Editor або натиснувши комбінацію клавіш ALT+F11.

Рис. 6. Редактор Visual Basic.

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

Створити новий модуль за допомогою пункту меню редактора Insert\Module.

В правій частині редактора написати процедуру мовою VBA, яка описує необхідні маніпуляції над елементами MS Excel.

Закрити редактор Visual Basic.

Створений макрос буде мати таке ж ім’я, як ім’я відповідної VBA-процедури, задане після ключового слова Sub (на рис. 6 зображено макрос з назвою FormatFont).

Переглянути створені макроси можна за допомогою пункту меню Tools\Macro\Macros…. На екрані з’являється діалогове вікно, зображене на рис. 7.

Рис. 7. Діалогове вікно перегляду списку макросів.

Вибравши відповідний макрос, його можна виконати (викликати) натисненням кнопки Run. Для зручності виклику макросів можна задавати гарячі клавіші або створити кнопку на інструментальному меню MS Excel. Гарячі клавіші присвоюються при створенні макросу або натиском кнопки Options… (Установки…) діалогового вікна перегляду списку макросів (див. рис. 7). Для створення кнопки інструментального меню MS Excel необхідно виконати наступні дії.

Викликати діалогове вікно налагодження за допомогою пункту меню Tools\Customize….

За допомогою закладки Toolbars (Інструментальні меню), як показано на рис. 8, створити нове інструментальне меню, натиснувши кнопку New… (Нове…). При цьому необхідно вказати назву нового інструментального меню. Після створення нового інструментального меню його назва відобразиться у відповідному списку діалогового вікна налагодження та воно з’явиться у середовищі MS Excel.

Рис. 8. Діалогове вікно налагодження: закладка Toolbars.

На закладці Commands (Команди) у списку категорій (Categories) вибрати пункт Macros (Макрос), як показано на рис. 9.

Рис. 9. Діалогове вікно налагодження: закладка Commands.

Зі списку команд (Commands) вибрати мишкою або Custom Menu Item (Користувацький елемент меню), або Custom Button (Користувацька кнопка), і, не відпускаючи лівої кнопки миші, перетягнути вибрану команду на створене інструментальне меню (див. рис. 10).

Рис. 10. Створення кнопки на інструментальному меню.

Клацнути правою кнопкою миші на створеній кнопці та в контекстному меню вибрати пункт Assign Macro (Призначити макрос).

Рис. 11. Діалогове вікно призначення макросу.

У діалоговому вікні, яке з’явилося, зі списку вибрати створений макрос та натиснути кнопку Ok.

Закрити діалогове вікно налагодження.

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

Рис. 12. Інструментальне меню з призначеним макросом у MS Excel.

Аналогічно створюються наступні потрібні макроси та відповідні кнопки на інструментальному меню.

ПРИКЛАД

Приклад створення макросу, який змінює параметри шрифту для виділеної ділянки робочого листка.

1) Створення з допомогою “Записувача макросів”.

Після виклику команди Tools\Macro\Record New Macro… і початку запису макросу здійснимо наступні дії:

На листку Sheet1 клацнемо правою кнопкою миші в комірці, наприклад, А2 і меню, що з»явилось вибираємо пункт Format Cells.

У вікні, що з’явилось, переходимо на вкладку Font.

Встановлюємо Font type в курсив (Italic) і Color вибираємо червоний.

Закриваємо вікно, натиснувши кнопку ОК.

На панелі інструментів Formatting натискаємо на кнопку В;

Зупиняємо запис макросу виконавши команду Tools\Macro\Stop Recording.

2) Створення з допомогою редактора VBA.

Після створення нового модуля в редакторі Visual Basic (пункт меню Insert\Module) набираємо текст наступної VBA-процедури.

Sub ChangeFont() ' Службове слово визначення процедури та її імені

With Selection.Font ' Оператор початку роботи з об’єктом Selection.Font

.Name = «Times New Roman» ' Встановлення імені шрифту об’єкта Selection.Font

.FontStyle = «Italic» ' Встановлення стилю шрифту об’єкта Selection.Font

.Size = 14 ' Встановлення розміру шрифту об’єкта Selection.Font

.ColorIndex = 3 ' Встановлення кольору шрифту об’єкта Selection.Font

.Bold = True ' Присвоєння значення "ІСТИНА" властивості Bold (Жирний) об’єкта Selection.Font

End With ' Оператор закінчення роботи з об’єктом Selection.Font

End Sub ' Службове слово закінчення процедури

Створення нових шаблонів

Створення шаблону

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

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

Аби створити шаблон на основі існуючого документа, виконаєте наступні дії.

1. Відкрийте документ, який використовуватиметься як шаблон і внесіть до нього необхідні зміни:

видалите непотрібний текст, графіку та інші всі елементи, які не повинні з'являтися в документах;

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

зміните параметри форматування символів і абзаців, визначите стилі, розміри полів і сторінок, задайте і відформатуйте колонтитули.

2. Збережете документ як шаблон таким чином:

виберіть команду Зберегти як (Save As) у меню Файл (File). У вікні Збереження документа (Save As) у полі Тип файлу (Save as type) встановите Шаблон документа (Document Template). У полі Тека (Look in) автоматично з'явиться тека Шаблони;

ви можете зберегти шаблон в цій теці. В цьому випадку при виборі команди Створити (New) з меню Файл (File) значок шаблону відображуватиметься на вкладці Загальні (General). При збереженні шаблону в одній з тек, вкладених в теку Шаблони значок шаблону відображуватиметься на однойменної вкладки діалогового вікна Шаблони (Templates);

введіть ім'я шаблону в поле Ім'я файлу (File Name) і натискуйте кнопку ОК. Ви можете створити шаблон для ділового листа, що посилається від імені вашої фірми, в якому будуть представлені емблема вашої фірми, її адреса, слова вітання, підпис і тому подібне

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

Аби створити новий шаблон на основі наявного шаблону, виконаєте наступні дії:

виберіть команду Відрити (Open) у меню Файл (File);

у списку, що розкривається Тип файлу (Files of Type) виберіть рядок Шаблони документів (*.dot) (Document Templates);

внесіть необхідні зміни до шаблону і збережете файл.

Прісоєдинение шаблону до документа

При роботі над документом, як правило, можна використовувати лише налаштування, збережені в шаблоні, приєднаному до цього документа, або в шаблоні Normal.dot. Для заміни шаблону і оновлення стилів виберіть в меню Сервіс (Tools) команду Шаблони і налаштування (Templates And Add-Ins) і в діалоговому вікні, що відкрилося, натискуйте кнопку Приєднати (Attach) (мал. 10.12). На екрані відображуватиметься діалогове вікно Приєднання шаблону (Attach Template) у якому буде видно вміст теки з шаблонами.

Виберіть шаблон, який ви хочете приєднати і натискуйте кнопку Відкрити (Open). Ви повернетеся в діалогове вікно Шаблони і надбудови, в якому встановите прапорець Автоматично оновлювати стилі (Automatically Update Document Styles) і натискуйте кнопку ОК.

Стилі абзаців і символів приєднаного шаблону копіюються, замінюючи всі існуючі стилі іншими іменами. Текст, графіка і налаштування параметрів сторінок не стають частиною документа.