В начало

Приложение. Ответы

9-й час. Основные объекты Excel

Пришло время для серьезной работы с объектами Excel. В этом часе основное внимание будет уделено трем основным и часто используемых объектам: Application, Workbook и Worksheet. Кроме того, вы узнаете, как работать с их свойствами и методами.

В этом часе будут рассмотрены следующие вопросы.

• Роль в VBA объекта Application.
• Создание и управление объектами Workbook.
• Применение объекта Worksheet.

Объект Application

В предыдущем часе указывалось, что объект Application (Приложение) занимает самый верхний уровень иерархии объектов Excel. Какие выводы из этого факта вы должны сделать как программист? Вывод прост: объект Application управляет установками и параметрами уровня приложения, т.е. такими, которые можно найти в диалоговом окне Параметры программы Excel (диалоговое окно Параметры открывается с помощью команды Сервис > Параметры). Чтобы убедиться в этом, закройте все открытые рабочие окна и откройте новую рабочую книгу. Затем выполните следующие шаги.

1. Выберите команду Сервис > Макрос > Начать макрос - откроется диалоговое окно Запись макроса. Введите название макроса, например ПрограммаУстановки. В списке Сохранить в выберите опцию Эта книга.
2. Щелкните на кнопке ОК для начала записи макроса.
3. Выполните команду Сервис > Параметры - Откроется диалоговое окно Параметры. Перейдите на вкладку Вид и снимите флажок опции строку состояния.
4. Перейдите на вкладку Общие и установите флажок опции Стиль ссылок R1C1. Щелкните на кнопке ОК.
5. Остановите запись макроса.
6. Выполните команду Сервис > Параметры и в диалоговом окне Параметры откройте вкладку Вид. Установите флажок опции строку состояния.
7. Перейдите на вкладку Общие и снимите флажок опции Стиль ссылок R1C1. Щелкните на кнопке ОК.
8. Выполните команду Сервис > Макрос > Макросы. Отобразится диалоговое окно Макрос. Выделите макрос ПрограммаУстановки.
9. Щелкните на кнопке Изменить. Откроется редактор Visual Basic.

В листинге 9.1 показан код макроса, который вы должны увидеть в окне кода редактора Visual Basic. (Конечно, в вашем коде будут некоторые отличия, например в имени пользователя или пути к рабочему каталогу. - Прим. ред.)

Листинг 9.1. Процедура ПрограммаУстановки

1: Sub ПрограммаУстановки()

2:  With Application

3:  .ReferenceStyle = xlR1C1.

4:  .UserName = "Alex"

5:  .StandardFont = "Arial"

6:  .StandardFontSize = "10"

7:  .DefaultFilePath = "С:\Мои документы"

8:  .EnableSound = False

9:  .RollZoom = False

10:  End With

11:  Application.DisplayStatusBar = False

12: End Sub

Свойства какого объекта описаны в этом примере? Правильно, объекта Application. Надеюсь, данный пример достаточно убедил вас в значимости объекта Application.

Использование встроенных функций Excel

Роль объекта Application не ограничивается заданием параметров и опций Excel. Объект Application также содержит встроенные функции Excel. Если вы хотите использовать в процедуре VBA встроенные функции Excel, то необходимо использовать объект Application.

Следующие инструкции помогут вставить функции AVERAGE (СРЕДНЕЕ) и SUM (СУММА) в код VBA.

1. Создайте новую процедуру и назовите ее ВстроенФункции.
2. Введите следующий код процедуры:

Dim sngAnswer As Single

sngAnswer=Application.Average(Worksheets("Лист1").Range("A1:A4"))

MsgBox "Среднее значение диапазона = " & sngAnswer

sngAnswer=Application.Sum(Worksheets("Лист1").Range("A1:A4"))

MsgBox "Сумма значений диапазона = " & sngAnswer

3. Перейдите на рабочий лист Лист1 открытой рабочей книги.
4. Введите в ячейку А1 число 100, в ячейку А2 - 200, в ячейку A3 - 300, в А4 - 400.
5. Выполните процедуру. Появится первое окно сообщения, показывающее среднее значение введенных чисел (рис. 9.1).
6. Для закрытия окна сообщения щелкните на кнопке OK. Второе окно сообщения покажет сумму введенных чисел.
7. Щелкните на кнопке ОК для закрытия окна сообщения и завершения процедуры.

Чтобы выполнить процедуру из рабочего листа, нажмите комбинацию клавиш <Alt+F8> и в диалоговом окне Макрос дважды щелкните на имени процедуры.

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

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

Свойства и методы объекта Application

Объект Application имеет множество полезных свойств и методов. Из длинного списка свойств выделим следующие.

