Программирование_на_VBA в_Excel

− за допомогою ключового слова Is, праворуч від якого записуються знак порівняння і деяке значення.

Наприклад:

Select Case x

Case 1,2

Range("a1") = "якщо x дорівнює 1 або 2"

Case 3 To 8

Range("a1") = "якщо x має значення від 3 до 8"

Case Is>10

Range("a1") = "якщо x більше 10"

End Select

5.2 Організація циклів

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

Розрізняють три види циклів:

−з відомим числом повторень;

−з умовою до виконання циклу;

−з умовою після виконання циклу.

Для програмування повторень із заздалегідь відомим їх числом застосовується оператор циклу For – Next, який називається циклом із лічильником. Він має вид:

For x = xn TO xk STEP dx

тіло циклу

Next x

Тут x – ім'я змінної, яку називають лічильником циклу; xn, xk – її початкове та кінцеве значення; dx – крок циклу, тобто величина, на яку змінюється значення лічильника при одному повторенні. Крок dx може бути як додатнім, так і від'ємним. Якщо значення dx від'ємне, параметр xk повинен бути менше параметра xn. Конструкція STEP dx

21

може бути відсутня. Тоді за замовчуванням приймається крок dx рівний одиниці.

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

Число повторень циклу можна розрахувати за формулою

n = xk − xn +1 . dx

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

	l =	a + e−0,5x		, q = arcctg		, якщо a = 1,6; 0,6 ≤ x ≤ 2,5; x = 0,2 ,
					2x
		3	+ x

тобто x змінюється від 0,6 до 2,5 з кроком 0,2. Фрагмент програми приведено нижче.

a=1.6

For x=0.6 TO 2.5 STEP 0.2

l = (a + Exp(-0.5 * x)) / (3 + x) q = Atn(1 / Sqr(2 * x))

Next x

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

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

22

тора DO – LOOP. Вихід з циклу відбувається за умовою, яка може перевірятися двома способами:

−While УмоваВиконання;

−Untile УмоваВиходу.

УмоваВиконання – це логічний вираз, що задає умову виконання дій в циклі, а УмоваВиходу – логічний вираз, що задає умову виходу з циклу.

У циклі з умовою до виконання циклу спочатку перевіряється умова і, якщо умова продовження циклу виконується, то здійснюється дія. Потім знову перевіряється умова і т. д. Виконання циклу припиняється, коли умова перестає виконуватися. Для цього необхідно, щоб дія в циклі впливала на зміну умови. Інакше відбудеться "зациклювання" – нескінченне виконання циклу. Воно є типовою помилкою в разі використання циклів. Оператор DO – LOOP з умовою до виконання циклу має наступний синтаксис:

Do While УмоваВиконання	або Do Untile УмоваВиходу
тіло циклу
Loop

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

a=1.6

x=0.6

Do While x<=2.5 або Do Untile x>2.5 l = (a + Exp(-0.5 * x)) / (3 + x)

q = Atn(1 / Sqr(2 * x)) x=x+0.2

Loop

Цикл з умовою після виконання циклу виконується аналогічно, але умова перевіряється після виконання дії. Повторення дії відбувається тоді, коли умова продовження циклу виконується. Дія в цьому циклі виконується завжди хоча б один раз, а з умовою до виконання циклу може не виконуватися жодного разу, якщо з самого початку умова не виконується. Оператор DO – LOOP з умовою після виконан-

23

ня циклу має наступний синтаксис:

Do
тіло циклу
Loop While УмоваВиконання	або Do Untile УмоваВиходу

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

a=1.6

x=0.6 Do

l = (a + Exp(-0.5 * x)) / (3 + x)

q = Atn(1 / Sqr(2 * x))
x=x+0.2
Loop While x<=2.5	або Loop Untile x>2.5

Для передчасного виходу з циклу For – Next при необхідності використовується оператор Exit For, з циклу Do – Loop – оператор

Exit Do.

5.3 Приклад програми на організацію циклів і розгалужень

Розробити програму на VBA для обчислення значень функції

	ìtgK + 4lg x + 2,26					x £ 2,5
	V = í			2			для K = 1,75; 1,2	≤ x ≤ 5; x = 0,3 .
		x + 2,1 - 0,8cos			x	x > 2,5
	î3

