Аннотация: Лекция посвящена описанию объектной модели MS Excel и подробному описанию методов, свойств и событий Application.

13.1. Особенности программирования для MS Excel

Microsoft Office Excel - это популярные электронные таблицы. Обычно, программируя для этой программы, преследуют такие цели:

• Автоматизация вычислений.
• Автоматизация ввода и обработки информации.
• Работа с базами данных - вывод, ввод, анализ, визуализация информации.
• Анализ финансовой и другой информации.
• Создание систем для организации автоматизированного ввода данных
• Математическое моделирование .

В общем виде программирование в Excel похоже на работу с Microsoft Word . Однако одно из главных отличий заключается в том, что в Excel рабочая область листа разбита на ячейки, каждая из которых имеет собственное имя. Имена ячеек могут быть двух видов.

• Первый вид (стиль A1) - это имя, состоящее из буквенного имени столбца и номера строки. Например, A1 - ячейка, находящаяся на пересечении столбца А (первого) и первой строки.
• Другой вид - это индексы ячеек (стиль именования R1C1 ). Для адресации ячейки в таком стиле указывают номер строки (R - Row - строка) и номер столбца (C - Column - столбец), на пересечении которых расположена ячейка. Строки изначально пронумерованы, а номера столбцов начинаются с 1 - первому столбцу соответствует столбец A , второму - В и т.д. Например, (2, 3) - это адрес ячейки, находящийся на пересечении второй строки и третьего столбца, то есть, если переложить это на стиль A1 - получим ячейку С2 (рис. 13.1 .)

Рис. 13.1.

Для выполнения большинства операций в MS Excel применяются следующие объекты.

• Excel.Application (Приложение) - объект, представляющий приложение Microsoft Excel, аналогичен Word.Application .
• Workbook ( Рабочая книга ) - представляет рабочую книгу - аналог документа Microsoft Word. Однако, в Word мы работаем с данными, расположенными в документе, а в Excel на пути к данным есть еще один объект - рабочий лист.
• Worksheet (Рабочий лист) - книга в MS Excel разбита на рабочие листы. Именно на листе расположены ячейки, которые могут хранить информацию и формулы.
• Range (Диапазон) - может быть представлен в виде одной ячейки или группы ячеек. Этот объект заменяет множество объектов для работы с элементами документов (character , word и т.д.), которые применяются в Microsoft Word. В результате работа с листом становится очень четкой и удобной - чтобы работать с какой-либо ячейкой, надо знать лишь ее имя (в формате A1) или адрес (R1C1 ).
• QueryTable (Таблица запросов) - этот объект используют для импорта в Microsoft Excel информации из баз данных. Подключение к базе данных , запрос информации и т.д. производятся средствами объекта, а итоги запроса выгружаются на лист MS Excel в виде обычной таблицы.
• PivotTable (Сводная таблица) - это особый вид электронной таблицы Excel - она позволяет в интерактивном режиме обобщать и анализировать большие объемы информации, в частности, взятой из базы данных.
• Chart (Диаграмма) - представляет собой диаграмму. Обычно их используют для визуализации данных.

Давайте начнем рассмотрение объектной модели MS Excel с объекта Application .

13.2. Объект Application

MsgBox Excel.Application.Name Листинг 13.1. Вывести имя приложения

После выполнения программы в окне сообщения отобразится имя приложения - в данном случае - Microsoft Excel . Очевидно, что свойство Name объекта Application возвращает имя приложения.

Теперь рассмотрим наиболее важные методы и свойства Application . Некоторые из них похожи на таковые в MS Word . Например, метод Quit точно так же, как в Word , закрывает приложение , свойство Visible отвечает за видимость окна программы и т.д.

13.3. Методы Application

13.3.1. Calculate - принудительный пересчет

Этот метод, вызываемый для объекта Application , позволяет пересчитать все открытия книги. Его же можно вызывать для отдельных книг (объект Workbook ) листов ( Worksheet ), ячеек и их диапазонов (Range ). Например, код из листинга 13.2 . позволяет пересчитать все открытые книги.

Application.Calculate Листинг 13.2. Пересчитать все открытые книги

13.3.2. GoTo - переход в ячейку

13-02-Excel GoTo.xlsm - пример к п. 13.3.2.

Позволяет выделить любой диапазон ячеек в любой книге, причем, если книга не активна - она будет активирована. Так же метод может запускать макросы Microsoft Excel.

Полный вызов метода выглядит так:

Goto(Reference, Scroll)

В качестве параметра Reference используется ссылка на ячейку или диапазон ячеек , которые должны быть выделены после вызова метода. Так же здесь может быть использовано имя макроса - тогда он будет запущен.

Параметр Scroll отвечает за "перемотку" листа Excel к выделенным ячейкам - так, чтобы левый верхний угол выделения совпадал бы с левым верхним углом отображаемой области листа. Если Scroll установлен в True - лист перематывается, если в False - нет.

Например, такой вызов (листинг 13.3 .) позволяет выделить ячейку H500 на активном листе.

Application.Goto _ Reference:=ActiveSheet.Range("H500"), _ Scroll:=True Листинг 13.3. Выделить ячейку H500

Как видите, обращение к активному листу очень напоминает обращение к активному документу в MS Word. Обратите внимание на то, что мы используем полное обращение к методу - Application.GoTo - как вы знаете, обычно свойства и методы объекта Application можно использовать в коде, не указывая этого объекта. Однако, если в этом случае не указать Application , то вместо метода GoTo программа попытается выполнить оператор безусловного перехода GoTo .

13.3.3. SendKeys - имитация нажатий на клавиши клавиатуры

13-03-Excel SendKeys.xlsm - пример к п. 13.3.3.

Очень интересный метод - позволяет передавать в активное окно приложения нажатия клавиш. Полный вызов метода выглядит так:

SendKeys(Keys, Wait)

Параметр Keys позволяет задавать клавиши, нажатия которых будут переданы приложению. Метод поддерживает эмуляцию как алфавитно-цифровых, так и управляющих клавиш , для которых применяется специальная кодировка. Алфавитно-цифровые клавиши указываются при вызове в своем обычном виде Например, для передачи символа "Ф" нужно указать его при вызове метода и т.д.