• ActiveWorkbook - возвращает активную (текущую) книгу.
• ActiveSheet - возвращает активный лист в активной рабочей книге. Возвращаемый лист может быть любого поддерживаемого типа, включая рабочий лист и лист диаграмм.
• ActiveCell - возвращает активную ячейку на активном листе активной рабочей книги.
• ThisWorkbook - возвращает рабочую книгу, где находится выполняемая процедура.
• MailSystem - возвращает почтовую систему, установленную на компьютере. Это свойство особенно полезно, когда электронная почта работает в автоматическом режиме.
• MailSession - может проверять идентификационную информацию о пользователе электронной почты.
• OperatingSystem - полезное свойство, если разрабатываемое приложение должно работать как на платформе Windows, так и Macintosh. Это свойство определит тип операционной системы, затем можно внести необходимые изменения в ход выполнения приложения.
• Selection - определяет текущее выделение. Выделением может быть диапазон ячеек, элементы диаграммы и т.п.

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

• MailLogon и MailLogoff - используемые совместно со свойствами MailSystem и MailSession эти методы позволяют начать или закончить сеанс работы электронной почты.
• Quit - применяется для выхода из Excel.
• Run - выполняет макросы, записанные в стиле Excel 4.0.

Объект Workbook

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

• Activate - делает активной указанную рабочую книгу.
• Close - закрывает рабочую книгу.
• Save - охраняет рабочую книгу.

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

• SaveAs - сохраняет рабочую книгу. Отличие этого метода от метода Save заключается в том, что метод SaveAs имеет ряд необязательных, но полезных аргументов, таких как Filename (Имя файла), FileFormat (Формат файла), Password (Пароль), WriteResPassword (Пароль на запись), ReadOnlyRecommendec (Рекомендовать режим "только для чтения").
• Printout - печать всего содержимого рабочей книги.
• Print Preview - отображает рабочую книгу в режиме предварительного просмотра.

Из большого списка свойств объекта Workbook рассмотрим только несколько, на мой взгляд, самых необходимых и полезных. Подобно объекту Application, который имеет свойство ActiveWorkbook, данный объект также имеет свойство ActiveSheet (Активный лист). Если необходимо найти каталог, в котором сохранена рабочая книга, используется свойство Path (Путь). Очень полезным свойством является свойство Saved (Сохраненная). Это свойства возвращает значение ИСТИНА, если книга была сохранена после внесения в нее последних изменений. В противном случае возвращается значение ЛОЖЬ.

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

1. Создайте новую процедуру с именем ПримерРабКниги.
2. Введите код процедуры:

Dim wbNewWorkbook As Workbook

Set wbNewWorkbook = Workbooks.Add

wbNewWorkbook.Worksheets("Лист1").Range("A1").Value = 100

wbNewWorkbook.SaveAs "Чac9"

wbNewWorkbook.Close

MsgBox "Рабочая книга закрыта"

3. Выполните программу. Вы ничего не увидите во время ее выполнения до появления окна сообщения.
4. Когда появится окно с сообщением, что рабочая книга закрыта, щелкните на кнопке ОК.
5. Откройте рабочую книгу Час9. (Если вы находитесь в редакторе Visual Basic, то перейдите в окно программы Excel и откройте книгу с помощью команды Открыть меню Файл.)
6. Вы увидите число 100 в ячейке А1 рабочего листа Лист1 - это действительно та книга, которую создала процедура.

Теперь подробнее рассмотрим все операторы этой процедуры. Ее полный код представлен в листинге 9.2.

Листинг 9.2. Процедура ПримерРабКниги

1: Sub ПримерРабКниги()

2:  Dim wbNewWorkbook As Workbook

3:  Set wbNewWorkbook = Workbooks.Add

4:  wbNewWorkbook.Worksheets("Лист1").Range("A1").Value = 100

5:  wbNewWorkbook.SaveAs "Чac9"

6:  wbNewWorkbook.Close

7:  MsgBox "Рабочая книга закрыта"

8: End Sub

Процедура начинается с объявления переменной-объекта:

Dim wbNewWorkbook As Workbook

Этой переменной-объекту назначается новая создаваемая рабочая книга:

Set wbNewWorkbook = Workbooks.Add

Приведенный оператор создает рабочую книгу, добавляя новый элемент в коллекцию Workbooks. При создании Excel дает рабочей книге имя по умолчанию, например Книга1 или Книга2. Другими словами, вы не знаете точное имя созданной рабочей книги. Поэтому для разрешения коллизии с именами созданная рабочая книга назначается переменной-объекту и далее в коде процедуры используется имя этой переменной вместо ссылки на книгу.

Далее процедура присваивает ячейке А1 значение

wbNewWorkbook.Worksheets("Лист1").Range("A1").Value = 100

Наконец, выполняются методы SaveAs и Close. Окно сообщения добавлено в процедуру для того, чтобы вы знали, что процедура завершилась.

wbNewWorkbook.SaveAs "Час9"

wbNewWorkbook.Close

MsgBox "Рабочая книга закрыта"

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

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

Объект Worksheet

