Элементы управления формы в MS EXCEL. Когда следует использовать внедренные объекты

Лабораторная работа

ИЗУЧЕНИЕ ИНТЕРФЕЙСА ТАБЛИЧНОГО ПРОЦЕССОРА MS EXCEL 2010. ПРОСТЫЕ ВЫЧИСЛЕНИЯ

Цели работы:

1)ознакомиться с возможностями табличного процессора;

2)ознакомиться с пользовательским интерфейсом программы MS Excel 2010;

3) ознакомиться с основными приемами работы в MS Excel 2010.

Задание:

1) изучите п. 1 «Учебный материал»;

2) выполните задания, приведенные в п. 2;

3) ответьте на контрольные вопросы (п. 3).

УЧЕБНЫЙ МАТЕРИАЛ

Основные понятия табличного процессора

Табличный процессор – это компьютерная программа для хранения и обработки информации, представленной в табличной форме. Структура программы – двумерный массив, состоящий из строк и столбцов, поэтому эти программные средства называют еще электронными таблицами (ЭТ).
С помощью ЭТ можно не только создавать таблицы, но и автоматизировать обработку данных.

Функции табличного процессора весьма разнообразны:

§ создание и редактирование таблиц;

§ оформление и печать таблиц;

§ создание многотабличных документов, объединенных формулами;

§ построение диаграмм;

§ работа с ЭТ как с базой данных; выборка данных по запросам;

§ создание итоговых и сводных таблиц;

§ решение задач типа «что-если» путем подбора параметров;

§ решение оптимизационных задач;

§ создание программ-макросов с помощью встроенного языка программирования Visual Basic for Applications (VBA).

Табличный процессор Excel является составной частью интегрированного программного пакета Microsoft Office.

После запуска Excel 2010 на экране появляется окно следующей структуры:

1. Заголовок программы .

2. Панель быстрого доступа с кнопкой настройки панели, по умолчанию расположенная в верхней части окна приложения Excel (рис. 1). Предназначена для быстрого доступа к наиболее часто используемым функциям. По умолчанию панель содержит всего три кнопки: Сохранить, Отменить, Вернуть (Повторить) . Панель быстрого доступа можно настраивать, добавляя в нее новые команды.

Рис. 1. Панель быстрого доступа

3. Лента. В Excel 2010 меню и панели инструментов выполнены в виде ленты. Команды упорядочены в логические группы, собранные на вкладках. Вкладки ориентированы на выполнение задач. Группы на каждой вкладке разбивают задачу на ее составляющие. Кнопки команд в каждой группе служат для выполнения команд или отображения меню команд. Значок (кнопка) группы – маленький квадрат в правом нижнем углу группы элементов во вкладке (рис. 2). Щелчок по значку открывает соответствующее этой группе диалоговое окно или область задач для расширения функциональных возможностей. Например, значок группы Шрифт вкладки Главная открывает диалоговое окно Формат ячейки . А значок группы Буфер обмена отображает область задач Буфер обмена .Не каждая группа имеет значок.

По умолчанию в окне отображается семь постоянных вкладок: Главная , Вставка , Разметка страницы , Формулы , Данные , Рецензирование , Вид .Вкладка Главная открывается по умолчанию после запуска программы.

Рис. 2. Кнопки групп

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

Перемещаться по ленте можно с помощью клавиатуры: если нажать клавишу , на ленте появятся подсказки для перехода к соответствующей вкладке (рис. 3).

Рис. 3. Клавиши перемещения по ленте

Перейдя на вкладку, можно увидеть подсказки по клавишам перехода по текущей вкладке (рис. 4).

Рис. 4. Клавиши перехода вкладки Главная

Заменить ленту панелями инструментов или меню предыдущих версий приложения Microsoft Excel нельзя. Удалить ленту также нельзя. Однако, чтобы увеличить рабочую область, ленту можно скрыть (свернуть). Способы:

1. Нажмите кнопку Свернуть ленту , расположенную в правой части линии названий вкладок. Для возврата нажать на кнопку Развернуть ленту .

2. Для быстрого скрытия/отображения ленты достаточно двойного щелчка по заголовку любой вкладки.

3. Ленту можно свернуть и иначе:

a) щелкните правой кнопкой мыши в любом месте ленты;

b) в контекстном меню выберите команду Свернуть ленту .

4. Чтобы свернуть или восстановить ленту, можно также нажать комбинацию клавиш .

Ленту можно настраивать: переименовывать и изменять последовательность расположения постоянных вкладок, создавать новые вкладки
и удалять их, создавать, удалять, изменять расположение групп элементов на вкладках, добавлять и удалять отдельные элементы и др.:

1) щелкните правой кнопкой мыши в любом месте ленты;

2) в контекстном меню выберите команду Настройка ленты .

Также настройка ленты производится в окне ПараметрыExcel в категории Настройка ленты меню Файл .

4. Вкладка (меню) «Файл» . Вкладка Файл всегда расположена в ленте первой слева. Меню содержит команды для работы с файлами (Сохранить , Сохранить как , Открыть , Закрыть , Последние , Создать ), для работы с текущим документом (Сведения , Печать, Доступ ), а также для настройки Excel (Справка , Параметры ). Кнопка Выход завершает работу
с приложением.

5. Мини-панели инструментов. Мини-панели инструментов (рис. 5) содержат основные наиболее часто используемые элементы для оформления текста документа, рисунков, диаграмм и других объектов. В отличие от других приложений Office 2010 (Word, PowerPoint и др.) в Excel 2010 мини-панель не отображается автоматически при выделении фрагмента листа. Для ее отображения щелкните правой кнопкой по выделенной области. Состав элементов мини-панелей постоянный и неизменный.

Рис. 5. Мини-панель инструментов и контекстное меню

6. Строка формул , состоящая из трех частей:

a) адреса активной ячейки ;

b) кнопок Отмена , Ввод , которые появляются в режиме ввода или правки, и кнопки Вставка функции f x ;

c) содержимого активной ячейки.

Для просмотра и редактирования содержимого выделенной ячейки можно увеличить высоту строки формул. Для этого щелкните по кнопке Развернуть строку формул . Если и в этом случае отображается не все содержимое ячейки, можно воспользоваться полосой прокрутки. Для того чтобы привести строку формул в исходное состояние, щелкните по кнопке Свернутьстроку формул. Убрать/отобразить строку формул можно следующим образом: Файл ® Параметры ® Дополнительно ® категория Экран ® Показывать строку формул .

7. Координатная строка – содержит имена столбцов.

8. Координатный столбец – содержит номера строк.

9. Рабочее поле.

10. Горизонтальная и вертикальная полосы прокрутки .

11. Ярлычки листов с кнопками перехода по рабочим листам.

12. Строка состояния , в которой указываются режимы работы Готово , Ввод , Правка ; режимы просмотра книги Обычный, Разметка страницы, Страничный ; масштабная линейка.

Основные объекты Excel

1. Столбец . Таблица Excel содержит 16384 столбца(2 14). Столбцыидентифицированы латинскими буквами. Так как их только 26, то после Z обозначение столбцов идет сдвоенными буквами AA, AB, AC, …, GA, GB, GC, …, HX, HY, HZ, а после столбца ZZ – строенными ААА, ААВ, ААС, …, AAZ, ABA , … Заканчивается нумерация на столбце XFD. Чтобы быстро перейти к первому или последнему столбцу (строке) рабочего листа, нужно нажать клавишу <Ctrl> и соответствующую клавишу управления курсором.

2. Строка – их в таблице 1048576 (2 20).

3. Ячейка – место пересечения строки и столбца. Каждая ячейка имеет уникальный адрес, в котором указывается имя столбца и номер строки, на пересечении которых она расположена. Примеры использования адресов ячеек в стиле A1 показаны в таблице 1.

Таблица 1

Стиль ячеек A1

Excel поддерживает альтернативную систему указания ячеек, называемую R1C1 (от англ. слов Row – строка и Column – колонка). В этой системе и столбцы, и строки таблицы пронумерованы, а номер строки предшествует номеру столбца. Например, ячейка А1 называется R1C1 (строка 1, столбец 1). Ячейка В1 – это R1C2 (строка 1, столбец 2). Перейти к альтернативному стилю и обратно можно, зайдя в меню Файл ® Параметры ® Формулы ® категория Работа с формулами ® стиль ссылок R1C1 .

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

4. Блок ячеек – это прямоугольник, в котором указываются адреса ячеек левого верхнего и нижнего правого углов, разделенных двоеточием, например А1:С5.Если в выполняемом действии указан блок ячеек, то задействованы все его ячейки.

5. Рабочий лист – это созданная таблица для решения задачи, диаграмма, макрос, рисунок. Стандартное имя листа – Лист1, Лист2, …. С рабочими листами можно выполнять следующие действия :

§ переименование;

§ удаление;

§ вставка;

§ перемещение;

§ копирование.

Эти действия выполняются с помощью контекстного меню при установленном указателе мыши на ярлычке листов или в группе Ячейки вкладки Главная (рис. 6).

Рис. 6. Группа Ячейки вкладки Главная

Меню Файл ® Параметры ® Дополнительно ® категория Показать параметры для следующей книги ® Показывать ярлычки листов позволяет убрать/отобразить ярлычки листов.

6. Рабочая книга – это файл, который хранится на диске и содержит один или несколько листов. По умолчанию рабочая книга имеет имя Книга1, Книга 2 , …. Сохранить файл можно с помощью пункта меню Файл ® Сохранить . Команда Сохранить как выбирается, если нужно сохранить уже записанный на диск файл под новым именем. Файлам Excel 2010 по умолчанию присваивается расширение .xlsx .

7. Диаграмма – это графическое отображение данных таблицы. Может храниться на отдельном листе, а может сопровождаться текстом или таблицей.

8. Рисунок – создается с помощью группы Иллюстрации вкладки Вставка в самой среде Excel или может быть вставлен из другого графического редактора.

9. Модули Visual Basic – программы, называемые макросами и созданные на языке программирования Visual Basic.

Типы данных

Как правило, в ЭТ используются следующие типы данных:

1. Текст – любая последовательность символов, используемая в основном для заголовков таблиц, строк, столбцов и комментариев.

2. Число . В ячейке Excel можно отобразить три типа числовых данных (констант):

a) целые числа – это последовательность цифр от 0 до 9 со знаком или без него: +25; –100.

b) вещественные числа с фиксированной запятой – это десятичные дроби, в которых целая часть отделяется от дробной запятой: 28,25; –3,765.

c) вещественные числа с плавающей запятой – это числа, записанные в следующей форме: 1,5Е+03 или 2Е-08 . Такую запись еще называют экспоненциальной формой записи числа (научный формат).

По умолчанию правильно введенное число выравнивается по правому краю ячейки. Неправильно введенное число считается текстом и выравнивается по левому краю. Если число не поместилось по ширине ячейки, то вся ячейка заполняется символом # (рис. 7).

Рис. 7. Вид чисел на рабочем листе

3. Формула – это выражение, состоящее из чисел, адресов ячеек, функций и знаков операций и начинающееся со знака = . Например:

А1*20%+12*A1*D12.

Порядок выполнения арифметических действий в формуле такой же, как принятый в математике.

4. Функция – это запрограммированные формулы, позволяющие производить часто встречающиеся последовательности вычислений (стандартные функции разного назначения).

5.Дата – это дата, представленная в различных форматах, с которой можно производить арифметические и логические операции. Например, 21.10.2011; 21 октября 2011 г. и др.

Копирование ячеек

Копирование можно осуществить следующими способами:

1) вкладка Главная ® группа Буфер обмена ® команда Копировать ;

2) с помощью команды Копировать контекстного меню;

3) с помощью комбинации клавиш ;

4) с помощью техники «перетащить-и-оставить» при нажатой клавише . В этом случае рядом с указателем мыши появляется знак + (признак копирования);

5) с использованием маркера в правом нижнем углу копируемой ячейки. При отсутствии этого маркера необходимо воспользоваться командой Файл ® Параметры ® Дополнительно ® категория Параметры правки ® Разрешить маркеры заполнения и перетаскивания ячеек .

К числу основных объектов MS Excel, которые описываются в этом разделе, относятся следующие: рабочая книга (Workbook) и семейство рабочих книг (Workbooks), рабочий лист (Worksheet) и семейство рабочих листов (Worksheets), диапазон ячеек или ячейка (Range).

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

Workbooks("KHMra1.xls").Worksheets("^MCT1").Activate

Семейство WorkBooks("KHMra1") обеспечивает доступ к рабочей книге. В скобках указывается имя книги.

Семейство WorkSheetsE".HMCTl") обеспечивает доступ к рабочему листу. В скобках указывается имя листа.

Семейство Range("^Mana30H") обеспечивает доступ к диа- пазону ячеек или к ячейке. В скобках указывается диапазон ячеек или имя ячейки.

Семейство Cells(№ строки, № столбца) обеспечивает

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

WorkBooks("Kнигa1")

WorkSheets("Лист1")

Range("Al:В10")

Еще по теме Основные объекты MS Excel:

1. 2.1. Информация как основной объект информационной сферы и системы права
2. § 4. Основные виды недвижимых вещей: Земельные участки, здания, сооружения, помещения, объекты незавершенного строительства и предприятия (п. 1239-1244)

Термин Объекты Excel (понимаемый в широком смысле, как объектная модель Excel) включает в себя элементы, из которых состоит любая рабочая книга Excel. Это, например, рабочие листы (Worksheets ), строки (Rows ), столбцы (Columns ), диапазоны ячеек (Ranges ) и сама рабочая книга Excel (Workbook ) в том числе. Каждый объект Excel имеет набор свойств, которые являются его неотъемлемой частью.

Например, объект Worksheet (рабочий лист) имеет свойства Name (имя), Protection (защита), Visible (видимость), Scroll Area (область прокрутки) и так далее. Таким образом, если в процессе выполнения макроса требуется скрыть рабочий лист, то достаточно изменить свойство Visible этого листа.

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

Доступ ко всем основным объектам Excel может быть осуществлён (прямо или косвенно) через объект Workbooks , который является коллекцией всех открытых в данный момент рабочих книг. Каждая рабочая книга содержит объект Sheets – коллекция, которая включает в себя все рабочие листы и листы с диаграммами рабочей книги. Каждый объект Worksheet состоит из коллекции Rows – в неё входят все строки рабочего листа, и коллекции Columns – все столбцы рабочего листа, и так далее.

В следующей таблице перечислены некоторые наиболее часто используемые объекты Excel. Полный перечень объектов Excel VBA можно найти на сайте Microsoft Office Developer (на английском).

Приведённая выше таблица показывает, как выполняется доступ к объектам Excel через родительские объекты. Например, ссылку на диапазон ячеек можно записать вот так:

Workbooks("Книга1").Worksheets("Лист1").Range("A1:B10")

Присваивание объекта переменной

В Excel VBA объект может быть присвоен переменной при помощи ключевого слова Set :

Dim DataWb As Workbook Set DataWb = Workbooks("Книга1.xlsx")

Активный объект

В любой момент времени в Excel есть активный объект Workbook – это рабочая книга, открытая в этот момент. Точно так же существует активный объект Worksheet , активный объект Range и так далее.

Сослаться на активный объект Workbook или Sheet в коде VBA можно как на ActiveWorkbook или ActiveSheet , а на активный объект Range – как на Selection .

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

Таким образом, чтобы сослаться на диапазон A1:B10 на активном рабочем листе активной книги, можно записать просто:

Range("A1:B10")

Смена активного объекта

Если в процессе выполнения программы требуется сделать активной другую рабочую книгу, другой рабочий лист, диапазон и так далее, то для этого нужно использовать методы Activate или Select вот таким образом:

Sub ActivateAndSelect() Workbooks("Книга2").Activate Worksheets("Лист2").Select Worksheets("Лист2").Range("A1:B10").Select Worksheets("Лист2").Range("A5").Activate End Sub

Методы объектов, в том числе использованные только что методы Activate или Select , далее будут рассмотрены более подробно.

Свойства объектов

Каждый объект VBA имеет заданные для него свойства. Например, объект Workbook имеет свойства Name (имя), RevisionNumber (количество сохранений), Sheets (листы) и множество других. Чтобы получить доступ к свойствам объекта, нужно записать имя объекта, затем точку и далее имя свойства. Например, имя активной рабочей книги может быть доступно вот так: ActiveWorkbook.Name . Таким образом, чтобы присвоить переменной wbName имя активной рабочей книги, можно использовать вот такой код:

Dim wbName As String wbName = ActiveWorkbook.Name

Ранее мы показали, как объект Workbook может быть использован для доступа к объекту Worksheet при помощи такой команды:

Workbooks("Книга1").Worksheets("Лист1")

Это возможно потому, что коллекция Worksheets является свойством объекта Workbook .

Некоторые свойства объекта доступны только для чтения, то есть их значения пользователь изменять не может. В то же время существуют свойства, которым можно присваивать различные значения. Например, чтобы изменить название активного листа на “Мой рабочий лист “, достаточно присвоить это имя свойству Name активного листа, вот так:

ActiveSheet.Name = "Мой рабочий лист"

Методы объектов

Объекты VBA имеют методы для выполнения определённых действий. Методы объекта – это процедуры, привязанные к объектам определённого типа. Например, объект Workbook имеет методы Activate , Close , Save и ещё множество других.

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

ActiveWorkbook.Save

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

Чтобы передать методу аргументы, необходимо записать после вызова метода значения этих аргументов через запятую. Например, если нужно сохранить активную рабочую книгу как файл .csv с именем “Книга2”, то нужно вызвать метод SaveAs объекта Workbook и передать аргументу Filename значение Книга2 , а аргументу FileFormat – значение xlCSV :

ActiveWorkbook.SaveAs "Книга2", xlCSV

Чтобы сделать код более читаемым, при вызове метода можно использовать именованные аргументы. В этом случае сначала записывают имя аргумента, затем оператор присваивания “:= ” и после него указывают значение. Таким образом, приведённый выше пример вызова метода SaveAs объекта Workbook можно записать по-другому:

ActiveWorkbook.SaveAs Filename:="Книга2", :=xlCSV

В окне Object Browser редактора Visual Basic показан список всех доступных объектов, их свойств и методов. Чтобы открыть этот список, запустите редактор Visual Basic и нажмите F2 .

Рассмотрим несколько примеров

Пример 1

Этот отрывок кода VBA может служить иллюстрацией использования цикла For Each . В данном случае мы обратимся к нему, чтобы продемонстрировать ссылки на объект Worksheets (который по умолчанию берётся из активной рабочей книги) и ссылки на каждый объект Worksheet отдельно. Обратите внимание, что для вывода на экран имени каждого рабочего листа использовано свойство Name объекта Worksheet .

"Пролистываем поочерёдно все рабочие листы активной рабочей книги "и выводим окно сообщения с именем каждого рабочего листа Dim wSheet As Worksheet For Each wSheet in Worksheets MsgBox "Найден рабочий лист: " & wSheet.Name Next wSheet

Пример 2

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

В коде, приведённом ниже, для объекта Range вызывается метод PasteSpecial . Этот метод передаёт аргументу Paste значение xlPasteValues .

"Копируем диапазон ячеек из листа "Лист1" другой рабочей книги (с именем Data.xlsx) "и вставляем только значения на лист "Результаты" текущей рабочей книги (с именем CurrWb.xlsm) Dim dataWb As Workbook Set dataWb = Workbooks.Open("C:\Data") "Обратите внимание, что DataWb – это активная рабочая книга. "Следовательно, следующее действие выполняется с объектом Sheets в DataWb. Sheets("Лист1").Range("A1:B10").Copy "Вставляем значения, скопированные из диапазона ячеек, на рабочий лист "Результаты" "текущей рабочей книги. Обратите внимание, что рабочая книга CurrWb.xlsm не является "активной, поэтому должна быть указана в ссылке. Workbooks("CurrWb").Sheets("Результаты").Range("A1").PasteSpecial Paste:=xlPasteValues

Пример 3

Следующий отрывок кода VBA показывает пример объекта (коллекции) Columns и демонстрирует, как доступ к нему осуществляется из объекта Worksheet . Кроме этого, Вы увидите, что, ссылаясь на ячейку или диапазон ячеек на активном рабочем листе, можно не указывать этот лист в ссылке. Вновь встречаем ключевое слово Set , при помощи которого объект Range присваивается переменной Col .

Данный код VBA показывает также пример доступа к свойству Value объекта Range и изменение его значения.

"С помощью цикла просматриваем значения в столбце A на листе "Лист2", "выполняем с каждым из них арифметические операции и записываем результат "в столбец A активного рабочего листа (Лист1) Dim i As Integer Dim Col As Range Dim dVal As Double "Присваиваем переменной Col столбец A рабочего листа "Лист2" Set Col = Sheets("Лист2").Columns("A") i = 1 "Просматриваем последовательно все ячейки столбца Col до тех пор "пока не встретится пустая ячейка Do Until IsEmpty(Col.Cells(i)) "Выполняем арифметические операции со значением текущей ячейки dVal = Col.Cells(i).Value * 3 - 1 "Следующая команда записывает результат в столбец A "активного листа. Нет необходимости указывать в ссылке имя листа, "так как это активный лист рабочей книги. Cells(i, 1).Value = dVal i = i + 1 Loop

Создание рабочего листа "Товары.xls"

Сначала рассмотрим, как можно выполнять операции с рабочим листом Microsoft Excel из программы Access. Для этого нам потребуется файл Товары.хls, содержащий рабочую книгу Excel с единственным рабочим листом "Товары", который представляет собой список товаров из демонстрационной базы данных "Борей". Вы можете использовать готовый файл, находящийся на сопровождающем книгу компакт-диске, либо создать его самим, экспортировав в Excel таблицу "Товары" с помощью команды меню Сервис, Связи с Office, Анализ в MS Excel (Tools, Office Links, Analyze It with Microsoft Excel) (см. разд. "Быстрый экспорт данных в другие приложения Microsoft Office" гл. 3).

