Функції обробки рядків
Функція | Дія | Приклад |
Ucase | Переводить усі символи рядка у верхній регістр | Ucase ("Ivanenko") =IVANENKO |
Lease | Переводить усі символи рядка в нижній регістр | Lease ("Ivanenko") =ivanenko |
Len | Визначає довжину рядка | Len ("Ivanenko") = 8 |
Right | Повертає задане число символів, починаючи від кінця рядка | Right ("Ivanenko", 2) = “ko" |
Left | Повертає задане число символів, починаючи від початку рядка | Left ("Ivanenko", 4) = "Ivan" |
Mid | Повертає задане число символів, починаючи від указаної позиції | Mid ("Ivanenko", 2, 3) = "van" |
String | Повторює вказаний символ установлену кількість разів | String (4, "$") = $$$$ |
Asс | Повертає ASCII-код для вказаного символу | Asс(„R”) = 82 |
Chr | Повертає символ для вказаного ASCII-коду | Chr(114) = r |
Функції вибору.Крім перерахованих функцій в VBA існують також функції вибору, які дублюють деякі аспекти операторів умовного переходу.
1. iif – повертає одну з двох альтернатив.
Синтаксис: iif(Expr, Truepart, Falsepart), де Truepart – значення або вираз, який повертається, якщо Expr має тип True, Falsepart – значення або вираз, який повертається, якщо Expr має тип False.
Приклад: a = iif(b > c, b, c)
2. Choose – повертає значення, вибране зі списку параметрів.
Синтаксис: Choose(Index, Choice1 [,Choice2, … [,ChoiceN]]), де Index – числовий вираз або поле, значенням якого є число, яке лежить між 1 і числом елементів списка, Choice – вираз типу Variant, який містить один з елементів списку.
Приклад: N=3
a = Choose(n, “1”,”2”,”3”,”4”)
В результаті виконання в а буде міститься “3”.
Перенос рядка.Для продовження рядка інструкцій VBA використовується конструкція <Прогалина> + <Знак підкреслення> в кінці рядка, яка забезпечує, що наступний рядок є продовженням попереднього. При цьому потрібно пам’ятати, що сам рядок не може складатись більше ніж з 1024 символів
Приклад: if a > b then _
с = a else _
с = b
Коментарі.Текст, що міститься в програмі за символом (‘) або оператором Rem до кінця рядка з права, ігнорується компілятором і є коментарем. Оператор Rem може бути, як і будь-який інший оператор VBA єдиним в рядку, а символ (‘) може стояти в будь-якому місці рядка, тобто стояти як на початку тексту так і бути другим оператором VBA.
Розміщення декількох операторів в одному рядку.Використання знака (:) дозволяє розмістити декілька операторів в одному рядку. Таким чином, наступні дві конструкції еквівалентні:
x = 1
x = x + 1
та
x = 1 : x = x + 1
Оператор присвоєння.Оператор присвоєння завжди включає знак (=).
Синтаксис:
Змінна (або Константа) = Вираз
Оператор присвоєння виконує вираз, який задано в правій частині і присвоює його значення (значення виразу правої частини) змінній або константі, які вказані в лівій частині.
Приклад:
х= 2
х = х + 2
Змінній х буде присвоєно значення 4.
Оператори умовного переходу.Оператори умовного переходу задають виконання групи інструкцій в залежності від значення виразу.
Синтаксис: 1. if Умова then [інструкція] [Else Інструкція_else]
2. if Умова1 then
[Інструкція1]
[elseif Умова2]
[Інструкція2]
. . .
[else]
[Інструкція_else]
endif
Приклад 1. Прочитати з робочого листа 1 Excel число і піднести його до квадрату, якщо воно від’ємне; піднести його до кубу, якщо воно додатне.
Public Sub tyr()
x = Worksheets("Лист1").Cells(1, 1)
If (x < 0) Then y = x ^ 2 Else y = x ^ 3
MsgBox Str(y)
End Sub
Приклад 2.
Знайти значення х:
y + (y + cos(y))^(1/3) y < -3
x = arccos(0.2y + 0.23) - 3 < y <3
arcctg(y – ey) 3 < y < 5
Public Sub tyr()
x = Worksheets("Лист1").Cells(1, 1)
If (x = -3) Or (x = 3) Or (x >= 5) Then
MsgBox "FNO"
ElseIf (x < 3) Then
y = x + Sgn(x + Cos(x)) * Abs(x + Cos(x)) ^ (1 / 3)
ElseIf ((x > -3) And (x < 3)) Then
y = Atn(0.2 * x + 0.23) / Sqr(1 - (0.2 * x + 0.23) ^ 2)
Else
y = 3.1416 / 2 - Atn(y - Exp(y))
End If
MsgBox Str(y)
End Sub
Оператор вибору.Якщо потрібно перевірити декілька умов, то використання оператору if може стати занадто складним. В таких випадках застосовують оператор вибору, який дозволяє виконати одну з декількох інструкцій, в залежності від значення виразу.
Синтаксис: select case
[case СписокВиразів_1]
[Інструкція_1]
. . .
[case списокВиразів_n]
[Інструкція_n]
[case else]
[Інструкція_else]
end select
- СписокВиразів - список, що розділяється, який складається з однієї або декількох форм наступного вигляду: Вираз, Вираз То Вираз, Is ОператорПорівняння Вираз, де Вираз – це будь-який числовий чи рядковий вираз. Ключове слово То задає діапазон значень. При використанні ключового слова То перед ним повинно знаходитись менше значення. Ключове слово Is з операторами порівняння задає діапазон значень. Якщо ключове слово Is не вказане, воно вставляється за замовчуванням.
- Інструкції – це одна або декілька інструкцій, які виконуються в тому випадку, якщо вираз співпадає з деяким значенням із списку від СписокВиразів-1 до СписокВиразів-n.
- Інструкції_Else – одна або декілька інструкцій, які виконуються в тому випадку, якщо вираз не співпадає ні з одним з пропозицій Case.
Приклад 1. В залежності від величини введеного числа відображається повідомлення, яке вказує на величину числа або діапазон, якому воно належить.
Public Sub iui()
x = InputBox("Введіть ціле число")
Select Case x
Case 1
MsgBox "Число дорівнює 1"
Case 2, 3
MsgBox "Число дорівнює 2 або 3"
Case 4 To 6
MsgBox "Число від 2 до 6"
Case Is >= 7
MsgBox "Число не менше"
End Select
Приклад 2. Залежно від вибраного значення n присвоїти значення змінній х.
Public Sub qq()
n = 4
Select Case n
Case 1
x = 1
Case 2 To 5
x = 2
Case Else
x = 3
End Select
MsgBox x
End Sub
Оператор For – Next (оператор циклу).Оператор For – Next виконує групу інструкцій вказану кількість разів.
Синтаксис:
For Лічильник = Початок То Кінець [Step Крок]
[Інструкції]
[Exit For]
[Інструкції]
Next [Лічильник]
Оператор For – Next повторює виконання групи інструкцій з вказаним Кроком доти, поки Лічильник змінюється від Початку до Кінця. Якщо Крок не вказаний, за замовчуванням він вважається рівним 1. Альтернативний вихід з циклу надає інструкція Exit For.
Приклад 1.
Знайти суму елементів одномірного масиву, введених з робочого листа Excel.
Public Sub rtr()
Dim a(100)
n = InputBox("Введіть кількість елементів масиву")
For i = 1 To n
a(i) = Worksheets("Лист1").Cells(i, 1)
Next i
s = 0
For i = 1 To n
s = s + a(i)
Next i
MsgBox Str(s)
End Sub
Оператор While-Wend (оператор циклу).Використовується рідко.
Оператор While-Wend виконує послідовність інструкцій, поки задана умова має значення True.
Синтаксис:
While Умова
[Інструкції]
Wend
Оператор While-Wend на відміну від For – Next працює не задану кількість раз, а поки виконується умова.
Приклад. Використання оператору While-Wend
Public Su1b rtr()
Dim a(100)
n = Val(InputBox("Введіть кількість елементів масиву"))
i = 0
While i <= n
i = i + 1
a(i) = Worksheets("Лист1").Cells(i, 1)
Wend
s = 0
i = 0
While i <= n
i = i + 1
s = s + a(i)
Wend
MsgBox Str(s)
End Sub
Оператор Do – Loop.Оператор Do – Loop повторює виконання набору інструкцій, поки умова має значення True (випадок While) або поки умова не прийме значення True (випадок Until).
Синтаксис:
Do [While | Until] Умова або Do
[Інструкції] [Інструкції]
[Exit Do] [Exit Do]
[Інструкції] [Інструкції] [Exit Do] Loop[(While | Until) Умова]
Loop
В будь-якому місці структури Do – Loop може бути розміщена будь-яка кількість інструкцій Exit Do, які забезпечують альтернативні можливості виходу з циклу Do – Loop.
Приклад 1 використання циклу Do – Loop.
Public Sub rtr()
Dim a(100)
n = Val(InputBox("Введіть кількість елементів масиву"))
i = 0
Do While i <= n
i = i + 1
a(i) = Worksheets("Лист1").Cells(i, 1)
Loop
s = 0
i = 0
Do While i <= n
i = i + 1
s = s + a(i)
Loop
MsgBox Str(s)
End Sub
Приклад 2 використання циклу Do – Loop.
Public Sub rtr()
Dim a(100)
n = Val(InputBox("Введіть кількість елементів масиву"))
i = 0
Do
i = i + 1
a(i) = Worksheets("Лист1").Cells(i, 1)
Loop While i <= n
s = 0
i = 0
Do
i = i + 1
s = s + a(i)
Loop While i <= n
MsgBox Str(s)
End Sub
Типи процедур.В VBA використовуються процедури двох типів: Sub i Function. Процедури типу Sub можуть повертати як одне так і декілька значень, а процедури типу Function завжди повертають тільки одне значення. Наприклад, функція у = х2 приймає значення х а повертає значення у. Будь-яка програма у VBA є процедура типу Sub або Function.
Процедури.Процедура є самостійною частиною коду, яка має ім’я і може мати параметри, змінювати їх значення і виконувати послідовність інструкцій.
Синтаксис:
[Private | Public] [Static] Sub Ім’я [СписокПараметрів]
[Інструкції]
[Exit Sub]
[Інструкції]
End Sub
Таблиця 4.1.10.
Параметр | Опис |
Public Private Static Ім’я СписокПараметрів Інструкції | Вказує, що процедура Sub доступна для всіх інших процедур в усіх модулях Вказує, що процедура Sub доступна для інших процедур тільки того модуля, де вона описана Вказує, що локальні змінні процедури Sub зберігаються в проміжках часу між викликами цієї процедури Ім’я процедури Sub, яке задовольняє стандартним правилам іменування змінних Список параметрів, значення яких передаються в процедуру або повертаються з процедури при її виклику. Розділителем в списку параметрів є кома Будь-яка група інструкцій, яка виконується в процедурі Sub |
Виклик процедури.Виклик процедури Sub з іншої процедури можна здійснити декількома способами.
Перший спосіб виклику процедури Sub.
Ім’яПроцедури СписокФактичнихПараметрів
Таблиця 4.1.11.
Параметри | Опис |
Ім’яПроцедури СписокФактичнихПараметрів | Ім’я процедури, що викликається Список параметрів, які передаються процедурі. Цей список повинен відповідати по кількості і типу списку параметрів, які задаються у процедурі при її визначенні |
Якщо необхідно використати декілька процедур з однаковими назвами, при їх виклику через крапку необхідно вказати ім’я модуля, де вони розташовані. А саме:
Ім’яМодуля. Ім’яПроцедури СписокФактичнихПараметрів
Другий спосіб виклику процедури Sub.
Цей спосіб здійснюється за допомогою інструкції Call.
Call Ім’яПроцедури (СписокФактичнихПараметрів)
Зверніть увагу на те, що в цьому випадку список фактичних параметрів береться в дужки. В першому способі дужки не використовувались.
Пояснення: формальні параметри – це такі параметри, які вказані у заголовку процедури; фактичні параметри – це параметри, вказані у операторі виклику процедури.
Вбудовані діалогові вікна.У проектах VBA часто зустрічаються два типи діалогових вікон:
1. Вікна повідомлень.
2. Вікна вводу (діалогу).
Вони вбудовані у VBA, і якщо їх можливостей достатньо, то можна обійтись без проектування діалогових вікон. Вікно повідомлень MsgBox виводить найпростіші повідомлення для користувача, а вікно вводу InputBox забезпечує введення інформації
Процедура MsgBox.MsgBox – це скринька з повідомленнями (скорочення від Message Box). Ця процедура виводить на екран діалогове вікно, яке містить повідомлення, встановлює режим очікування натиснення кнопки користувачем.
Синтаксис:
MsgBox (Prompt, Buttons, Title)
Prompt – рядковий вираз, який відображає повідомлення в діалоговому вікні.
Buttons – числовий вираз, або константа, еквівалентна числовому виразу, що являє собою суму значень, які вказують число і тип кнопок, що відображаються в діалоговому вікні, основну кнопку і модальність вікна повідомлень. Значення за замовчуванням цього параметру дорівнюють нулю.
Title – вираз, який відображається у рядку заголовка діалогового вікна. Якщо цей параметр опущений, то в рядок заголовка поміщається ім’я додатку.
Таблиця 4.1.12.
Константа | Значення | Кнопки |
VbOKOnly VbOKCancel VbAbortIgnore VbYesNoCancel VbYesNo VbRetryCancel | OK ОК Отмена Стоп Повтор Пропустить Да Нет Отмена Да Нет Повтор Отмена |
Значення параметру Buttons процедури MsgBox, які визначають кнопки,
що відображаються у діалоговому вікні
| Значення | Знак повідомлення | |
VbCritical VbQuestion VbExclamation VbInformation | ? ! і |
Значення параметру Buttons процедури MsgBox, які визначають інформаційні значки у діалоговому вікні
Значення параметру Buttons процедури MsgBox, які визначають основну кнопку в діалоговому вікні повідомлень, тобто активну кнопку
Константа | Значення | Номер основної кнопки |
VbDefaultButton1 VbDefaultButton2 VbDefaultButton3 VbDefaultButton4 |
Активна кнопка буде працювати, якщо ми натиснемо клавішу Enter в діалоговому вікні.
Приклади використання процедури MsgBox.
1. Вивести вікно повідомлень, яке містить повідомлення “Hello Word”, в заголовку якого вказати своє ім’я.
Public Sub MyFirstProgram()
MsgBox "Hello Word", , "Oльга"
End Sub
2. Вивести вікно повідомлень, яке містить кнопки Да і Нет, значок 5, а також повідомлення Hello Word.
Public Sub MyFirstProgram()
MsgBox "Hello Word", vbYesNo + vbCritical
End Sub
1. Вивести вікно повідомлень, яке містить лише ім’я в рядку заголовка діалогового вікна.
Public Sub MyFirstProgram()
MsgBox " ", , "Ольга"
End Sub
Отже, в процедурі MsgBox є обов’язковим лише перший параметр, інші необов’язкові.
Процедура MsgBox зручна для виводу тієї чи іншої інформації. Однак, якщо необхідно дізнатись, який вибір зробив користувач при натисненні кнопок з діалогового вікна, то процедуру MsgBox треба використовувати як функцію. В цьому випадку значення, яке повертає MsgBox треба присвоїти якійсь змінній, а її параметри треба взяти в дужки. Отже, MsgBox може бути використана як функція і тоді вона повертає значення коду натиснутої кнопки.
З вищесказаного випливає, що результатом виконання функції MsgBox є коди натиснутих кнопок.
Таблиця 4.1.13.
Константа | Значення | Натиснута кнопка |
VbOK VbCancel VbAbort VbRetry VbIgnore VbYes VbNo | ОК Отмена (Cancel) Прервать (Abort) Повторить (Retry) Пропустить (Ignore) Да (Yes) Нет (No) |
Таблиця кодів кнопок.
Приклад. Записати у вікно повідомлень один з кодів натиснутих кнопок : Да, Нет, Отмена.
Створимо вікно повідомлень, в якому будуть 3 кнопки: Да, Нет, Отмена і активною другою кнопкою
Public Sub MyFirstProgram()
MsgBox "", vbYesNoCancel + vbDefaultButton2
End Sub
Використаємо MsgBox як функцію, яка повертатиме код натиснутої кнопки з діалогового вікна
Public Sub MyFirstProgram()
a = MsgBox("", vbYesNoCancel + vbDefaultButton2)
If a = 6 Then MsgBox "6"
If a = 7 Then MsgBox "7"
If a = 2 Then MsgBox "2"
End Sub
Приклад. Розглянемо наступну програму, яка записує на робочий лист Excel Лист1 в комірку (2, 2) текст “Hello Word”.
Public Sub MySecondProgram()
Worksheets("Лист1").Cells(2, 2) = "Hello Word"
End Sub
Приклад. Прочитати з листа Excel з комірки (3, 3) число і записати його в MsgBox.
Public Sub MyNextProgram()
a = Worksheets("Лист1").Cells(3, 3)
MsgBox Str(a)
End Sub
Примітка. Оскільки перший параметр в MsgBox має тип рядковий, щоб побачити число, яке записане на робочому листі в комірці (3, 3), необхідно перетворити його в рядок. Для цього використовуємо функцію Str, яка детально буде описана нижче.
Приклад. Прочитати з листа Excel з комірок (3, 3), (4, 3), (5, 3) числа і записати їх в MsgBox.
Public Sub MyNextProgram()
a = Worksheets("Лист1").Cells(3, 3)
b = Worksheets("Лист1").Cells(3, 4)
c = Worksheets("Лист1").Cells(3, 5)
MsgBox Str(a) + Chr(13) + Str(b) + Chr(13) + Str(c) + Chr(13)
End Sub
Примітка. Для того, щоб кожне число у вікні повідомлень MsgBox записувалось з нового рядка, було використано функцію Chr(13), яка діє аналогічно клавіші ENTER.
Параметри процедури Sub пишуться без дужок, а процедури Function – у дужках.
Функція InputBox. Функція Inputbox виводить на екран діалогове вікно, що містить повідомлення і поле вводу, дві кнопки ОК і Cancel. Встановлює режим очікування вводу тексту користувачем і натиснення кнопки, а потім повертає значення типу String по натисненню кнопки ОК, яке містить текст, введений в поле вводу. Отже, Inputbox завжди повертає текст. Щоб зробити з нього число, необхідно скористатись функціями str() або cstr(), якщо необхідно вивести дату – cdata().
Синтаксис:
Inputbox(Prompt, Title, Default, xPos, yPos, Helpfile, Context)
Promp – рядок, який відображається як повідомлення в діалоговому вікні. Рядковий вираз Promp може містити декілька рядків. Для розділу рядків допускається використання символу повернення каретки Chr(13), символу переводу рядка Chr(10), або комбінації цих символів Chr(13) & Chr(10), де &- конкатенація (зчеплення) рядків. (Замість знаку & допускається використання знаку + ).
Title – рядок, який відображається в рядку заголовка діалогового вікна. Якщо цей параметр опущений, то в рядок заголовку поміщається ім’я додатку.
Default – рядок (підказка), який відображається в полі вводу. Він буде використаний за замовчуванням, якщо користувач не введе інший рядок. Якщо цей параметр опущений, то поле вводу відображається пустим.
xPos, yPos – координати вікна повідомлень на екрані.
Helpfile і Context – довідка.
Перші три парамерти є обов’язковими.
Приклад.
Public Sub MyNextProgram()
a = InputBox("Введіть значення", "Ольга", 10)
End Sub
Оператор виведення даних Print.Дія цього оператора полягає у виведенні результатів роботи програми у форму. Синтаксис:
[ Form n ]. Print [ список елементів виведення ]
Де до списку елементів виведення можуть входити: константи, змінні, арифметичні вирази, послідовність символів у лапках, функції Tab, Spc, String. Елементи списку відокремлюються комою (,) або крапка з комою (;). У випадку розділення комою наступна інформація друкується з нової зони (одна зона 14 позицій),. У випадку розділення крапкою з комою наступна інформація друкується безпосередньо після попередньої. Якщо елементом списку є вираз, то спочатку система обчислює значення виразу а друкує результат. Функція Tab (n) – визначає номер позиції з якої відбувається друк. Функція Spc (n) –визначаєкількість позицій, які треба пропустити. Функція String (n, “символ”) – визначає кількість символів, які друкуються.
Рис. 4.1.1.
Об’єктна модель Ms Excel являє собою ієрархію об’єктів, підпорядкованих об’єкту Application, який відповідає самому додатку Ms Excel. Більшість з цих об’єктів зібрані у бібліотеці об’єктів Ms Excel, але деякі входять до складу бібліотеки об’єктів Ms Office.
Основні об’єкти та сімейства:
Таблиця 4.1.14.
Об’єкт | Сімейство | Опис |
Application | Активний додаток Ms Excel | |
WorkBook | WorkBooks | Сімейство об’єктів, кожний з яких являє собою робочу книгу |
Worksheet | Worksheets | Сімейство об’єктів, кожний з яких являє собою робочий аркуш даної робочої книги |
Chart | Charts | Сімейство об’єктів, кожний з яких являє собою діаграму у даній робочій книзі |
Name | Names | Сімейство об’єктів, кожний з яких є ім’ям об’єкта , наприклад ім’ям робочого аркуша |
Style | Styles | Сімейство об’єктів, кожний з яких є вбудованим стилем або стилем користувача, який використовується у робочій книзі |
Window | Windows | Сімейство об’єктів, кожний з яких є вікном робочої книги |
WorksheetFunction | Об’єкт, який являє собою контейнер для функцій робочого аркуша | |
Range | Діапазон комірок робочого аркуша. Приклади: Range(“A:G”) – діапазон комірок вказаного діапазону стовпців; Range(“2:2”) - діапазон комірок другого рядка; Range(“А2”) - комірка А2. |
Діапазон комірок може задаватись не тільки як об’єкт Range, а й з використанням функцій робочого аркуша (об’єкта Worksheet) Rows та Columns. Наприклад: Rows(4); Columns(3).
Альтернативним способом звернення до комірки робочого аркуша є властивість Cells робочого аркуша (об’єкта Worksheet). Наприклад, комірку А5 робочого аркуша можна описати як Range(“А5”) або Cells(1, 5) (комірка першого стовпця і п’ятого рядка).
Крім того можливі такі способи опису діапазону комірок А2:С7:
1) Range(“А2:С7”)