Программирование VBA. Федеральное агентство по образованию государственное образовательное учреждение высшего профессионального образования
 Скачать 2.93 Mb.
|
|
Математические операции Операнд!] + [Операнд2] Сложение [ Операнд!] -- [Операнд2] Вычитание - [Операнд] Перемена знака Операнд!] * [Операнд2] Умножение [Операнд!] / [Операнд2] Деление [Операнд1] \ [Операнд2] Целочисленное деление [Операнд1] Mod [Операнд2] Остаток от деления по модулю [Операнд1] ^ [Операнд2] Возведение в степень Операции отношения [Операнд1] < [Операнд2] Меньше [Операнд1] > [Операнд2] Больше [Операнд1] <= [Операнд2] Меньше или равно [Операнд1] >= [Операнд2] Больше или равно [Операнд1] <> [Операнд2] Не равно [Операнд1] = [Операнд2] Равно [Операнд1] Is [Операнд2] Сравнение двух операндов, содер- жащих ссылки на объекты [Операнд1] Like [Операнд2] Сравнение двух строковых выраже- ний 21 Логические операции [Операнд1] And [Операнд2] Логическое умножение [Операнд1] Or [Операнд2] Логическое сложение [Операнд1] Хог [Операнд2] Исключающее or (или) [Операнд1] Not [Операнд2] Логическое отрицание Строковые операции [Строка1] & [Строка2] Сложение (объединение) строк Приоритеты операций Приоритет Операция 1 Вызов функции и скобки 2 ^ 3 – (смена знака) 4 *, / 5 \ 6 Mod 7 +, – 8 >, <, >=, <=, = 9 Not 10 And 11 Or 12 Xor 2.8. Объектная модель Excel Как отмечено во введении, при разработке приложений в VBA можно использовать средства процедурного программированияи объектно- ориентированного программирования. Программирование в Excel строит- ся на языке VBA и объектной модели Excel. Приложение Excel состоит из объектов, функциональные возможности которых можно использовать в программах, написанных на языке VBA. Совокупность объектов Excel на- зывается объектной моделью Excel. 2.8.1. Свойства и методы объектов При работе с объектами используются такие термины, как свойства и методы объектов. Объект инкапсулирует (объединяет в себе) его свой- ства и методы, которые доступны из программы, составленной на VBA. Использование объекта сводится к использованию его свойств и методов. Свойство – это совокупность характеристик и атрибутов, описываю- щих объект. С помощью свойств можно задать ,например, цвет, значение, шрифт или формат диапазона ячеек. Одни свойства доступны только для чтения, а другие доступны как для чтения, так и для записи (изменения). Метод представляет собой действия, выполняемые объектом. На- пример, метод Clear (Очистить) объекта Range (Диапазон) удаляет содер- жимое ячеек диапазона. Методы могут принимать значения параметров, уточняющие характер действия, которое необходимо выполнить. 22 2.8.2. Отображение объектной модели Excel Для освоения способов управления Excel с помощью VBA необходи- мо ознакомиться с объектной моделью Excel. Объектная модель описы- вает объекты приложения и связи между ними. В объектной модели Excel представлено более 100 объектов. В большинстве случаев используются не более 20 объектов. Для отображения всех объектов Excel нужно вы- полнить следующие действия. 1. Открыть новую рабочую книгу. 2. Нажать комбинацию клавиш Visual Basic. 3. На вкладке Введите вопрос ответов ввести текст: Microsoft Excel Objects. 4. Нажать клавишу Excel Object. Отобразится диаграмма объектной модели (рис. 1). 5. Щелкнуть на стрелке, направленной влево, над строкой Microsoft Excel Objects (стрелка окрасится в голубой цвет). Отобразятся объ- екты уровня рабочего листа (рис. 2). Рис. 1. Объектная модель Excel 23 Рис. 2. Объектная модель Worksheet (Рабочий лист) Вершиной иерархии объектной модели Excel является объект Application (Приложение). На следующем уровне иерархии объектной мо- дели находится объект WorkBook (Рабочая книга), который совпадает с файлом рабочей книги Excel. Объект WorkBook содержит объекты более низкого уровня, в частности объект Worksheet (Рабочий лист). Объект Worksheet, в свою очередь, состоит из других объектов (рис. 2). Среди более 100 объектов Excel в программировании на VBA боль- шей частью используются следующие: 24 Application (Приложение) WorkBook (Рабочая книга) Worksheet (Рабочий лист) Range (Диапазон) Chart (Диаграмма) Объект Application представляет саму программу Excel. Объект WorkBook представляет рабочую книгу Excel, т.е. файл Excel. Объект Worksheet (Рабочий лист) – отдельная страница в рабочей книге, имеющая индивидуальное имя и предназначенная для хранения данных и выполнения вычислений. Рабочий лист в свою очередь состоит из ячеек. Ячейки представляет объект Range, который может состоять из одной или нескольких ячеек. Одним из часто используемых объектов является объект Chart (Диа- грамма). Все, действия, которые можно выполнить с диаграммами в Excel, можно запрограммировать в VBA. 2.8.2.1. Иерархия объектной модели На вершине объектная модель Excel находится объект Application. Под этим объектом расположены другие объекты, среди которых находит- ся WorkBook. В терминологии объектно-ориентированного программиро- вания такое подчинение одного объекта другому называется вложением, т.е. объект WorkBook вложен в объект Application, а объект Worksheet – в объект WorkBook. 2.8.2.2. Ссылка на объекты в коде VBA Концепция вложения объектов широко используется при создании ссылок на объекты в коде VBA. Для полного определения объекта необхо- димо указать последовательно все уровни иерархии объектной модели. Например, чтобы сослаться на ячейку А1 на рабочем листе Лист1 рабочей книги Книга1, надо применить следующий код: Application.Workbooks("Книга1").Worksheets("Лист1").Range("A1") Если в программе нет ссылки на другое активное приложение, то объект Application можно не указывать, и предыдущая ссылка может быть представлена так: Workbooks("Книга1").Worksheets("Лист1").Range("A1") Если активной является Книга1, то объект Книга1 можно не указы- вать, и предыдущая ссылка может быть представлена так: Worksheets("Лист1").Range("A1") Аналогично, если активным листом является Лист1, то ссылка может быть записана так: Range("A1") 25 Указание иерархии объектов VBA аналогично указанию иерархии папок в файловой системе Windows при назначении пути для доступа к файлу. 2.8.3. Работа с объектами Работа с любым объектом заключается в следующем: задание свойств объекта; получение значений свойств объекта; выполнение методов объекта. 2.8.3.1. Задание свойств объекта Задание значения свойству объекта имеет следующий формат: Объект.ИмяСвойства = Значение Объект обозначает имя объекта, а ИмяСвойства – соответственно имя свойства, которому присваивается значение. Имя объекта отделяется от имени свойства точкой. Например, чтобы определить свойство Value (Зна- чение) объекта Range , т.е. поместить в ячейку A1 значение 10, а в ячейку B1 значение Товар, применяются следующие инструкции: Range("A1").Value = 10 Range("B1").Value = “Товар” Чтобы получить значение свойства объекта, используется формат: ИмяПеременной = Объект.ИмяСвойства Такой код применяется для присвоения переменной значения свойства объекта или свойству другого объекта. Если надо присвоить переменной значение свойства Value объекта Range, т.е. переменная принимает зна- чение из ячейки рабочего листа, используются следующие инструкции: Dim SngValue As Single SngValue = Range("A1").Value Для вывода на экран значения, содержащегося в ячейке B1, можно ис- пользовать одну из следующих инструкций: MsgBox "Ячейка B1 содержит значение " & Range("B1").Value или MsgBox "Ячейка B1 содержит значение " & SngValue 2.8.3.2. Использование методовобъекта Для выполнения метода объекта применяется один из следующих форматов: ИмяОбъекта.ИмяМетода ИмяОбъекта.ИмяМетода Аргумент1, Аргумент2, …, АргументN ИмяОбъекта.ИмяМетода (Аргумент1, Аргумент2, …, АргументN) 26 Это означает, что: метод может не иметь Аргументов или все они не обязательные; метод может иметь несколько Аргументов, не заключаемых в круг- лые скобки; метод иметь несколько Аргументов, заключаемых в круглые скоб- ки. Кроме того, аргументы могут быть неименованными и именованными, обя- зательными и необязательными. Значения неименованных аргументов должны быть перечислены в заданном порядке, определяемом форматом метода, отделяя каждый аргумент запятой и включая запятые на месте пропущенного необязательного аргумента. Именованные аргументы име- ют формат НаименованиеАргумента := ЗначениеАргумента Примеры В этом примере удаляются формулы и форматирование ячеек A1:G37 на листе Лист1. Worksheets("Лист1").Range("A1:G37").Clear В следующем примере очищается область диаграммы Диаграмма1 (объ- ект ChartArea; удаляются данные и форматирование диаграммы). Charts("Диаграмма1").ChartArea.Clear Например, для выполнения метода Open (Открыть) рабочей книги Приме- ры запишите код Workbooks("Примеры").Open Некоторые методы имеют аргументы, обязательные или необязательные. В следующем примере метод SaveAs (Сохранить), применяемый для со- хранения текущей рабочей книги, в качестве аргумента использует имя файла, в котором будет сохранена рабочая книга. ThisWorkbook.SaveAs Filename:= "Текущий бюджет" Этот же способ используется в тех редких случаях, когда значения параметров передаются свойству. Порядок следования значений должен строго соответствовать порядку, в котором были объявлены сами пара- метры. Второй способ передачи значений параметров при вызове метода отличается от первого лишь отсутствием круглых скобок: ИмяОбъекта.ИмяМетода аргумент!, аргумент2, ... Требования к порядку следования значений такие же, как и для пер- вого способа. 27 2.8.3.3. Переменные-объекты Переменными-объектами называются переменные, имеющие тип Object. Переменные-объекты объявляются точно так же, как переменные других типов, т.е. с помощью оператора Dim, используя при этом общий тип данных Object, или с помощью оператора Set, назначив переменным- объектам тип конкретного объекта. Ниже приведены несколько примеров объявления переменных-объектов. Dim Таблица1 As Object Dim Продажи As Worksheet Dim Книга1 As Workbook Dim Курс As Range Set Лист1 = Workbooks("Финансы").Worksheets("Бюджет") Set Лист2 = Workbooks("Финансы ").Worksheets("Бюджет") Set Книга1 = Workbooks("Финансы") Set Таблица1 = Workbooks("Бюджет").Worksheets(1).Range("A1:A12") После назначения переменным-объектам конкретных объектов име- на переменных-объектов можно использовать в кодах процедур вместо имен самих объектов. Пример такого использования показан в листинге процедуры Объекты . Sub Объектs() Dim РабОбл As Range Set РабОбл = Workbooks("Финансы ").Worksheets("Лист1").Range("A2:D2") РабОбл.Font.Bold = True РабОбл.Font.Italic = True РабОбл.Font.Name = "Courier" End Sub В этой процедуре имя переменной РабОбл применяется вместо ссылки на диапазон A2:D2, который находится на рабочем листе Лист1 рабочей книги Финансы. Такая замена упрощает написание длинного пол- ного имени объекта. В результате выполнения этой процедуры шрифт в ячейках рабочего листа Лист1 будет отформатирован как жирный(Bold) курсив(Italic) тип Courier. Например, слово “шрифт” будет иметь вид “шрифт”. 2.8.3.4. Коллекции Коллекцией называется группа подобных объектов. Рассмотрим сле- дующее полное имя объекта: Workbooks("Финансы ").Worksheets("Лист1").Range("A2:D2") 28 Здесь Workbooks и Worksheets – это коллекции, Книra1 – это элемент коллекции Workbooks. Объект Range не является коллекцией. Одно из оп- ределяющих свойств коллекции – возможность добавлять в нее новые элементы. В объект Range нельзя добавить другие диапазоны ячеек, так как в Excel диапазоны жестко определены и ограничены еще во время их задания. На рис. 1 внизу окна объектной модели Excel приведены обозначе- ния цветовых выделений объектной модели. Желтым цветом выделены объекты и коллекции, голубым – объекты, не являющиеся коллекциями. 2.8.3.5. Метод Add В коллекции можно добавлять новые элементы. Для этого использу- ется метод Add (Добавить). Например, новую рабочую книгу можно соз- дать с помощью следующего кода: Workbooks.Add Этот код эквивалентен выполнению в Excel команды Файл | Создать. Что- бы добавить новый лист в рабочую книгу, используется код Worksheets.Add 2.8.3.6. Свойство Count Свойство коллекции Count (Счет) хранит количество элементов, со- ставляющих коллекцию. Для определения количества листов в текущей рабочей книге можно использовать следующие инструкции: Dim КолРабЛист As Integer КолРабЛист = Worksheets.Count Можно найти много применений свойству Count. Предположим, вы создаете приложение, где рабочая книга должна содержать отдельный лист на каждый рабочий день недели. С помощью свойства Count можно проверить, сколько содержит рабочая книга рабочих листов. Код процеду- ры представлен ниже. Sub КолРабЛист () Dim КолРЛ As Byte Dim Сообщ As String КолРЛ = Worksheets.Count Сообщ = "Книга содержит " & КолРЛ Сообщ = Сообщ & " листов " MsgBox Сообщ End Sub 2.8.4. Свойства и методы основных объектов Excel 2.8.4.1. Объект Application Объект Application (Приложение) занимает самый верхний уровень иерархии объектов Excel и управляет установками и параметрами уровня 29 приложения, т.е. такими, которые можно найти в диалоговом окне Пара- метры программы Excel. Объект Application содержит также встроенные функции Excel, и его необходимо использовать для применения в процедуре VBA встроенных функций Excel. В процедуре МодФункции моделируются функции Excel Average (Среднее) и Sum (Сумма). Option Base 1 Sub МодФункции() Dim Число As Variant Число = Array(10, 20, 30, 40) Dim СрЗнач As Integer СрЗнач = Application.Average(Число) MsgBox "Среднее значение массива = " & СрЗнач MsgBox "Сумма значений массива = " & Application.Sum(Число) End Sub В процедуре МодФункции для подсчёта среднего значения и суммы используются функции VBA Excel Average и Sum, параметром которых яв- ляется массив Число, значения которого задаются при помощи функции VBA Excel Array. Ниже приведены результаты выполнения процедуры МодФункции. Этот пример показывает, что для использования встроенных функций Excel необходимо указывать объект Application и аргументы функций. Объект Application имеет множество свойств и методов. Из свойств выделим следующие. ActiveWorkbook – возвращает активную (текущую) книгу. ActiveSheet – возвращает активный лист в активной рабочей книге. Возвращаемый лист может быть любого поддерживаемого типа, включая рабочий лист и лист диаграмм. ActiveCell – возвращает активную ячейку на активном листе актив- ной рабочей книги. ThisWorkbook – возвращает рабочую книгу, где находится выпол- няемая процедура. Selection – определяет текущее выделение. Выделением может быть диапазон ячеек, элементы диаграммы и т.п. Среди методов объекта Application выделим следующие. InputBox – отображает окно ввода и позволяет указать тип возвра- щаемого значения. 30 OnKey – устанавливает выполнение указанной процедуры при на- жатии заданной комбинации клавиш. OnTime – назначает выполнение указанной процедуры на опре- делённое время. Quit – применяется для выхода из Excel. 2.8.4.2. Объект Worksheet Объект Worksheet представляет собой рабочий лист. В Excel рабочий лист можно выбирать, переименовывать, копировать, удалять, вставлять новый лист в рабочую книгу. Для выполнения таких же и множества других действий с объектом Worksheet используются его многочисленные свойст- ва и методы. Свойства объекта Worksheet Из многочисленных свойств объекта Worksheet рассмотрим следующие, наиболее употребительные. 1. Cells – возвращает ячейку рабочего листа. Формат свойства: Worksheets(индекс).Cells(<№ строки>,<№ столбца>),где индекс – номер или имя рабочего листа, <№ строки>,<№ столбца> – целочисленные выражения, задающие значения № строки и № столбца рабочего листа. Пример ‘Ячейка, находящаяся в строке 2 в столбце 5 (ячейка E5) листа Лист1, получает значение 10. i=1 k=5 Worksheets(Лист1).Cells(i*2+1,k).Value=10 2. Name – возвращает имя рабочего листа. Формат свойства: ActiveSheet.Name = < имя рабочего листа> Пример ‘Активный рабочий лист получает имя “Товары” ActiveSheet.Name =“Товары” Методы объекта Workbook При работе с объектом Worksheet часто применяются следующие методы: Activate – активизирует рабочий лист; Add – вставляет новый лист в рабочую книгу; CheckSpelling – используется для проверки орфографии содержи- мого рабочего листа; Delete – удаляет рабочий лист из рабочей книги. Пример |