Для того чтобы подготовить файл Товары.xls к дальнейшим экспериментам, выполните следующие действия:

1. Откройте файл Товары.xls, запустив Microsoft Excel.
2. Выделите ячейки с А4 по D12 в рабочем листе. Выберите команду меню Вставка, Имя, Присвоить (Insert, Name, Define). В диалоговом окне Присвоение имени (Define Name) введите в текстовое поле Имя (Names in workbook) имя диапазона: workRange (рис. 15.25) и щелкните по кнопке ОК. Тем самым создается именованный диапазон, который будет использоваться в последующих примерах.

Рис. 15.25. Создание именованного диапазона в таблице "Товары.xls"

1. Выберите в меню Сервис (Tools) команду Надстройки (Adds-Ins) и снимите все флажки в списке Список надстроек (Add-Ins Available) диалогового окна Надстройки (Add-Ins). Удаление надстроек уменьшает время, требующееся для запуска приложения Excel. Чтобы закрыть диалоговое окно, нажмите на кнопку ОК.
2. Сохраните изменения, выполнив команду Файл, Сохранить (File, Save) или Файл, Сохранить как (File, Save As).
3. Закройте Microsoft Excel.

Иерархия объектов VBA приложения Microsoft Excel

Для того чтобы программно работать с объектами Excel, нужно иметь представление об объектной модели Microsoft Excel. Мы не будем здесь подробно описывать эту модель, поскольку она достаточно сложна, представим только ее основные объекты.

• Объект Application является экземпляром собственно Microsoft Excel. Применяя к этому объекту различные методы, можно воспользоваться практически любыми командами меню Excel. Объект Application обладает свойствами ActiveWorkbook и

  ActiveSheet, которые указывают на текущие объекты Workbook (рабочая книга) и Worksheet (рабочий лист). Можно указать Excel.Application в качестве значения аргумента <класс> функций CreateObject () и GetObjectO, а также в операторе Dim objPlMH As New <класс>.

• Основной объект Excel - Workbook (рабочая книга). С помощью этого объекта осуществляется доступ к свойствам и структуре рабочей книги Excel. Объекты workbook - это файлы, которые состоят из нескольких объектов, создаваемых Microsoft Excel: Worksheet (рабочий лист) и Chart (диаграмма). Объекты Worksheet и Chart содержатся В семействах Worksheets и Charts.
• Объекты Worksheet являются подчиненными по отношению к объекту Workbook и обеспечивают доступ к свойствам и структуре рабочих листов книги Excel. Основным объектом взаимодействия приложений Microsoft Access 2002 и Microsoft Excel 2002 является именно объект Worksheet. Имеется возможность перенести информацию из строк и столбцов объекта Recordset приложения Microsoft Access в ячейки объекта Worksheet приложения Microsoft Excel и наоборот. Если в качестве значения аргумента <класс> функции GetObject() либо в операторе Dim оbj Имя As New <класс> указать Excel.Sheet, то по умолчанию откроется первый объект семейства Worksheets - первый рабочий лист (ActiveSheet) объекта Workbook.
• Объекты sheet аналогичны объектам Worksheet, но они входят в семейство Sheets, которое включает в себя как объекты Worksheet, так и объекты Chart, представляющие листы диаграмм в рабочей книге.
• Объект Range - диапазон ячеек рабочего листа Excel. Это могут быть отдельная ячейка, строка, столбец, прямоугольная область рабочего листа или несвязанный (произвольный набор ячеек) или трехмерный диапазон ячеек, включающий ячейки на нескольких рабочих листах. Можно получить либо установить значения диапазона ячеек, определив объект Range. Таких объектов, как ячейка, строка, столбец просто не существует. Именно объект Range со своими свойствами и методами обеспечивает работу как с отдельной ячейкой, так и с их диапазоном. Для ссылки на необходимую группу ячеек можно использовать имя диапазона, состоящего из этих ячеек. Если именованные диапазоны отсутствуют на рабочем листе, можно использовать метод Cells объекта worksheet, чтобы задать координаты ячейки или группы ячеек. Ниже приводятся два способа изменения значения ячейки А1:

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

Открытие существующего рабочего листа Excel и работа с ним

Прежде чем работать с автоматизированными объектами Microsoft Excel, установим ссылку на библиотеку объектов Microsoft Excel. Для этого:

1. Запустите Access и, если необходимо, откройте базу данных "Борей". Вызовите редактор VBA, открыв любой модуль.
2. Выберите в окне редактора VBA команду меню Сервис, Ссылки (Tools, .References), чтобы открыть диалоговое окно References (Ссылки).
3. Установите флажок у элемента Microsoft Excel 10.0 Object Library (рис. 15.26), затем нажмите на кнопку ОК, чтобы закрыть окно References.

Рис. 15.26.

Команды автоматизации удобно изучать при помощи окна отладки Immediate. Поэтому, выведите данное окно на экран, если оно не отображается. Для этого достаточно нажать соответствующую кнопку на панели инструментов или комбинацию клавиш +.

Чтобы программно открыть рабочий лист рабочей книги "Товары":

1. Закройте Excel, если он запущен.
2. Создайте новый модуль, выполнив команду меню Insert, Module (Вставка, Модуль).
3. Добавьте в раздел описаний следующие описания переменных:

1. Введите в окне отладки следующий оператор (рис. 15.27):

При нажатии затем на клавишу приложение Microsoft Excel запускается в режиме /automation. Функция CurDir возвращает полное имя текущей папки. Если файл Товары.хls был сохранен где-нибудь в другом месте, измените в предыдущем операторе путь к этому файлу. В зависимости от скорости функционирования компьютера, запуск Excel может продолжаться достаточно долю. Загрузка приложения Excel завершена, когда в строке состояния окна отладки надпись Выполнение (Running) исчезает и появляется надпись Готово (Ready). В результате будет создан экземпляр класса Application Microsoft Excel и переменной xlwProd будет присвоена ссылка на объект Workbook. Обратите внимание, что функция Getobject () открывает скрытый экземпляр приложения Excel, значок Excel не появляется на панели задач и интерактивно обратиться к рабочей книге Excel нельзя.

Замечание

В данном операторе аргумент Excel. Sheet является необязательным. Если его не указать, то тип создаваемого объекта будет определен автоматически по расширению файла, указанного в первом аргументе.

Рис. 15.27.

1. Чтобы убедиться, что рабочая книга открыта и мы можем к ней обратиться, введите в окне отладки следующую команду?xlwProd.Name

Свойство Name созданного объекта workbook содержит имя файла Excel: Това-pbi.xls (рис. 15.28).

Рис. 15.28. Команды, позволяющие читать и устанавливать значения отдельных ячеек в рабочем листе "Товары"

1. Файл Toвapы.xls содержит только один объект Worksheet, поэтому рабочий лист "Товары" является активным рабочим листом - объектом ActiveSheet. Чтобы убедиться в этом, введите в окно отладки команду:

Свойство Name этого объекта содержит имя рабочего листа: Товары.

1. Теперь попробуйте обратиться к первой ячейке рабочего листа. Введите?xlwProd.ActiveSheet.Celled, 1). После короткой паузы появится ожидаемый результат - строка "Код товара". Это заголовок первого столбца таблицы.
2. Метод Cells позволяет обратиться к любой ячейке рабочего листа. Введите?xlwProd. ActiveSheet. Cells (R, С), где R - номер строки, а С - номер столбца заданной ячейки, т. е. ее координаты (рис. 15.28).
3. Можно изменить содержимое ячейки, если ввести выражение вида: xlwProd.ActiveSheet.Cells(2,2).Value = "brown rice". Подобно тому как многие объекты Access при введении имени объекта возвращают его значение, метод Cells не требует явного указания свойства Value по умолчанию. Чтобы убедиться в том, что содержимое ячейки изменилось, введите?xlwProd.ActiveSheet.Cells (3,2), не дописывая выражения.Value.

Для установления значения ячейки можно также использовать свойство Formula. Преимущество использования свойства Formula состоит в возможности его применения с целью введения формул с использованием "родного" синтаксиса Microsoft Excel, т. е. в виде ссылок на конкретные ячейки, например "=А2+С6".

Использование именованных диапазонов ячеек

Если в рабочем листе Excel создан именованный диапазон ячеек, то можно получить значения ячеек, содержащихся в этом диапазоне, если сослаться на свойство Range объекта Worksheet. Сначала посмотрим, какие именованные диапазоны присутствуют в открытом нами объекте. Введите в окно отладки команду (рис. 15.29)

XlwProd.Names(1).Name

Семейство Names представляет все имена, определенные в рабочей книге. В данном случае первый элемент этого семейства содержит имя диапазона: WorkRange.

Можно посмотреть не только имя, но и что собой представляет этот диапазон. Введите команду

XlwProd.Names(1).Value Результат будет: =Товары!$А$4:$D$12,

т. е. прямоугольная область А4-D12 на рабочем листе "Товары".

На рис. 15.29 приведены выражения для управления объектом Range.

Рис. 15.29.

Пусть переменная xlsProd ссылается на рабочий лист "Товары". Для этого введите команду:

Set xlsProd = xlwProd.ActiveSheet.

Для указания конкретной ячейки внутри именованного объекта Range можно использовать следующий оператор:

XlsProd.Range("WorkRange").Cells(1,1)

Здесь используется свойство Range объекта Worksheet для доступа к именованному диапазону, а затем свойство Cells объекта Range - для указания конкретной ячейки в диапазоне. Первая цифра указывает строку, а вторая - столбец.

Для того чтобы обратиться к объекту, который находится на уровень выше в иерархии объектов модели, можно воспользоваться свойством Parent. На рис. 15.29 представлено, как обратиться к рабочей книге Excel, содержащей текущий рабочий лист, и как установить объектную переменную xlaProd, которая должна ссылаться на объект Application Microsoft Excel:

Set хlwРабочаяКнига = хlsРабочийЛист.Parent.

Закрытие объектов Workbook и Application

Объект Microsoft Worksheet закрыть нельзя. Для закрытия объекта Excel Workbook может быть использован метод Close, а для выхода из приложения - метод Quit. Следующие операторы закрывают объект Workbook и затем осуществляют выход из приложения сервера автоматизации, освобождая системные ресурсы:

XlwProd.Close xlaProd.Quit Set xlsProd = Nothing Set xlwProd = Nothing Set xlaProd = Nothing

Если программно были внесены изменения в рабочем листе, то при закрытии объекта Workbook будет выдан вопрос о необходимости сохранения изменений. Если вы не хотите, чтобы пользователь получил такой вопрос, введите аргумент False для метода Close. Чтобы гарантировать освобождение всех ресурсов, необходимо освободить все использованные объектные переменные.

Замечание

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

Создание рабочего листа Excel с помощью кода автоматизации

Те же действия, что происходят при нажатии кнопки Анализ в MS Excel, можно осуществить при помощи кода автоматизации VBA. Преимуществом такого способа является возможность форматировать созданный объект специально под нужды конкретного приложения. Рассмотрим функцию CreateCustomSheet (), создающую новый объект Worksheet и заполняющую его данными из таблицы "Товары" базы данных Microsoft Access:

