Редактор VBA

Для створення процедур і функцій мовою Visual Basic for Application, перегляду та редагування макросів використовується редактор VBA. Для активізації редактору можна:

- подати команду меню Сервис – Макрос – Редактор Visual Basic;

- скористатися інструментом Редактор Visual Basic на панелі інструментів Visual Basic (для виведення панелі інструментів потрібно подати команду меню Вид – Панели инструментов - Visual Basic).

Інтерфейс редактору Visual Basic for Application включає такі основні компоненти (рис. 7.1):

- вікно проекту Project – VBA Project;

- вікно редагування коду;

- вікно властивостей Properties;

- вікно перегляду об’єктів Object Browser;

- вікно редагування форм UserForm.

Вікно проекту Project – VBA Project у редакторі VBA можна вивести командою меню View – Project Explorer або натисканням на кнопці Project Explorer на панелі інструментів Standard. У вікні подається дерево об’єктів додатку Microsoft Excel. Для кожної робочої книги, що розчинена у середовищі Excel, у дереві містяться елементи, які використовуються для створення та редагування модулів для листів робочої книги, для книги у цілому, для кожної форми користувача, яку створено у проекті, для макросів.

Вікно редактору коду можна розчинити подвійним клацанням на елементі в дереві проекту або командою меню View – Code (розчиняються вікно коду для об’єкту, який обрано в дереві). Вікно редагування коду використовується для створення підпрограм додатку – процедур і функцій, редагування макросів.

У вікні властивостей перелічені настанови властивостей об’єктів – обраної форми, елементів керування, листів робочої книги, робочої книги у цілому. Це вікно можна застосовувати для перегляду та зміни властивостей об’єктів. Для виведення вікна властивостей слід виконати команду меню View – Properties Window або скористатися інструментом Properties Window на панелі інструментів Standard.

Вікно перегляду об’єктів Object Browser відображується у редакторі VBA після виконання команди меню View – Object Browser або натискання на інструменті Object Browser на панелі інструментів Standard. У цьому вікні міститься список усіх об’єктів, які існують у системі та які можна використовувати при створенні проекту. Можна обрати будь-який об’єкт зі списку Classes і у списку Members буде відображуватися перелік властивостей і методів для даного об’єкту. При чому властивості характеризуються символом , а методи символом - . Значення властивості або методу можна переглянути у рідку стану вікна Object Browser.

Рисунок 7.1 - Вид вікна редактору VBA

Для створення вікон діалогу додатків, що розробляються, у VBA використовуються форми. Форма до проекту додається виконанням команди Insert – UserForm. У результаті до проекту додається порожня форма з панеллю елементів (рис.7.2). Використовуючи панель елементів можна помістити у форму необхідні елементи управління – командні кнопки, етикетки, текстові поля, прапорці та ін., за допомогою вікна властивостей визначити властивості форми і кожного елемента управління. Панель елементів стає активною після активізації форми. Якщо панель елементів зачинено, то вивести її у вікні редактору VBA можна за допомогою команди меню View – Toolbox.

Рисунок 7.2 - Вікно редагування форм UserForm і панель елементів

7.3 Створення функцій користувача

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

Для створення нової функції потрібно:

- активізувати вікно редактору VBA;

- додати до проекту новий модуль командою меню Insert – Module (зверніть увагу на те, що до дерева елементів проекту буде додано нову гілку Modules, до якої додаються модулі проекту – у нашому випадку Module1);

- у вікні коду модуля слід ввести нову функцію.

Функції у VBA створюються як і в середовищі Visual Basic 6.0. Створення функції може починатися із визначення статусу функції, далі повинно слідувати зарезервоване слово Function після якого визначається ім’я функції та у круглих дужках список аргументів функції. Після списку аргументів може бути визначено тип значення, яке повертає функція. Параметрами функції є дані, які визначатимуться як аргументи функції. Це можуть бути адреси чарунок, числа, блоки чарунок. Синтаксис функцій такий:

[Public | Private ] Function <ім’я функції> [(список аргументів)] [As тип]

[оператори]
[ім’я функції = вираз]

End Function

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

Обов’язковим параметром є ім’я функції, після імені визначається список аргументів. У списку аргументів параметри функції відокремлюються комами, можуть зазначатися лише іменами, а можна визначати імена аргументів і їх типи (наприклад, X As Integer).

