Программирование_на_VBA в_Excel

КАФЕДРА ЕКОНОМІЧНОЇ КІБЕРНЕТИКИ ТА ІНФОРМАЦІЙНИХ ТЕХНОЛОГІЙ

МЕТОДИЧНІ ВКАЗІВКИ

до виконання лабораторних робіт

за темою "Програмування на VBA в середовищі Excel" з курсів "Інформатика" та

"Інформатика, обчислювальна техніка і програмування"

(для студентів всіх спеціальностей I курсу всіх форм навчання)

Рекомендовано на засіданні кафедри ЕК та ІТ

Протокол № 7 від 17.03.10

Затверджено на засіданні методради ДонДТУ

Протокол № 5 від 02.04.10

Алчевськ

ДонДТУ

2010

ББК У.в6

Методичні вказівки до виконання лабораторних робіт за темою "Програмування на VBA в середовищі Excel" з курсів "Інформатика" та "Інформатика, обчислювальна техніка і програмування" (для студ. всіх спец. I курсу всіх форм навч.) / Укл.: Н.М. Лепіло. – Алчевськ:

ДонДТУ, 2010. – 48 c.

Наведено основні теоретичні відомості та завдання до лабораторних робіт за темою "Програмування на VBA в середовищі Excel". Розглянуто роботу з макросами і об’єктами в середовищі Excel, основні елементи мови VBA, програмування алгоритмів лінійної, циклічної і розгалуженої структур, програмування з використанням масивів, створення функції користувача в Excel, створення екранної форми для вводу даних.

Укладач	Н.М. Лепіло, доц.
Відповідальний редактор	С.І. Зайцев, проф.
Відповідальний за випуск	Л.А. Мотченко, інж.

		ЗМІСТ
1	ІНТЕГРОВАНЕ СЕРЕДОВИЩЕ РОЗРОБКИ VISUAL BASIC FOR
	APPLICATION .....................................................................................		4
2	РОБОТА З МАКРОСАМИ В EXCEL..................................................		5
3	РОБОТА З ОБ’ЄКТАМИ В EXCEL.....................................................		7
	3.1	Основні поняття об'єктно-орієнтованого програмування..............	7
	3.2	Загальні принципи побудови програми на VBA............................	8
	3.3	Ієрархія об'єктів Excel ....................................................................	9
	3.4	Використання властивостей і методів об'єктів ............................	10
4	ОСНОВНІ ЕЛЕМЕНТИ МОВИ VBA................................................		12
	4.1	Алфавіт мови VBA .......................................................................	12
	4.2	Константи і змінні ........................................................................	12
	4.3	Арифметичні і логічні операції....................................................	14
	4.4	Організація введення-виведення даних........................................	16
	4.5	Програмування лінійних процесів ...............................................	17
5	ПРОГРАМУВАННЯ РОЗГАЛУЖЕНИХ І ЦИКЛІЧНИХ
	ОБЧИСЛЮВАЛЬНИХ ПРОЦЕСІВ...................................................		18
	5.1	Організація розгалужень ..............................................................	18
	5.2	Організація циклів........................................................................	21
	5.3	Приклад програми на організацію циклів і розгалужень.............	24
6	ПРОГРАМУВАННЯ З ВИКОРИСТАННЯМ МАСИВІВ .................		25
	6.1	Основні поняття про масиви ........................................................	25
	6.2	Приклади виконання операцій з масивами..................................	26
7	СТВОРЕННЯ І ВИКОРИСТАННЯ ФУНКЦІЇ КОРИСТУВАЧА .....		28
8	СТВОРЕННЯ ЕКРАННОЇ ФОРМИ ДЛЯ ВВОДУ ДАНИХ .............		29
	8.1	Створення екранної форми і розміщення на ній елементів
		керування ......................................................................................	29
	8.2	Приклад використання форми для вводу даних ..........................	30
9	ЗАВДАННЯ ДЛЯ ЛАБОРАТОРНИХ РОБІТ....................................		32
СПИСОК РЕКОМЕНДОВАНОЇ ЛІТЕРАТУРИ...................................			48

3

1 ІНТЕГРОВАНЕ СЕРЕДОВИЩЕ РОЗРОБКИ

VISUAL BASIC FOR APPLICATION

Visual Basic for Application (VBA) – це мова програмування в середовищі Microsoft Office. Сучасні середовища розробки програм є інтегрованими, тобто об'єднують в собі декілька складових, що необхідні для розробки програм. Щоб перейти в інтегроване середовище розробки (IDE) з будь-якого додатку Microsoft Office, необхідно натиснути комбінацію клавіш Alt + F11 або виконати команду Сервис → Макрос → Редактор Visual Basic (Tools → Macros → Visual Basic Editor).

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

Таблиця 1.1 – Основні складові IDE VBA

Назва вікна (па-	Команда View		Призначення
нелі)	(Вид)
Project Explorer	Project Explorer	Відображення всіх		відкритих
(Проект)	(Окно проекта)	проектів і їх складових
Toolbox (Панель	Toolbox (Панель	Містить елементи керування для
элементов)	элементов)	конструювання форм
Userform	Object (Объект)	Використовується для		створення
		форм шляхом розміщення на них
		елементів керування
Code (Програм-	Code (Програм-	Написання, редагування і пере-
ма)	ма)	гляд програми
Properties (Свой-	Properties	Завдання і відображення власти-
ства)	(Окно свойств)	востей виділених об'єктів
Object Browser	Object Browser	Швидке	отримання	інформації
(Просмотр объе-	(Просмотр объе-	про об'єкти
ктов)	ктов)
Immediate	Immediate	Швидке	виконання	інструкцій,
(Проверка)	(Окно отладки)	що вводяться у вікно
Locals	Locals	Відображення всіх змінних про-
(Локальные	(Окно локальных	цедури
переменные)	переменных)

4

Головне меню відкриває доступ до багатьом засобам розробки програм і містить пункти:

File – для збереження змін в проекті, виводу на екран і друк коду програми;

Edit – для виконання операцій редагування програми;

View – для управління відображенням на екрані елементів середовища VBA;

Insert – для вставки в проект процедур, модулів, форм та інших об'єктів;

Format – для оформлення форм і діалогових вікон в режимі їх конструювання;

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

Run – для запуску програмного коду на виконання, переривання, відновлення роботи, а також повернення перерваної програми в початковий стан;

Tools – для настройки параметрів середовища і виконання спеціальних команд;

Add-Ins – для завантаження додаткових компонентів, що розширюють можливості IDE;

Window – для організації роботи з вікнами, що відкриті в VBA; Help – для відкриття меню довідкової системи.

2 РОБОТА З МАКРОСАМИ В EXCEL

Якщо потрібне періодичне виконання яких-небудь дій в середовищі Microsoft Office, то їх можна автоматизувати за допомогою макросу. Використання макросів – це найпростіший спосіб програмування на VBA, коли програма створюється автоматично. По суті макрос – це серія команд і функцій, що зберігаються в модулі VBA. Під час запису макросу зберігається інформація про кожен крок виконання послідовності команд. Подальші запуски макросу викликають повторення ("відтворення") цих команд.

5

Додатки Microsoft Office можуть бути настроєні так, що не дозволяють запускати макроси. Тому перед тим, як приступати до створення макросів, необхідно дати команду Сервис → Макрос → Безопасность і переконатися, що перемикач Уровень безопасности встановлений в положення Средняя. Інакше необхідно задати середній рівень безпеки, закрити і знову викликати додаток Microsoft Office. Це потрібно зробити тільки один раз на початку роботи.

При запису макросу в середовищі Microsoft Excel можна використовувати абсолютні і відносні посилання на комірки. Якщо використовуються абсолютні посилання, то при запуску макросу дії виконуються з тими ж комірками, що і при запису макросу. При використанні відносних посилань адреси комірок коректуються щодо тієї комірки, яка була активною у момент початку запису макросу (або щодо області виділених комірок).

Для запису макросу в середовищі Microsoft Excel необхідно, знаходячись на відкритому листі Excel, виділити комірку (або групу комірок) для початку відліку адресації, виконати команду Сервис → Макрос → Начать запись. На екрані з'являється вікно Запись мак-

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

На екрані з'являється панель інструментів Остановить запись. Якщо вона не з'явилася, необхідно показати її на екрані за допомогою команди Вид → Панели инструментов. Якщо при запису макросу необхідно використовувати відносні посилання на комірки, слід включити відносну адресацію клацанням по кнопці Относительная ссылка на панелі Остановить запись. Цю панель не слід вбирати з екрану до кінця запису макросу. Допустимо, при необхідності, в одному макросі використовувати і абсолютні, і відносні посилання, міняючи по-

ложення кнопки Относительная ссылка.

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

кові, тому перед записом макросу необхідно ретельно продумати всю

6

послідовність записуваних дій. По закінченню виконання дій клацнути по кнопці Остановить запись на панелі Остановить запись.

Для запуску макросу необхідно, знаходячись в потрібному місці листа Excel, натиснути сполучення клавіш, що задане при запису макросу, або дати команду Сервис → Макрос → Макросы → Виділити потрібний макрос → Выполнить. Якщо макрос записаний коректно, після його запуску повинні виконуватися дії, що були записані.

Для перегляду тексту макросу виконати команду Сервис → Макрос → Макросы → Виділити потрібний макрос → Войти. При цьому запускається редактор VBA, і текст макросу з'являється на екрані у вікні редактора; при необхідності його можна відредагувати, але для цього треба знати мову програмування VBA. Текст макросу при необхідності можна надрукувати або скопіювати в буфер обміну (у момент копіювання перемикач мови повинен бути встановлений у положення Русский) і вставити в потрібне місце, наприклад на лист Excel. Для подальшого запуску макросу вікно редактора VBA треба закрити.

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

3РОБОТА З ОБ’ЄКТАМИ В EXCEL

3.1Основні поняття об'єктно-орієнтованого програмування