Function CreateCustomSheet() As Integer "Создание рабочего листа MS Excel из таблицы "Товары" "Описание локальных переменных "(Объектные переменные описаны на уровне модуля) Dim сйэБорей As Database "Текущая база данных Dim rstProd As Recordset "Объект Recordset Dim intRow As Integer "Счетчик строк Dim intCol As Integer "Счетчик столбцов "Открытие таблицы в текущей базе данных Set dbБорей = CurrentDb() Set rstProd = dbBopeu.OpenRecordset("Товары", dbdpenTable) DoCmd.Hourglass True "Создание нового объекта Excel Workbook Set xlwProd = CreateObject("Excel.Sheet") ""Создание объекта Application для применения метода Quit Set xlaProd = xlwProd.Parent intRow = 1 intCol = 1 rstProd.MoveFirst "Переход к первой записи Do Until rstProd.EOF "Цикл с шагом в одну запись For intCol = 1 То rstProd.Count "Цикл с шагом в одно поле If (Not IsNull(rstProd(intCol -1))) Then xlwProd.ActiveSheet.Cells(intRow, intCol).Value = CStr(rstProd(intCol -I}} End If Next intCol rstProd.MoveNext intRow = intRow + 1 Loop For intCol = 1 To xlwProd.ActiveSheet.Columns.Count "Форматирование каждого столбца рабочего листа xlwProd.ActiveSheet.Columns(intCol).Font.Size = 8 xlsCust.ActiveSheet.Columns(intCol).AutoFit If intCol = 8 Then "Выравнивание по левому краю числовых и "смешанных почтовых кодов xlwProd.ActiveSheet.Columns(intCol).HorizontalAlignment = _xlLeft End If Next intCol DoCmd.Hourglass False xlwProd.SaveAs (CurDir & "\Товары_2.xls") xlaProd.Quit End Function

Тип данных, возвращаемых выражением rstProd(intCol-l), следует специально изменить с variant на string при помощи функции cstr(), иначе Microsoft Excel вместо нужной величины отобразит в соответствующем столбце #н/д (#N/A#). Если объект Recordset содержит поля, типы которых отличны от Text, то для определения типа данных в столбце используйте соответствующую функцию СТуре().

Константа xlLeft, присвоенная в качестве значения свойству HorizontalAlignment восьмого столбца, представляет собой встроенную константу Excel, определяемую в тот момент, когда устанавливается ссылка на объектную библиотеку Microsoft Excel 10.0 Object Library. Выделение элемента Constants (Константы) в списке Модули/ Классы окна просмотра объектов при подключенной библиотеке Excel отображает константы xlConst. На рис. 15.30 приведено числовое значение константы xlLeft, которая является одной из констант для задания значения свойства HorizontalAlignment.

Рис. 15.30. Значения встроенных констант xlConst в окне Object Browser Access

Ввод оператора? CreateCustomSheet () в окне отладки запускает функцию, которую мы рассматривали выше. На рис. 15.31 приведена рабочая книга "ToBapы_2.xls" с рабочим листом, созданным при помощи функции CreateCustomSheet () и открытым в Microsoft Excel.

Рис. 15.31. Часть рабочего листа Excel, созданного из таблицы "Товары"

Практика показывает, что приложения Microsoft Office (Excel, Word, Power Point и т.п.) являются одними из наиболее часто используемых Windows-приложений. Каждое из них является СОМ-сервером, а следовательно, любой входящий в него объект может быть использован вашей программой как собственный.

Существуют два способа обращения к методам и свойствам СОМ-объекта: путем ссылки на его библиотеку типов (раннее связывание) и по имени (позднее связывание). Для Object Pascal предпочтительным является раннее связывание, так как в этом случае компилятор может проконтролировать правильность обращения к свойствам и методам внешних объектов, а создаваемый им код исполняется, как правило, быстрее. В то же время базовый язык обращения к серверам Microsoft Office - Visual Basic for Application (VBA) не поддерживает работу с указателями и, следовательно, не может использовать интерфейсы. Специально для такого рода языков (помимо VBA c указателями не работают также языки JavaScript, SmallTalk и некоторые другие) в технологию СОМ введены диспинтерфейсы, позволяющие обращаться к методам и свойствам по имени, а не по адресу. При инсталлировании Office можно установить справку по VBA, в которой детально описываются интерфейсы серверов Microsoft Office с указанием назначения методов и свойств, а также параметров обращения к ним. Фактически это единственные доступные программисту документы, на которые ему следует опираться при программировании доступа к мощным возможностям серверов Microsoft Office. Замечу, что при стандартном инсталлировании Microsoft Office справки по VBA не устанавливаются. Если в каталоге Program Files | Microsoft Office | Office вы не найдете файлов vbaxl8.hlp (справка по Excel), vbawrd8.hlp (справка по Word) и т. п., вы должны их добавить с помощью аплета Пуск | Настройка | Панель управления | Установка и удаление программ.

В версию 5 Delphi включены компоненты страницы Servers, позволяющие обращаться к СОМ-объектам этих серверов с помощью библиотек типов, однако эти компоненты практически не документированы. Более того, сами библиотеки уже внедрены в пакет dclaxserver50, так что с помощью этой версии Delphi мне так и не удалось получить их тексты. Во всех случаях изучение обширных текстов библиотек (например, файл Excel_TLB.pas содержит более 20 тыс. строк) мало что дает даже опытному программисту.

