Лекція 18 "Інформатика та комп'ютерна техніка" Тема Мова програмування Visual Basic в застосуванні до ms excel об’єкти vba: комірка, область, елемент керування, сторінка, книга, застосування, та їх методи
| Вид материала | Лекція |
- Лекція 5 "Інформатика та комп'ютерна техніка" Тема Операційна система microsoft windows, 51.8kb.
- «Программирование в среде Visual Basic», 469.58kb.
- Лекція 21 "Інформатика та комп'ютерна техніка" Тема Бази даних та системи керування, 106.88kb.
- Робоча навчальна програма з дисципліни " Інформатика та комп’ютерна техніка" для професійного, 660.32kb.
- Текст програми це набір інструкцій (команд), які можуть бути виконані комп'ютером., 221.57kb.
- Робоча навчальна програма з дисципліни " Статистичні методи у наукових дослідженнях", 246.28kb.
- Лекція 6 "Інформатика та комп'ютерна техніка" Тема Сервісні та прикладні програми Види, 55.04kb.
- Лекція 3 "Інформатика та комп'ютерна техніка" Тема Основи побудови та функціонування, 125.34kb.
- Опис модуля назва модуля: Visual Basic для додатків. Код модуля, 40.19kb.
- Лекція 4 "Інформатика та комп'ютерна техніка" Тема Системне програмне забезпечення, 99.46kb.
Лекція 18 "Інформатика та комп'ютерна техніка"
Тема 7.2. Мова програмування Visual Basic в застосуванні до MS EXCEL
Об’єкти VBA: комірка, область, елемент керування, сторінка, книга, застосування, та їх методи.
Об’єкти VBA – це такі програмні одиниці, що містять у себе і дані і процедури їх обробки. Багато з них мають візуальне відображення на екрані: комірки таблиці, робочі листи, панелі керування і навіть Excel в цілому – все це є екземпляри базових об'єктів. Об'єкти можуть бути вкладені один в одного, до них можна звертатися з мови програмування VBA.
Наприклад, якщо ви бажаєте виконати якісь дії з коміркою робочого листа, наприклад, занести у комірку А5 листа "Лист1" книги "MyBook.XLS" число 654, треба записати наступне:
Workbooks("MyBook.XLS ").Sheets("Лист1").Range("A5") .value = 654
Тут з усіх відкритих на поточний час об'єктив-книг використовується екземпляр з назвою "MyBook.XLS ". З усіх його листів обирається об'єкт-лист з назвою "Лист1". В ньому розшукується об'єкт-група комірок, що у даному випадку складається з однієї комірки "A5". І тільки після цього значення об'єкту-комірки набуває числа 654.
Властивості об’єкту VBA – це константи і змінні, що належать екземпляру об'єкта і несуть у себе дані про цей конкретний екземпляр. Наприклад, розмір та колір окремої комірки, формула, що в неї записана, значення тій формули і багато іншого є властивостями об'єкту "комірка".
Наприклад, об'єкт Range, тобто група комірок, має понад 80 властивостей. З них часто використовуються наступні: .Value – значення, що записано або обчислено, .Formula - формули для обчислень, .Font – шрифт та його атрибути для даної групи комірок, .Borders – параметри рамки.
Методи об’єкту VBA – це макроси (процедури і функції), що зв'язані з об'єктом і можуть виконуватися над значеннями властивостей його екземплярів. Методи і властивості записуються після назви об'єкту через крапку. Оскільки в результаті виконання методів можуть виникати нові екземпляри об'єктів або посилання на вже існуючі екземпляри, до них теж можна застосовувати методи. Тому мають сенс і часто використовуються ланцюжки типу Об'єкт.Метод1.Метод2.Метод3...
Наприклад, об'єкт Range, має понад 70 методів. З них часто використовуються наступні: .Select – обирає групу комірок для поточної обробки, .Activate – робить комірку активною, тобто видимою на екрані, і ставить на неї маркер, .Calculate – обчислює формули у комірках, .Clear – знищує всі дані та форматування у комірках, .ClearContents – знищує зміст комірки, не порушуючи її форматування, .Delete – видаляє комірки.
Багато методів та властивостей є однаковими для різних видів об'єктів, але треба бути уважними до особливості їх застосування.
Уявлення даних: типи даних, константи та змінні. Масиви.
Константи – це явно задані або означені дані. Вони об'являються зі словом Const.
Наприклад, Const Пи_5 = 3.14159, Const Имя1 = "Всеволод". Тут Пи_5, Имя1 – найменування констант, а 3.14159, "Всеволод" – їх значення. Константи не можуть зміняти своїх значень при виконанні макросу.
Змінні – це дані, значення яких можна змінювати при виконанні макросу. Вони об'являються зі словом Dim. Змінні мають тип.
Наприклад Dim I27 As Integer, Dim Пи_8 as Double. Тут I27, Пи_8 – найменування змінних, а Integer, Double – їх тип.
Типи даних, що використовуються у VBA, зведено у наступну таблицю:
| Тип | Розмір у байтах | Інтервал визначення |
| Boolean | 2 | тільки True або False |
| Integer | 2 | від -32 768 до 32 767 |
| Long | 4 | від -2 147 483 648 до 2 147 483 647 |
| Single | 4 | від -3.402823E38 до 3.402823E38 |
| Double | 8 | від -1.79769313486232E308 до 1.79769313486232E308 |
| Currency | 8 | -922,337,203,685,477.5808 to 922,337,203,685,477.5807. |
| Date | 8 | от 1 января 0100 г. до 31 декабря 9999. |
| Object | 4 | будь-яке посилання на об'єкт. |
| String | по1 на символ | от 0 до 65 535 символів |
| Variant | 16 + по 1 на символ | діапазон Double або String |
| Структура (Type) | сумм. розмір елементів | стосовно до типу елементів |
Тип Variant використовується, якщо його задано явно, або коли інше не вказано. Змінні цього типу можуть приймати значення замість будь-якого з інших простих типів.
Для завдання константи достатньо записати службове слово Const, після якого її ім'я, потім знак "=" і саме значення. Текстові константи беруться у лапки. Наприклад:
Const Пи_5 = 3.14159
Const Имя1 = "Всеволод"
Дни_недели = ("Понедельник", "Вторник", "Среда", "Четверг", "Пятница", _
"Суббота", "Воскресенье")
Для завдання змінних використовується службове слово Dim, після якого ставиться її ім'я, потім службове слово As і вказується тип змінної. Наприклад:
Dim Площадь As Double
Dim Ширина
Dim I, J22, K3_a As Integer
Окремим видом змінних є масиви. При їх завданні після імені змінної ставиться у дужках список інтервалів зміни індексів (у простішому випадку - кількість елементів у масиві). Наприклад:
Dim АА(50)
Dim ВВ3(1 To 5, 4 To 9, 3 To 5) As Double
Dim Массив_из_неопределенного_заранее_числа_вариантов()
Якщо треба створити масив з наперед заданими визначеннями елементів, можна скористатися функцією Array. Але слід пам'ятати, що вона повертає не справжній масив, а змінну типу Variant, що містить у себе заданий масив у символьному виразі.
Якщо треба кількість елементів може бути визначена тільки під час розрахунків, і тому не можна об'явити масив заздалегідь, або по-суті треба створити динамічний масив, використовується оператор ReDim. Наприклад,
ReDim Массив_1(N) As Integer
Своєрідними масивами стають списки однотипних елементів, що містяться у об'єктах. Наприклад:
Set AAA_1 = ActiveWorkbook.Names
' Объявление идентификатора ААА_1 для списка имен,
' назначенных ячейкам активной книги
Set RRR_1 = ThisWorkbook.Sheets("Лист1").Range("A5:D10")
' Список всех ячеек в диапазоне A5:D10 на Листе 1
Для визначення реальної кількість елементів у такому випадку використовується метод Count. Наприклад, вираз:
MsgBox ("Всего ячеек в диапазоне " + RRR_1.Count)
видасть повідомлення про наявність комірок у діапазоні A5:D10.
Керуючі структури VBA: оператори розгалуження, вибору та циклу.
Оператор розгалуження перевіряє задану умову та стосовно до її істинності виконує перший або другий операнди. Наприклад:
Function Квадратный_корень_от_частного (X, Y) As Double
If Y=0 Then
MsgBox ("Нельзя делить на 0")
Elseif ((X<0) and (Y>0)) or ((X>0) and (Y<0)) Then
MsgBox ("Нельзя брать кв_корень из отрицательного числа")
Else
Квадратный_корень_от_частного =Sqr(X/Y)
MsgBox ("Порядок!")
End If
End Function
Оператор вибору використовується тоді, коли треба виконувати декілька операндів у відповідності зі значенням змінної або виразу-ключа. Наприклад:
Function Проверка_ячейки(X As Integer) As String
Select Case X
Case Is < 0
Проверка_ячейки = "Так не бывает..."
Case 1 To 18
Проверка_ячейки = "Маловато будет..."
Case 19, 20
Проверка_ячейки = "Достаточно?"
Case 21
Проверка_ячейки = "Ура!"
Case Else
Проверка_ячейки = "Перебор :-("
End Select
End Function
Оператори циклу бувають декількох видів.
Перш за все це оператори, в яких явно вказано діапазон зміни спеціального лічильника. Наприклад, у наступному макросі використано два цикли, що вкладені один у другий. Лічильниками є N_строки, що зменшується від 14 до 2 із кроком -2, тобто через один рядок, і N_столбца, що для кожного обраного рядку пробігає значення від 1 до 8. Тим самим обирається пара комірок, для котрої встановлюються атрибути: колір, розмір тощо, та видаляється значення.
Sub Проверка_For()
Set S1 = ThisWorkbook.Sheets("Автозапис")
For N_строки = 14 To 2 Step -2
For N_столбца = 1 To 8
With S1.Range(S1.Cells(N_строки, N_столбца), _
S1.Cells(N_строки - 1, N_столбца))
.ClearContents
End With
Next N_столбца
Next N_строки
End Sub
Аналогічно попередньому організовано цикл For Each, різниця в тому, що він базується не на лічильнику, а на окремих елементах, що поступово вибираються із списку. Наприклад, цикл
For Each NС In ThisWorkbook.Sheets("Автозапис").Range("A5:F15")
NС.Value = 5
Next NС
запише число 5 у кожну комірку діапазону A5:F15.
Зовсім інакше працює цикл While. Він виконується доти, доки умова у заголовку є істиною. Наприклад, цикл
i=1
While ThisWorkbook.Sheets("Автозапис").cells(1,i) <> ""
i=i+1
Wend
буде перебирати всі комірки у першому рядку, доки не знайде пусту.
Процедури та функції.
Макрос – це текст програми, що замінює ручний ввід команд. У Excel використовується для автоматизації обробки електронних таблиць, програмування діалогів тощо. Макрос завжди має ім'я, завдяки якому його може бути викликано на виконання. Макроси можуть бути параметризовані, тобто мати список параметрів, через який отримують вихідні дані та повертають результати обчислень. Наприклад, макрос
Розрахунок_рентабельності( Назва_підприємства, Норма_відрахувань, Коефіцієнт ) має ім'я Розрахунок_рентабельності і два параметри, що розділяються комою. Він розраховує коефіцієнт рентабельності для заданого у першому параметрі підприємства виходячи з норми відрахувань (другий параметр). Результат повертається у змінній "Коефіцієнт".
Необов'язково всі необхідні для розрахунків дані мають передаватися через параметри, вони можуть братися макросом з робочих аркушів книги Excel, глобальних змінних і констант тощо.
Макроси складаються з керуючих структур VBA, арифметичних та логічних виразів, викликів інших макросів, об'явлення констант та змінних, інших команд.
Макроси VBA поділяються на два види – процедури і функції.
Функція – це такий макрос, ім'я якого може використатися як змінна, що повертає результат розрахунків. Він може використовуватися як функція робочого листа, але його не може бути назначено команді меню, кнопці та іншим елементам керування. Наприклад:
Function Площадь3(Ширина, Высота) As Double
Площадь3 = Ширина * Высота
End Function
Процедура – це макрос, що на відміну від функції не повертає результати через своє ім'я і через те не може використовуватися як функція робочого листа. Але якщо він не має параметрів, його можна призначати елементам керування. Наприклад:
Sub Площадь2()
i = ActiveCell.Row
j = ActiveCell.Column
ActiveSheet.Cells(i, j) = ActiveSheet.Cells(i, j - 1).Value _
* ActiveSheet.Cells(i, j - 2).Value
End Sub
Стандартні функції: обробка та перетворення рядків, математичні, статистичні тощо.
Деякі функції обробки рядків: Len(S1) – повертає кількість символів у рядку S1, Mid(S1, i1, L), Left(S1, L), Right(S1, L) – повертають частину рядка S1 довжиною L символів, що узята відповідно з i1-го символу, з лівого та з правого кінця. Функція Val(S2) і перетворює текст у число, а функція CStr(V1) навпаки.
Деякі математичні функції: Sqr(X) – квадратний корінь аргументу Х, Abs(X) – модуль числа, Sin(Alpha) – сінус кута у радіанах, Sgn(X) – знак числа (1 для позитивних, -1 для від'ємних, 0 для 0), Rnd(X) – для генерації випадкових чисел.
Статистичні функції використовуються у SQL-запитах до зовнішніх баз даних для розрахунку статистичних параметрів наборів даних.
Наприклад Avg(СтоимостьДоставки) – розраховує середнє арифметичне по полю СтоимостьДоставки.
Щоб більш дізнатися про призначення цих функцій, можна встановити курсор на них та натиснути на Alt-F1.
Розробка структури програми.
Текст програми – це набір інструкцій (команд), які можуть бути виконані комп'ютером. Текст програми записується на мові програмування, у даній лабораторній роботі це мова Visual Basic у орієнтації на створення додатків до системи Excel (VBA).
Програма складається з макросів – процедур та функцій, і зберігається у модулі з власним ім'ям. На початку модуля розташовуються глобальні дані (константи та змінні), що будуть доступні у всіх макросах. Далі йдуть самі макроси.
Розробка програми – складається з підготовки тексту програми, його трансляції та налагоджування. У Excel для розробки використовується загальне для всіх компонентів MS Office середовище програмування Visual Basic. Воно містить текстовий редактор, транслятор, налагоджувач та довідкову систему.
Трансляція програми – обробка тексту програмою – транслятором для перетворення у внутрішню форму (машинний код або внутрішнє уявлення) для ефективного виконання. Під час трансляції перевіряється правильність тексту і виводяться повідомлення про помилки. Як правило, курсор встановлюється на місце, де транслятор знайшов помилку, і виводиться повідомлення про її характер.
Виконання програми – поступове виконання команд, записаних у тексті програми і (можливо) перетворених у машинний код. При виконанні можуть мати місце помилки, які не були виявлені на етапі трансляції. Наприклад – відсутність належних даних, хибність алгоритму тощо. Для пошуку та усунення таких помилок застосовується покрокове виконання програми з перевіркою проміжних результатів засобами налагоджувача.
Питання самоінсталяції застосувань, що створені у середовищі MS Excel.
Програмування роботи з файлами та використання процедур з динамічних бібліотек.
22.9.1999 11:20:00 AM