Практичне завдання № 8. Visual Basic for Applications

MICROSOFT EXCEL

Visual Basic for Applications.
Створення функцій користувача

Хід роботи:

1. Відкрити з персональної папки робочий файл.
2. Добавити новий лист, перейменувати його на “Табель” та перемістити на останнє місце в списку листів (див. завдання №4).
3. На листі “Табель” створити таблицю за наступним зразком:

- - у клітинках D4-D7 вставити позначення для днів відпустки, лікарняних та ін, так як на рисунку для пояснень та розрахунків в табелі;

- - в клітинці J9 вставити початкову дату звітного місяця (у нашому випадку – 01.01.2002);

- - клітинку K9, заповнити при допомозі формули “=J9+1”;

- - решту клітинок (L9 …AN9) заповнити при допомозі автозаповнення;

- - заповнити клітинку J8, яка вказує порядковий номер дня тижня (1 – понеділок, 2 –вівторок і т.д.) для певної дати за допомогою формули “=ДЕНЬНЕД(J9;2)”

* ДЕНЬНЕД() – функція, яка перетворює дату в числовому виді у порядковий номер дня тижня;

* J9 – клітинка, в якій знаходиться дата;

* 2 вказує на тип відліку тижня (1 – нд=1...сб=7; 2 пн=1... нд=7; 3 – пн=0... нд = 7).

- - для цієї клітинки встановити умовне форматування (“Формат Þ Условное форматирование...”), зазначивши, що якщо значення у клітинці більше або дорівнює 6, то виводити його жовтим кольором на червоному фоні;

- - за допомогою автозаповнення заповнити увесь рядок для решти днів;

- - для клітинки з зазначенням місяця записати формулу “=J9” і встановити формат числа: “Формат Þ Ячейки... Þ Число Þ Все форматы”; вибрати формат “ mmmm”;

- - аналогічну формулу записати для зазначення року, встановити формат числа: “Формат Þ Ячейки... Þ Число Þ Все форматы”; вибрати формат yyyy”.

1. Створений табель заповнити даними за приведеним нижче прикладом.

- - дані для заповнення стовпчиків №, ПІБ, Посада взяти з листа “Кадри” (у випадку відсутності такого листа заповнити за зразком);

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

- - для клітинки “Відпрацьовано, годин” директора за допомогою функції “=СУММ(J10:AN10)” провести підрахунок відпрацьованих годин

* слід відмітити, що коли для функції “=СУММ()” задається діапазон, в якому, окрім числових значень, наявні і текстові, то клітинки, в яких знаходяться текстові значення, ігноруються і на результат сумування не впливають.

- - за допомогою автозаповнення заповнити решту клітинок стовпчика “Відпрацьовано годин”.

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

1. Викликати панель інструментів “Visual Basic”.

- - вибрати пункт меню “ВидÞПанели инструментовÞVisual Basic”;

1. Запустити “Visual Basic” і відкрити існуючий модуль (у якому вже записана функція розрахунку прибуткового податку):

- - на панелі “Visual Basic” вибрати кнопку – Редактор Visual Basic;

- - у вікні проекта “Project – VBA Project” вибрати ModulesÞМодуль1;

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

* як аргументи функції будуть передаватися два параметри: 1 – діапазон даних, серед яких буде проводитися пошук необхідного дня; 2 – посилання на клітинку, яка містить літерне позначення шуканого дня (“о”, “л”, “п”); як результат функція повертає кількість шуканих днів.

- - встановити курсор у вікно редагування коду модуля (при допомозі мишки);

- - вибрати пункт меню “InsertÞProcedure…”;

- - у вікні “Add Procedure” встановити ім’я функції – “Кількість_днів”, зазначити тип – “Function”, вказати доступність – “Public” (доступна для всієї робочої книги).

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

Option Explicit

Dim Масив() As String * 3

Dim Символ As String * 3

1. Перейти на рядок функції “Кількість днів” і записати наступний код:

Public Function Кількість_днів(Масив, Символ) As Integer

Dim i As Integer

Кількість_днів = 0

For i = 1 To 31

If Масив(і) = Символ Then

Кількість_днів = Кількість_днів + 1

End If

Next i

End Function

1. Після введення коду функції необхідно провести її компіляцію для синтаксичної перевірки функції на синтаксичні помилки:

- - виконати пункт меню “DebugÞCompile VBAProject”.

* у випадку знаходження помилки, компілятор перерве свою роботу, виділить рядок з помилкою червоним кольором у вікні редагування коду та видасть повідомлення про помилку;

* якщо код функції не містить помилок, - повідомлення не буде;

* при наявності помилки, її потрібно виправити та повторити компіляцію.

1. Закрити вікно Visual Basic.
2. У вікні Excel перейти на лист “Табель”.
3. В стовпчику “Відпустка, днів” для рядка директор записати функцію

=Кількість_днів(J10:AN10;$D$7)

* де J10:AN10 – діапазон, в якому буде проводитися пошук; $D$7 – посилання на клітинку, яка містить позначення днів відпустки – “о”

1. В стовпчику “Прогули, днів” для рядка директор записати функцію:

=Кількість_днів(J10:AN10;$D$5)

1. В стовпчику “Лікарняний, днів” для рядка директор записати функцію:

=Кількість_днів(J10:AN10;$D$4)

Поиск по сайту: