1Структура Excel-97

Вид материала

Документы

Содержание 4Використання VBA в Excel. 4.2Доступ до даних в "книгах MS Excel" Worksheets("My sheet").Range("B2") 4.2.1Пряма адресація 4.2.2Відносна адресація

Подобный материал:

• Применение Microsoft Excel для обработки табличных данных. Выполнение расчетов в таблицах, 14.68kb.
• Введение в Excel Цели, 81.13kb.
• Реферат на тему, 302.36kb.
• Загальна характеристика табличного процесора, 109.35kb.
• Окно программы ms excel 2 Основные понятия ms excel. 2 Адреса ячеек 3 Типы данных, 742.75kb.
• Назначение программы Microsoft Excel (или просто Excel ) и создание и обработка электронных, 184.32kb.
• Программа Excel курсоваяработ а натем у: "прикладная программа excel", 583.33kb.
• Программа курса повышения квалификации профессорско-преподавательского состава по программе:, 46.44kb.
• Пособие для начинающих Воробьев В. В. Microsoft Excel, 439.68kb.
• Курс лекций по дисциплине " основы компьютерных технологий" Часть II. Microsoft Excel, 457.54kb.

4Використання VBA в Excel.

Використання даного матеріалу вимагає від користувачів знання та навичок програмування на мові Basic.

4.1Знайомство з VBA.

Електроний процесор Excel-97 дає можливість створювати програми на мові Visual Basic for Application (VBA), яка своїми основними командами відповідає мові Qbasic, що вивчалася в курсі "Інформатика і КТ" і відрізняється від неї в основному командами введення/виведення інформації. Оператори введення/виведення данних мови VBA дозволяють безпосередньо отримувати дані, які розміщені в комірках листа Excel.

Простий синтаксис мови робить її інтуїтивно зрозумілою. Користувачі, що мають досвід програмування на мові Basic (або її діалектах, наприклад Qbasic) мають можливість повною мірою використовувати свої знання при програмуванні на VBA.

Ця можливість дозволяє створювати поряд з "стандартними" функціями обробки даних ЕТ Excel нові функції, конкретизовані на розв‘язок поставленої задачі.

Мова VBA є об‘єктно-орієнтованою мовою. В даному розділі розглянемо основні особливості програмування на VBA в Excel, які дозволять непідготованому користувачеві створювати прості програми, не вдаючись в подробиці об‘єктно-орієнтованого програмування.


Перша програма на мові VBA.

Вибираємо пункт меню Сервіс-Макрос-Макроси. В діалоговому вікні МАКРОС (рис.2) записуємо ім‘я макроса, що створюється. Зверніть увагу на те, що елементи керування даного вікна дозволяють виконувати такі операції над макросами як: виконання, знищення та редагування існуючих макросів.

Наприклад введемо нове ім‘я: "NewMacros".



Рисунок 2

З допомогою кнопки "Создать" створюється макрос і автоматично завантажується редактор VBA, зовнішній вигляд якого подано на рис.3.



Рисунок 3


Відредагуємо текст макроса NewMacros вставивши відмічені рядки в його макет:

Sub NewMacros()

Worksheets.Add

Worksheets(1).Name = "My sheet"

Worksheets("My sheet").Range("A1").Value = "Hello Word"

Worksheets("My sheet").Range("A2").Value = "(2+5)*3="

Worksheets("My sheet").Range("B2").Value = (2 + 5) * 3

End Sub

Використовуючи пункт меню "Запуск"-"Запуск подпрограмм" (F5 або відповідну кнопку на панелі інструментів), запустимо на виконання даний макрос.



Рисунок 4


Натиснувши кнопку з піктограмою Excel (або використовуючи комбінацію клавіш Alt+Tab) повернемося до додатку Excel. Значення комірок A1,A2,B2 змінилося: в комірках A1,A2 тепер розміщено текст, а комірка B2 містить результат виконання операції обчислення виразу.

4.2Доступ до даних в "книгах MS Excel"

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

Visual Basic дозволяє проводити опрацювання таких об'єктів за допомогою методів або властивостей.

Наприклад, в MS Excel застосовуються:

Workbook – об‘єкт; книга MS Excel;

Worksheet – об‘єкт; листок (Worksheet) книги MS Excel;

Cells – об‘єкт; комірка на листі книги MS Excel;

Range – об‘єкт; діапазон комірок на листі книги MS Excel.


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

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

Наприклад:

об'єкт MS Excel Workbooks визначає всю сукупність об'єктів Workbook;

об'єкт MS Excel Worksheets визначає всю сукупність об'єктів Worksheet.

Елемент сімейства може бути ідентифіковано за ім‘ям або номером.

Звернемося до прикладу з попереднього розділу:

Worksheets("My sheet") — визначає звернення до відкритого об'єкту Worksheet з ім‘ям "My sheet";

Workbooks(1) — визначає перший відкритий об'єкт Workbook;


Метод являє собою дію, виконувану над об'єктом.

Наведемо приклад використання методу:

Worksheets.Add

Метод Add виконує дію над об‘єктом Worksheets – додає новий лист до книги (об‘єкт Worksheet).

