Програмування в Microsoft Office

Графік і діаграма з областями мають схоже оформлення. Горизонтальна лінія -- це вісь X, вертикальна -- вісь Y. Ці осі використовуються при побудові графіків. На стовпчастій діаграмі осі повернені на 90 градусів, вісь Х розташована ліворуч.

Гистограмма -- це стовпчаста діаграма з розташуванням осі Х знизу. Є й тривимірні варіанти таких діаграм. Циліндрична, конічна, пірамідальна -- все це різновиду гистограмм.

Тривимірні діаграми мають три осі. При цьому вісь Х розташована знизу. Вертикальна вісь називається Z. Вісь Y спрямована як би вглиб, забезпечуючи тривимірність зображення. Призначення осей можна не запам'ятовувати: існують способи довідатися це в ході створення або редагування діаграми.

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

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

На третьому етапі задайте різні параметри діаграми за допомогою наступних вкладок.

Заголовки -- тут задають назва діаграми в цілому, осі Х й осі Y.

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

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

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

Подписи данных -- тут визначають відображення тексту або значення як підпис даних.

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

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

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

На останньому етапі роботи майстра визначається місце розміщення діаграми - на поточному або новому робочому аркуші тієї ж книги. Клацніть на кнопці Готово -- діаграма буде створена й розміщена.

1.10 Табличний процесор Excel - рішення пошукових завдань лінійного програмування

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

Всі завдання цього типу вирішуються за допомогою інструмента Excel Пошук рішення. Цей режим викликається за допомогою пунктів меню Сервис\Поиск решения, при цьому на екрані виникає вікно наступного виду:

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

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

Поля Ограничения служать для відображення списку граничних умов поставленого завдання. Система обмежень організується шляхом вказівки на комірки із записаними формулами командами Добавить, Изменить, Удалить. При цьому необхідно вказати вид порівняння за допомогою вікна введення обмежень (рис.1.6), у якому є присутнім посилання на комірку з формулою обмеження, знак порівняння. У поле Ссылка на ячейку вводяться адреси комірок або діапазону, на значення яких накладаються обмеження. Зі списку, що розкривається, вибирається умовний оператор, який необхідно розмістити між посиланням і його обмеженням. Щоб приступити до набору нової умови, натисніть кнопку Додати.

Команда Выполнить служить для запуску пошуку рішення поставленого завдання. Команда Закрыть служить для виходу з вікна діалогу без запуску пошуку рішення поставленого завдання. При цьому зберігаються установи, зроблені у вікнах діалогу, що з'являлися після натискань на кнопки Параметры, Добавить, Заменить або Удалить.

Кнопка Параметры служить для відображення діалогового вікна Параметры поиска решения, у якому можна завантажити або зберегти модель, яка оптимізується і вказати передбачені варіанти пошуку рішення.

Кнопка Отмена служить для очищення полів вікна діалогу й відновлення значень параметрів пошуку рішення, використовуваних за замовчуванням.

Рис.1.6- Вид вікна в режимі Поиск решения

Рис.1.7- Діалогове вікно Добавление ограничений

Настроювання параметрів алгоритму й програми проводиться в діалоговому вікні Параметры поиска решения (рис.1.8). У вікні установлюються обмеження на час рішення завдань, вибираються алгоритми, задається точність рішення, надається можливість для збереження варіантів моделі і їхнього наступного завантаження. Значення й стани елементів управління, використовувані за замовчуванням, підходять для рішення більшості завдань.

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

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

Поле Относительная погрешность служить для завдання точності (припустимої погрішності), з якої визначається відповідність комірки цільовому значенню або наближення до зазначених границь. Поле повинно містити число з інтервалу від 0 (нуля) до 1, наприклад, 0,0001. Висока точність збільшить час, потрібний для того, щоб зійшовся процес оптимізації. Чим менше введене число, тим вища точність результатів

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



Рис. 1.8 - Діалогове вікно Параметры поиска решения

Поле Сходимость результатів пошуку рішення застосовується тільки до нелінійних завдань. Коли відносна зміна значення в цільовій комірці за останні п'ять ітерацій стає менше числа, зазначеного в полі Сходимость, пошук припиняється.

Прапорець Линейная модель служить для прискорення пошуку рішення лінійного завдання оптимізації або лінійної апроксимації нелінійного завдання.

Прапорець Неотрицательные значения дозволяє установити нульову нижню межу для тих комырок, для яких вона не була зазначена в поле Оганичения діалогового вікна Добавление ограничения.

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

Прапорець Показывать результаты итераций служить для припинення пошуку рішення для перегляду результатів окремих ітерацій.

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

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

Кнопки Метод поиска служать для вибору алгоритму оптимізації (метод Ньютона або сполучених градієнтів) - при необхідності. Кнопка Ньютона служить для реалізації квазіньютонівского методу, у якому запитується більше пам'яті, але виконується менше ітерацій, чим у методі сполучених градієнтів. Тут обчислюються частки похідні другого порядку. Кнопка Сопряженных градиентов служить для реалізації методу сполучених градієнтів, у якому запитується менше пам'яті, але виконується більше ітерацій, чим у методі Ньютона. Даний метод варто використовувати, якщо завдання досить велике й необхідно заощаджувати пам'ять, а також якщо ітерації дають занадто малу відмінність у послідовних наближеннях. Для рішення лінійних завдань використовуються алгоритми симплексного методу.

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

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

	Стіл	Стілець	Диван	Запас
Дошки	9	5	6	600
Цвяхи	4	5	6	400
Клей	3	4	5	800
Прибуток	20	18	22

Побудуємо математичну модель завдання знаходження оптимального плану. Позначимо x_i, i=1,2,3, відповідно обсяги випуску столів, стільців і диванів. Тоді завдання максимізації прибутку буде виглядати в такий спосіб: знайти

20x₁+18x₂+22x₃ max

за умови дотримання наступних обмежень:

9x₁+5x₂+6x₃ 600

4x₁+5x₂+6x₃ 400

3x₁+4x₂+5x₃ 800

x₁0 , x₂0, x₃0

Рішення поставленого завдання проведемо в табличному процесорі Excel за допомогою режиму «Поиск решения». Вихідні дані завдання оптимізації прибутку:

Рис.1.9 - Результат пошуку оптимального рішення

Рис.1.10 - Формули, які введені для пошуку рішення

2. Програмування мовою VBA

2.1 Вступ

VBA (Visual Basic for Application) - стандартна мова програмування в додатках MS Office. Слово "стандартна" означає, що незалежно від того, у якому додатку іде робота, досить вивчити стандартний набір команд і методик, щоб успішно їх застосовувати.

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

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

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

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

2.2 Робота у вікні модуля. Структура програми

Основним елементом програмування на VBA є процедура - блок операторів програми в модулі. Модуль - це набір оголошень і процедур мовою VBA, зібраних в одну програмну одиницю. Його можна створювати у вікнах будь-яких додатків MS Office. В Microsoft Access створення модуля ведеться в наступній послідовності:

Пуск Программы MS Access Новая база данных

У вікні, що відкриється, вибрати диск, папку, задати ім'я нової бази даних і нажати на кнопку «Создать».

Після цього у вікні відкритої бази даних створюється модуль за допомогою команд:

Модули Создать

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

Після написання модуля його потрібно зберегти командами: Файл Сохранить как, у текстовому полі задати ім'я і ОК.

Щоб створити нову процедуру у вже наявному модулі, потрібно відкрити його в режимі "Конструктор", помістити курсор у порожній рядок вікна модуля і виконати команди: Вставка (Insert) Процедура (Procedure), у вікні, що відкрилося, вибрати тип процедури, область визначення, записати ім'я і ОК.

Для виконання процедури потрібно помістити курсор миші в будь-яке місце даної процедури і виконати команди

Запуск(Run) перейти/продолжить (RunSub/UserForm)

Рис.2.1 - Вікно нового модуля

Якщо модуль містить велику кількість процедур, то для пошуку потрібної потрібно розкрити список у вікні (Описания), виділити назва необхідної процедури й здійснити її запуск.

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

Структура програми. Правила написання програм. Якщо програма складається тільки з однієї процедури, то в найпростішому випадку її структура наступна:

Sub ім'я()

Оператори

End Sub

(Докладніше процедури будемо розглядати далі).

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

Якщо рядок програми занадто довгий, то використовують один з наступних варіантів:

продовжують писати на тому ж рядку, допускаючи, щоб частина програми

перебувала за межами екрану;

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

Усередині програм можна поміщати коментарі. При цьому кожний рядок коментаря починається зі знака апострофа.

2.3 Основні типи даних

В VBA є велика кількість типів даних, основні з яких наведені нижче.

Типи даних:

- integer - цілий тип (від -32768 до 32767);

- single - числовий тип одинарної точності (до 7 значущих цифр);

- double - числовий тип подвійної точності (до 14 значущих цифр);

- long - цілий тип (від -2147483648 до 2147483647);

- date - використовується для введення дати;

- string - строковий тип;

- currency - використовується у фінансових розрахунках;

- boolean - логічний тип (дані цього типу можуть приймати значення: true (істина) або false (неправда);

- object - посилання винятково на об'єкт;

- variant - довільний тип.

2.4 Змінні, масиви, константи і їхній опис

Змінні, масиви, константи й інші об'єкти мають ім'я, що повинно підкорятися наступним правилам:

- символи імені - букви, цифри й знак підкреслення;

- ім'я повинне починатися з букви;

- довжина ім'я не повинно перевищувати 255 символів;

- ім'я повинно бути унікальним усередині області дії.

Опис змінних

Синтаксис опису змінних:

Dim Им'я Змінної As тип, …, Им'я Змінної As тип

Наприклад:

Dim n As Integer, s As Double

Dim k As String

Масиви і їхній опис.

Масив - упорядкована сукупність даних одного типу. Масиви можуть мати одну, дві й більше размірностей (але не більше 60). Наприклад, X=(x_i), i=0,1,2,…10 - одновимірний масив з 11 елементів; A=(a_ij), i=0,1,2,…,5;j=0,1,2,…,6; - двовимірний масив з 67 елементів. Елемент масиву - це змінна з індексами. Індекси в VBA обмежуються круглими дужками й відділяються один від одного комами. Наприклад, x_i x(i); a_ij a(i,j).

У найпростішому випадку синтаксис опису масиву фіксованого розміру має вигляд:

Dim им'я Массиву (n1,n2,…,nk) As тип

де n1,n2,…,nk-максимальні значення відповідно 1-го, 2-го, ..., k-го індексів, мінімальне значення індексу вважається рівним 0.

Наприклад, Dim X (19) As String

Dim Y (3,7) As Single

Тут описаний строковий масив Х, що складається з 20 елементів і числовий масив Y, що складається з 4 рядків і 8 стовпців. Щоб нумерація індексів елементів починалася з 1, необхідно на початку модуля ввести команду Option Base 1.

Можна явно вказати початкове й кінцеве значення індексів, наприклад,

Dim b(1 to 3, 1 to 4) As Single

Для динамічного масиву (масиву зі змінними розмірами) синтаксис опису має вигляд:

Dim им'я Массиву () As тип

Після визначення розмірів динамічного масиву його потрібно перевизначити з використанням оператора ReDim. Наприклад, одновимірний масив А описується як динамічний: Dim А() As Single, а після визначення n (кількості елементів А ) масив перевизначається:

ReDim(1 to n) As Single

Константи і їхній опис

Константи - це незмінні величини. Cинтаксис опису констант:

Const им'я Константи = значення

Напримеp: Const G=9.8

Загальні правила описів

Строго кажучи, в VBA не потрібно описувати більшість змінних перед їхнім застосуванням. У цьому випадку вони називаються неявно описаними, і їм автоматично присвоюється тип Variant. При явному описі змінних на початку модуля повинна бути інструкція Option Explicit (вона з'являється автоматично при відкритті вікна модуля). Інструкція Option Explicit має наступні особливості:

якщо неправильно вказати ім'я описаної змінної, то при компіляції видається

повідомлення про помилку, а це є ознакою того, що в імені змінної допущена орфографічна помилка;

на зберігання даних типу Variant затрачається більше пам'яті і швидкість

їхньої обробки менша.

Якщо користуватися неявним описом, то інструкцію Option Explicit треба вилучити.

Важливим в VBA поняттям є поняття "область визначення змінних". Є три рівні такої області:

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

процедурі;

змінна рівня модуля описується за допомогою оператора Dim, розміщеного

в розділі опису модуля;

загальна змінна описується за допомогою оператора Public, а не оператора

Dim, у розділі опису модуля.

2.5 Операції VBA

Операції VBA умовно розділяються на три основні категорії: арифметичні, порівняння і логічні.

2.5.1 Арифметичні операції

Операція	Ім'я	Приклад	Результат
+	Додавання	10+5	15
-	Віднімання	10-5	5
*	Множення	10*5	50
/	Ділення	10/4	2.5
\	Цілочисленне ділення	10\4	2
%	Відсотки	10%	0.1
^	Піднесення до ступеня	2 ^ 3	8
Mod	Залишок від ділення	10 mod 3	1

2.5.2 Операції порівняння

Операції порівняння використовуються в командах при порівнянні двох або більше числових, строкових величин. Якщо результат порівняння вірний, то результату присвоюється логічне значення True (Істина). Якщо результат порівняння невірний, то результату присвоюється логічне значення False (Неправда).

Оператор	Ім'я	Приклад	Результат
=	Дорівнює	10=5	Неправда
>	Більше	10>5	Істина
<	Менше	10<5	Неправда
>=	Більше або дорівнює	"a">="b"	Неправда
<=	Менше або дорівнює	"a"<="b"	Істина
<>	Не дорівнює	"a"<>"b"	Істина

2.5.3 Логічні операції

Логічні операції використовуються для побудови логічних виражень.

Оператор	Загальний вид	Результат
And	Вираз1 And вираз2	Повертає значення "Істина", якщо вірні вираз1 і вираз2. У протилежному випадку повертає "Неправда".
Or	Вираз1 Or вираз2	Повертає значення "Істина", якщо хоча б один з виразів вірний. Значення "Неправда" буде тільки у випадку, якщо обидва вирази помилкові.
Xor	Вираз1 Xor вираз2	Повертає значення "Істина", якщо тільки один з виразів вірний. Якщо вірні обидва вирази або помилкові обидва вирази, то повертає значення "Неправда".
Not	Not вираз	Повертає значення "Неправда", якщо вираз вірний, і значення "Істина", якщо вираз невірний.

2.6 Стандартні функції

Для обчислення стандартних функцій необхідно вказувати ім'я функції і у дужках аргумент. Перелік основних стандартних функцій:

Функція	Призначення	Аргумент
Abs(x)	x	Будь-який числовий вираз
Sqr(x)	x	Будь-який числовий вираз >0
Exp(x)	ex	Будь-який числовий вираз (<=709.7827)
Log(x)	Ln x	Будь-який числовий вираз >0
Sin(x)	Sin x	Аргумент у радіанах
Cos(x)	Cos x	Аргумент у радіанах
Tan(x)	Tg x	Аргумент у радіанах
Atn(x)	Arctg x	Будь-який числовий вираз. Повертає значення в радіанах.
Int(x)	Відкидання дробової частини	Будь-який числовий вираз

2.7 Вирази

В VBA можна виділити арифметичні, логічні й строкові вирази.

Арифметичні вирази (АВ) - це сукупність числових констант, змінних, функцій, зв'язаних арифметичними операціями: +, -, *, /, ^, \, mod.

Наприклад: sin(x)+log(y)/a^3*2.7

Правила запису арифметичних виразів

1. Не можна опускати знак множення.

2. АВ записується в рядок. Наприклад, записують як (a+b)/c.

3. Послідовність виконання дій:

- обчислення функцій;

- множення, ділення, mod;

- додавання віднімання.

Наприклад: 1)

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

Логічні вирази (ЛВ) - це сукупність логічних констант, логічних змінних, порівнянь, функцій логічного типу, з'єднаних логічними операціями. Результатом обчислення ЛВ може бути True або False.

Наприклад: 1) x < a+c 2) x*y < 0 and a+b>0

Строкові (текстові) вирази - сукупність строкових констант, строкових змінних, строкових функцій, з'єднаних операцією об'єднання &. Строкова константа (рядок) - це послідовність символів, розташованих в лапках. Строкові змінні (мають тип String) приймають значення строкових констант. Наприклад:

1)"Мені " & "двадцять " & "років"

2) Dim a As String, b As String, c As String

a=”Мені “ : c=”двадцять років”

c=a & b

Якщо вираз містить операції різних типів, то першими виконуються арифметичні операції, потім - операції порівняння, останніми - логічні операції.

2.8 Оператор присвоювання

Синтаксис оператора: v = a ,

де v - ім'я змінної; (=) знак присвоювання; a - вираз (може бути арифметичним, логічним, строковим). Оператор обчислює вираз a, потім отримане значення присвоюється змінній v.

Наприклад: c = a+b, x = y, a = 2.5, s=”студент”

В операторі присвоювання змінна в лівій частині і вираз в правій частині повинні бути одного типу. Однак, якщо змінна в лівій частині описана як змінна типу Variant, те вона прийме тип виразу правої частини.

2.9 Введення даних користувачем

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

Синтаксис функції:

InputBox (запрошення[, заголовок][, по умовчанню][, гор][, верт]

[, файл допомоги[, контекст]),

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

заголовок - текст, що з'являється в панелі заголовка діалогового вікна;

по умовчанню - значення, що вводиться по умовчанню, у текстовому вікні;

гор - горизонтальне розташування діалогового вікна стосовно лівої межі екрана. Якщо цей аргумент опущений, то діалогове вікно розміщається в центрі;

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

файл допомоги - вказівка на файл довідки;

контекст - число, що вказує на розділ довідки.

Всі параметри, крім першого, є необов'язковими. Функція InputBox повертає рядок символів, введених користувачем. Для перетворення цього рядка в число використовується зовнішня функція Val, яка виконується після завершення роботи InputBox.

Наприклад, потрібно ввести значення змінної а=3,5. Запишемо фрагмент програми

Dim a As single

а = Val(InputBox ("Введіть значення а"))

Рис.2.2 - Вікно діалогу функції Inputbox

При виконанні фрагмента програми на екрані з'явиться діалогове вікно, представлене на рис.2.2. У текстове поле вікна потрібно ввести 3.5 і Рис.1.6- Вид вікна в режимі Поиск решения натиснути кнопку ОК.

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

Им'я Масиву=Array([елемент1],[елемент2],…,[элемент])

Функцію Array можна присвоїти тільки змінній типу Variant. Наприклад, для завдання одномірного масиву x=(2.5, 5, 87, 6, 35) варто записати

Dim x As Variant

x=Array(2.5,5,87,6,35)

При завданні двовимірного масиву елемент - рядок матриці, що представляється, як Array. Наприклад, для завдання двовимірного масиву варто записати

Dim x As Variant

x=Array(Array(0,2,5), Array(3,7,2))

Для звертання до елемента масиву x_ij варто записати x(i)(j).

2.10 Виведення даних

1) Функція MsgBox використовується для виведення даних або повідомлень на екран. Синтаксис функції:

MsgBox (запрошення[, кнопки][, заголовок][, файл допомоги][, контекст]),

де кнопки - константа, що визначає командні кнопки, які з'являються в діалоговому вікні. За замовчуванням установлюється значення 0. Інші параметри мають той же зміст, що і у функції InputBox.

Деякі значення констант кнопок функції MsgBox.

Константа Опис

0 Виводиться тільки кнопка ОК.

1 Виводяться кнопки ОК і Отмена.

3 Виводяться кнопки Да, Нет Отмена.

4 Виводяться кнопки Да, Нет.

ПРИМІТКА. Як і у всіх функціях VBA, вказівка аргументів функції MsgBox у круглих дужках необхідна тільки тоді, коли планується використання повертаюче значення цієї функції. Якщо виводиться велике повідомлення, то VBA робить перенос тексту в рамках діалогового вікна. Для вказівки власних меж переносу в кожному рядку використовується функція Chr і код повернення каретки (ASCII 13), наприклад, дане повідомлення буде виводитися у два рядки:

MsgBox "Перший рядок" & Chr(13) & "Другий рядок"

2) Оператор Debug.Print використовується для виведення даних у вікно налагодження.

Синтаксис оператора:

Debug.Print a₁ & a₂ & a₃ …

де a₁, a₂, a₃ - константи, змінні, вирази. Щоб відкрити вікно налагодження, потрібно виконати команди

Вид(View) ( Вікно налагодження (Immediate Window)

Приклад лінійної програми. Обчислити площу трапеції за формулою S= , де a,b - основи, h - висота.

Sub Трапеція()

Dim a As Single, b As Single, h As Single, s As Single

a = Val (InputBox("Введіть значення а"))

b = Val (InputBox("Введіть значення b"))

h = Val (InputBox("Введіть значення h"))

s = (a + b) * h / 2

MsgBox "Площа трапеції = " & s

End Sub

Після запуску програми потрібно ввести значення a,b,h і на екрані з'явиться результат обчислення у вікні виду (рис.2.3.).

2.11 Оператори переходу. Програмування розгалужуваних обчислювальних процесів

1) Оператор If ... Then

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

If умова Then оператор

так і блокового синтаксису

If умова Then

Блок операторів

End If

Тут і далі умова - логічний вираз. Якщо значення умови true, то виконується Блок операторів, у протилежному випадку управління передається операторові, який знаходиться за End if.

Приклад 1. Дані три числа: a, b, c. Від'ємні числа замінити їхніми квадратами, позитивні залишити без зміни.

Sub Разв1()

Dim a As Single, b As Single, c As Single

a = Val (InputBox("Введіть значення а"))

b = Val (InputBox("Введіть значення b"))

c = Val (InputBox("Введіть значення c"))

If a < 0 then a=a^2

If b < 0 then b=b^2

If c < 0 then c=c^2

MsgBox “a=” & a & “ b=” & b & “ c=” & c

End Sub

2) Оператор If...Then...Else.

Синтаксис оператора:

If умова Then

Блок операторів для значення true

Else

Блок операторів для значення false

End If

Приклад 2. Знайти розмір комісійних залежно від суми продажів за наступними правилами: якщо сума продажів (s) більше 15000, то комісійні (c) установлюються на рівні 8 відсотків. У протилежному випадку - 5 відсотків

Sub Sales1()

Dim s As Single , c As Single

s = Val(InputBox("Введіть суму продажів"))

If s > 15000 Then c = 0.08*s Else c = 0.05*s

MsgBox "Комісійні=" & c

End Sub

Тепер приведемо програму для цього ж прикладу з використанням багаторядкового синтаксису оператора if.

Sub Sales2()

Dim s As Single, c As Single

s = Val(InputBox("Введіть суму продажів"))

If s > 15000 Then

c = 0.08*s

Else

c = 0.05*s

End If

MsgBox "Комісійні=" & c

End Sub

4) Оператор безумовного переходу GoTo

Оператор GoTo дозволяє виконати безумовний перехід до заданого рядка. Його синтаксис:

GoTo Мітка

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

MsgBox "Даний оператор виконується"

GoTo NN

Msgbox "Даний оператор пропускається"

NN:

Msgbox "Тепер можна продовжувати"

2.12 Оператори циклу. Програмування циклічних обчислювальних процесів

програмування word excel vba

1) Оператор Do ...Loop

Цей оператор має чотири варіанти синтаксису:

а) Do While умова

Блок операторів

Loop

Якщо умова має значення True, виконуються Блок операторів до Loop. Потім управління повертається до Do While і знову перевіряється умова. Якщо умова, як і раніше, має значення True, процес повторюється. Якщо умова має значення False, виконання відновляється з операторів, що знаходяться за Loop. Цикли Do While. . . Loop можуть мати будь-яку глибину вкладеності.

б) Do Until умова

Блок операторів

Loop

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

в) Do

Блок операторів

Loop While умова

Параметр умова перевіряється після однократного проходження циклу. Блок операторів виконується тільки в тому випадку, якщо умова - True. Така форма оператора Do…Loop використовується в тих випадках, коли виконання блоку команд необхідне як мінімум один раз.

г) Do

Блок операторів

Loop Until умова

Параметр умова перевіряється після однократного проходження циклу. Блок операторів виконується тільки в тому випадку, якщо умова - False. Така форма оператора Do…Loop використовується в тих випадках, коли виконання блоку команд необхідне як мінімум один раз.

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

y= , де x[x₀, x_k] і змінюється із кроком h.

Sub Р1()

Dim x0 As Single, xk As Single, h As Single

Dim a As Single, y As Single, x As Single

x0 = Val(InputBox("Введіть х0"))

xk = Val(InputBox("Введіть хk"))

h = Val(InputBox("Введіть h"))

a = Val(InputBox("Введіть a"))

x = x0

Do While x <= xk

y = Sqr(x ^ 2 + 25) / (x ^ 2 + a ^ 2)

Debug.Print "y=" & y & " x=" & x

x = x + h

Loop

End Sub

Приклад 2. Записати програму для виведення у вікно налагодження натуральних чисел від n до 1, їхні квадрати і куби.

Позначення: n - число, n2, n3 - відповідно квадрат і куб числа.

Sub Р2()

Dim n As Integer, n2 As Integer, n3 As Integer

n = Val(InputBox("Введіть n"))

Debug.Print "Число" & " Квадрат числа" & " Куб числа"

Do

n2 = n ^ 2: n3 = n2 * n

Debug.Print n & " " & n2 & " " & n3

n = n - 1

Loop Until n = 0

End sub

2) Оператор While...Wend

Цей оператор циклу є різновидом оператора Do…Loop...

Синтаксис оператора:

While умова

Блок операторів

Wend

Якщо умова має значення True, виконуються Блок операторів до Wend. Якщо умова має значення False, виконання відновляється з операторів, що випливають за Wend. Цикли WhileWend можуть мати будь-яку глибину вкладеності.

3) Оператор For... Next

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

For лічильник = початок To кінець [step крок]

Блок операторів

Next [лічильник]

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

Приклад 3. Дан масив Х=(12,24,67,38,56,69) щомісячного випуску продукції в 1-му півріччі. Визначити випуски продукції за 1- й і 2 - й квартали.

Sub Р3()

Dim x As Variant

Dim kv1 As Single, kv2 As Single, i As Integer

x=Array(12,24,67,38,56,69)

kv1 = 0: kv2= 0

For i = 1 To 3

kv1=kv1+x(i)

kv2=kv2+x(i+3)

Next i

MsgBox " випуск продукції за 1-ий квартал =" & kv1

MsgBox " випуск продукції за 2-ий квартал =" & kv2

End Sub

4) Оператор For Each...Next

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

Синтаксис оператора:

For Each елемент In група

Блок операторів

Next [елемент]

