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

Редактор VВА: загальні відомості

Завантаження редактора VВА

Для завантаження редактора VВА без завантаження модуля, що містить певний макрос (спосіб описано раніше), треба з додатка МS Ехсеl виконати команду Сервис / Макрос / Редактор Visual Basic.

Редактор VВА завантажується з вікна додатка МS Оffiсе, тому після його закриття закривається і редактор VВА.

Рис. 1.9. Текст макросу мовою VВА

Екранний інтерфейс редактора VВА

Вікно редактора VВА, як i будь-яке вікно додатка, має стандартний набір елементів: рядок заголовка, рядок меню, панелі інструментів та робочу зону (рис. 1.10).

У робочій зоні в її лівій частині можна виводити вікно проектів та вікно

властивостей об'єкта. У правій частині у вікнах коду відбувається перегляд програмного коду вибраних (2 сlіск) об'єктів.

Вікно проектів відображає ієрархічну структуру об'єктів, з якими в даний момент працює VВА:

I. Файли, що автоматично завантажуються разом з МS Ехсеl і які є закритими для доступу, наприклад, книга Fипсrеs.хls.

II. VВА-проект книги Personal.xls складається з таких об'єктів:

а) об'єктів МS Ехсеl, тобто самої книги та її робочого листа;

b) форм (якщо вони є) — діалогових вікон, що їх створив користувач;

с) модулів, в яких містяться тексти процедур та функцій, що доступні з будь-якої книги МS Ехсеl.

III. VВА-проекти всіх відкритих книг, які мають таку саму структуру, як і VВА-проект книги Рersоnal.xls.

Рис. 1.10. Елементи вікна редактора VBA

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

Існують такі види процедур: підпрограми та функції (рис. 1.10).

Підпрограма — це процедура, яка:

1) обрамляється операторами Sub іEnd Sub;

2) не повертає значення в основну програму;

3) не використовується у виразах та операціях присвоювання;

4) має таку структуру:

Функція — це процедура, яка:

1) обрамляється операторами Function і Еnd Function;

2) завжди повертає значення в основну програму;

3) використовується у виразах та операціях присвоювання змінній значення функції;

4) має таку структуру:

Макрос — це підпрограма з порожнім списком аргументів, а тому його можна викликати не тільки з іншої підпрограми чи функції, а й за допомогою комбінації клавіш, команд меню, кнопок на п/і тощо.

Функції поділяють па вбудовані та створені користувачем.

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

Формула в комірці G6 ‑ Ф(G4). Згідно із змістом комірки G4 в комірці G6 з’являться значення 9999 чи 1000.

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

Функція користувача — це програма, записана користувачем мовою VВА в модулі поточної книги або особистої книги макросів, яка належить до класу процедур Функція (тому обрамляється операторами Function та Еnd Function) та дозволяє провадити обчислення над значеннями її аргументів.

Правила створення та використання функції користувача:

1. Функції користувача пишуться мовою VBA, тому що спочатку треба завантажити Сервис / Макрос / Редактор Visual Basic.

2. Функцію користувача можна зробити:

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

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

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

3. Функції користувача пишуться в модулях, тому в шапці потрібної книги треба розгорнути шапку з модулями та

- або відкрити існуючий модуль «2 click на назві модуля;

- або вставити новий: меню Вставка / Модуль

4. В тексті коду модуля функція обрамляється двома операторами Function та End Function

5. Ім’я функції

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

- не повинно збігатися з назвою жодної функції та не збігатися з назвами операторів VBA;

6. Аргументи функції:

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

- записуються через кому;

- максимальна кількість – 29.

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

8. Функція користувача може містити:

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

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

9. Можна використовувати вбудовані функції (дод.З):

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

- якщо в VBA немає потрібної функції, її викликають з MS Excel.

10. Для того, щоб повернути результат обчислень формули, яка включає в себе функцію, наприкінці треба присвоїти результат змінній, ім’я якої збігається з ім’ям цієї функції.

Для введення створеної функції в будь-яку формулу робочого листа треба вставити її стандартним способом; користувацькі функції містяться в категорії «Определенные пользователем».

Управляючі конструкції розгалуження, вибору та циклів

Для організації простого розгалуження використовують оператор розгалуження ІF-Then.

Для організації складного розгалуження використовують ланцюжок операторів розгалуження ІF-Then:

Для організації циклів використовують оператори циклів з лічильником (Fог-То), циклів масиву (Fог Еасh-Іn) та оператори циклів з умовою (Do –While та Do-Until).

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

For <лічильник> = <нач знач> Tо <кін знач> крок<блок операторів> Next [<лічильник>]

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

Цикли з умовою використовують, коли не відомо, скільки разів треба повторити виконання блоку операторів, які утворюють тіло циклу. Такі цикли продовжують роботу, поки зазначена умова виконується (Do While), або ж навпаки, поки вказана умова не буде виконана (Do Until). Умову можна перевіряти або перед виконанням блоку операторів циклу, або після.

Рис. 1.11. Перелік циклів, які прововжують роботу

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

2. Завдання для виконання за допомогою Microsoft Excel

Завдання 1

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

Завдання 2

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

Завдання 3

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

Завдання 4

Написати процедуру, яка формує двовимірний масив розмірністю 5Х5 по наступній схемі для визначення попиту на товар А в мережі супермаркетів

1 2 3 4 5

1 4 9 16 25

1 8 27 64 125

1 16 81 256 625

1 32 243 1024 3125

Завдання 5

Маємо формуляр, що складається з назви книг, прізвища автора, назви видання і року видання. Сформувати список, впорядкований по прізвищу автора. Результат представити у вигляді таблиці в новому файлі. Для введення даних використовувати форму і елементи електронної бази бібліотеки ХНЕУ.

Завдання 6

Підприємство з метою визначення економії власних коштів надало завдання своїм системним адміністраторам створити систему моніторингу плати за розмови. Вихідними даними є розмови до А хвилин в місяць по 8 грн., а розмова понад встановлену норму оплачується з розрахунку З грн. за хвилину. Написати програму, що обчислює плату за користування телефоном для введеного часу розмов за місяць.

Завдання 7

Маємо формуляр, що складається з назви книг, прізвища автора, назви видання і року видання. Надрукувати список, впорядкований за роками видання. Результат представити у вигляді таблиці в новому файлі. Для введення даних використовувати форму і елементи електронної бази бібліотеки ХНЕУ.

Завдання 8

У хмарочосі N поверхів і всього 1 під'їзд; на кожному поверсі по 3 квартири; ліфт може зупинятися тільки на непарних поверхах. Людина сідає в ліфт і набирає № квартири M. На який поверх ліфт повинен доставити пасажира?

Завдання 9

Створіть масив даних для дослідження інвестицій за регіонами України, який має вигляд: х1,х2...хn. Визначити регіони з найбільшим обсягом інвестицій. Розмірність масиву і задане число вводити за допомогою оператора MsgBox.

Завдання 10

Дано дві матриці руху населення в Україні. Зменшити максимальну кількість елементів в кожній з цих матриць в 10 разів.

Завдання 11

Скласти програму, що імітує застосування комп'ютера в книжковому магазині. Комп'ютер запрошує вартість книг, суму грошей, внесену покупцем: якщо здачі не вимагається, друкує на екрані «спасибі»: Якщо грошей внесені більше, ніж необхідно, то друкує: «Візьміть здачу» і вказує суму здачі. Якщо грошей недостатньо, то друкує повідомлення про це і вказує недостатню суму.

Завдання 12

Маємо К заявок на отримання житлоплощі, в кожній вказано термін подачі заяви, площа займаної квартири і кількість членів сім'ї. Сформувати список подачі заяв по даті їх подання. Для введення даних використовувати форму і елементи управління.

Завдання 13

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

Завдання 14

Маємо список групи з оцінками екзаменаційної сесії. Підрахувати кількість відмінників в групі і в полі "Є заборгованість" студентам, що мають двійки надрукувати "так", а всім останнім "ні". Результати сформувати у вигляді таблиці, впорядкованої за алфавітом. Для введення даних використовувати форму і елементи управління.

Навчальне видання

Методичні рекомендації до проведення виробничої практики для студентів 3 курсу напряму підготовки 6.030506 "Прикладна статистика" денної форми навчання

Укладачі:	Раєвнєва Олена Валентинівна Горохова Ольга Іванівна Гольтяєва Людмила Анатоліївна
Відповідальний за випуск	Раєвнєва О.В.
Редактор
Коректор