Текст програми на VBA наведено нижче.

Private Sub CmdPrim3_Click()

Range("a2") = "Програма з використанням циклу і розгалужень" Range("a4") = "Результати розрахунку"

Dim V As Single, k As Single, x As Single k = 1.75

Range("a5") = "k" : Range("a6") = Str(k) Range("b5") = "x" : Range("c5") = "v"

i = 6

For x = 1.2 To 5 Step 0.3

24

If x <= 2.5 Then

V = Tan(k) + 4 * Log(x) / Log(10) + 2.26 Else

V = (x + 2.1) ^ (1 / 3) - 0.8 * Cos(x) ^ 2 End If

Cells(i, 2) = Str(x) : Cells(i, 3) = Str(V) i = i + 1

Next x

End Sub

6 ПРОГРАМУВАННЯ З ВИКОРИСТАННЯМ МАСИВІВ

6.1 Основні поняття про масиви

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

До імен масивів висуваються такі ж самі вимоги, як і для звичайної змінної. Індекс – це або ціле невід'ємне число, або змінна, значенням якої теж має бути ціле невід'ємне число. Воно вказує на номер елемента масиву в списку (нумерація починається з нуля). Щоб нумерація індексів масивів починалася з одиниці, необхідно в область оголошень модуля перед оголошеннями змінних або процедур помістити оператор Option Base 1. Для багатовимірних масивів вказується декілька індексів, між якими ставиться кома. В разі двовимірного масиву перший індекс задає номер рядка таблиці, а другий – номер її стовпця. У тривімірному масиві додається третій індекс, що вказує номер самої таблиці.

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

Dim A(1 To 15) As Integer, B(12) As Single

25

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

6.2 Приклади виконання операцій з масивами

Для формування масиву, значення якого змінюються випадково в заданому інтервалі значень, використовується функція Rnd, яка генерує випадкові числа в діапазоні від 0 до 1. Перед першим зверненням до цієї функції треба ініціалізувати датчик випадкових чисел, що виконується за допомогою процедури Randomize.

Нижче приведено фрагмент блоку програми, що формує масив x із 20 елементів, значення якого змінюються випадково в інтервалі від -3 до 10,5, і виводить його із загальним заголовком по 4 елементи в рядку з точністю двох знаків після коми.

Range("a4") = "Масив x"

Dim x(1 to 20) As Single, I As Integer, j As Integer, k As Integer Randomize ' Ініціалізація датчика випадкових чисел

i = 5 : j = 1

For k = 1 To 20

x(k) = -3 + 13.5 * Rnd ' Rnd генерує випадкові числа від 0 до 1 Cells(i, j) = Str(x(k))

j = j + 1 ' збільшення номера стовпця на 1

If (k Mod 4) = 0 Then ' перехід на новий рядок i = i + 1

j = 1 End If

Next k

Range("a5:d11").NumberFormat = "##.##"

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

Dim s As Single, p As Single, sr As Single s=0 : p=1

For k=1 To 20 s =s + x(k)

26

p = p * x(k) Next k

sr = s / 20

Алгоритм пошуку елемента масиву, що має максимальне значення, та його номера ілюструється наступним блоком програми.

Dim max As Single, imax As Integer max = x(1)

imax = 1 ' imax – номер найбільшого елемента

For k = 2 To 20

If x(k) > max Then Max = x(k) imax = k

End If Next k

Пошук мінімального значення виконується аналогічно.

У наступному фрагменті програми показано, як знайти кількість елементів, які за абсолютною величиною більше 2.

' Блок визначення кількості елементів

Dim kol As Integer kol = 0

For k =1 To 20

If Abs(x(k)) > 2 Then kol=kol+1 Next k

Дії з матрицями виконуються аналогічно, тільки для перебору їх елементів організовується подвійний цикл. Розглянемо цей процес на наступному прикладі. Сформувати матрицю x(5,4), значення елементів якої змінюються випадково в інтервалі від -3,6 до 7,1. Сформувати масив z із сум елементів стовпців матриці. Вивести матрицю і масив. Текст блоку програми на VBA приведено нижче.

Dim x(5,4) As Single, z(4) As Single, s As Single Dim і As Integer, j As Integer, k As Integer

' Формування і виведення матриці