В этом разделе приводятся краткое описание основных объектов двух наиболее популярных серверов - Excel и Word, а также примеры использования Excel в стиле VBA (по имени) и с помощью компонентов страницы Servers. Поскольку специально для версии MS Office 97 язык VBA был существенно расширен, этот материал нельзя использовать для работы с более ранними версиями пакета.

Основные объекты серверов Excel и Word

В терминологии VBA используются понятия «объект» и «коллекция». Объект - это обычный интерфейсный объект СОМ, имеющий свойства, методы и события. Коллекция - это группа однотипных объектов. Например, главный объект сервера Excel - Application определяет основные свойства и методы сервера, а коллекция Worksheets представляет собой набор табличных страниц в текущей рабочей книге и т.д. Представленные ниже иерархии объектов и коллекций взяты из файлов vbaXXX.hlp. В отличие от объектов VCL они построены не по принципу наследования, а по функциональной подчиненности.

Объекты Excel

Сервер Excel - это мощный табличный процессор, реализующий размещение и обработку различного рода данных (как числовых, так и текстовых), в том числе построение на их основе графиков и диаграмм. При работе с Excel создается так называемая рабочая книга (файл данных) с одним или несколькими листами. Все листы одной рабочей книги могут быть связаны друг с другом, что позволяет организовать совместные вычисления над размещенными на них данными.

Теперь небольшие пояснения. Переменные Sheet и Range введены только для сокращения текста программы: везде вместо Sheet, например, можно писать Excel.Workbooks.Sheets. С версией Delphi 4 поставлялись файлы XLCONST.PAS и XLCONST.DCU, в которых определены используемые в документации vbaxl8.hlp константы xlXXX. С версией 5 эти файлы не поставляются, поэтому я использую их числовые эквиваленты. Ширина полей печатного документа Excel задается во внутренних единицах, соответствующих приблизительно 3,5 мм, так что указанные в операторах Sheet.PageSetup.ХХХMargin значения установят левое, нижнее и правое поля шириной 1,1 см, а верхнее - 1,4 см. Ширина столбца определяется в символах текста, умещающегося в столбце без отсечения.

Переменная Excel определяет поле класса TForm1. При создании класса в него автоматически помещается значение VarEmpty. После завершения работы с Excel пользователь может закрыть его. Но в моей программе Excel не визуализировался, его работа проходила «за кулисами», а созданная таблица записывалась в указанный пользователем файл с помощью оператора Excel.Workbooks.SaveAs(FileName).

После этого Excel закрывался. Поскольку в нашем случае Excel показывает свое окно, а пользователь может его не закрыть, полезно написать такой обработчик события OnDestroy формы:

Procedure TForm1.FormDestroy(Sender: TObject); begin if not VarIsEmpty(Excel) then Excel.Quit end;

Запуская пример, помните, что создание прайс-листа с помощью Excel - процесс достаточно длительный. На моем компьютере (400 МГц, 64 Мбайт) он занял около минуты (для примера - аналогичный прайс-лист средствами Quick Report создается менее чем за 2 с). В конце обработчика в метку lb помещается общее время работы.

Раннее связывание

Следующий пример в функциональном плане повторяет предыдущий. В нем также с помощью Excel создается прайс-лист, но на этот раз используется доступ непосредственно через интерфейсы сервера. Вас ожидает «сюрприз»: время выполнения второго примера на 40 с больше! Я не смог найти разумного объяснения этому феномену, но оба примера находятся на сопровождающем диске, так что вы в любой момент можете убедиться в этом сами.

Поскольку форма второго примера в точности повторяет форму первого, я не буду объяснять, что нужно сделать для ее создания. Добавьте только на форму компонент TExcelApplication и настройте его свойства: Name=Excel, AutoConnect=True, AutoQuit=True. Если вы используете форму предыдущего примера как шаблон, не вставляйте поле Excel в класс TForm1. Обработчик Button1Click должен выглядеть так (см. листинг 2).

Как видите, он во многом напоминает обработчик предыдущего примера. Поэтому остановлюсь на различиях.

При обращении к свойству SheetsInNewWorkbook, как и во многих других случаях обращения к интерфейсным свойствам и методам, требуется указание идентификатора языка локализации (lcid). Значением 0 кодируется умалчиваемый язык. Этот же идентификатор передается вторым параметром обращения к методу Excel.Workbooks.Add. Первым параметром нужно указать имя файла (в формате WideString), если рабочая книга уже была ранее создана, или «пустой» параметр EmptyParam, если книга создается впервые.

Все мои попытки работать с объектами Range оказались неудачными. Чтобы вы не слишком осуждали меня, я поместил библиотеку типов Excel_TLB.pas в каталог размещения примера - полистайте ее на досуге и попробуйте найти нужное решение для изменения ширины колонок и полей листа, а также для раскрашивания диапазона, выравнивания текста и т.п.

Есть свои нюансы и при обращении к ячейкам. Во-первых, ими владеет объект Application, а не Sheet. Во-вторых, обращение к конкретному элементу коллекции Cells (как и любой другой коллекции) возможно только через ее свойство Item.

Подводя итоги, еще раз хочу обратить ваше внимание на то, что по времени выполнения позднее связывание хотя бы не проигрывает раннему - во всяком случае, для рассмотренных примеров. Если учесть, что единственными доступными подавляющему большинству программистов документами по серверам MS Office являются справочные файлы vbaXXX.hlp, можно сделать вывод: использование вариантов (позднее связывание) проще, удобнее, а главное - намного понятнее, чем непосредственная работа с интерфейсами (раннее связывание).

КомпьютерПресс 6"2001