От описания объекта Workbook (Рабочая книга) нетрудно перейти к рассмотрению объекта Worksheet (Рабочий лист) так же, как при изучении Excel. Вы знаете, что можно делать с рабочим листом: выбирать его, переименовывать, копировать, удалять, вставлять новый лист в рабочую книгу. Чтобы выполнить аналогичные действия с объектом Worksheet, необходимо знать его свойства и методы.

При работе с этим объектом вы в основном будете использовать его свойства. Одно из часто используемых свойств - свойство Name (Имя). В VBA задание свойства Name эквивалентно двойному щелчку на ярлыке рабочего листа и вводу нового имени. Для задания активному листу нового имени Бюджет применяется код

ActiveSheet.Name = "Бюджет"

Предположим, что необходимо форматировать весь рабочий лист. Как выбрать все ячейки листа? Для этого используется свойство Cells (Ячейки). Вот небольшой пример задания шрифта для всех ячеек листа:

Worksheets("Лист1").Cells.Font.Name = "Arial"

При работе с объектом Worksheet часто применяются следующие методы (конечно, список всех методов этого объекта значительно шире):

• Activate - активизирует рабочий лист;
• CheckSpelling - используется для проверки орфографии содержимого рабочего листа;
• Delete - удаляет рабочий лист из рабочей книги.

А как вставить новый лист в рабочую книгу? Ну конечно же, с помощью метода Add! Чтобы вставить и переименовать рабочий лист, выполните следующее.

1. Создайте новую процедуру с именем НовыйЛист.
2. Введите код процедуры

Dim wsNewWorkSheet As Worksheet

Set wsNewWorkSheet = Worksheets.Add

wsNewWorksheet.Name = Format(Date, "d mmmm yyyy")

3. Выполните процедуру.
4. Если вы находились в редакторе Visual Basic во время выполнения процедуры, то перейдите в окно программы Excel. Вы увидите новый рабочий лист, где в качестве имени листа будет стоять текущая дата (рис. 9.2).

Рис. 9.2. Обратите внимание, что в качестве имени нового листа стоит дата

Этот код похож на код создания рабочей книги. Процедура также начинается с объявления переменной-объекта, которой затем назначается создаваемый рабочий лист.

Dim wsNewWorkSheet As Worksheet

Set wsNewWorkSheet = Worksheets.Add

После создания листа используется свойство Name для задания имени в виде текущей даты.

wsNewWorkSheet.Name = Format(Date, "d mmmm yyyy")

Функция Format (Формат) применяется для управления внешним видом (форматом) даты. В данном случае дата будет иметь такой формат: день, полное название месяца, четыре цифры для записи года.

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

Резюме

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

В следующем часе мы ближе познакомимся с другим основным объектом Excel - объектом Range (Диапазон).

Вопросы и ответы

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

Ответ. Нет, обычно для создания законченного приложения достаточно знать около 20 объектов. Если возникнет необходимость работы с незнакомыми для вас объектами, есть два выхода: справочная система Visual Basic и запись макросов.

Вопрос. Почему Excel в VBA трактуется как приложение?

Ответ. Доступ к некоторым установкам и параметрам среды Excel можно получить только в том случае, если рассматривать Excel как "цельное" приложение. Эти и некоторые другие причины приводят к тому, что в VBA Excel выделяется в отдельный объект Application.

Вопрос. Почему метод Add используется и для создания новой рабочей книги, и для вставки нового листа в книгу?

Ответ. Этот метод универсальный, так как его назначение - добавлять новые элементы в коллекцию. Обратите внимание, что объект Workbook является элементом коллекции Workbooks, а объект Worksheet - коллекции Worksheets. Надеюсь, теперь понятно, почему для создания новых рабочих книг и листов применяется метод Add.

Практикум

С помощью тестов и упражнений вы проверите, насколько хорошо усвоили изложенный материал. Ответы на вопросы смотрите в Приложении.

Тесты

1. Как в VBA применить функцию МАХ для нахождения максимального значения из диапазона ячеек А1:С5?
2. Какой объект находится на высшем уровне иерархии объектов?
3. Какой метод применяется для создания новых рабочих книг и рабочих листов?
4. Как в VBA удалить рабочий лист из рабочей книги?
5. Истинно или ложно следующее утверждение: из процедуры VBA нельзя вызывать на исполнение макросы, записанные в стиле Excel 4.0?

Упражнение

Создайте новую процедуру УпрЧас9, которая должна выполнить следующие действия:

• создать новую рабочую книгу;
• вставить в эту книгу новый рабочий лист;
• дать новому рабочему листу ваше имя;
• сохранить рабочую книгу под именем УпрЧас9.

Выполните процедуру. Откройте рабочую книгу УпрЧас9 и введите несколько значений в рабочий лист с вашим именем. Создайте новую процедуру с именем СохрЧас9. Эта процедура должна определить, сохранена ли рабочая книга после внесения в нее изменений. Если книга была сохранена, то должно выводиться окно с соответствующим сообщением. Выполните эту процедуру.