Randomize ‘ Ініціалізація датчика випадкових чисел

Range("a2") = "Матриця x"

27

i = 3 : j = 1 For k =1 To 5

For j = 1 To 4

x(k,j) = -3.6 + 10.7*Rnd Cells(i, j) = Str(x(k,j))

Next j

i = i+1 ' Перехід на новий рядок

Next k

' Формування і виведення масиву z Range("a9") = "Масив z"

i = 10

For j = 1 TO 4 s = 0

For k = 1 TO 5 s =s + x(k,j)

Next k z(j) = s

Cells(i, j) = Str(z(j)) NEXT j

7 СТВОРЕННЯ І ВИКОРИСТАННЯ ФУНКЦІЇ КОРИСТУВАЧА

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

Function Ім'яФункції(СписокАргументів) As ТипФункції

де Ім'яФункції – ім'я функції; СписокАргументів – список аргументів, що розділяються кома-

ми, з вказівкою їх типу (аргументи можуть бути відсутніми). За допомогою списку аргументів функції можна передавати необхідні значення;

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

ної, ім'я якої збігається з ім'ям функції. Забороняється використовувати це ім'я у правій частині виразів. Функція повинна закінчуватися

28

оператором End Function.

Для створення функції користувача необхідно викликати редактор VBA за допомогою команди Сервис→ Макрос → Редактор

Visual Basic. Після цього дати команду Insert (Вставка) → Module (Модуль) для створення листа модуля. У вікні, що з'явилося, ввести текст функції на VBA. Наприклад, функція для обчислення значення V

	ìtgK + 4lg x + 2,26					x £ 2,5
	за формулою V = í			2			буде мати вид:
		x + 2,1 - 0,8cos			x	x > 2,5
	î3

Function V(k As Single, x As Single) As Single

If x <= 2.5 Then

V = Tan(k) + 4 * Log(x) / Log(10) + 2.26

Else

V = (x + 2.1) ^ (1 / 3) - 0.8 * Cos(x) ^ 2

End If

End Function

Для використання функції, знаходячись на листі Excel, виконується стандартна процедура вставки функції, при цьому вибирається категорія Определенные пользователем. Інакше кажучи, функція користувача використовується в Excel так само, як і будь-яка убудована функція.

8 СТВОРЕННЯ ЕКРАННОЇ ФОРМИ ДЛЯ ВВОДУ ДАНИХ

8.1 Створення екранної форми і розміщення на ній елементів керування

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

Для вставки екранної форми, знаходячись на робочому листі Excel, необхідно викликати редактор VBA за допомогою команди

Сервис→ Макрос → Редактор Visual Basic і дати команду Insert

29

(Вставка) → UserForm (Пользовательская форма). Потім розмісти-

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

До основних елементів керування відносяться:

TextBox (префікс txt) – текстове поле, застосовується для введення або виведення інформації;

CommandButton (cmd) – кнопка, застосовується для керування початком, перериванням або закінченням процесу;

ListBox (lst) – список, застосовується для вибору потрібного елементу із запропонованого переліку;

ComboBox (cbo) – комбіноване поле, поєднує можливості списку і текстового поля. Якщо потрібний елемент присутній в списку, він вибирається із списку, якщо відсутній, то вводиться;

Label (lbl) – напис, застосовується для виведення пояснюючого тексту. Основними методами, що використовуються в роботі з формами, є: Show – показати; Hide – сховати. Метод Show застосовується,

щоб показати форму для введення даних на екрані, метод Hide – щоб сховати форму по закінченню введення даних. Зазвичай перед введенням даних за допомогою форми виконується її ініціалізація. Для цього створюється процедура, в якій заповнюють значеннями списки і комбіновані поля, при необхідності задають значення змінним, що потім використовуються при обробці введених даних. Щоб додати елемент в список або комбіноване поле, використовується метод AddItem.

8.2 Приклад використання форми для вводу даних

Розглянемо приклад програми на VBA, що організує введення даних у таблицю Excel по записах за допомогою форми. Спочатку необхідно, знаходячись на робочому листі Excel, вставити екранну форму; задати формі властивості (Name – FrmVvod, Caption – Введення даних); розмістити на ній елементи керування. Якщо для поля необхідно вибирати дані зі списків і вводити ті значення, які відсутні в списках

30