Чтобы передать приложению нажатия клавиши Backspace - используйте код {BS} . Для передачи нажатия кнопки Enter используйте значок ~ (

Лекция Объектная модель MS Excel.

1. Общая характеристика объектной модели MS Excel

2. Характеристика объектов

3. Работа в среде VBA. Управление свойствами объектов

Введение

Табличный процессор Excel является программой с достаточно широкими возможностями, с большинством из которых вы знакомы. Однако кроме этого, разработчики заложили в нее средства адаптации для повышения эффективности решения конкретных задач. К этим средствам относятся различные настройки, средства автоматизации действий пользователя (макросы), а также встроенная система программирования VBA. Само приложение разработано с использованием ООП, а VBA обеспечивает доступ к объектам программы, благодаря чему в распоряжении квалифицированного пользователя имеются практически неограниченные возможности по адаптации этого приложения. На этом и нескольких последующих занятиях мы познакомим вас с тем, как это можно использовать.

1. Общая характеристика объектной модели MS Excel

ООП построен на использовании объектов. Реальные объекты окружающего мира обладают двумя основными характеристиками: они имеют набор свойств и способны разными методами изменять эти свойства и реагировать на события, возникающие как в окружающем мире, так и внутри самого объекта. Именно в таком виде в языках программирования и реализовано понятие объекта, как совокупности свойств (структур данных, характерных для этого объекта) и методов их обработки (подпрограмм изменения свойств).

Свойства – это параметры, которые описывают объект. Свойства, например, идентифицируют объект, определяют его внешнее оформление, положение на экране и размеры.

Методы – это выполняемые объектом действия, его рабочие операторы. В общем, методы – это процедуры (действия), которые можно выполнить, а свойства - это атрибуты, устанавливаемые пользователем или системой.

При выполнении ООП-программ реализуется событийный принцип, суть которого состоит в том, что объекты программы могут реагировать на события, происходящие в системе. События – это сообщения, которые определяют порядок действий программы. События могут носить разную природу – щелчки кнопками мыши, перемещение указателя мыши, ввод или изменение данных, нажатие на кнопки в окнах программы или на клавиши клавиатуры, ошибки программы и системы и т. п. Реакция состоит в выполнении одного из методов объекта. Для большинства объектов характерно событие Click (щелчок), которое наступает, если щелкнуть левой кнопкой мыши на избранном объекте. Характерными являются также события: DblClick (двойной щелчок), MouseDown, MouseUp (нажатие и отпускание кнопки мыши), Exit и Enter (потеря и получение фокуса) и многие другие.

Задача программиста при создании программы сводится к тому, чтобы из всех имеющихся объектов включить в программу необходимые объекты, определить список событий, на которые должна реагировать программа, и указать какие методы следует использовать при наступлении каждого события.

Если нет объекта с требуемыми характеристиками, программист может создать такой объект и в дальнейшем использовать его в этой или другой программе.

При адаптации приложения нет необходимости создавать новые объекты, достаточно использовать уже существующие. Нужно только знать их. Представление о составе и отношениях между объектами дает объектная модель приложения. У каждого приложения она своя.

Объектная модель MS Excel представляет собой иерархию объектов, подчиненных объекту Application , который соответствует самому приложению.

Число объектов достаточно велико, они образуют древовидную структуру (более мелкие ветви являются свойствами более крупных). Корнем этого "дерева" (начальным объектом) является Application (само приложение MS Excel).

Часть объектов являются единичными (т.е. существуют в единственном экземпляре), а часть являются членами коллекций (семейств). Для единичных объектов достаточно указать имя, а для членов семейств - имя семейства и номер или имя члена семейства.

Ниже приведены часть объектов MS Excel, которые мы будем использовать в своих программах.

Рис. 1. Упрощенная объектная модель Excel

На этом рисунке для семейств в скобках записаны имена объектов из семейств.

Для обращения к свойству или методу объекта (ссылки на объект) может использоваться либо полная, либо неявная ссылка.

Полная ссылка состоит из ряда имен вложенных друг в друга объектов (в соответствии с иерархией). В качестве разделителя имен используется точка. Например, полная ссылка на ячейку A5 рабочего листа Лист1 рабочей книги Книга1 (и например, записи в нее числа 10) в операторе присваивания имеет вид:

Application.Workbooks ("Книга 1").Worksheets("Лист 1").Range("A5") = 10

Объектная модель Excel

Объекты Excel

Программист, работающий в Excel , должен свободно ориентироваться в мире его объектов. Мощь офисного программирования определяется тем, что изначально в распоряжении программиста находится большое число уже готовых объектов. Чтобы с толком распорядиться предоставляемыми возможностями, объекты нужно знать.

Разговор об объектах Excel целесообразно начать с рассмотрения каркаса документа Excel . О важном понятии каркаса документа я много писал в предыдущих книгах . Многочисленные библиотеки объектов Office 2000, совокупность которых для программиста и представляют Office 2000, задают каркас всех документов, которые можно построить в этой среде. Когда создается новый документ, например, рабочая книга Excel , то по умолчанию из всей совокупности библиотек выбирается несколько, объекты которых и составляют каркас документа. Эти объекты доступны программисту, без каких либо дополнительных усилий. Центральную роль в каркасе документов Excel играют, конечно же, объекты библиотеки Excel . Но знание и всех других объектов, входящих в каркас, необходимо. Например, при программном создании интерфейса необходимо знание общих объектов библиотеки Office. Отмечу еще, что при желании программист всегда может расширить каркас документа, добавив в него те или иные библиотеки. Каркас, создаваемый по умолчанию в тот момент, когда открывается новая рабочая книга , состоит из объектов, входящих в состав следующих библиотек:

· Excel - библиотека, задающая основу документов Excel. Здесь хранится класс, задающий корневой объект Excel.Application, и все классы объектов, вложенных в корневой объект.

· Office - библиотека объектов, общих для всех приложений Office 2000. Здесь находятся классы, определяющие инструментальные панели - CommandBar и классы других общих объектов. Здесь же находятся классы, задающие Помощника (объект Assistant и все классы, связанные с ним). В частности, появился новый объект, которого не было в предыдущей версии - Мастер Ответов (Answer Wizard).

· Stdole - библиотека классов, позволяющая работать с OLE - объектами и реализовать Автоматизацию.

· VBA - библиотека классов, связанных с языком VBA. Здесь хранятся все стандартные функции и константы, встроенные в язык, классы Collection и ErrObject.

· VBAProject - проект по умолчанию, связанный с документом. Классы, которые могут создаваться программистом в этом проекте, методы, свойства, - все это доступно для просмотра, так же, как и объекты классов, встроенных в стандартные библиотеки.

Если сравнить каркас рабочей книги Excel , например, с каркасом документа Word , то они отличаются тем, что в основе одного лежит библиотека Excel , в основе другого - библиотека Word . Эти библиотеки содержат специфические для данных приложений объекты. Что же касается интерфейсных объектов, объектов определяющих среду редактора VBA , автоматизацию, то здесь используются общие объекты. Библиотеки Office, Stdole, VBA - это общие для всех приложений Office 2000 библиотеки. Об объектах этих библиотек я подробно рассказывал в .

Замечу, что хотя каркас документа Excel не изменился в Office 2000 в сравнении с предыдущей версией, вместе с тем в объектной модели произошли довольно существенные изменения, появились новые объекты, новые свойства и методы у ранее существовавших объектов.

Объектная модель Excel

Прежде всего, несколько слов о том, как устроена объектная модель Excel и других приложений Office 2000. В этой модели объекты связаны между собой отношением встраивания . На нулевом уровне иерархии существует некоторый центральный объект , в который встроены другие объекты, составляющие первый уровень иерархии. В каждый из объектов первого и последующих уровней могут быть встроены объекты следующего уровня. Так этот процесс продолжается. Таким образом, объекты в этой модели "толстые", поскольку в них встроено большое число других объектов. В особенности это касается объектов, стоящих на верхних уровнях иерархии.

Модель объекта Excel - основа использования VBA в Excel. Программирование в Excel отличается от программирования в других приложениях VBA тем, что здесь добавлены некоторые команды для доступа к таблицам и книгам для обеспечения наилучшей функциональности, чтобы пользователь мог нормально ориентироваться в структуре меню Excel.

Объектная модель Microsoft Access, например, содержит команды и объекты, ориентированные на работу с базами данных, что значит - работа с таблицами, запросами, формами и отчетами. В Excel, которая ориентирована на работу с книгами и таблицами, объектная модель предназначена именно для этого. Excel - это приложение, состоящее из трех слоев: слой, обеспечивающий взаимодействие с пользователем, слой объектной модели и слой работы с самими данными. Пользовательский интерфейс в виде таблиц Excel, который вы обычно видите, является слоем для клиентов и наиболее оптимизирован для работы пользователей.

Таблицы зачастую очень важны в бухгалтерском деле. Для более качественной работы с ними нужна хорошая программа для бухгалтерии, одной из которых является Инфо-Предприятие . Это эффективная программа для бухгалтерского учета, позволяющая вести различные типы отчетности, например, налоговую отчетность, бухгалтерскую отчетность и т.п. Благодаря своей простоте и легкости в освоении она очень хорошо подходит для индивидуальных предпринимателей и малого бизнеса.

Всегда, когда вы что-нибудь делаете в своей таблице, вы подаете команды через объектную модель Excel. Например, когда вы открываете книгу, выполняется код, который привязан к пунктам главного меню Excel Файл - Открыть, и тот же самый код использует команда Workbooks. Open, которая открывает книгу Excel и добавляет данные о ней в коллекцию объектов книг. Аналогично, если вы в настройках Excel измените режим вычисления на ручной и нажмете клавишу F9 для выполнения пересчета формул, будет выполнена та же программа, что и в результате вызова команды Application. Calculate. Используя объектную модель Excel и язык программирования Visual Basic, не так сложно разработать собственное приложение представления данных с такой же функциональностью, как и Microsoft Excel. Каждая команда в меню и каждая кнопка в Microsoft Excel отображена на объектную модель Excel. Нельзя сказать, что Excel использует такие же объекты, но Microsoft дает вам возможность использовать объекты и методы так, что вы сможете сделать все в вашем коде, как и в меню Excel. Может показаться странным, но если вы решите создать свое собственное приложение представления данных, то объем кода, который потребуется написать, будет относительно небольшим, ведь вся функциональность уже содержится в существующих объектах.

Под объектной моделью расположен слой работы с данными, который хранит сами данные и отвечает за их хранение и обработку. В объектной модели Excel содержится огромное количество объектов, например Workbook, Worksheet, Range, Chart, Pivot Table, Comments. Эти объекты предлагают разнообразные возможности для обработки данных. Наиболее важным является то, что они могут быть контролируемы вашим кодом.

При программировании в VBA вы используете стандартные операторы и функции, такие как For…Next, If...Then...Else, MsgBox, но вы также используете объектную модель, чтобы связаться с приложением Excel, управляя свойствами и методами различных объектов.

Объект - это некоторая структура, содержащая данные и методы работы с ними, она существует как единое целое, доступ обеспечивается через специальный программный интерфейс.

Объект - это часть программы Excel. Объекты имеют свою иерархию. Следующим за Application следует объект Workbook, за ним - Worksheet. Следующим после каждого объекта Worksheet идет Range и т.д. Каждый объект имеет свои настройки, называемые свойствами, и действия, которые можно производить с объектом, называемые методами.

Основой объектной модели Excel является объект Worksheet, представляю­щий один рабочий лист в файле. Каждый объект Worksheet является частью коллекции Worksheets, которая принадлежит объекту Workbook, представляю­щему файл рабочей книги Excel. Так как в приложении Excel одновременно мо­жет быть открыто несколько рабочих книг, существует также и коллекция Workbooks, содержащая по одному объекту Workbook на каждый открытый файл. Как и во всех приложениях пакета Office, объект Application находится в вершине иерархии объектов и доступен в качестве неявной ссылки при работе в Excel.

Существует еще один тип листов в рабочих книгах Excel - лист диаграммы (объект Chart) - особая таблица, которая содержит только диаграмму. Коллек­ция Charts объекта Application реализует доступ ко всем диаграммам всех от­крытых файлов, а коллекция charts объекта Workbook - только к диаграммам данной рабочей книги. Диаграммы также могут быть внедрены в рабочие листы вместо того, чтобы иметь собственный лист. Такие диаграммы не являются ча­стью коллекции charts, а вместо этого доступны в составе коллекции Chartob-jects объекта Worksheet. Коллекция Sheets осуществляет доступ ко всем рабо­чим листам, как к таблицам, так и к диаграммам.

Большая часть того, что вы делаете в Excel, связана с объектом Range. Хотя имя этого объекта не отличается от своего двойника в приложении Word, их со­ставы различны. Однако его функции практически те же: обрамление части до­кумента, с которым производится работа. Диапазоном может выступать отдель­ная ячейка, двумерный блок ячеек на рабочем листе или трехмерный блок яче­ек, охватывающий несколько рабочих листов.

Еще одной важной составной частью объектной модели Excel является объект Chart. Существует один объект chart, содержащийся в коллекции charts для каждой диаграммы в рабочей книге. Каждому объекту Chart подчинено множе­ство объектов, представляющих составные части диаграммы, такие как оси и ле­генда.

Мы будем изменять наш макрос, зайдите в пункт меню "макросы", выберите наш и скажите "изменить":

Dim book As String

Dim sheet As String

Dim addr As String

book = Application.ActiveWorkbook.Name

sheet = Application.ActiveSheet.Name

Workbooks(book).Activate

Worksheets(sheet).Activate

Range("A1") = book

Range("B1") = sheet

Dim xList As Integer

xList = Application.Sheets.Count

For x = 1 To xList

s = addr + LTrim(Str(x))

Программирование на VBA можно рассматривать, как управление обьектами приложения. Вот именно обьектами и управляет наше приложение. В нашем случае, если упростить иерхическую архитектуру, то это выглядит так.

То есть главный обьект - приложение. В приложении могут быть несколько книг (Workbook), внутри которых находятся листы (Worksheets) и листы разбиты на ячейки (Cell). При работе активными могут быть только одна книга и один лист. Вот я своим макросом и пытаюсь это выяснить. А заодно сколько листов в текущей книге.

DIM - обьявляет переменную с типом string. Используя обьект Application, мы получаем имена текущих книг и листа. С помощью Range("...") можно выделить ячейку и поместить значения в неё или считать. Вообщем обьекты имеют огромное количество свойств. Задача программиста на VBA знать эти свойства и методы.