Необов’язковий параметр [As тип] визначає тип значення, яке повертає функція (Integer, String, Byte або ін.). Якщо даний параметр пропущено, то функція повертає значення типу Variant. Обов’язково в середині функції привласнити імені функції значення, яке вона повинна повертати.

При розробці функції можна застосовувати оператори привласнення, умовний оператор If, оператор циклу For... Next, інші оператори мови Visual Basic Після створення функції можна активізувати робочу книгу Microsoft Excel і за допомогою майстра функцій помістити у будь-яку чарунку формулу із застосуванням нової функції. Створену таким способом функцію буде віднесено до категорії функцій Определенные пользователем.

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

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

якщо нарахована заробітна плата менш або рівно 85 грн., то від суми, що перевищує 17 грн., сплачується 10% як прибутковий податок;

якщо нарахована заробітна плата менш або рівно 170 грн., то від суми, що перевищує 85 грн., сплачується 15% плюс податок від 85 грн. як прибутковий податок;

якщо нарахована заробітна плата більше 170 грн., то від суми, що перевищує 170 грн., сплачується 20% податок від 170 грн. як прибутковий податок.

Для створення функції потрібно завантажити табличний процесор Excel, перейти до редактору VBA (команда меню Сервис – Макрос – Редактор Visual Basic), виконати команду меню Insert – Module для додавання до проекту нового модуля, у вікні коду модуля ввести код функції:

Function ПРИБПОД(x)

If x <= 17 Then

n = 0

Else

If x <= 85 Then

n = (x - 17) * 0.1

Else

If x <= 170 Then

n = (x - 85) * 0.15 + 6.8

Else

n = (x - 170) * 0.2 + 19.55

End If

End If

End If

ПРИБПОД = n

End Function

Функція має ім’я ПРИБПОД, приймає для обробки значення одного аргументу, яке поміщається в змінну х. У тілі функції здійснюється аналіз значення змінної х: якщо х менше або рівно 17, то змінній n привласнюється значення 0, інакше якщо х менше або рівно 85, то змінній n привласнюється значення (x - 17) * 0.1 – 10% від нарахованої заробітної плати мінус 17, інакше якщо х менше або рівно 170, то змінній n привласнюється значення (x - 85) * 0.15 + 6.8, інакше змінна n дорівнюватиме (x - 170) * 0.2 + 19.55 – податок з суми, що перевищує 170 грн. Далі імені функції привласнюється значення змінної n – значення, яке відповідає суму розрахованого прибуткового податку.

Після створення функції можна перейти у вікно поточної робочої книги і за допомогою майстра функції створити формулу із застосуванням щойно створеної функції. Функцію можна обрати, розчинивши категорію Определенные пользователем. Таблиця із застосуванням функції ПРИБПОД може виглядати так:

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

Якщо аргументом функції є діапазон чарунок, то VBA працює з ним як з масивом, індексація елементів якого починається з 1. Щоб дізнатися скільки рядків і стовпців міститься в діапазоні можна використовувати властивості Rows і Columns, які відповідають колекції, що складається з усіх рядків або стовпців діапазону. Оскільки кожна колекція характеризується властивістю Count – кількість елементів у колекції, то визначити загальну кількість рядків, колонок у діапазоні, що є аргументом функції, не складно.

Наприклад, якщо аргументом функції є діапазон з іменем Block, то можна помістити у змінні x і y значення кількості рядків і стовпців у блоці чарунок:

x = Block.Rows.Count

y = Block.Columns.Count

Щоб звернутися до чарунки, яка є верхнім лівим кутом блоку, потрібно зазначити ім’я діапазону та індекси елементів масиву Block(1, 1), чарунка, яка є правим нижнім кутом блоку, відповідає елементові масиву Block(x, y).

Для прикладу можна створити функцію, за допомогою якої менеджер на основі даних про реалізацію товарів у поточному періоді може швидко визначити розмір знижки, яка надається конкретному покупцеві. Тим покупцям, які протягом певного періоду придбали товарів більше або рівно ніж на 10 000 грн., надається знижка у розмірі 3%. Якщо покупець придбав товарів більше або рівно ніж на 5 000 грн., він може отримати знижку у 1,5%. Якщо покупець придбав товарів менше ніж на 5 000 грн., однак здійснив більше трьох операцій по закупівлі товару, він може розраховувати на знижку в 1%. Іншим покупцям знижка не надається. Для автоматизації визначення розміру знижки даному покупцеві можна створити таку функцію:

Function Zn(Покупець, УсіПокупці, СумиРеалізації)

N = УсіПокупці.Rows.Count

s = 0

k = 0

For i = 1 To N

If УсіПокупці(i, 1) = Покупець Then

k = k + 1

s = s + СумиРеалізації(i, 1).Value

End If

Next i

If s >= 10000 Then

Zn = 3

Else

If s >= 5000 Then

Zn = 1.5

Else

If k > 3 Then

Zn = 1

Else

Zn = 0

End If

End If

End If

End Function

Прокоментуємо функцію. Функція Zn містить три аргументи:

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

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

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

Оскільки блоки чарунок, які є аргументами функції, містять по одній чарунці у стовпцях і декілька чарунок у рядках, потрібно визначити лише кількість чарунок у рядку хоча б одного з блоків (блоки повинні бути пропорційного розміру). Для цієї мети використовується змінна N, якій привласнюється значення УсіПокупці.Rows.Count – загальна кількість рядків у масиві чарунок з ім’ям УсіПокупці.

Змінним s – підсумкова сума реалізації та k – кількість операцій привласнюється значення 0.

Далі виконується обробка значень усіх чарунок масивів. Індексація елементів масивів здійснюється від 1 до N. Усередині циклу якщо назва чергового елементу масиву УсіПокупці співпадає зі значенням аргументу функції Покупець, то на 1 збільшується загальна кількість операцій і накопичується значення у змінній s – підсумкова сума реалізації. При розрахунку підсумку здійснюється звернення до чергового елементу масиву СумиРеалізації, а саме до значення властивості Value елементів – уміст чарунок електронної таблиці.

Після завершення роботи циклу в залежності від значень змінних s і k привласнюється нове значення імені функції Zn, це значення функція і повертатиме.

Під час роботи з електронною таблицею можна буде звернутися до майстра функцій, обрати функцію Zn з категорії Определенные пользователем, визначити, наприклад так, аргументи функції:

На листі робочої книги дані можуть виглядати таким чином:

Для наочності створено зведену таблицю, у якій містяться дані про загальну суму реалізації та кількість операцій по кожному клієнту. Для того, щоб не визначати розміри знижок вручну у чарунках E16, Е18, Е20 створено формули із застосуванням функції Zn. Зверніть увагу на те, що після створення формули з функцією у чарунці E16 перед копіюванням її потрібно відредагувати – змінити адреси чарунок у аргументах - діапазонах на абсолютні для того, щоб під час копіювання вони не змінювалися.

7.4 Автоматизація розрахунків на робочому листі

Мова Visual Basic for Application дає змогу для автоматизації розрахунків на листах робочих книг використовувати елементи управління – командні кнопки, перемикачі, прапорці та ін. Для створення цих об’єктів слід вивести панель елементів за допомогою інструмента Элементы управления на панелі інструментів Visual Basic або виконавши команду меню Вид – Панели инструментов - Элементы управления. У результаті у вікні програми Microsoft Excel з’явиться панель елементів, яка схожа з панеллю елементів середовища Visual Basic 6.0:

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

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

Подвійне клацання лівою кнопкою миші на об’єкті дає змогу створити процедуру для обробки події за замовчуванням для об’єктів даного класу. Наприклад, після подвійного клацання на командній кнопці можна почати створення процедури, яка буде обробляти подію Click - клацання лівою кнопкою миші на командній кнопці. За допомогою списку, що розчиняється, Procedure можна обрати іншу подію. Процедура обробки події буде створюватися у вікні редактору VBA, буде пов’язана з тим листом робочої книги, на якому розташовано елемент управління.

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

Можна працювати з об’єктом Range, якому відповідає конкретна чарунка або діапазон чарунок. Об’єкт Range характеризується властивістю Value. Властивість Value – це вміст чарунки. Наприклад, для запису у чарунку D3 числа 4 можна подати команду:

Range("D3").Value = 4

За допомогою наступної команди в усі чарунки діапазону D3:E5 записується значення 0:

Range("D3:E5").Value = 0

До об’єкту Range можна застосувати метод Select, який призводить до виділення чарунок певного діапазону. Для виділення чарунок діапазону А2:А5 можна виконати команду:

Range("a3:a5").Select

