Lab_Excel_Macros

Лабораторна робота №8

Тема: Створення макросів в табличному процесорі Excel.

Завдання:

1.Ознайомитися з теоретичними відомостями до роботи.

2.Законспектувати алгоритм запису макроса.

3.Записати обидва макроси-приклади, виконати та відредагувати їх.

4.Створити макрос під назвою МакросЛР за завданням викладача. Призначити йому комбінацію клавіш Ctrl+F. Зберегти макрос у поточній робочій книзі. Виконати тестування макросу.

5.Внести необхідні поправки до тексту макроса.

6.Переписати текст макроса до зошиту.

7.Зберегти файл з макросом під назвою Lab_Macro.xlsm.

Теоретичні відомості

Операції, які можна виконувати за допомогою макросів VBA:

Вставити рядок тексту або формулу.

Автоматизувати процедуру, яка часто виконується. Наприклад, якщо треба підготувати місячний звіт і якщо його структура не дуже складна, то можна написати макрос, який виконає всю роботу.

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

Створити нову команду.

Створити нову кнопку на панелі інструментів для запуску написаних макросів.

Створити спрощений "зовнішній інтерфейс" для користувачів, які недостатньо знайомі з Excel. Наприклад, спеціальний шаблон для введення даних, причому ці дані не зможе випадково зіпсувати некваліфікований користувач.

Розробити нову функцію робочого листа.

Створювати закінчені додатки, керовані за допомогою макросів.

Призначення макросу кнопки на робочому аркуші

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

1.Виведемо на стрічку вкладинку «Разработчик». Для цього оберемо «Файл» – «Параметры» – «Настройка ленты» і поставити прапорець «Разработчик» (у

вікні ліворуч).

2.Виберіть команду Разработчик – Вставить - Кнопка. Потім мишкою розтягнути потрібну область на робочому аркуші, де буде знаходитися кнопка.

3.У вікні, що з’явиться, виберіть із списку ім'я макроса і клацніть на кнопці ОК. Якщо макрос ще не створено, то натисніть кнопку «Записать».

4.Для зміни тексту на кнопці, клацніть правою кнопкою мишки на кнопці, та оберіть у контекстному меню «Изменить текст».

Хід роботи

Запис макроса: приклад

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

шрифт Arial Cyr розміром 16 пунктів,

напівжирний,

виділений червоним кольором.

Щоб створити такий макрос, треба виконати наступні дії:

1.Введіть до клітини В2 такий текст “Миколаївський національний аграрний університет”.

2.Виберіть клітину В2 (зробіть її активною).

3.Натисніть кнопку «Запись макроса» у рядку стану.

4.З'явиться діалогове вікно Запись макроса.

5.Введіть нове ім'я макроса, щоб замінити стандартне ім'я Macro1. Можна вибрати, наприклад, таке ім'я: FormattingMacro.

6.Призначте цьому макросу комбінацію клавіш <Ctrl+Shift+D>, клацнувши мишкою у полі Сочетание клавиш та натиснувши потім клавіші <Shift+D>.

7.Клацніть на кнопці ОК, щоб закрити діалогове вікно Запись макроса.

8.Виберіть команду Главная - Шрифт. Виберіть полужирный шрифт Arial з розміром 16 пунктів і призначте символам червоний колір. Щоб закрити діалогове вікно Формат ячеек, клацніть на кнопці ОК.

9.Оскільки запис макроса на цьому закінчується, клацніть на кнопці Остановить запись.

Дослідження макроса

Макрос було записано до нового модулю під назвою Модуль1. Щоб переглянути текст макроса в цьому модулі, необхідно активізувати засіб Редактор Visual Basic (VBE). Це можна зробити будь-яким з двох способів.

 Натисніть комбінацію клавіш <Alt+F11>.

 Виберіть команду Разработчик - Visual Basic.

Відкриємо вікно VBE. Незважаючи на те, що наш модуль збережений в поточній робочій книзі Excel, переглянути його можна тільки у вікні засобу Редактор Visual

Basic.

У вікні Проект (Project) відображений список всіх відкритих робочих книг і надбудов. Цей список має вигляд деревовидної діаграми. Текст макроса, який було записано, зберігається в модулі Модуль1 в поточній робочій книзі. Якщо двічі клацнути на імені Модуль1, текст макроса відобразиться у вікні Програма.

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

Sub FormattingMacro()

'FormattingMacro Макрос

'Макрос форматує текст у виділених клітинах

'Сочетание клавиш: Ctrl+Shift+D

With Selection.Font

.Name = "Arial"

.FontStyle = "полужирный"

.Size = 16

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.Color = 255

.TintAndShade = 0

.ThemeFont = xlThemeFontNone End With

End Sub

Записаний макрос - це підпрограма (оскільки починається з оператора Sub), яка називається FormattingMacro.

Excel вставив коментарі у верхніх рядках макроса. Це та сама інформація, яка знаходилася в діалоговому вікні Запись макроса.

Підпрограму FormattingMacro було згенеровано засобом запису макросів.

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

Strikethrough, Superscript і Subscript привласнене значення False

(Брехня).

Це, так би мовити, побічний ефект методу, який використовується в Excel при перетворенні послідовності дій в текст макроса. Excel встановлює значення для всіх параметрів вкладинки Шрифт діалогового вікна Формат ячеек.

Тестування макроса