Опис про підтримувані об'єктом властивості і методи міститься в документації до використовуваного додатка (Excel-97).


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

Щоб змінити значення властивості об'єкта, треба ідентифікувати об'єкт, через крапку вказати ім'я властивості, потім проставити знак рівності (=) і нове значення властивості.

В прикладі представленому на рис.2, змінюється властивість Value¹ об‘єкта Worksheets("My sheet").Range("B2"):

Worksheets("My sheet").Range("B2").Value = (2+5)*3

Щоб прочитати значення властивості об'єкта, треба записати ідентифікатор змінної, якій ми маємо присвоїти це значення, потім знак рівності (=), далі вказати об'єкт і через крапку ім'я властивості.

Наприклад, прочитаємо значення властивості Value об‘єкта Range("B2") в змінну Xm:

Xm= Range("B2").Value


Будемо розглядати книгу MS Excel як сукупність листів, які в свою чергу представляють набір комірок (Cells). В деяких випадках, зручно працювати з групами комірок. Такі групи розглядатимемо, як діапазони комірок (Range).

Доступ до даних, розміщених в книгах MS Excel в VBA передбачає точне визначення адреси комірки(-ок), яка містять данні в межах листа, книги. Іншими словами, необхідно вказати книгу, листок, комірку, де розміщені дані.

При програмуванні в VBA використовується два типи адресації:

• пряма (за ім‘ям чи за індексом (index));
  
• відносна.
  

4.2.1Пряма адресація

Синтаксис

ObjectName[(arg)]

де

ObjectName	імя об‘єкта;
Arg	параметр, який визначає фізичне імя об‘єкта; використовуєтся в двох нотаціях: A1, R1C1.

В нотації А1 відбувається звертання до об‘єкта по його імені, а в нотації R1C1 по номерам рядків і стовпців ЕТ.


Приклад звертання до книги в нотації A1:

Workbooks("C:\MyFolder\MyBook.xls")

Приклад звертання до листа книги в нотації A1:

WorkSheets("Лист1")

Наступні приклади демонструють звертання до діапазону комірок в нотації A1:

Звертання	Опис
Range("B25")	Комірка B25
Range("A1:B5")	Блок комірок A1:B5
Range("C5:D9,G9:H16")	Блоки комірок C5:D9 та G9:H16
Range("A:A")	Стовпчик A
Range("A:A,C:C,F:F")	Стовпчик A, C, F
Range("A:C")	Стовпчики A, B, C
Range("1:1")	Рядок 1
Range("1:1,3:3,8:8")	Рядки 1, 3, 8
Range("1:5")	Рядок з 1 по 5

Стиль нотації R1C1 використовується для звертання по адресам комірок ЕТ. Наступний приклад демонструє використання стилю нотації R1C1 при адресації до комірок:

For i = 1 To 20

If Abs(Worksheets("Sheet1").Cells(i, 3).Value) < 0.01 Then

Worksheets("Sheet1").Cells(i, 3).Value = 0

EndIf

Next i


Як було зазначено при прямій адресації звертатися до книги або її листа можна не тільки за ім‘ям, але і за індексом. Справа в тому, що після створення або відкриття книг в MS Excel, їм присвоюється індекс, який відповідає порядку їх створення (відкриття). Такі ж індекси надаються листам книги.

Приклад індексного звертання до книги:

Workbooks(1)

Приклад звертання до першого листа книги за його індексом:

Workbooks("Book1").Sheets(1)

4.2.2Відносна адресація

Відносна адресація комірок виконується методом Offset(<рядок>,<стовпець>), який визначає комірку відносно активної (поточної).

Наступний приклад демонструє роботу з активними комірками:

Sub a()

Workbooks(1).Worksheets(1).Range("B5").Activate

ActiveCell.Value = 555

ActiveCell.Offset(2, 0).Value = 777

End Sub


На листі MS Excel є маркер заповнення - чорний квадрат що позначає активну комірку.

Щоб розмістити в комірці, наприклад "B5", число, ми переміщуємо маркер на вказану комірку (робимо її активною). Потім редагуємо вміст вказаної активної комірки.

Все це можна виконати програмно.

В даному прикладі застосовується метод Activate до комірки "B5". При цьому комірка стає активною. Залишається змінити властивість Value активної комірки (об‘єкт ActiveCell): ActiveCell.Value = 555.

Наступна інструкція виконує зміну властивості Value для комірки "B7", що розташована на 2 рядки нижче по відношенню до комірки "B5" в поточному стовпчику.

ActiveCell.Offset(2, 0).Value = 777


Об‘єкти Workbooks і Worksheets також мають метод Activate. Розглянемо наступний приклад:

Sub a()

Workbooks(1).Worksheets(1).Activate

Range("С5").Value = 5557

Cells(1, 1).Value = 8777

End Sub


Інструкція Workbooks(1).Worksheets(1).Activate робить перший лист першої відкритої книги активним. Після визначення активного листа для звернення до комірки можна застосовувати відносну адресацію:

Range("С5").Value = 5557

Cells(1, 1).Value = 8777

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