Основи офисного програмування Visual basic for applications

МОДУЛЬ № 3

ОСНОВИ ОФІСНОГО ПРОГРАМУВАННЯ. VISUAL BASIC FOR APPLICATIONS (VBA)

 

 

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

Розробка макросів та програм лінійних алгоритмів за допомогою мови програмування Visual Basic For Applications (VBA)

 

Мета: ознайомлення з середовищем VBA. Придбання навичок розробки макросів та розробки лінійних програм.

 

План

1. Використання VBA в Microsoft Excel.
2. Редактор Visual Basic Application.
3. Створення макросів.
4. Мова програмування VBA.
5. Оголошення змінних. Типи даних.
6. Використання констант.
7. Оператор присвоювання. Вирази та операції.
8. Введення та виведення даних за допомогою вбудованих вікон.
9. Функції користувача.

 

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

Використання VBA в Microsoft Excel

Visual Basic for Application (VBA) – це об’єктно-орієнтована мова програмування високого рівня, що є одним з діалектів дуже популярної мови програмування Visual Basic. За допомогою VBA можна легко й швидко створювати різні програми, навіть не будучи фахівцем в області програмування. VBA – потужний засіб розробки повнофункціональних програм, працюючих у середовищі MS Office. Це сполучення однієї із самих простих мов програмування Basic з спеціальним механізмом, який дозволяє програмам, що написані цією мовою, звертатися до об’єктів всіх базових додатків MS Office – Excel, Word, Power Point, Access та ін.

VBA має графічне інструментальне середовище, що дозволяє створювати екранні форми, а також елементи керування. VBA є вбудованою мовою програмування пакету MS Office. Особливістю VBA є те, що при створенні програми за основу береться документ: не можна створити програму незалежно від документа (робочої книги в Excel, документа в Word). З його допомогою можна створювати свої власні функції для Excel, що викликаються майстром функцій, розробляти макроси, створювати власні меню та ін.

Розглянемо  роботу VBA у середовищі Excel.

 

 

Редактор Visual Basic Application

Редактор VBA активізується з Excel одним з наступних способів:

• командою меню Сервис/Макрос/Редактор Visual Basic;
• комбінацією клавіш [Alt+F11];
• кнопкою на панелі інструментів .

Повернутися з редактора VBA у електронні таблиці Excel можна за допомогою команди меню View/Microsoft Excel або по комбінації клавіш [Alt+F11], по команді меню File/Close and Return to Microsoft Excel або по комбінації клавіш [Alt+Q] або через панель задач.

При відкритті робочої книги автоматично активізується об’єкт Application, що визначає сам Excel, і всі вбудовані в нього об’єкти. Цей об’єкт перебуває на вершині ієрархічної структури. Excel включає робочі книги (Workbooks) і різні надбудови. У свою чергу, робочі книги містять робочі аркуші (Sheets) і інші об’єкти. Нарешті, робочі аркуші включають найважливіший об’єкт – діапазон клітинок (Range).

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

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

Головне вікно редактора (рис. 1) включає такі елементи: заголовок вікна, меню, панелі інструментів, вікно проекту (Project), вікно властивостей, вікно форми (UserForm), вікно програмного коду (Code), вікно перегляду (Object Browser).

У проекті може бути будь-яка кількість об’єктів. Проект є комплекс файлів, які використовуються для розробки додатків. До складу проекту входять: листи Excel, файли проекту (розширення *.vbp), файли форм (розширення *.frm), файли основних модулів (розширення *.bas), файли модулів класів (розширення *.cls). У вікні проекту (VBAProject) представлена ієрархічна структура файлів (ліворуч), форм і модулів поточного проекту. Під смугою заголовка вікна розміщено три кнопки:

• перехід у вікно коду для виділеного компонента (View Code);
• перехід у вікно форми (View Object);
• зміна режиму перегляду (Toggle Folders).

 

Рис. 1. Вікно VBA

 

Вікно проекту в редакторі VBA активізується вибором команди Вид/Вікно проекту (View/Project Explorer) або натисканням кнопки або [Ctrl+R].

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

Модулем називається сукупність текстів програм мовою VBA. У проекті автоматично створюється модуль для кожного робочого листа і для всієї книги. Крім того, модулі створюються для кожної форми користувача, макросів і класів. За своїм призначенням модулі поділяються на два типи: модулі об’єктів і стандартні. До стандартних модулів відносяться ті, що містять макроси та функції користувача. До модулів об’єктів відносяться модулі, зв’язані з робочою книгою, робочими аркушами, формами. Модулі додаються в проект командою Вставка/Модуль (Insert/Module), у цьому випадку програмний код складається користувачем або при створенні макросу (коли програма створюється автоматично). Створені модулі мають стандартні імена Module1, Module2 і т.д. Кожен модуль відображається у вікні, що має дві області: загальну область і область підпрограм. У загальній області містяться оператори опису змінних, які є загальними для всіх процедур і функцій цього модуля. В області підпрограм міститься код програми.