Параметр елемент - змінна типу Variant (ця обов'язкова умова), що представляє елемент сімейства або масиву. Група - ім'я масиву або сімейства. Блок операторів виконується тільки один раз для кожного члена групи.

Найважливіше в операторі For Each…Next те, що не потрібно заздалегідь знати, скільки елементів утримується в масиві або сімействі. Таким чином, не потрібно підраховувати число елементів або перевіряти досягнення кінця групи. Це дозволяє виключити одну з найбільш загальних помилок при програмуванні: неправильну індексацію елементів масивів.

Приклад 4. Дано масив А=(a_i), i=1,2,...,10. Знайти добуток позитивних елементів.

Позначення: p - добуток позитивних елементів.

Sub Р4()

Dim a(1 to 10) As Double

Dim p As Single, i As Integer, n As Variant

For i = 1 To 10

a(i) = Val(InputBox("Введіть "& str(i) & “ елемент масиву а"))

Next i

p = 1

For Each n In a

If n > 0 Then p = p * n

Next n

MsgBox "p=" & p

End Sub

Приклад 5. Дана матриця D=(d_ij), i= 1,2, . . . ,m; j=1,2, . . . ,n (m10, n20). Знайти суми додатних елементів кожного стовпця.

Позначення: sum[j] - сума додатних елементів j-го стовпця.

Sub Р5()

Dim d(1 to 10, 1 to 20) As Single, sum(1 to 20) As Single

Dim i As Integer, j As Integer, n As Integer, m As Integer

n = Val(InputBox("Введіть кількість рядків n"))

m = Val(InputBox("Введіть кількість стовпців m"))

For i = 1 To n

For j = 1 To m

d(i, j) = Val(InputBox("Введіть масив d"))

Next j

Next i

For j = 1 To m

sum(j) = 0

For i = 1 To n

If d(i, j) > 0 Then sum(j) = sum(j) + d(i, j)

Next i

Debug.Print sum(j)

Next j

End Sub

Приклад 6. Дана матриця З=(с_ij), i=1,2, . . .n; j=1,2,. . . m (n50, m50). Визначити для кожного рядка максимальний елемент і його індекси. (Передбачається, що в кожному рядку є єдиний максимальний елемент).

Позначення: max - максимальний елемент у рядку, jmax - номер стовпця максимального елемента.

Sub Р6()

Dim c(1 to 50, 1 to 50) As Double, max As Double

Dim i, j, n, m, jmax As Integer

n = Val(InputBox("Введіть кількість рядків n"))

m = Val(InputBox("Введіть кількість стовпців m"))

For i = 1 To n

For j = 1 To m

c(i, j) = Val(InputBox("Введіть масив c"))

Next j

Next i

For i = 1 To n

jmax = 1: max = c(i, 1)

For j = 1 To m

If c(i, j) > max Then max = c(i, j): jmax = j

Next j

Debug.Print "max= " & max & “ imax=” & i & " jmax=" & jmax

Next i

End Sub

2.13 Процедури

Програма може являти собою сукупність окремих блоків-підпрограм (процедур). Використання процедур дозволяє реалізувати один із самих прогресивних методів програмування - структурне програмування.

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

В VBA є 3 види процедур: процедура Function, процедура Sub і процедура Property. У даній роботі розглядаються тільки процедури Function і Sub.

Процедура Function

VBA містить безліч вбудованих функцій, наприклад, cos, sqr, mid, але користувач може створювати власні функції, використовуючи для цього процедуру Function.

Синтаксис поцедури Function:

[Private Public Static] Function Ім'я(Список аргументів) [As тип]

Блок операторів

End Function

Тут і далі ( означає, що використовується один з варіантів: Private, Public або Static.

Private - означає, що процедура Function доступна для інших процедур тільки того модуля, у якому вона описана.

Public - означає, що процедура Function доступна для інших процедур у всіх модулях.

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

Ім'я - ідентифікатор функції, що задовольняє стандартним правилам побудови імен;

Тип - тип значення, що повертається Function;

Список аргументів - список змінних, що представляють аргументи, які передаються в процедуру Function при її виклику. Аргументи в списку відділяються комами.

Структура аргументу:

[Optional] [ByVal ByRef] [ ParamАrray] Ім'я [As тип]

Optional - указує, що даний аргумент є необов'язковим. Аргументи, позначені кваліфікатором Optional, повинні перебувати наприкінці списку аргументів.

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

ByRef - аргумент передається по посиланню. Передача аргументу по посиланню дає процедурі доступ до вмісту змінної за її адресою в пам'яті. Значення змінної може бути змінене процедурою, що викликається.

За замовчуванням установлюється ByRef.

ParamАrray - позначає необов'язковий аргумент, що є масивом довільного числа змінних типу Variant. Цей аргумент може бути тільки останнім у списку аргументів. З ним не можуть використовуватися ключові слова ByVal, ByRef, Optional.

Тип - тип аргументу, може використовуватися будь-який стандартний тип даних. За замовчуванням використовується Variant.

Приклади заголовка процедури-функції:

1) Function Sum(n Аs Integer, a As Single) Аs Integer

Ця функція - Public (за замовчуванням), доступна для всіх модулів. Ім'я функції - Sum, аргументи: n - цілого типу, а - числового, обидва аргументи мають кваліфікатор ByRef (за замовчуванням). Функція повертає значення типу Integer.

2)Private Function F1(a As Single, ParamArray x) As Single

Функція доступна тільки для модуля, у якому вона записана. Аргументи: а - числова змінна типу Single, х - масив довільного числа елементів типу Variant.Функція повертає значення типу Single.

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

Виконання Function починається з першого рядка тіла процедури й триває доти, поки не буде досягнута інструкція End Function або Exit Function (вихід з Function). Далі управління передається в основну процедуру.

Процедура Function повертає в основну процедуру значення, присвоює ім'я Function оператором: ім'я = вираз. Цей оператор може бути записаний у тілі Function кілька разів, при цьому Function повертає останнє присвоєне значення. Якщо тип функції не заданий явно, то функція повертає значення типу Variant.

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

Приклад 1. Обчислити , визначення максимуму виконати в процедурі Function

Private Sub pp1()

Dim y As Single, a As Single, x As Single

a = Val(InputBox("Введіть а"))

x = Val(InputBox("Введіть x"))

y = (max(3.5, x + a) + max(x, a)) / max(x ^ 2, 120)

MsgBox "y=" & y

End Sub

Private Function max(b As Single, c As Single) As Single

If b > c Then max = b Else max = c

End Function

Приклад 2. Обчислити C = , де n>m. Визначення факторіала виконати в процедурі Function.

Private Sub pp2()

Dim n As Integer, m As Integer, c As Single

n = Val(InputBox("Введіть n"))

m = Val(InputBox("Введіть m"))

c = fact(n) / (fact(m) * fact(n - m))

MsgBox "c=" & c

End Sub

Private Function fact(n As Integer) As Single

Dim i As Integer, p As Single

p = 1

For i = 1 To n

p =p * i

Next I

fact=p

End Function

Процедура Sub.

Синтаксис процедури Sub:

[Private Public Static] Sub Ім'я([Список аргументів])

Блок операторів

End Sub

Загаловок Sub аналогічний заголовку Function, тільки в процедурі Sub не вказується тип процедури.

Процедура Sub не повертає значення, присвоює її імені, як це робить процедура Function, однак як і Function, Sub може змінювати значення аргументів, переданих до неї. Тому, якщо процедура Sub призначена для розрахунку якихось величин, то в списку аргументів процедури Sub повинні бути присутнім як вхідні аргументи, так і вихідні.

Процедура Sub, на відміну від Function, не може бути викликана по імені функції усередині виразу. Для її виклику використовується окремий оператор. Застосовуються 2 модифікації виклику:

1) Call Ім'я (Список аргументів)

2) Ім'я Список аргументів