В даний час при розробці програм використовується технологія об'єктно-орієнтованого програмування (ООП). Об'єктно-орієнтована програма складається з об'єктів. VBA – це об'єктно-орієнтована мова програмування високого рівня. У кожному VBA-додатку є свій унікальний набір об'єктів з їх власними властивостями (характеристиками стану) і методами (командами обробки цих властивостей).

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

виконані над об'єктом. Ім'я класу вказує на тип об'єктів, що він міс-

7

тить. Класи можна розглядати як основу для створення інших об'єктів цього ж типу, тому всі об'єкти одного класу будуть діяти однаково. У системі зберігається програмний опис кожного класу, на підставі якого, при необхідності, створюються екземпляри класу.

Властивість – це характеристика об'єкту.

Методом називається дія, яку можна виконати з об'єктом.

Подія – це деяка дія, яку можна виконувати з об'єктом і на яку треба програмувати відгук (у відповідь на реакцію системи).

Розглянемо процес створення процедури обробки події, яка відбувається при клацанні по кнопці, що розміщена на робочому листі Excel. Спочатку треба визвати Excel і на поточному робочому листі за допомогою панелі інструментів Элементы управления розмістити кнопку, потім задати їй властивості. Для цього слід, знаходячись на кнопці, викликати контекстне меню, вибрати Свойства. На екрані з'явиться Окно свойств Properties, у якому перераховані всі властивості для вибраного елементу. У цьому вікні можна змінити потрібну властивість. Для кнопки основними властивостями є:

Name – ім'я кнопки, що використовується при зверненні до кнопки в програмі на VBA (наприклад, CmdПример1);

Caption – напис на кнопці.

Процедура обробки події описує ті дії, які необхідно виконати програмним шляхом у відповідь на реакцію користувача (у нашому прикладі – клацання по кнопці). Для завдання процедури необхідно, знаходячись в режимі Конструктор, встановити покажчик на кнопку, виконати подвійне клацання. Автоматично відкриється вікно програми, в якому будуть задані перший і останній оператори процедури. Текст програми набирається між ними.

3.2 Загальні принципи побудови програми на VBA

Програмний код VBA складається з наступних блоків. Оператор – це найменша одиниця VBA-коду. Він призначений

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

8

вати в одному рядку. Тоді вони розділяються двокрапкою.

Процедура – це окрема одиниця програмного коду VBA, яка має ім'я і може виконуватися самостійно. Будь-яка процедура містить один або декілька операторів.

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

Проект – включає всі модулі, форми і пов'язані з додатком об'- єкти, що відносяться до конкретного документа, причому проект зберігається разом з самим цим документом.

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

Для полегшення читання в програму вставляють пояснення (коментарі). Вони починаються з апострофа і ігноруються при виконанні програми.

3.3 Ієрархія об'єктів Excel

У VBA Microsoft Excel використовується більш 100 об'єктів, що знаходяться на різних рівнях ієрархії. Верхнім вузлом в цій ієрархії є об'єкт Application, що представляє собою додаток Excel. Кожна відкрита в Excel робоча книга представлена об'єктом Workbook, що належить до колекції Workbooks об'єкту Application. Іншими об'єктами, що часто використовуються при розробці програм на VBA в середовищі Excel, є: Range – діапазон комірок; Cells – комірка; ActiveCell –

9

активна комірка; Sheet – лист; Sheets – листи; WorkSheet – робочий лист; WorkSheets – сім'я робочих листів; DialogSheet – діалогове вікно; DialogSheets – сім'я діалогових вікон.

3.4 Використання властивостей і методів об'єктів

Для завдання або зміни властивостей об'єктам в програмі на VBA необхідно записати оператор

Iм'я об'єкта.Властивість = Значення

Властивості залежать від конкретного об'єкту. Наприклад, для зміни формату представлення даних в комірці використовуються властивості об'єкту Range.

Для зміни шрифту використовується об'єкт Font об'єкту Range, який має наступні основні властивості: Name – ім'я шрифту; Size – розмір шрифту в пунктах; Color – колір; Bold – напівжирне зображення

(True або False); Italic – курсив; Underline – підкреслений.

Для зміни кольору використовуються константи кольору:

VbBlack – чорний; VbBlue – синій; VbCyan – блакитний; VbGreen – зе-

лений; VbMagenta – рожевий; VbRed – червоний; VbWhite – білий;

VbYellow – жовтий.

Для зміни способу вирівнювання даних в комірці використовується властивість HorisontalAligment об'єкту Range, яка може приймати значення: xlLeft – вирівнювання по лівому краю; xlCenter – вирівнювання по центру; xlRight – вирівнювання по правому краю.

Щоб дістати доступ до об'єкту, потрібно мати посилання, яке на нього вказує. Посилання – це ім'я в кодах програми, що вказує на даний об'єкт. Якщо об'єкт ще не створений, програма повинна його створити, тільки після цього вона отримає на нього посилання. Використання ключового слова Set перед ім'ям змінної обов'язково, якщо змінній привласнюється значення посилання на об'єкт.

Для застосування методу слід записати оператор

Iм'я об'єкта.Метод

10