Для знищення модуля треба: виділити ім’я модуля, вибрати команду File/Remove Module, клацнути у вікні діалогу на кнопці Ні.

 

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

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

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

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

Для запису макросу необхідно:

1. Виконати команду Сервис/Макрос/Начать запись.
2. У діалоговому вікні Запись макроса задати ім’я макросу й сполучення клавіш для швидкого виконання макросу (рис. 2).
3. Виконати потрібні дії.
4. Закінчити запис макросу по команді Сервис/Макрос/Остановить запись.

При записі макросу використаються команди меню, кнопки панелі інструментів, ярлички аркушів, клавіатура.

Макрос представляє процедуру, що починається із ключового слова Sub і завершується словом End Sub (див. рис. 5). Перші рядки процедури є коментарями, вони містять ім’я макросу, дату запису, автора й комбінацію клавіш. Далі знаходяться команди, записані за правилами VBA. Кожна команда відповідає виконаній дії.

Створений макрос необхідно  виконати, щоб одержати результат його роботи. Для запуску макросу на виконання є кілька способів:

- використати комбінацію клавіші Ctrl і обраної букви;

- виконати команду меню Сервис/Макрос/Макросы. У діалоговому вікні Макрос вказати в списку ім’я потрібного макросу й клацнути на кнопці Выполнить.

- скористатися кнопкою на панелі інструментів або натиснути клавішу F5.

 

 

 

Мова програмування VBA

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

Sub Obmin_valut()    ‘заголовок програми

 

Dim kurs_dol As Single, k As Integer ‘опис змінних та констант

Dim Suma_dol As Single

…

Suma_dol = k / kurs_dol   ‘оператори тіла програми

Suma_euro = k / kurs_euro

…

End Sub     ‘завершення програми

 

Для переносу частини команди на інший рядок наприкінці першого необхідно поставити пробіл, потім символ підкреслення ( _ ). Наприклад:

Іf  х  <   0  _

And в <   0 _

And  z   <   0  Then Р  =  х +  у +   z

Щоб розташувати декілька операторів в одному рядку, між ними ставиться символ двокрапки, наприклад:    а = 1   :   b = 2   :   c=3

В програмі використовують коментарі. Коментарі записуються  на початку програми для пояснення  всієї програми, перед блоком операторів або праворуч від будь-якого оператора, для пояснення ідеї тієї або іншої виконуваної операції. Перед коментарем ставиться апостроф. Знак апостроф ( ‘ ) означає, що команда або повідомлення в рядку використається як коментар і ігнорується VBA (тобто не транслюється в машинні коди). Такі рядки в процедурі відзначені зеленим кольором.

Імена змінних, констант, процедур позначають за допомогою ідентифікаторів. Ідентифікатор – це ім’я будь-якого об’єкта (константи, змінної, процедури, функції, модуля). Ідентифікатор задається за правилами:

• складається з букв, цифр та знака підкреслення;
• повинен починатися тільки з латинської букви;
• кількість символів не більше 255;
• не містить крапки, пробілу, розділових символів,
• не можна використовувати зарезервовані слова в якості ідентифікаторів;
• ім’я повинно бути унікальним та змістовним;

Приклади неправильних ідентифікаторів: 2Week, Second.Week. Приклади правильних ідентифікаторів: strMyName, strфамилия.

Зарезервовані слова (ключові або службові) – це слова, призначення яких зафіксовано і в інших випадках вони використовуватися не можуть, наприклад оператори, типи даних, класи та ін (As, Public, Then, All і ін.). Виводяться синім кольором.

Оголошення змінних. Типи даних

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

Тип даних – це множина значень (наприклад, цілі числа, текти, дати і т. п.). Кожна змінна  належить до якого-небудь типу даних. Це означає, що в певному полі пам’яті можуть зберігатися значення тільки з визначеного типу даних. Типи даних, які можуть використовуватися у VBA наведені у таблиці 1.

Таблиця 1

Типи даних

Тип	Зміст	Пам’ять, байт	Діапазон можливих значень
Integer	Цілі числа	2	[-215;215-1]
Long	Довгі цілі числа	4	[-231;231-1
Single	Дійсні числа одинарної точності	4	[-2128;-2-149] и [2-149;2128-1]
Double	Дійсні числа подвійної точності	8	[-21024;-21074] и [2-1047;21024-1]
Currency	Число з фіксованою крапкою (грошовий тип). Ціла частина - до 15 цифр, дробова - до 4 цифр.	8	[-263*10-4;(263-1)*10-4]
String	Текстові дані	1 для кожного символу	До 216 символів для рядків фіксованої довжини. До 231 символів для динамічних рядків
Byte	Цілі числа (коротке позитивне  ціле)	1	[0;28-1]
Boolean	Логічні значення	2	[True, False]
Date	Дата й час	8	[1.01.100;31.129999]
Object	Об’єкт	4	Посилання на об’єкт
Variant	Будь-яке значення з цієї таблиці	16 + (1 для кожного символу)	Діапазон не визначений

 

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