Програма на VBA – це послідовність операторів.
При складанні програм слід дотримуватися низки угод. Так, на одному рядку можна розмістити декілька операторів. Між операторами в одному рядку ставиться двокрапка.
Будь-який рядок можна розбити на два, розташувавши наприкінці першої знаки символів «Пробіл» + «Знак підкреслення» (_), у разі другий рядок буде вважатися продовженням першої.
Для того, щоб зробити програму, що легко читається, використовують коментарі. У мові VBA існують два способи введення коментарів: застосування апострофа ('), який можна поставити в будь-якому місці рядка, і слово зарезервоване Rem замість апострофа.
1. Оператор Dimпризначений для оголошення типів змінних.
· Dim A As Integer - змінна A оголошується як ціле, тобто. у ній зберігатимуться лише цілі величини .
· Dim D As Date - оголошується змінна D для зберігання дат.
· Dim Прізвище, Найменування As String – оголошуються змінні. Прізвище та Назва, призначені для зберігання тексту.
· Dim B(12) As Integer – оголошується одновимірний масив (вектор), що складається з 12 цілих чисел, причому за замовчуванням перший елемент масиву буде B(0), а останній B(12).
· Dim B (3,3) As Single - оголошується двомірний масив 3х3 (матриця), що складається із дійсних чисел.
Якщо тип змінної не вказано, то за замовчуванням використовується тип Variant. Проте вказівку конкретного типу змінної робить програму надійніше і прискорює її, т.к. VBA не потрібно витрачати час на розпізнавання неоголошеної змінної при кожному зверненні до неї.
Якщо розмір масиву M заздалегідь не відомий і визначається в ході програми, то при описі масиву кількість елементів не вказується і масив визначається так:
Dim M() As Integer
Після визначення кількості елементів масиву, наприклад N, необхідно записати оператор
2. Оператор привласненняпризначений для привласнення змінної будь-якого значення.
Синтаксис:
Змінна (або якість об'єкта) = вираз.
· а = 5 - змінної А надати значення 5 ;
· b = «Менеджер» - змінної b привласнитизначення «Менеджер»;
· Адреса = Sheets ( "Організації"). Cells (2,2) - змінною адресою присвоїти вміст осередку B2, який знаходиться на аркуші Організації в поточній робочій книзі;
· Прізвище = UserForm1.TextBox1.Text - змінної Прізвище присвоїти вміст поля TextBox1 користувача форми UserForm1.
3. Оператор With/End withпозбавляє програміста великої кількості повторень імені однієї й тієї ж об'єкта.
Синтаксис:
With об'єкт
оператор1
оператор2
операторN
Наприклад, замість послідовності операторів
UserForm1.TextBox1.Text = Date
UserForm1.TextBox2.Text = “ “
UserForm1.ComboBox1.Text = “ “
можна записати так
TextBox1.Text = Date
. TextBox2.Text = “ “
. ComboBox1.Text = “ “
REM На аркуші Лист1 у стовпці А, починаючи з другого рядка, записані тарифи співробітників. Заповнимо поле зі списком ComboBox1 в ‘користувацькій формі UserForm1
'Перший рядок програми – на аркуші Лист1 у стовпці А 'підраховується кількість заповнених осередків, результат 'надається змінною N
N=Application.CountA(Sheets("Лист1").Range("A:A")).
D=”A2:A”&Cint(N)
Sheets("Лист1").Range(D).Name="Тарифи"
TextBox1.Text = Date
. TextBox2.Text = “ “
. ComboBox1.Text = “ “
. ComboBox1.Rowsource = "Тарифи"
4. Умовний оператор If/Then/Else– дозволяє перевірити деяку умову та залежно від результатів перевірки виконати ту чи іншу дію
Синтаксис:
Ifумова Thenоператори1 [ Elseоператори2]
Якщо умова істинна, то виконуються оператори1, інакше виконуються оператори2.
Допускається також використання складного умовного оператора, який записується як блок:
Ifумова1 Then
оператори1
ElseIfумова2 Then
Логічні оператори VBA використовуються для об'єднання результатів окремих логічних виразів, щоб створити складні критерії прийняття рішень у процедурі (таблиця Б.3).
Таблиця Б.3 - Логічні оператори VBA
Логічний |
Синтаксис |
Ім'я/Опис |
||
оператор |
||||
El And E2 |
Кон'юнкція. True, якщо обидва Е1 та Е2 |
|||
мають значення True, інакше – False |
||||
El Or E2 |
Диз'юнкція. True, якщо одне виро- |
|||
ження або обидва (Е1 і Е2) є |
||||
рівними True; інакше – False |
||||
Not El |
Заперечення. True, якщо Е1 має зна- |
|||
чення False; False, якщо El є |
||||
рівним True |
||||
El Xor E2 |
Виняток. True, якщо Е1 та Е2 |
|||
мають різні значення; інакше – |
||||
El Eqv E2 |
Еквівалентність. True,якщо Е1 має- |
|||
ет те саме значення, як і Е2; |
||||
інакше – False |
||||
El Imp E2 |
Імплікація. False, коли Е1 є |
|||
рівним True та Е2 одно False; інакше |
||||
True. |
||||
Операнди Е в цій таблиці є будь-яким допустимим виразом з результатом типу Boolean, таке як операція порівняння.
Пріоритети виконання операцій при обчисленні складних виразів
Складний (складний) вираз (complex expression) – це будь-який вираз, утворений з двох або більше виразів. У таблиці Б.4 зібрані операції за групами в порядку зменшення пріоритету, тобто чим нижче розташована група, тим пізніше виконуються зазначені в ній операції. Для зміни цього порядку використовуються круглі дужки. Операції одного рівня виконуються зліва направо у порядку, у якому записані у вираженні.
Таблиця Б.4 - Пріоритети операцій VBA
Оператор |
Коментарі |
Зведення у ступінь, найвищий пріоритет |
|
Унарний мінус – зміна знака числа |
|
Множення та поділ мають рівні пріоритети; вони ви- |
|
Поділ націло |
|
Залишок від поділу націло |
|
Складання та віднімання мають рівний пріоритет; вони ви- |
|
числяться у міру появи у вираженні зліва направо |
|
Будь-яка конкатенація рядків виконується після будь-яких |
|
арифметичних операцій у вираженні та перед будь-якими |
|
операціями порівняння чи логічними операціями |
|
<, <=, >, >=, |
Усі оператори порівняння мають рівні пріоритети і ви- |
числяться у міру появи у вираженні зліва направо. |
|
Використовуйте круглі дужки для групування опера- |
|
рів порівняння у виразах |
|
Логічні |
Not And Or Xor Eqv Im p – мають нижчий пріоритет |
оператори |
|
Додаток В Математичні функції VBA
VBA надає стандартний набір математичних функцій, наведений у таблиці В.1.
Таблиця B.1 – Математичні функції VBA
Повертає / дію |
||
Повертає абсолютне значення N |
||
Косинус кута N, де N – це кут, виміряний в ра- |
||
Повертає синус кута; N – це кут, виміряний у |
||
радіанах |
||
Повертає тангенс кута; N – кут у радіанах |
||
Повертає арктангенс N як кут у радіанах |
||
Повертає константу е, зведену до ступеня N (е – |
||
це основа натуральних логарифмів і вона (при- |
||
близько) дорівнює 2,718282 |
||
Повертає цілу частину N. Fix не округляє число, а |
||
негативним, Fix повертає найближче заперечення- |
||
тельне ціле більше, ніж або дорівнює N |
||
Повертає цілу частину N. Int не округляє число, а |
||
відкидає будь-яку дрібну частину. Якщо N є |
||
негативним, Int повертає найближче заперечення- |
||
тельне ціле менше, ніж або дорівнює N |
||
Повертає натуральний логарифм N |
||
Повертає довільне число; аргумент є не- |
||
обов'язковим. Використовуйте функцію Rnd тільки |
||
після ініціалізації VBA-генератора випадкових чи- |
||
сів оператором Randomize |
||
Повертає знак числа: –1 якщо N – негативне; |
||
1, якщо N-позитивне; 0, якщо N дорівнює 0 |
||
Повертає квадратний корінь з N. VBA відображає |
||
помилку виконання, якщо N – негативне |
||
Операнд N означає будь-яке чисельне вираз? допустиме в
Аргумент тригонометричних функцій (синусу, косинусу та тангенсу) задається в радіанах, а не в градусах. Зворотна тригонометрична функція A t n (x) повертає в програму значення кута в радіанах від -π/2 до π/2, тангенс якого дорівнює значенню арифметичного виразу х.
Зв'язок між радіанним і градусним заходами кута виражається через ірраціональне число π ≈ 3,14159265358979323846.
Радіани = |
Градуси× π |
Градуси = |
Радіани× 180 |
Для переведення градусів у радіани можна також використовувати вбудовану функцію Excel- Application.Radians(x ), де x - кут, за-
даний у радіанах.
Для отримання числового значення константи π з точністю 15 цифр мантиси можна використовувати вбудовану функцію Excel Application.Pi(), або функцію VBA арктангенс
Pi = Application.Pi () або Pi = 4 * Atn (1)
Приклади запису тригонометричних функцій
При написанні коду VBA Excel набір вбудованих операторів використовують на кожному кроці. Ці оператори поділяються на математичні, рядкові, оператори порівняння та логічні. Далі ми розглянемо кожну групу операторів.
Математичні оператори
Основні математичні оператори VBA перераховані у таблиці нижче.
У правому стовпчику таблиці вказано пріоритет операторів, прийнятий за умовчанням за відсутності дужок. Додаючи у вираз дужки, можна змінювати порядок виконання операторів VBA за власним бажанням.
Строкові оператори
Основний рядковий оператор Excel VBA – це оператор конкатенації & (злиття):
Оператори порівняння
Оператори порівняння використовуються для порівняння двох чисел або рядків та повертають логічне значення типу Boolean(True або False). Основні оператори порівняння Excel VBA перераховані в цій таблиці:
Логічні оператори
Логічні оператори, як і оператори порівняння, повертають логічне значення типу Boolean(True або False). Основні логічні оператори Excel VBA перераховані у таблиці нижче:
У наведеній вище таблиці перераховані в повному обсязі логічні оператори, доступні в VBA. Повний списоклогічних операторів можна знайти на сайті Visual Basic Developer Center.
Вбудовані функції
В VBA є безліч вбудованих функцій, які можуть бути використані при написанні коду. Нижче перераховані деякі з найчастіше використовуваних:
| Функція | Дія |
|---|---|
| Abs | Повертає абсолютну величину заданого числа.
|
| Chr | Повертає символ ANSI, що відповідає числовому значенню параметра.
|
| Date | Повертає поточну системну дату. |
| DateAdd | Додає певний часовий інтервал до заданої дати. Синтаксис функції: DateAdd( інтервал , число , дата ) Де аргумент інтервалвизначає тип часового інтервалу, що додається до заданої датиу кількості, зазначеній у аргументі число . Аргумент інтервалможе приймати одне з наступних значень:
|
| DateDiff | Обчислює кількість певних часових інтервалів між двома заданими датами.
|
| Day | Повертає ціле число, що відповідає дню місяця у заданій даті. Приклад: Day(“29/01/2015”)повертає число 29 |
| Hour | Повертає ціле число, яке відповідає кількості годин у заданому часі. Приклад: Hour(“22:45:00”)повертає число 22 |
| InStr | Приймає як аргументи ціле число і два рядки. Повертає позицію входження другого рядка всередині першого, починаючи пошук із позиції, заданої цілим числом.
Примітка:Аргумент-число може бути не заданий, у такому разі пошук починається з першого символу рядка, заданого у другому аргументі функції. |
| Int | Повертає цілу частину заданого числа. Приклад: Int(5.79)повертає результат 5. |
| Isdate | Повертає Trueякщо задане значення є датою, або False- Якщо датою не є.
|
| IsError | Повертає Trueякщо задане значення є помилкою, або False- Якщо помилкою не є. |
| IsMissing | Як аргумент функції передається ім'я необов'язкового аргументу процедури. IsMissingповертає Trueякщо для аналізованого аргументу процедури не передано значення. |
| IsNumeric | Повертає Trueякщо задане значення може бути розглянуто як число, в іншому випадку повертає False. |
| Left | Повертає задану кількість символів від початку переданого рядка. Синтаксис функції ось такий: Left( рядок , довжина ) де рядок– це вихідний рядок, а довжина– кількість символів, що повертаються, рахуючи від початку рядка.
|
| Len | Повертає кількість символів у рядку. Приклад: Len("абвгдеж")повертає число 7 |
| Month | Повертає ціле число, яке відповідає місяцю у заданій даті. Приклад: Month(“29/01/2015”)повертає значення 1. |
| Mid | Повертає задану кількість символів із середини переданого рядка. Синтаксис функції: Mid( рядок , початок , довжина ) де рядок– це вихідний рядок, початок- позиція початку видобувного рядка, довжина– кількість символів, які потрібно вийняти.
|
| Minute | Повертає ціле число, що відповідає кількості хвилин у заданому часі. Minute(“22:45:15”)повертає значення 45. |
| Now | Повертає поточну системну дату та час. |
| Right | Повертає задану кількість символів від кінця переданого рядка. Синтаксис функції: Right( рядок , довжина ) Де рядок– це вихідний рядок, а довжина– це кількість символів, які треба витягти, рахуючи від кінця заданого рядка.
|
| Second | Повертає ціле число, яке відповідає кількості секунд у заданому часі. Приклад: Second(“22:45:15”)повертає значення 15. |
| Sqr | Повертає квадратний корінь числової величини, що передається в аргументі.
|
| Time | Повертає системний час. |
| Ubound | Повертає верхній індекс вимірювання заданого масиву. Примітка:Для багатовимірних масивівяк необов'язковий аргумент може бути зазначено, індекс якого саме виміру треба повернути. Якщо не зазначено, то за замовчуванням 1. |
| знак | назва | Опис |
|---|---|---|
| = | Рівно | Повертає True якщо лівий та правий операнди рівні. Зверніть увагу, що це навантаження оператора присвоєння. |
| <> | Не дорівнює | Повертає True якщо лівий та правий операнди не рівні. |
| > | Краще ніж | Повертає True якщо лівий операнд більше правого операнда. |
| < | Менше ніж | Повертає True якщо лівий операнд менше правого операнда. |
| >= | Більше або дорівнює | Повертає True якщо лівий операнд більше або дорівнює правому операнду. |
| <= | Менше або дорівнює | Повертає True якщо лівий операнд менший або дорівнює правому операнду. |
| Is | Довідковий капітал | Повертає значення True, якщо посилання на лівий об'єкт - це той самий екземпляр, що й посилання на правий об'єкт. Він також може використовуватися з Nothing (посилання на нульовий об'єкт) з обох боків. Примітка.Оператор Is спробує змусити обидва операнди до Object перед виконанням порівняння. Якщо будь-яка сторона є примітивним типом або Variant , який містить об'єкт (або не-об'єктний підтип, або vtEmpty), порівняння призведе до помилки часу виконання 424 - «Потрібен об'єкт». Якщо будь-який операнд належить іншому інтерфейсуодного і того ж об'єкта, порівняння поверне True. Якщо вам потрібно перевірити справедливість як екземпляра, так іінтерфейсу, ObjPtr(left) = ObjPtr(right) використовуйте ObjPtr(left) = ObjPtr(right) . |
нотатки
Синтаксис VBA дозволяє «ланцюжка» операторів порівняння, але ці конструкції слід уникати. Порівняння завжди виконується ліворуч праворуч тільки на 2 операндах за раз, і кожне порівняння призводить до Boolean . Наприклад, вираз...
A = 2: b = 1: c = 0 expr = a > b > c
Може бути прочитаний в деяких контекстах як перевірка того, чи b між a і c . У VBA це оцінюється так:
A = 2: b = 1: c = 0 expr = a > b > c expr = (2 > 1) > 0 expr = True > 0 expr = -1 > 0 "CInt(True) = -1 expr = False
Будь-який оператор порівняння, крім Is використовуватися з Object як операнда буде виконуватися на повертається значення Object «s члена за замовчуванням . Якщо об'єкт не має члена за замовчуванням, порівняння призведе до помилки часу виконання 438 - "Об'єкт не підтримує його властивість або метод".
Якщо Object не ініціалізований, порівняння призведе до помилки часу виконання 91 - «Об'єктна змінна або З блокованою змінною блоку».
Якщо літерал Nothing використовується з будь-яким оператором порівняння, відмінним від Is, це призведе до помилки компіляції - "Неприпустиме використання об'єкта".
Якщо Object за замовчуванням Object є інший Object, VBA буде постійно викликати елемент за замовчуванням кожного наступного значення, що повертається до тих пір, поки не буде повернено примітивний тип або не буде піднята помилка. Наприклад, припустимо, що SomeClass є член за замовчуванням Value , який є екземпляром ChildClass з членом ChildValue по ChildValue . Порівняння...
Set x = New SomeClass Debug.Print x > 42
Оцінюватиметься як:
Set x = New SomeClass Debug.Print x.Value.ChildValue > 42
Якщо операнд є числовим, а іншийоперандом є String або Variant підтипу String, буде виконано числове порівняння. У цьому випадку, якщо String не може бути віднесено до числа, результатом порівняння буде помилка часу виконання 13 - "Невідповідність типу".
Якщо обидва операнда є String або Variant підтипу String , порівняння рядків буде виконуватися на основі параметра порівняння параметрів модуля коду. Ці порівняння виконуються за характером. Зверніть увагу, що символьне уявлення String містить число, не збігається із зіставленням числових значень:
Public Sub Example() Dim left As Variant Dim right As Variant left = "42" right = "5" Debug.Print left > right "Prints False Debug.Print Val(left) > Val(right) "Prints True End Sub
З цієї причини переконайтеся, що змінні String або Variant передаються до числа перед виконанням чисельних порівнянь нерівності.
Якщо один з операндів - це Date , то числове порівняння за базовим подвійним значенням буде виконуватися, якщо інший операнд є числовим або може бути перетворений на числовий тип.
Якщо інший операнд є String або Variant підтипу String який може бути перенесений в Date з використанням поточного мовного стандарту, String буде передана у Date . Якщо він не може бути застосований до Date у поточній локалі, результатом порівняння буде помилка часу виконання 13 – «Невідповідність типу».
Слід бути обережними при порівнянні значень Double або Single і Booleans. На відміну від інших числових типів ненульові значення не можна вважати True через поведінку VBA у просуванні типу даних порівняння з використанням числа з плаваючою точкою в Double:
Public Sub Example() Dim Test As Double Test = 42 Debug.Print CBool(Test) "Prints True." Debug.Print CBool(Test) = True "Prints True Debug.Print CDbl(-1) = CDbl(True) "Prints True End Sub
Оператори VBA: арифметичні, логічні, порівняння, присвоєння
Оператор- це найменша здатна виконуватись одиниця коду VBA. Оператор може оголошувати чи визначати змінну, встановлювати параметр компілятора VBA або виконувати будь-яку дію у програмі.
Арифметичних операторів у VBA всього 7. Чотири стандартні: додавання (+), віднімання (-), множення (*), поділ (/) та ще три:
- зведення у ступінь (^), наприклад 2^3 = 8 ;
- цілісний поділ (\). Поділяє перше число на друге, відкидаючи (не округляючи) дробову частину. Наприклад, 5\2 = 2 ;
- розподіл за модулем (Mod). Поділяє перше число на друге, повертаючи лише залишок від розподілу. Наприклад, 5 Mod 2 = 1.
Оператор присвоєння VBA - знак рівності. Можна записувати так:
Let nVar = 10
а можна ще простіше:
nVar = 10
У другому випадку не плутайте знак рівності з оператором рівності.
Вираз
nVar = 10
означає "присвоїти змінній nVar значення 10", а якщо рядок виглядає так:
If (nVar = 10)
то це означає "якщо значення змінної nVar дорівнює 10".
Якщо змінної необхідно призначити об'єкт, робиться це іншими методами.
Операторів порівняння в VBA всього 8:
- рівність (=), наприклад, If (nVar = 10);
- більше, ніж і менше, ніж (> і<), например, If (nVar > 10);
- більше або одно і менше або одно (>= і<=), например, If (nVar >= 10);
- не дорівнює (<>), наприклад, If(nVar<>10) ;
- порівняння об'єктів (Is). Визначає, чи посилаються об'єктні змінні на той самий об'єкт або на різні, наприклад, If(obj1 is obj2);
- подібність (Like). Порівнює рядковий об'єкт із шаблоном та визначає, чи підходить шаблон.
Оператори порівняння завжди повертають true або false - true, якщо твердження є істинним, і false, якщо хибно.
Трохи для порівняння строкових значень:
- при порівнянні рядкових значень регістр враховується;
- прогалини у рядкових значеннях також враховуються;
- при порівнянні текстових рядків на більше/менше за умовчанням порівнюються просто двійкові коди символів - які є більшими або меншими. Якщо потрібно використовувати той порядок, що йде в алфавіті, можна скористатися командою
Option Compare Text
Докладніше про оператор Like. Загальний синтаксис виглядає як
Вираз1 Like Вираз2
При цьому Вираз1 – будь-який текстовий вираз VBA, а Вираз2 – шаблон, який передається оператору Like. У цьому шаблоні можна використовувати спеціальні символи підстановки (див. Табл. 3.1)
Табл. 3.1Підстановочні символи для оператора LIKE
Дуже часто під час перевірки кількох умов використовуються логічні оператори:
- AND - логічне І, повинні бути істинними обидві умови;
- OR - логічне АБО, має бути істинним хоча б одну з умов;
- NOT - логічне заперечення, що повертає TRUE, якщо умова хибна;
- XOR – логічний виняток. У виразі E1 XOR E2 повертає TRUE, якщо E1 = TRUE або тільки E2 = TRUE, інакше - FALSE;
- EQV - еквівалентність двох виразів, що повертає TRUE, якщо вони мають однакове значення;
- IMP - імплікація, що повертає FALSE, якщо E1 = TRUE і E2 = FALSE, інакше - TRUE.
Пам'ятати треба про AND, OR, NOT, інші логічні оператори застосовуються рідко.
Майже у будь-якій програмі VBA використовуються оператори конкатенації. У VBA їх два – + або &. Рекомендується завжди використовувати &, тому що:
- при використанні & виконується автоматичне перетворення числових значень на рядкові - немає небезпеки припуститися помилки;
- при використанні оператора + додавання рядкового значення зі значенням типу Null дає Null.
MsgBox "Повідомлення користувача" & vUserName
Порядок застосування операторів можна регулювати за допомогою круглих дужок.