Під час створення програм часто використовується властивість об’єктів Application, Worksheet, Range, Selection Cells(i, j), яка повертає об’єкт – певну чарунку листа робочої книги. У круглих дужках визначається адреса чарунки, при чому і – номер рядка, j – номер стовпця. Об’єкт Cells(i, j) характеризується властивостями:

- Value – уміст чарунки;

- NumberFormat – числовий формат;

- Formula – уміст чарунки, формула у звичайному вигляді;

- Font – шрифт символів;

- FormulaR1C1 – формула у форматі R1C1.

Можна навести приклади зміни властивостей об’єкту Cells(i, j):

Cells(1, 1).Value = 23	У чарунку А1 записується число 23.
Cells(20, 3).Formula = "Разом:"	У чарунку С20 записується текст “Разом:”.
Cells(4, 2).NumberFormat = "0.00"	Для чарунки В4 визначається числовий формат з двома знаками після десяткової крапки.
Cells(5, 6).NumberFormat = "0.00%"	Для чарунки F5 визначається процентний формат.
Cells(3, 1).Formula = "=A1 + A2"	У чарунку А3 записується формула =A1 + A2.
Cells(3, 1).Font.Bold = True	Для чарунки А3 визначається напівжирний шрифт.
Cells(3, 1).Font.Size = 16	Для чарунки А3 визначається розмір шрифту 16 пунктів.
Cells(3, 1).Font.Color = QBColor(9)	Для чарунки А3 визначається синій колір символів.

Введення формули у форматі FormulaR1C1 дає можливість визначити як абсолютні так і відносні стосовно поточної чарунки адреси чарунок у формулі. При застосуванні абсолютних адрес чарунок можна визначати номери рядків (R – row) і номери стовпців (C – column). Так, наприклад, у рядку коду

Cells(5, 1).FormulaR1C1 = "=R1C1+R2C1"

у чарунку А5 вводиться формула =$A$1+$A$2.

Можна також формувати формулу з адресами чарунок, які задаються відносно поточної, активної чарунки. Відносні значення визначають зсув на певну кількість рядків, стовпчиків, значення зсуву задається завжди у квадратних дужках. Так у рідку коду

Cells(3, 2).FormulaR1C1 = "=R[-2]C+R[-1]C"

у чарунку В3 вводиться формула =В1+В2. Адреса чарунки В1 визначається так: чарунка на 2 рядка вище поточної чарунки В3 і того ж рядка.

Використання елементів управління на листах робочої можна розглянути на прикладі кнопки “Розрахунок сум”, за допомогою якої можна розрахувати суми по рядках і колонках для всіх чарунок виділеного діапазону. Тобто якщо виділити, наприклад, блок чарунок B2:C3, то у чарунку D2 буде записано суму значень, які знаходяться в чарунках B2:C2, у чарунку D3 – суму значень чарунок B3:C3, у чарунку В4 - суму значень чарунок B2:В3 і т.д.

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

Вивести панель елементів за допомогою інструмента Элементы управления на панелі інструментів Visual Basic або виконавши команду меню Вид – Панели инструментов - Элементы управления.

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

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

У вікні властивостей визначити властивості кнопки Name – cmdSum і Caption – Розрахунок сум.

Виконати подвійне клацання лівою кнопкою миші на командній кнопці для створення процедури, яка буде обробляти подію за замовчуванням для об’єктів даного класу – подію Click (клацання лівою кнопкою миші на командній кнопці). У вікні коду ввести уміст процедури:

Дані в рядку коду	Коментар
Sub cmdSum _Click()	Заголовок процедури, яка виконуватиметься після натискання на кнопці “Розрахунок сум”.
n = Selection.Rows.Count	Змінній n привласнюється значення кількості рядків у виділеному діапазоні чарунок.
m = Selection.Columns.Count	Змінній m привласнюється значення кількості стовпців у виділеному діапазоні чарунок.
' Підсумки по рядках	Коментар.
For i = 1 To n	Для i що змінюється від 1 до n виконуються дії (для всіх чарунок рядка i).
s = 0	Змінній s привласнюється значення 0.
For j = 1 To m	Для j що змінюється від 1 до m виконуються дії (для всіх чарунок стовпця j).
s = s + Selection.Cells(i, j).Value	У змінній s накопичується сума значень чарунок певного рядка.
Next j	Кінець циклу обчислення підсумкового значення по рядку.
Selection.Cells(i, m + 1).Value = s	У чарунці з адресою – певний рядок і колонка m + 1 (чарунка ліворуч від останньої чарунки виділеного діапазону) виводиться значення суми, що обчислено.
Next i	Кінець циклу обчислення підсумкових значень по всіх рядках.
' Підсумки по стовпцях	Коментар.
For j = 1 To m + 1	Для j що змінюється від 1 до m+1 виконуються дії (для всіх чарунок стовпця j). У циклі обробляються значення для m+1 колонок тому, що суми слід розрахувати і для чарунок, які є підсумками по рядках.
s = 0	Змінній s привласнюється значення 0.
For i = 1 To n	Для i що змінюється від 1 до n виконуються дії (для всіх чарунок рядка i).
s = s + Selection.Cells(i, j).Value	У змінній s накопичується сума значень чарунок певного стовпчика.
Next i	Кінець циклу обчислення підсумкового значення по стовпчику.
Selection.Cells(n + 1, j).Value = s	У чарунці з адресою – рядок n + 1 і колонка j (чарунка нижче останньої чарунки виділеного діапазону) виводиться значення суми, що обчислено.
Next j	Кінець циклу обчислення підсумкових значень по всіх стовпчиках.
End Sub	Кінець процедури.

Після створення процедури можна активізувати робочий лист, на якому створено кнопку “Розрахунок сум”, вийти з режиму конструктору, виділити будь-який діапазон чарунок і натиснути на кнопці “Розрахунок сум” для перевірки правильності виконання розрахунків.

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

Після натиснення на кнопці “Розрахунок” буде здійснено обчислення суми і відсотка відхилень від плану реалізації по кожній товарній групі, незалежно від кількості рядків у таблиці нижче заповнених даних буде додано підсумковий рядок для розрахунку загальних значень прогнозу щодо реалізації, фактичної реалізації, а також підсумкові значення відхилень. Далі здійснюватиметься розрахунок питомої ваги фактичної реалізації по кожній товарній групі в загальному обсязі реалізації. У результаті виконання процедури у чарунки електронної таблиці будуть записані не результати розрахунків, а формули обчислення показників.

З кнопкою “Розрахунок” слід пов’язати таку процедуру:

Дані в рядку коду	Коментар
Private Sub cmdR_Click()	Заголовок процедури, яка виконуватиметься після натискання на кнопці “Розрахунок”.
k = 0	Змінній k, яка використовується для підрахунку заповнених рядків таблиці, привласнюється значення 0.
For i = 4 To 60	Для і від 4 до 60 виконуються дії в циклі. 4 це номер першого заповненого рядка таблиці, 60 номер максимального можливого заповненого рядка.
If Cells(i, 3).Value <> 0 Then	Якщо для рядка і фактичне значення реалізації не дорівнює 0, то...
k = k + 1	Змінна k збільшується на 1.
Cells(i, 4).FormulaR1C1 = "=RC[-1]-RC[-2]"	У чарунку Cells(i, 4) – D4, D5... для виведення суми відхилення фактичної реалізації від прогнозного значення записується формула =Cі-Bi (=C4-B4, =C5-B5...). Операнди формули визначаються так: чарунка того ж рядка, що і визначена (D4, D5...), і стовпця з номером –1 – стовпчик ліворуч мінус чарунка того ж рядка і стовпця з номером –2 – на 2 стовпчика ліворуч.
Cells(i, 5).FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-3]*100"	у чарунку Cells(i, 5) для виведення відсотку відхилення фактичної реалізації від прогнозного значення записується формула записується формула =(Cі-Bі)/Bі*100 (=(C4-B4)/B4*100...).
Cells(i, 5).NumberFormat = "0.00"	Для чарунки, у якій виводиться відсоток відхилення, визначається числовий формат з двома знаками після десяткової крапки.
End If	Кінець оператору If.
Next i	Кінець циклу для розрахунку відхилень і визначення кількості заповнених рядків таблиці.
Cells(k + 4, 1).Formula = "Разом:"	У чарунці з адресою рядок - k + 4, стовпчик 1 виводиться текст "Разом:".
Cells(k + 4, 2).FormulaR1C1 = "=Sum(R4C2:R[-1]C2)"	У чарунку для розрахунку підсумкового значення по прогнозних сумах реалізації вводиться формула =СУММ(B4:B(k+4-1)). Адреси чарунок діапазону: R4C2 - $B$4, R[-1]C2 – чарунка попереднього рядка відносно чарунки (k + 4, 2) і другого стовпчика.
Cells(k + 4, 3).FormulaR1C1 = "=Sum(R4C3:R[-1]C3)"	У чарунку для розрахунку підсумкового значення по сумах фактичної реалізації вводиться формула =СУММ(С4:С(k+4-1)).
Cells(k + 4, 4).FormulaR1C1 = "=Sum(R4C4:R[-1]C4)"	У чарунку для розрахунку підсумкового значення по сумі відхилень вводиться формула ==СУММ(D4:D(k+4-1)).
Cells(k + 4, 5).FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-3]*100"	У чарунку для розрахунку підсумкового значення по відсотку відхилення вводиться формула =(C(k+4)-B(k+4))/B(k+4)*100.
Cells(k + 4, 5).NumberFormat = "0.00"	Для чарунки, у якій виводиться підсумок по відсотку відхилення, визначається числовий формат з двома знаками після десяткової крапки.
n = Trim(k + 4)	Змінній n привласнюється значення k + 4 у символьному форматі.
For i = 4 To k + 3	Для і від 4 до k+3 (для всіх інформаційних рядків таблиці) виконуються дії в циклі.
Cells(i, 6).FormulaR1C1 = "=RC[-3]/R" & n & "C3"	У чарунку чергового рядка поміщається формула =Ci/C(k+4).
Cells(i, 6).NumberFormat = "0.00%"	Для чарунок, в яких виводиться питома вага реалізації в загальному обсязі реалізації, встановлюється процентний формат.
Next i	Кінець циклу.
End Sub	Кінець процедури.

