Глава 8.Разработка приложений с помощью Excel

Компонент Excel является наиболее популярным для разработки приложений, предназначенных для различных сфер: клиентских приложений для баз данных; финансовых моделей и инструментов анализа; систем подготовки отчетов, статистических и финансовых презентаций; экспертных информационных систем.

Данные в Excel представляются в виде таблиц, размещаемых на рабочих листах. Любое значение (число, дата, символьная строка и т.п.), вводимое пользователем, помещается в ячейку таблицы. Каждая ячейка таблицы находится на пересечеиии строки и столбца. Строки и столбцы таблицы последовательно нумеруются (строки – числами от 1 до 65 536, столбцы – буквами от A до IV).

В таблицах можно не только размещать данные, но и выполнять расчеты, осуществлять анализ данных, формировать на их основе отчеты, представляя данные в удобной для восприятьия форме. Excel позволяет выполнять операции как над отдельными значениями, размещенными в ячейках таблицы, так и над диапазонами ячеек, над таблицами в целом.

При создании таблиц лучше соблюдать правило: один рабочий лист – одна таблица, что позволит избежать проблем при форматировании таблиц и выполнении различных операций над ними.

Рабочие листы «вкладываются» в рабочие книги, которые хранятся в файлах с расширением XLS. Пользователь имеет возможность работать с несколькими книгами одновременно, связывать данные, размещенные в различных книгах.

Работа приложения строится на объектной модели, которая рассматривается ниже.

8.1.Иерархия объектов Excel

Объектная модель Excel содержит свыше 100 встроенных объектов и объекты, совместно используемые всеми приложениями Microsoft Office. К совместно используемым относятся CommandBars Data Access Objects (объекты доступа к данным), Forms (пользовательские диалоговые окна и элементы управления), Office Assistant и FileSearch. Объекты из других приложений Microsoft Office и BackOffice можно также использовать при разработке приложений на основе Excel.

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

Объекты Excel находятся на разных уровнях иерархии. Для их эффективного использования в программах VBA необходимо представлять себе эту иерархию (рис. 8). На рис. 9-11 показана иерархическая схема объекта WorkBook. Символ ‘’ справа от названия объекта означает, что его структура раскрыта на других схемах.

Расположенный на верхнем уровне иерархии объект Application представляет Microsoft Excel. Остальные объекты расположены на следующих уровнях иерархии. Второй уровень:

• AddIn – объект, представляющий файл надстройки Excel;

• Assistant – объект для управления помощником (совместно используется всеми приложениями MS Office);

• AutoCorrect – объект для доступа к средствам автозамены;

• CommandBar – объект, представляющий панели команд;

• Debug – объект, представляющий окно отладки;

• Dialog – объект, представляющий встроенное диалоговое окно;

• FileFind – объект для доступа к средствам диалогового окна «File Find» (только для Macintosh);

• FileSearch – объект, применяемый для поиска файлов (совместно используется приложениями Office);

• Name – объект, представляющий имя диапазона ячеек;

• ODBCError – объект, используемый для представления ошибок при операциях с базами данных через ODBC;

• RecentFile – объект, представляющий файл из списка последних использовавшихся файлов;

• VBE – объект для управления редактором VBA (совместно используется приложениями Office);

• Window – объект, используемый для доступа к окнам;

• Workbook – объект, представляющий файл рабочей книги;

• WorkSheetFunction – объект, используемый для исполнения функций рабочего листа из программы на VBA.

Следующие уровни иерархии включают набор дополнительных объектов, используемых для поддержки (обеспечения функционирования) объектов второго уровня. На рис. 8-11 изображены основные объекты Excel. В приведенные схемы не включены некоторые объекты, оставленные для совместимости с предыдущими версиями, объекты Microsoft Map и др. Более полную информацию можно найти в диалоговом окне «Просмотр объектов» и в справочной системе VBA.

Чтобы управлять свойствами и методами объекта, иногда нужно записать ссылки на все объекты, находящиеся в иерархии выше него. Ссылки на объекты записываются последовательно, начиная с верхнего уровня иерархии. В качестве разделителя между ссылками (именами) ставится точка. Например, оператор

Application.Workbooks(“Книга1”).Worksheets(1). _ Range(“A1”).Value = 1

заносит значение в указанный диапазон ячеек заданного рабочего листа выбранной рабочей книги.

Ссылки на объекты в семействе требуют указания индексов.

Для сокращения записи можно воспользоваться оператором With.

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

Если код выполняется из среды Excel, то указание объекта Application не является необходимым.

Для неявной ссылки на рабочую книгу можно использовать ссылки ActiveWorkbook (ссылка на активную рабочую книгу, с которой в данный момент работает пользователь) и ThisWorkbook (ссылка на рабочую книгу, в которой хранится исполняемый в настоящий момент код). Если открыта только одна книга, то ссылку на рабочую книгу можно опустить. Если открыто насколько рабочих книг, а ссылка на книгу опущена, действия выполняются над активной рабочей книгой.

Для доступа к активному рабочему листу можно использовать ссылку ActiveSheet. Ссылка на активный лист также может быть опущена.

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

Ссылки ActiveWorkbook, ThisWorkbook, ActiveSheet и Selection – это ссылки на свойства объектов.