Практична робота №4
Створення програмних додатків засобами VBA
- Створити Лист Microsoft Excel.
- Заповнити «Лист 1» наступним чином:
- Заповнити стовпчик «Ціна, грн.» .
- Метою завдання є створення форми, яка імітує касовий апарат. Результатом роботи повинна бути подібна форма (візуальне оформлення може бути відмінним):
- Для початку треба зайти в редактор VBA: Сервис – Макрос – Редактор Visual Basic
- Створити форму «Обслуговування покупця» (меню Insert – UserForm):
- Виділити форму, для цього треба клацнути в будь-якому місці форми. Навколо неї з’являться 8 чорних квадратів – маркерів – це свідчення, що об’єкт виділений. В панелі властивостей (Properties)* відображуються властивості виділеного об’єкта. Використовуючи цю панель необхідно змінити Ім’я (Name) форми з UserForm1 на Sales, та заголовок (Caption) з UserForm1 на «Обслуговування покупця»:
*Якщо панелі властивостей (Properties) немає, її можна ввімкнути кнопкою F4.
Властивості (Properties) об’єкта Userform: | |
Name | Sales |
Caption | «Обслуговування покупця» |
Для візуального відокремлення об’єктів один від одного використовуємо об’єкт Frame. Панель об’єктів (Toolbox) має наступний вигляд:
У разі відсутності панелі об’єктів її можна ввімкнути в меню View (Вид):
- Далі по черзі створимо всі елементи форми
- Створити об’єкт Frame. Ввести заголовок (Caption) першого об’єкту Frame1 «Виберіть код товару».
Властивості (Properties) об’єкту Frame1: | |
Name | Frame1 |
Caption | Виберіть код товару |
- Створити об’єкт ComboBox, привласнити йому ім’я (Name) «Codtovaru». Помістити об’єкт Codtovaru в об’єкт Frame1.
Властивості (Properties) об’єкту Codtovaru: | ||
Name | Codtovaru | |
ColumnCount | 2 | Двійка означає, що у виринаючому списку буде відображатись інформація з двох совпців |
Enabled | False | При завантаженні об’єкт - неактивний |
RowSource | A2:B11 | Стовпці, з яких береться інформація |
ColumnWidths | 17 pt;45 pt | 17 pt – ширина стовпця А у виринаю чому списку; 45 pt – ширина стовпця В |
- Створити об’єкт Frame2. Ввести заголовок (Caption) «Назва товару».
- Створити об’єкт Label, привласнити йому ім’я (Name) «Nazvatovaru». Помістити об’єкт Label в середину Frame2.
Властивості (Properties) об’єкту Nazvatovaru: | ||
Name | Nazvatovaru | |
Caption | Залишити пункт пустим |
- Створити об’єкт Frame3. Ввести заголовок «Ціна, грн.».
- Створити об’єкт Label, привласнити йому ім’я «Cinatovaru». Помістити об’єкт Label всередину Frame3.
Властивості (Properties) об’єкту Cinaatovaru: | ||
Name | Cinatovaru | |
Caption | Залишити пункт пустим |
- Створити об’єкт Frame4. Ввести заголовок «Введіть кількість одиниць товару».
- Створити об’єкт TextBox, привласнити йому ім’я «Kilkist». Помістити об’єкт Label всередину Frame4.
Властивості (Properties) об’єкту Kilkist: | ||
Name | Kilkist | |
Enabled | False | Після завантаження форми об’єкт неактивний |
- Створити об’єкт CommandButton. Ввести заголовок «Порахувати».
Властивості (Properties) об’єкту CommandButton1: | ||
Name | CommandButton1 | |
Enabled | False | Після завантаження форми об’єкт неактивний |
Caption | Порахувати | Текст на кнопці |
- Створити об’єкт Frame5. Ввести заголовок «Клієнт».
- Створити два об’єкти OptionButton, привласнити їм імена «Newk» і «Oldk» відповідно. Помістити об’єкти OptionButton всередину Frame5. Ввести заголовки об’єктам OptionButton «Новий клієнт (знижка 0%)» і «Постійний клієнт (знижка %)».
Властивості (Properties) об’єкту Newk: | |
Name | Newk |
Caption | Новий клієнт (знижка 0%) |
Властивості (Properties) об’єкту Oldk: | |
Name | Oldk |
Caption | Постійний клієнт (знижка 5%) |
- Створити об’єкт Frame6. Ввести заголовок «Вартість».
- Створити об’єкт Label, привласнити йому м.’я «Summa». Помістити об’єкт Label всередину Frame6.
Властивості (Properties) об’єкту Summa: | ||
Name | Summa | |
Caption | Залишити пункт пустим |
- Створити об’єкт Frame7. Ввести заголовок «Податок, м. .».
- Створити об’єкт Label, привласнити йому м.’я «Podatok». Помістити об’єкт Label всередину Frame7.
Властивості (Properties) об’єкту Podatok: | ||
Name | Podatok | |
Caption | Залишити пункт пустим |
- Створити об’єкт Frame8. Ввести заголовок «Пенсійний фонд, м. .».
- Створити об’єкт Label, привласнити йому м.’я «Fond». Помістити об’єкт Label всередину Frame8.
Властивості (Properties) об’єкту Fond: | ||
Name | Fond | |
Caption | Залишити пункт пустим |
- Створити об’єкт Frame9. Ввести заголовок «Готівка, м. .».
- Створити об’єкт TextBox, привласнити йому м.’я «Cash». Помістити об’єкт TextBox всередину Frame9.
Властивості (Properties) об’єкту Cash: | ||
Name | Cash | |
Enabled | False | Після завантаження форми об’єкт неактивний |
- Створити об’єкт CommandButton. Ввести заголовок «Здача».
Властивості (Properties) об’єкту CommandButton2: | ||
Name | CommandButton2 | |
Enabled | False | Після завантаження форми об’єкт неактивний |
Caption | Здача | Текст на кнопці |
- Створити об’єкт Frame10. Ввести заголовок «Здача, м. .».
- Створити об’єкт Label, привласнити йому м.’я «Zdacha». Помістити об’єкт Label всередину Frame10.
Властивості (Properties) об’єкту Zdacha: | ||
Name | Zdacha | |
Caption | Залишити пункт пустим |
- Створити об’єкт CommandButton. Ввести заголовок «Завершити обслуговування».
- Привласнити ім’я «EndSale»
Властивості (Properties) об’єкту EndSale: | ||
Name | EndSale | |
Enabled | False | Після завантаження форми об’єкт неактивний |
Caption | Завершити обслуговування | Текст на кнопці |
- Створити об’єкт CommandButton. Ввести заголовок «Очистити форму».
- Привласнити ім’я «Clear»
Властивості (Properties) об’єкту Clear: | ||
Name | Clear | |
Enabled | False | Після завантаження форми об’єкт - неактивний |
Caption | Очистити форму | Текст на кнопці |
- Створити об’єкт CommandButton. Ввести заголовок «Вихід».
- Привласнити ім’я «CloseWindow»
Властивості (Properties) об’єкту CloseWindow: | ||
Name | CloseWindow | |
Enabled | True | Після завантаження форми об’єкт - активний |
Caption | Вихід | Текст на кнопці |
- Створити об’єкт Label. Ввести заголовок «Чек»
Властивості (Properties) об’єкту Label: | ||
Caption | Чек | Текст на ярлику |
- Створити об’єкт Label. Стерти текст із поля «Заголовок» (Caption) у вікні властивостей об’єкту.
- Привласнити ім’я «Chek»
Властивості (Properties) об’єкту Chek: | ||
Name | Chek | |
Autosize | True | Розмір |
Caption | Залишити пункт пустим | |
BackStyle | 1fmBackStyleOpaque | Об’єкт - непрозорий |
SpecialEffect | 3 – fmSpecialEffectEtched | Лейбл обрамляється рамкою |
Width | 80 | Ширина об’єкта = 80 пунктів |
Візуальне оформлення форми завершене. Можна приступати до написання коду програми.
- Для початку необхідно оголосити глобальні змінні, котрі будуть використовуватися в коді програми. Для цього треба натиснути правою кнопкою миші на вільному полі форми і вибрати пункт контекстного меню View Code. Далі у вікні, що з’явилося, у верхньому лівому куті треба вибрати General, а у верхньому правому – Declarations і ввести наступний код:
Код – оголошення глобальних змінних:
Public Summ, Change, Tax, PF As Currency
- Далі необхідно задати початкові значення параметрів, які будуть використовуватися у обчисленнях. Для цього, після оголошення глобальних змінних треба зробити два відступи рядку і вставити наступний код:
Код налагоджень форми після її завантаження:
Private Sub Form_Load()
Summ = 0
Change = 0
Tax = 0
PF = 0
End Sub
Private Sub UserForm_Initialize()
With Codtovaru
.RowSource = "A2:B100"
End With
End Sub
- Першою дією, яку буде виконувати користувач створюваного додатку, - це вибір статусу клієнта – постійний-непостійний. В залежності від обраної опції буде залежати подальше обчислення – надаватиметься чи ні знижка 5%.
Щоб ввести код програми конкретного об’єкту, необхідно клацнути на ньому двічі лівою кнопкою миші.
Код для об’єкту «Newk»:
Private Sub Newk_Click() | Подія |
Codtovaru.Enabled = True | Активація об’єкта Codtovaru |
Kilkist.Enabled = True | Активація об’єкта Kilkist |
CommandButton1.Enabled = True | Активація об’єкта CommandButton1 |
Cash.Enabled = True | Активація об’єкта Cash |
End Sub | Кінець події |
Код для об’єкту «Oldk»:
Private Sub Oldk_Click() | Подія |
Codtovaru.Enabled = True | Активація об’єкта Codtovaru |
Kilkist.Enabled = True | Активація об’єкта Kilkist |
CommandButton1.Enabled = True | Активація об’єкта CommandButton1 |
Cash.Enabled = True | Активація об’єкта Cash |
End Sub | Кінець події |
- Після вибору клієнта, користувач вибирає код товару, після чого виводиться інформація про товар – його назва і ціна.
Код для об’єкту «Codtovaru»:
Private Sub Codtovaru_Change()
Dim i As Long
If Codtovaru.Text <> "" Then
For i = 1 To 100
If Range("A" & CStr(i)) = Codtovaru.Text Then
Nazvatovaru.Caption = Range("B" & CStr(i))
Cinatovaru.Caption = Range("C" & CStr(i))
End If
Next i
End If
End Sub
- Після вибору/вводу коду товару користувач вводить кількість одиниць товару і натискає кнопку «Порахувати».
Код для об’єкту «CommandButton1» (кнопка «Порахувати»):
Private Sub CommandButton1_Click()
If OldK.Value = True Then
Summ = Summ + CCur(Val(Cinatovaru.Caption) * Val(Kilkist.Text))
Summ = Summ - Summ * 0.05
Else
Summ = Summ + CCur(Val(Cinatovaru.Caption) * Val(Kilkist.Text))
End If
Newk.Locked = True
OldK.Locked = True
Tax = Summ * 0.2
PF = Summ * 0.075
Chek.Caption = Chek.Caption & "Ціна:" & Cinatovaru.Caption & "*" & "Кількість:" & Kilkist.Text & Chr(13)
Chek.Caption = Chek.Caption & "Сума:" & Str(Val(Cinatovaru.Caption) * Val(Kilkist.Text)) & Chr(13)
Kilkist.Text = ""
Cinatovaru.Caption = "0"
Codtovaru.Value = ""
Nazvatovaru.Caption = ""
Summa.Caption = Str(Summ)
Podatok.Caption = Str(Tax)
Fond.Caption = Str(PF)
CommandButton2.Enabled = True
Clear.Enabled = True
End Sub
- Після натиснення на кнопку «Порахувати» вводиться відповідна інформація в поля «Вартість», «Податок» і «Пенсійний фонд». Поля «Ціна» і «Кількість одиниць товару» очищуються.
Операцію вибору товару і кількості штук можна повторити.
- По закінченню вибору товарів, покупець дає готівку і користувач натискає кнопку «Здача». Якщо готівки менше ніж сума в полі «Готівка, грн.», виводиться повідомлення, про те, що готівки недостатньо. Якщо готівки достатньо, в полі «Здача, грн.» виводиться сума здачі. Кнопка «Завершити обслуговування» активується.
Код об’єкту «CommandButton2» (кнопка «Здача»):
Private Sub CommandButton2_Click()
Change = CCur(Val(Cash.Text)) - Summ
If (Change >= 0) Then
Zdacha.Caption = Str(Change)
CommandButton1.Enabled = False
EndSale.Enabled = True
Codtovaru.Enabled = False
Else
MsgBox "Покупець дав мало готівки!", vbOKCancel + vbCritical, "Помилка"
EndSale.Enabled = False
Cash.SetFocus
End If
End Sub
- Далі користувач натискає на кнопку «EndSale» (Завершити обслуговування). В поле «Чек» виводиться остаточна інформація: знижка, податок, пенсійний фонд, дата, час тощо.
Код об’кту «EndSale» (кнопка «Завершити обслуговування»):
Private Sub EndSale_Click()
Chek.Caption = Chek.Caption & "Вартість: " & Str(Summ) & Chr(13)
If OldK.Value = True Then
Chek.Caption = Chek.Caption & "Знижка 5%: " & Str(Summ * 100 / 95 * 0.05) & Chr(13)
Else
Chek.Caption = Chek.Caption & "Знижка 0%: " & Str(Summ * 0) & Chr(13)
End If
Chek.Caption = Chek.Caption & "---------------" & Chr(13)
Chek.Caption = Chek.Caption & "ПДВ=20%: " & Str(Tax) & Chr(13)
Chek.Caption = Chek.Caption & "ПФ=7.5%: " & Str(PF) & Chr(13)
Chek.Caption = Chek.Caption & "---------------" & Chr(13)
Chek.Caption = Chek.Caption & "Готівка: " & Cash.Text & Chr(13)
Chek.Caption = Chek.Caption & "Здача: " & Str(Change) & Chr(13)
Chek.Caption = Chek.Caption & "---------------" & Chr(13)
Chek.Caption = Chek.Caption & Str(Date) & Chr(13)
Chek.Caption = Chek.Caption & Str(Time) & Chr(13)
Chek.Caption = Chek.Caption & "---------------" & Chr(13)
Chek.Caption = Chek.Caption & "Дякуємо за покупку!" & Chr(13)
Chek.Caption = Chek.Caption & "Фіскальний чек" & Chr(13)
Summ = 0
Change = 0
Tax = 0
PF = 0
CommandButton2.Enabled = False
EndSale.Enabled = False
Cash.Enabled = False
Kilkist.Enabled = False
Codtovaru.Enabled = False
Clear.Enabled = True
Newk.Locked = False
OldK.Locked = False
Newk.Value = False
OldK.Value = False
End Sub
- Під час введення даних може виникнути ситуація, коли необхідно очистити форму. Для цього створена кнопка «Clear » (Очистити форму). Також цю кнопку користувач натискає після завершення обслуговування клієнта.
Код об’єкта «Clear»:
Private Sub Clear_Click() 'кнопка "Очистити форму"
Summ = 0
Chek.Caption = ""
Summa.Caption = ""
Podatok.Caption = ""
Fond.Caption = ""
Zdacha.Caption = ""
Nazvatovaru.Caption = ""
Cinatovaru.Caption = ""
Cash.Text = ""
Codtovaru.Enabled = False
Kilkist.Enabled = False
CommandButton1.Enabled = False
Cash.Enabled = False
CommandButton2.Enabled = False
EndSale.Enabled = False
Newk.Enabled = True
OldK.Enabled = True
Newk.Locked = False
OldK.Locked = False
Newk.Value = False
OldK.Value = False
Codtovaru.Value = ""
Chek.width = 80
End Sub
- Кнопка «CloseWindow» (Вихід) необхідна для закриття форми.
Код об’єкту «CloseWindow»:
Private Sub CloseWindow_Click()
End
End Sub
- Створити на робочому аркуші книги MS Excel кнопку запуску макросу:
- Зайти в меню Сервис – Макрос – Макросы, задати ім’я макросу (латинськими літерами своє прізвище), натиснути кнопку СОЗДАТЬ.
- Ввести наступний код:
Sub Vashe prizvusche()
Sales.Show
End Sub
Зберегти зміни.
- На вільному місці панелі інструментів викликати контекстне меню, в ньому вибрати пункт ФОРМЫ.
- На панелі ФОРМЫ вибрати елемент „Кнопка” та створити її на робочому аркуші MS Excel поряд з таблицею.
- Натиснути правою кнопкою миші на створеній кнопці та вибрати пункт Назначить макрос... Вибрати макрос та натиснути кнопку ОК.
- Змінити текст на кнопці на „Відкрити форму”.
- Загалом, код програмного додатку повинен набути такого вигляду:
Public Summ, Change, Tax, PF As Currency
Private Sub Form_Load()
Summ = 0
Change = 0
Tax = 0
PF = 0
End Sub
Private Sub Clear_Click()
Summ = 0
Chek.Caption = ""
Summa.Caption = ""
Podatok.Caption = ""
Fond.Caption = ""
Zdacha.Caption = ""
Nazvatovaru.Caption = ""
Cinatovaru.Caption = ""
Cash.Text = ""
Codtovaru.Enabled = False
Kilkist.Enabled = False
CommandButton1.Enabled = False
Cash.Enabled = False
CommandButton2.Enabled = False
EndSale.Enabled = False
Newk.Enabled = True
OldK.Enabled = True
Newk.Locked = False
OldK.Locked = False
Newk.Value = False
OldK.Value = False
Codtovaru.Value = ""
End Sub
Private Sub CloseWindow_Click()
End
End Sub
Private Sub Codtovaru_Change()
Dim i As Long
If Codtovaru.Text <> "" Then
For i = 1 To 100
If Range("A" & CStr(i)) = Codtovaru.Text Then
Nazvatovaru.Caption = Range("B" & CStr(i))
Cinatovaru.Caption = Range("C" & CStr(i))
End If
Next i
End If
End Sub
Private Sub CommandButton2_Click()
Change = CCur(Val(Cash.Text)) - Summ
If (Change >= 0) Then
Zdacha.Caption = Str(Change)
CommandButton1.Enabled = False
EndSale.Enabled = True
Codtovaru.Enabled = False
Else
MsgBox "Покупець дав мало готівки!", vbOKCancel + vbCritical, "Помилка"
EndSale.Enabled = False
Cash.SetFocus
End If
End Sub
Private Sub CommandButton1_Click()
If OldK.Value = True Then
Summ = Summ + CCur(Val(Cinatovaru.Caption) * Val(Kilkist.Text))
Summ = Summ - Summ * 0.05
Else
Summ = Summ + CCur(Val(Cinatovaru.Caption) * Val(Kilkist.Text))
End If
Newk.Locked = True
OldK.Locked = True
Tax = Summ * 0.2
PF = Summ * 0.075
Chek.Caption = Chek.Caption & "Ціна:" & Cinatovaru.Caption & "*" & "Кількість:" & Kilkist.Text & Chr(13)
Chek.Caption = Chek.Caption & "Сума:" & Str(Val(Cinatovaru.Caption) * Val(Kilkist.Text)) & Chr(13)
Kilkist.Text = ""
Cinatovaru.Caption = "0"
Codtovaru.Value = ""
Nazvatovaru.Caption = ""
Summa.Caption = Str(Summ)
Podatok.Caption = Str(Tax)
Fond.Caption = Str(PF)
CommandButton2.Enabled = True
Clear.Enabled = True
End Sub
Private Sub EndSale_Click()
Chek.Caption = Chek.Caption & "Вартість: " & Str(Summ) & Chr(13)
If OldK.Value = True Then
Chek.Caption = Chek.Caption & "Знижка 5%: " & Str(Summ * 100 / 95 * 0.05) & Chr(13)
Else
Chek.Caption = Chek.Caption & "Знижка 0%: " & Str(Summ * 0) & Chr(13)
End If
Chek.Caption = Chek.Caption & "---------------" & Chr(13)
Chek.Caption = Chek.Caption & "ПДВ=20%: " & Str(Tax) & Chr(13)
Chek.Caption = Chek.Caption & "ПФ=7.5%: " & Str(PF) & Chr(13)
Chek.Caption = Chek.Caption & "---------------" & Chr(13)
Chek.Caption = Chek.Caption & "Готівка: " & Cash.Text & Chr(13)
Chek.Caption = Chek.Caption & "Здача: " & Str(Change) & Chr(13)
Chek.Caption = Chek.Caption & "---------------" & Chr(13)
Chek.Caption = Chek.Caption & Str(Date) & Chr(13)
Chek.Caption = Chek.Caption & Str(Time) & Chr(13)
Chek.Caption = Chek.Caption & "---------------" & Chr(13)
Chek.Caption = Chek.Caption & "Дякуємо за покупку!" & Chr(13)
Chek.Caption = Chek.Caption & "Фіскальний чек" & Chr(13)
Summ = 0
Change = 0
Tax = 0
PF = 0
CommandButton2.Enabled = False
EndSale.Enabled = False
Cash.Enabled = False
Kilkist.Enabled = False
Codtovaru.Enabled = False
Clear.Enabled = True
Newk.Locked = False
OldK.Locked = False
Newk.Value = False
OldK.Value = False
End Sub
Private Sub Newk_Click()
Codtovaru.Enabled = True
Kilkist.Enabled = True
CommandButton1.Enabled = True
Cash.Enabled = True
End Sub
Private Sub OldK_Click()
Codtovaru.Enabled = True
Kilkist.Enabled = True
CommandButton1.Enabled = True
Cash.Enabled = True
End Sub
Private Sub UserForm_Initialize()
With Codtovaru
.RowSource = "A2:B100"
End With
End Sub