Перед записом макроса встановили опцію, яка призначила цьому макросу комбінацію клавіш <Ctrl+Shift+D>. Щоб протестувати макрос, верніться до Excel будь-яким з наступних способів.

Натисніть комбінацію клавіш <Alt+F11>.

Клацніть на кнопці Вид Microsoft Excel панелі інструментів Стандарт VBE.

Коли Excel стане активним, активізуйте робочий лист (він може бути в робочій книзі, яка містить VBA-модуль, або в будь-якій іншій робочій книзі). Виберіть клітину або діапазон і натисніть комбінацію клавіш <Ctrl+Shift+D>. Макрос вмить відформатує вибрані клітини (або клітину).

Перевірте роботу макроса на інших клітинах (макрос повинен працювати однаково).

Редагування макроса

Записавши макрос, можемо змінити його. Наприклад, використаємо на робочому листі шрифт розміром не 16, а 14 пунктів. У цьому випадку можна, звичайно, перезаписати макрос. Однак, оскільки ця дуже незначна зміна, краще просто відредагувати текст макроса. Для цього знайдіть оператор, в якому встановлюється розмір шрифту, і змініть число 16 на 14. Крім цього, можете видалити наступні рядки:

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlNone

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

Sub. FormattingMacro()

With Selection.Font

.Name = "Arial"

.FontStyle = "полужирный"

.Size = 16

.Color = 255

End With

End Sub

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

Другий приклад

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

1.Копіювання діапазону в буфер обміну.

2.Вибір команди Правка \ Специальная вставка. При цьому треба встановити перемикач Значение, щоб вставити замість формул значення, що обчислюються за цими формулами.

Макрос дозволяє об'єднати ці операції і виконувати їх за допомогою однієї команди.

Крім того, у вас повинна існувати можливість звернутися до цієї команди за допомогою клавіатурного еквівалента (Ctrl+Shift+V). Щоб створити цей макрос, необхідно виконати наступні дії.

1.Введіть деякі формули до діапазону клітин на другому аркуші. Підійдуть будьякі формули. Наприклад

2.Виберіть діапазон, що містить формули (С1:С5).

3.Виберіть команду Разработчик - Запись макроса.

4.З'явиться діалогове вікно Запись макроса.

5.Заповніть діалогове вікно (ім'я макроса FormulaConvert, комбінація клавіш

<Ctrl+Shift+V>).

6.Клацніть на кнопці OK, щоб почати запис макроса.

7.Поки діапазон ще не вибрано, виконайте команду Ctrl+С, щоб скопіювати його в буфер обміну.

8.Викличте контекстне меню (ПКМ), оберіть команду Специальная вставка - значения, а потім клацніть на кнопці ОК.

9.Щоб відмінити режим вставки, натисніть клавішу Esс. (Excel видалить рухому рамку навколо вибраного діапазону.)

10.Клацніть на кнопці Остановить запись або виберіть команду Разработчик -

Остановка записи.

Щоб протестувати макрос, активізуйте робочий лист, введіть які-небудь формули і виберіть деякі з них. Запустити макрос можна двома способами.

Натиснути комбінацію клавіш <Ctrl+Shift+V>.

Вибрати команду Разработчик - Visual Basic і двічі клацнути на імені макроса

FormulaConvert.

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

Призначена комбінація клавіш <Ctrl+Shift+V> діє тільки у випадку, коли відкрита відповідна робоча книга. Якщо її закрити, натиснення клавіш <Ctrl+Shift+V> ні до чого не приведе.

Записаний макрос буде виглядати таким чином:

Sub FormulaConvert()

’FormulaConvert Макрос

’Сочетание клавиш: Ctrl+Shift+V

Sub ConvertFormulas()

Selection.Copy

Selection.PasteSpecial Paste:=xlValues, Operation:= xlNone, _

SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False

End Sub

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

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

Третій оператор відміняє рухому рамку навколо вибраного діапазону (Excel згенерував цей оператор, оскільки після операції вставки натиснули клавішу <Esc>).

Можна видалити символ підкреслення у другому операторі і об'єднати два рядки в один.

Варіанти індивідуальних завдань

1.Відкрити власний файл lab6.xls. Записати макрос форматування всіх коментарів зі шрифтом Times New Roman розміром 12 пт, синього кольору. Виконати макрос на всіх заповнених листах робочої книги. Змінити в тексті макросу колір шрифту коментарів на чорний. Знов виконати макрос на всіх заповнених листах робочої книги.

2.Ввести таблицю. Записати макрос оформлення таблиці (заголовки напівжирним шрифтом, побудова рамок) та обчислення всіх формул. Скопіювати Лист1 під назвою ПробаМакроса. Видалити всі формули та оформлення з листа. Виконати записаний макрос.

3.Ввести таблицю. Записати макрос побудови об’ємної гістограми за 1-3 числовими стовбцями з коментарями. Скопіювати таблицю на Лист2. Виконати записаний макрос.

4.Ввести таблицю із завдання 3. Записати макрос сортування даних у стовбцях з оцінками по 1-2 предметах та обчислення середнього балу кожного студента. Виконати макрос на всіх заповнених листах робочої книги.

5.Ввести таблицю. Виконати в ній всі необхідні розрахунки. Записати макрос, що перетворює в клітинах з введеними формулами самі формули на числа (використовуючи спеціальну вставку). Скопіювати Лист1 під назвою ПробаМакроса. Змінити числа в таблиці. Виконати записаний макрос.