Приклад 3. Знайти напівсуму довжин двох векторів З=(c₁,c₂,c₃,c₄) і D=(d₁,d_2,d₃,d₄,d₅). Обчислення довжини вектора оформити, як процедуру Sub.

Позначення в процедурі обчислення довжини вектора: srv - ім'я процедури, x - вхідний масив з n елементами, s -довжина вектора. Позначення в основній процедурі: s1 - довжина вектора C, s2 - довжина вектора D.

Sub pp3()

Dim c(1 To 4) As Single

Dim d(1 To 5) As Single

Dim k As Integer, s1 As Single

Dim s2 As Single

For k = 1 To 4

c(k) = Val(InputBox("Введіть c(" & k & ")"))

Next k

Call srv(c, 4, s1)

For k = 1 To 5

d(k) = Val(InputBox("Введіть d(" & k & ")"))

Next k

Call srv(d, 5, s2)

MsgBox "rez=" & (s1 + s2) / 2

End Sub

Private Sub srv(x() As Single, n As Integer, s As Single)

s = 0

For k = 1 To n

s = s + x(k) ^ 2

Next k

s = Sqr(s)

End Sub

Список літератури

1. Джон Вейскас. Эффективная работа с Microsoft Access 7.0 для Windows .- С-Пб.:Питер, 1997.

2. М.Хэлворсян, М.Янг. Эффективная работа с Microsoft Office 97.- С-Пб.:Питер, 1997.

3. МакФедризг Пол и др. Microsoft Office 97. Энциклопедия пользователя: пер. с англ.-К.: Издательство "ДиаСофт", 1998.

4. К. Гетц, М. Джилберт. Программирование в Microsoft Office. Полное руководство по VBA: пер. с англ.-К.: Издательская группа BHV, 1999.

5. Ю.А.Козак, В.Т.Артеменко, А.Б.Куприянов, Ю.Г.Лобода. Опорный конспект лекций, методические указания и задания к лабораторным работам по теме "Изучение табличного процессора Excel 7.0" для студентов 2 курса всех специальностей дневной и заочной форм обучения. - Одесса: ОГАПТ, 1999.

6. Король В.И. Visual Basic 6.0, Visual Basic for Application 6.0. Язык программирования. Справочник с примерами.-М.: КУДРИЦ-ОБРАЗ, 2000.

7. Каммингс С. VBA для “чайников”: Пер. с англ.: М.:”Вильямс”, 2000.

8. Кен Гетц, Майк Джилберт. Программирование в Microsoft Office: пер. с англ.-К:”Ирина”. BHV, 2000.

9. С.Браун. Visial Basic: учебный курс. - СПб.: Питер, 2001.

Размещено на Allbest.ru

...