Після створення процедури можна скористатися кнопкою “Розрахунок”. Таблиця – приклад після застосування командної кнопки буде виглядати так:

7.5 Робота з формами у середовищі VBA

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

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

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

Для початку розрахунку користувач може натиснути на відповідному інструменті на панелі інструментів після чого з’явиться така форма:

Після вибору виду знижки і натискання на кнопці “Розрахунок” буде виконано відповідну процедуру для розрахунку підсумків по інформаційних рядках і документу в цілому. Після виконання процедури, яка пов’язана з командною кнопкою “Розрахунок” таблиця буде виглядати так:

Для реалізації поставленої задачі потрібно у вікні редактору VBA створити нову форму за допомогою команди меню Insert – UserForm. У результаті виконання команди до дерева основних об’єктів проекту буде додано гілку Forms, до якої належить об’єкт UserForm1 – вікно нової форми користувача. Коли вікно форми активно за допомогою панелі елементів можна створювати об’єкти у формі та визначати їх властивості з використанням вікна властивостей Properties. Для розробки форми, за допомогою якої можна визначити розмір та здійснити розрахунок показників документу “Видаткова накладна”, потрібно створити об’єкти і визначити їх властивості за описом:

Об’єкт	Властивість
Найменування	Значення
Форма UserForm1	Name	frmRoz
Caption	Розрахунок показників документу
BackColor	білий
Font	Times New Roman, кирилиця, звичайний, 11 пунктів
Командна кнопка CommandButton	Name	cmdRozr
Caption	Розрахунок
Командна кнопка CommandButton	Name	cmdV
Caption	Відміна
Група Frame	Name	FraZn
Caption	Оберіть вид знижки для даного покупця
BackColor	білий
Перемикач OptionButton	Name	opt1
Caption	знижка не надається
BackColor	білий
Перемикач OptionButton	Name	opt2
Caption	знижка 1%
BackColor	білий
Перемикач OptionButton	Name	opt3
Caption	знижка 2%
BackColor	білий

Після створення форми можна починати розробку процедур, які обробляють події – клацання лівою кнопкою миші на командних кнопках. Для цього потрібно виконати подвійне клацання лівою кнопкою миші на відповідній кнопці. Щоб створити процедуру, яку буде виконано якщо користувач натисне на кнопці “Відміна”, потрібно двічі клацнути на цій кнопці. У результаті розчиниться вікно коду з заголовком і завершальним рядком процедури:

Private Sub cmdV_Click()

End Sub

Всередині процедури за допомогою операторів мови VBA слід описати дії, до повинні виконуватися, якщо відбудеться подія Click з даним об’єктом cmdV. У даному випадку слід завершити роботу за допомогою оператору End. Процедура буде виглядати так:

Private Sub cmdV_Click()

End

End Sub

Аналогічним чином слід приступити до створення процедури, яка обробляє подію Click для командної кнопки “Розрахунок”. Процедуру потрібно створити за описом:

Рядок коду	Коментар
Private Sub cmdRozr_Click()	Заголовок процедури, що виконується щоразу після клацання лівою кнопкою миші на командній кнопці у формі cmdRozr (Розрахунок).
If opt1.Value = True Then	Якщо обрано перемикач opt1 (знижка не надається), то
Range("B6").Value = 0	у чарунку B6 записується число 0
Else	інакше
If opt2.Value = True Then	якщо обрано перемикач opt2 (знижка 1%), то
Range("B6").Value = 1	у чарунку B6 записується число 1
Else	інакше (обрано третій перемикач)
Range("B6").Value = 2	у чарунку B6 записується число 2.
End If	Кінець внутрішнього оператору If.
End If	Кінець зовнішнього оператору If.
k = 0	Змінній k привласнюється значення 0. Змінна k буде використовуватися як лічильник інформаційних рядків документу.
For i = 8 To 50	Для і, що змінюється від 8 (перший інформаційний рядок документа) до 50 (максимально можлива кількість рядків) виконуються дії.
If Cells(i, 4).Value <> 0 Then	Якщо вміст чарунки Cells(i, 4) - чарунки і-го рядка та 4-ої колонки не дорівнює 0 (у документі є черговий інформаційний рядок), то
Cells(i, 5).FormulaR1C1 = "=RC[-1]*(100-R6C2)/100"	У чарунку Cells(i, 5) – чарунку і-го рядка та 5-ої колонки поміщається формула розрахунку ціни зі знижкою =RC[-1]*(100-R6C2)/100. Формулу можна прочитати так: =чарунка того ж рядка, що і чарунка з формулою, і колонки зі зсувом на –1 (колонки, що розташована ліворуч) * на (100 – чарунка R6C2 при абсолютній адресації це чарунка 6-го рядка та 2-го стовпця B6)/100.
Cells(i, 5).NumberFormat = "0.00"	Значення чарунки Cells(i, 5) перетворюється на числовий формат з двома знаками після десяткової крапки.
Cells(i, 6).FormulaR1C1 = "=RC[-3]*RC[-2]"	У чарунку Cells(i, 6) – чарунку і-го рядка та 5-ої колонки поміщається формула розрахунку суми без ПДВ і без знижки =RC[-3]*RC[-2]. Формулу можна прочитати так: =чарунка того ж рядка і колонки зі зсувом на –3 (кількість) * на чарунку того ж рядка і колонки зі зсувом на –2 (ціна без ПДВ).
Cells(i, 7).FormulaR1C1 = "=RC[-4]*RC[-2]"	У чарунку Cells(i, 7) – чарунку і-го рядка та 7-ої колонки поміщається формула розрахунку суми без ПДВ і зі знижкою =RC[-4]*RC[-2]. Формулу можна прочитати так: =чарунка того ж рядка і колонки зі зсувом на –4 (кількість) * на чарунку того ж рядка і колонки зі зсувом на –2 (ціна без ПДВ зі знижкою).
k = k + 1	Значення k – кількості інформаційних рядків у документі збільшується на 1.
End If	Кінець оператора If.
Next i	Змінна циклу збільшується на 1.
If k > 0 Then	Якщо k > 0, то виконуються наступні дії. У іншому випадку, коли у документі немає інформаційних рядків, процедура завершує свою роботу.
Cells(k + 8, 1).FormulaR1C1 = "Разом:"	У чарунку з адресою (рядок k + 8, стовпчик 1) вводиться новий уміст "Разом:".
Cells(k + 8, 1).Font.Bold = True	Шрифт чарунки (k + 8, 1) перетворюється у напівжирний.
Cells(k + 8, 6).FormulaR1C1 = "=Sum(R8C6:R[-1]C)"	У чарунку з адресою (рядок k + 8, стовпчик 6) вводиться формула розрахунку підсумку по сумі без ПДВ – сумування чарунок блоку R8C6 (рядок 8, колонка 6 – перша інформаційна чарунка даного показника): чарунка R[-1]C, яка розташована на рядок вище від чарунки (k + 8, 6) і у тому ж стовпчику.
Cells(k + 8, 7).FormulaR1C1 = "=Sum(R8C7:R[-1]C)"	У чарунку (k + 8, 7) вводиться формула для розрахунку суми без ПДВ зі знижкою.
Cells(k + 9, 5).FormulaR1C1 = "Загальна сума знижки:"	У чарунку (k + 9, 5) вводиться текст "Загальна сума знижки:".
Cells(k + 9, 5).Font.Bold = True	Шрифт чарунки (k + 9, 5) перетворюється у напівжирний.
Cells(k + 9, 7).FormulaR1C1 = "=R[-1]C[-1]-R[-1]C"	У чарунку з адресою (рядок k + 9, стовпчик 7) вводиться формула розрахунку загальної суми знижки =R[-1]C[-1]-R[-1]C.
Cells(k + 10, 5).FormulaR1C1 = "ПДВ:"	У чарунку (k + 10, 5) вводиться текст "ПДВ:".
Cells(k + 10, 5).Font.Bold = True	Шрифт чарунки (k + 10, 5) перетворюється у напівжирний.
Cells(k + 10, 7).FormulaR1C1 = "=R[-2]C*0.2"	У чарунку з адресою (рядок k + 10, стовпчик 7) вводиться формула розрахунку суми ПДВ =R[-2]C*0.2.
Cells(k + 11, 5).FormulaR1C1 = "Усього з ПДВ:"	У чарунку (k + 11, 5) вводиться текст "Усього з ПДВ:".
Cells(k + 11, 5).Font.Bold = True	Шрифт чарунки (k + 11, 5) перетворюється у напівжирний.
Cells(k + 11, 7).FormulaR1C1 = "=R[-3]C+R[-1]C"	У чарунку (k + 11, 7) вводиться формула розрахунку суми реалізації з ПДВ =R[-3]C+R[-1]C.
Range(Cells(k + 8, 6), Cells(k + 11, 7)).Font.Bold = True	Шрифт діапазону чарунок з підсумковими значеннями перетворюється на напівжирний.
Range(Cells(k + 8, 6), Cells(k + 11, 7)).Font.Italic = True	Шрифт діапазону чарунок з підсумковими значеннями перетворюється у курсив.
End If	Кінець If.
End	Завершення виконання програми.
End Sub	Кінець процедури.

Після завершення створення форми і процедур, які з нею пов’язані, потрібно до проекту додати новий модуль командою меню Insert – Module, у вікні модуля створити процедуру – макрос, який буде виконуватися після натискання на відповідній кнопці на панелі інструментів користувача. Макрос буде виконувати лише одну дію – активізацію форми frmRoz. Це можна здійснити за допомогою методу Show, який буде застосовуватися по відношенню до об’єкту frmRoz. Макрос буде виглядати так:

Sub VN()

frmRoz.Show

End Sub

Далі залишилося лише визначити зв’язок між макросом і відповідною кнопкою на панелі інструментів:

1) Команда меню Вид – Панели инструментов – Настройка або команда Настройка контекстного меню панелей інструментів.

2) Якщо панелі інструментів користувача не існує, на вкладці Панели инструментов скористатися кнопкою Создать і визначити ім’я панелі інструментів (наприклад, “Користувач” або “Документи”).

3) На вкладці Команди у списку Категории обрати Макросы, перетягти об’єкт Настраиваемая кнопка на відповідну панель інструментів.

4) Викликати контекстне меню кнопці на панелі інструментів користувача, виконати команду контекстного меню Назначить макрос, обрати макрос VN, натиснути на кнопці Ок.

5) У контекстному меню кнопки визначити її ім’я “Розрахунок видаткової накладної”.

6) Зачинити вікно діалогу команди Настройка.

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

7.6 Контрольні запитання

1. Що є об’єктом Visual Basic for Application?

2. Чим відрізняється поняття “властивість” у Visual Basic for Application від аналогічного поняття у Visual Basic?

3. Які основні елементи можуть знаходитися у вікні редактору VBA? Як управляти їх виведенням?

4. Як створити нову функцію Microsoft Excel? Як нею скористатися?

5. Що є об’єктом Range?

6. Що означає властивість FormulaR1C1?

7. Як створити командну кнопку на робочому листі Microsoft Excel?

8. Як створити нову форму?

7.7 Практичні завдання