Всущности, VBA — это не что иное, как язык обработки объектов, принадлежа щих хост-приложению, например Excel, Word или PowerPoint. В Excel используется довольно много разнообразных объектов, любой из них можно обрабатывать с помо щью макросов VBA.
Вэтой главе основное внимание уделяется самому важному для нас объекту — Chart (диаграмма). Как вы увидите, объект Chart может содержать другие объекты, которые, в свою очередь, тоже могут содержать объекты. Иными словами, нам пред стоит работать с иерархией объектов.
Объекты и коллекции
в этом разделе представлен широкий обзор объектов и коллекций Excel.
Иерархия объектов
Объект Application (т.е. активизированная программа Excel) содержит все другие объекты. Вот несколько примеров объектов, которые могут входить в объ ект Application:
•объект Workbook (рабочая книга);
•объект Window (окно);
•объект Addin (надстройка).
Группа похожих объектов называется коллекцией. Например, коллекция Workbooks содержит все открытые рабочие книги. Каждый объект Workbook, входящий в кол лекцию Workbooks, может содержать такие объекты:
•Worksheet (рабочий лист);
•Chart (лист диаграмм);
•Name (имя).
Как и ожидается, все эти объекты тоже могут входить в коллекции. Например, все объекты Worksheet некоторой рабочей книги составляют коллекцию Worksheets. Перечисленные выше объекты могут содержать другие объекты. Например, в объект Worksheet могут входить следующие объекты:
•Chartobject (контейнер для внедренной диаграммы);
•Range (диапазон);
•PageSetup (параметры страницы);
•PivotTable (сводная таблица).
Если все это сбивает вас с толку, поверьте мне, со временем вы поймете, что ие рархия объектов Excel вполне логична и хорошо структурирована. В справочной сис теме Excel вы можете найти диаграммы, изображающие полную объектную модель Excel (рис. 15.1).
П З ^ Ф ^Ф#1#
шщ
Мастер ответов | Укдавтель |
Microsoft Excel Objects
(Charts)
^
Выберите действием
fee Also
jobject model
ICharts (Chart)
HCJMHtAreir
HOataTabie
Нвёти
8ь^«|»1Твр<^1дел}
3
HWatts
И^ЯУ^
Microsoft Excel Objects (Shapes)
•JLegendEntrles (LegendEntry)
Microsoft Excel Objects (Worksheet)
jPageSctup
-jtegendKey
Understanding Automation
iChatttHJe
Hshape» (Shape)
jSeriesCoHection (Series)
IScripts (Script)
•jTrendlines (Trendline)
HchartGroups (ChartGrot^)
[Axes ( A H » )
HwvirtLaymit:
HA*^Titre
Legend
jDMayUmHlatMsl
U
Object and collection
HGridilneg
и
Object only
r
Click arrow to expand chart
HtickLafaefa "
Поиск e Инт<фнвтв
d
Puc. 15.1. Полную иерархию объектов Excel можно найти в справочной системе
Коллекции
Как уже упоминалось, коллекция — это группа похожих объектов. Коллекция тоже является объектом. В коде VBA можно обращаться как ко всей коллекции, так и к любому входящему в нее объекту. Чтобы сослаться на отдельный объект коллекции, нужно указать в кавычках его имя или индекс после имени коллекции, например так:
Charts("Диаграмма!")
Если Диаграмма! является первым листом диаграмм, входящим в коллекцию, то предьщущую ссылку можно записать так:
Charts (!)
382
Часть III. Использование VBA
Ссылка на второй лист диаграмм в рабочей книге выглядит как Charts (2) и т.д. В Excel определена коллекция Sheets, содержащая все листы рабочей книги (как
рабочие листы, так и листы диаграмм). Если лист диаграмм Диаграмма! является первым листом рабочей книги, то к нему можно обратиться так:
Sheets (!)
Ссылки на объекты
При ссылке на объект в коде VBA его квалифицируют с помощью точки (которую иногда называют "оператор точка") с последующим именем объекта. Но как быть, ес ли одновременно открыты две рабочие книги, каждая из которых содержит рабочий лист с именем Лист!? Тогда ссылка должна быть квалифицирована путем добавления контейнера объекта, например:
Workbooks("Книга!").Worksheets("Лист!")
Без квалификатора рабочей книги транслятор VBA будет искать Лист! в активной рабочей книге.
Обратиться к определенному диапазону (например, к ячейке А1) в листе Лист! ра бочей книги Книга! можно следующим образом:
Однако чаще всего объект Application в ссылке можно опустить, он предполага ется по умолчанию. Если объект Книга! является активной рабочей книгой, то в ссылке можно опустить и его.
Worksheets("Лист!").Range("А!")
Кроме того, если заранее известно, что Лист! является активным рабочим листом, то ссылку можно записать еще проще.
Range ("А!")
Как уже упоминалось, в Excel нет объекта, представляющего отдельную ячейку. Одна ячейка — это просто объект Range, состоящий из единст венной ячейки.
Сама ссылка на объект (как в предыдущих примерах) не выполняет никаких дей ствий. Она имеет смысл, только если с ее помощью читается, изменяется свойство объекта или задается метод, выполняющий какое-либо действие над объектом.
Свойства и методы
в этом разделе рассматриваются два ключевых атрибута объекта: свойства и методы.
Свойства объекта
Каждый объект может иметь (и чаще всего имеет) свойства. Например, у объекта Chart есть свойство HasTitle (иметь заголовок). Оно возвращает значение True, ес ли у диаграммы есть заголовок, или False, если заголовка нет. Можно написать код VBA, выводящий или устанавливающий значение свойства HasTitle.
Чтобы обратиться к свойству, нужно добавить к имени объекта точку и имя свой ства. В приведенной ниже процедуре значение свойства HasTitle выводится с помо щью функции MsgBox. Диаграмма расположена в листе диаграмм Д1.
Глава 15. Объекты, коллекции, свойства и методы
3 8 3
Sub ЗаголовокЕстьО
MsgBox Charts("Д1").HasTitle End Sub
Код предыдущего примера выводит текущее значение свойства HasTitle диа граммы, расположенной в листе диаграмм Д1. Если при запуске макроса листа диа грамм Д1 не существует, то будет сгенерировано сообщение об ошибке. Иными сло вами, процедура не содержит защиты от ошибок.
Для добавления заголовка в диаграмму нужно присвоить свойству HasTitle значение True. По умолчанию в заголовок записывается текст Chart Title. Записать в заголовок более описательный текст можно с помощью свойства Text объекта ChartTitle.
Sub
ДобавлениЗаголовкаО
C h a r t s ( " Д 1 " ) . H a s T i t l e = True
End
C h a r t s ( " Д 1 " ) . C h a r t T i t l e . T e x t = "Выполнение бюджета"
Sub
После выполнения этой процедуры в диаграмме, расположенной в листе Д1, будет выведен заголовок Выполнение бюджета.
Ниже перечислены свойства объекта Application (т.е. Excel).
•Application.ActiveWorkbook — возвращает активную рабочую книгу Excel (объект Workbook).
•Application.ActiveSheet — возвращает активный рабочий лист (объект Sheet) активной рабочей книги.
если в данный момент одна из диафамм активизирована.
•Application.ActiveCell — возвращает объект активной ячейки (объект Range) активного окна.
• Application . Selection — возвращает объект, выделенный в данный момент в активном окне объекта Application. Это может быть диапазон, диаграмма, рисунок или любой другой объект, который можно выделить.
Важно понимать, что свойство ссылается на объект, поскольку само является объ ектом. Например, ссылка Application.ActiveChart возвращает объект Chart. Сле довательно, к свойству можно обратиться, например, с помощью такого оператора:
Application.ActiveChart.HasTitle = True
В этом операторе ActiveChart уже не свойство, а объект, имеющий свойство HasTitle. Поскольку свойства объекта Application используются часто, квалификатор Application в ссылках можно опускать. Например, приведенная ниже ссылка обращается к свойству HasTitle активной диаграммы. Квалификатора Application в ней, как видите, нет.
ActiveChart.HasTitle
Методы объекта
Кроме свойств, объекты имеют методы. Метод — это некоторое действие, выпол няемое над объектом. Ниже приведена простая процедура, в которой используется метод PrintPreview объекта Chart. В результате выполнения процедуры активная диаграмма выводится в окне предварительного просмотра печати Excel.
Sub ПредвПросмО ActiveChart.PrintPreview
End Sub
Bo многих методах используются аргументы, определяющие параметры выполняе мого действия. В приведенном ниже примере метод Export объекта Chart сохраняет
3 8 4
Часть III. Использование VBA
активную диаграмму в файле GIF. При вызове метода Export в него передаются два параметра (оба строковые), задающие имя и тип графического файла, в который экс портируется диаграмма.
Sub ЭкспортДиаграммы()
ActiveChart.Export "mychart.gif", "GIF" End Sub
Задание аргументов для методов и свойств
В методах аргументы используются для более подробной детализации выполняемого дейст вия. В некоторых свойствах аргументы предназначены для более точного задания значения свойства. Иногда один или несколько аргументов могут быть необязательными.
Если при вызове метода используются аргументы, их нужно располагать после имени ме тода и разделять запятыми. Если аргумент необязателен, то он просто пропускается, вместо него вводится запятая. Рассмотрим метод Protect объекта рабочей книги. За гляните в справочную систему Excel, и вы увидите, что метод Protect принимает три ар гумента: пароль, структура и окно. Эти аргументы соответствуют параметрам, задавае мым в диалоговом окне Защита книги.
Например, чтобы защитить рабочую книгу MyBook.xls, выполните следующий оператор:
После его выполнения рабочая книга защищена с паролем, заданным в первом аргумен те, структура книги защищена (второй аргумент), а окна не защищены (третий аргумент).
Если пароль не нужен, то выполните оператор
Workbooks("MyBook.xls").Protect , True, False
Обратите внимание: первый аргумент опущен, вместо него введена запятая.
Чтобы код был более понятным, можно применить именованные аргументы, как в сле дующем примере:
Workbooks{"MyBook.xls").Protect S t r u c t u r e : = T r u e , _ Windows:=False
Именованные аргументы часто бывают весьма полезными, особенно если метод имеет много аргументов, а задать нужно только несколько из них. Когда используются имено ванные аргументы, ставить запятые вместо опущенных аргументов не нужно.
Если аргументы используются в свойствах, то их нужно заключить в скобки. Например, свойство Address объекта Range принимает пять аргументов, все они необязательные. Приведенный ниже оператор неправильный, поскольку опущены скобки.
MsgBox Range("А1").Address False 'Неправильно!
Правильный оператор записывается так:
MsgBox Range("А1") . Address(False)
Этот же оператор можно записать с использованием именованного аргумента:
MsgBox Range("А1").Address(rowAbsolute:=False)
Все эти тонкости станут намного понятнее после нескольких экспериментов с аргумента ми свойств и методов.
Где найти информацию об объектах
Excel предоставляет вам буквально тысячи различных объектов, методов и свойств. Где можно найти информацию о них? Существует три основных способа.
•С помощью записи макросов (см. следующий раздел).
•С помощью справочной системы. Чтобы получить справку о некотором объек те, методе или свойстве, введите его имя в окне кодов VBA и нажмите клавишу
Глава 15. Объекты, коллекции, свойства и методы
385
<F1>. На рис. 15.2 показан пример окна справочной системы для объекта Series. Обратите внимание: в окне расположены гиперссылки, позволяющие быстро вывести справку о свойствах и методах этого объекта.
Изучая (или присваивая) коды VBA, написанные другими. Начните с кодов, приведенных в главе 16, "Использование VBA в диаграммах".
т шшшшш
Ш <!•<=> " * • # • #
• ^
Series Object
See Also
Properties
Methods
Events
rOiert» (Chart)
jChartGroup» (ChartGroup)
SerlesCoMectlon (Series)
iPoint» (Point)
llntettor
Represents a series in a chart. The Series object is a member of the SerfesCoWection collection.
Using the Series Object
Use SeriesColection(«i0(!Ai)^ where ndexis the series index number or name, to return a single Series object. The following example sets the color of the interior for the first series in embedded chart one on Sheet1.
The series index number irvdicates the order in which the Series were added to the chart. S e r i e s C o l i e c t i o n (1) is the first series added to the chart, and S e r i e s C o l i e c t i o n ( S e r i e s C o l i e c t i o n . Count) is the last one added.
zi
Рис. 15.2, В справочной системе Excel можно найти информацию о любом объект
Получение справки о свойствах и методах
При вводе кода VBA часто бывает полезным всплывающее окно подсказки, содержащее список свойств и методов объекта. Окно подсказки показано на приведенном ниже ри сунке. Список появляется на экране, как только пользователь вводит точку после имени объекта. Если щелкнуть на одном из имен списка, оно автоматически вводится в код по сле точки. Свойства и методы отмечены в списке разными пиктограммами.
- PiclS 04.KIS - Modulel (Code)
j (General)
' j " | I Форматирование Дияграммы
S ub Фо рматир о вание Диаг р аимы()
АсtiveChart . PlotArea .
Щ
й» Interior
EffLen
Ei* Name
Щ:'\
Bf Parent
•ii
ES* Width
Л
]^ш<\- \
386
Часть III. Использование VBA
Если список не появляется, выберите в главном меню редактора Visual Basic команду Tools'=>Options (Сервис«=^Параметры), в открывшемся диалоговом окне Options (Параметры) ак тивизируйте вкладку £iy/to/-(Редактор) и установите флажок Auto Ust Members (Автоматический вывод списка членов). Кроме того, проверьте, правильно ли объявлена переменная объекта.
Объектная модель диаграммы
Используя VBA при работе с диаграммами, вы быстро обнаружите, что объект Chart весьма сложен. Начнем с того, что он может входить в один из двух разных ро дительских объектов. Родительским объектом диаграммы, расположенной в листе диаграмм, является объект Workbook — рабочая книга, содержащая лист диаграмм. В этом случае иерархия объектов имеет вид
Workbook
Chart
Родительским объектом внедренной диаграммы является Chartobject, родитель ским объектом которого является Worksheet. Иерархия родительских объектов вне дренной диафаммы имеет следующий вид:
Workbook Worksheet
Chartobject
Chart
Объект Chart содержит другие объекты. Ниже приведена частичная иерархия объ екта Chart.
Эти объекты, в свою очередь, также содержат другие объекты. Например, коллек ция SeriesCollection содержит объекты Series. Объект Series содержит следую щие объекты:
Series Border
Points (Коллекция) I n t e r i o r
Рассмотрим коллекцию Points. Каждый объект Point (входящий в коллекцию Points) содержит следующие объекты:
Point Border DataLabel Interior
Допустим, код VBA должен установить в подписи данных текст Январь. Предположим, подпись данных принадлежит первой точке первого ряда первой диаграммы первого рабо чего листа активной рабочей книги. В этом случае оператор VBA должен пройти по иерар хии объектов и присвоить значение свойству Text соответствующего объекта DataLabel.
Workbook Worksheet
Глава 15. Объекты, коллекции, свойства и методы
3 8 7
ChartObject
Chart
Series
Point DataLabel
Выполняющий указанную задачу оператор имеет следующий вид (в одной строке он не помещается):
. SeriesCollection(l) . Points(1) . DataLabel . Text = "Январь"
Однако это еще не все. Если свойство HasDataLabel объекта Point не уста новлено в True, то попытка установить свойство Text объекта DataLabel сгенерирует ошибку.
Имена коллекций
Как вы уже знаете, коллекция — это объект, содержащий похожие объекты. Обычно имя коллекции образуется из имен входящих в нее объектов с добавлением суффикса s (множественное число существительного в английском языке). Например, коллекция Workbooks содержит все объекты открытых рабочих книг Workbook, коллекция ChartObjects— объекты ChartObject и т.д. Как же быть с объектами Series (ряд)? В английском языке единственное и множественное число слова series идентичны, поэтому образовать имя коллекции путем добавления суффикса s не удается. Выход из этой ситуации простой: коллекцией объектов Series является объект SeriesCollection.
Запись макросов
Инструмент записи макросов — весьма удобное средство их создания. С его помо щью можно преобразовать действия с клавиатурой и мышью в код VBA. Однако по ряду причин инструмент записи не может быть основным средством создания макросов.
•Инструмент записи применим только для простых макросов или для записи небольших частей сложных макросов. Вы быстро обнаружите, что при записи трудно избежать ошибок: человек не может помнить, какие клавиши он нажи мал всего несколько секунд назад. В то же время в коде VBA все операции представлены в удобном для просмотра виде.
•Инструмент записи макросов довольно ограничен: он не может генерировать код, содержащий циклы, операторы присваивания, условные операторы, вывод диалоговых окон и т.д.
•Сгенерированный код существенно зависит от параметров среды, установлен ных на момент генерации.
•Код макроса, сгенерированного с помощью инструмента записи, чаще всего нуждается в коррекции с помощью редактора Visual Basic.
Кроме главного меню Excel, команды управления записью макросов есть так же в окне редактора Visual Basic. На панели инструментов Visual Basic есть (или можно установить) кнопки Run Macro (Выполнить макрос), Record Macro (Запись макроса), Stop Macro (Остановка записи) и др.
Записываем макрос
Выполним несложное упражнение. В новой рабочей книге введите данные и соз дайте на их основе диаграмму, как показано на рис. 15.3.
3 8 8
Часть III. Использование VBA
|c|picl5_03.K«s
С 1
0 " Г
E f f \
O f
f l ^ ^ ^ ^ P
""fpiT-vli
[
A \ '
'
W \
H . f 1
['Ч
1
Регион-1
....
j 3_
41
151
Регион-1
40 294
4
30 462
lA.
30 885
50 000
П
ГшГ
30 311
40 000
-
1 1I 1 IT
(J7_
30 361
mill1 2 3 4 5 6
Ipf
30 000
-
U
1 и Регион-11
LE
20 000
-
Iftf
10 000
-
It 12
0
•
||!3
1114^
i|*t ^
•
^hJ}f!!SJ.U
"
'
\<L.
J
iA
Рис, 15.3, Гистограмма, выводящая данные диапазона А1:А7
Выполните перечисленные ниже действия.
1.Активизируйте диаграмму.
2.Выберите команду Сервис'=>Макрс)С'=>Начать запись; активизируется диалоговое окно Запись макроса.
3.В диалоговом окне Запись макроса введите имя макроса (пробелы недопусти мы). В данном примере введите имя макроса ФорматированиеДиаграммы. В поле Сохранить в оставьте значение Эта книга.
4.Чтобы начать запись макроса, щелкните на кнопке ОК. С этого момента все ва ши действия будут преобразованы в код VBA. Обратите внимание на небольшую плавающую панель инструментов, содержащую кнопку с синим квадратиком (это кнопка Остановить запись). Вы можете переместить эту панель инструмен тов так, чтобы она вам не мешала, однако не закрывайте ее,
5.Измените цвет области построения на белый.
6.Измените цвет столбцов гистограммы на красный.
7.Установите пунктирные линии сетки.
8.Удалите легенду.
9.Щелкните на кнопке Остановить запись, расположенной ria плавающей панели инструментов, или выберите команду Сервис'=>Макрос'=>Остановить запись.
Теперь рабочая книга содержит модуль VBA с макросом ФорматированиеДиа граммы. Для просмотра макроса нажмите клавиши <Alt-f-Fll> или дважды щелкните на модуле макроса в окне проектов (по умолчанию он называется Modulei). На рис. 15.4 показан код записанного нами макроса. Ваш код может немного отличаться, в зависимости от того, как вы выполняли операции форматирования. Например, если вы сначала вьщелили легенду, а затем нажали клавишу <Del>, то код будет иным, чем полученный при удалении легенды с помощью кнопки Легенда, расположенной на панели инструментов Диаграммы.
Если при записи макроса вы пользовались диалоговыми окнами форматиро вания, то обнаружите, что код макроса содержит намного больше операторов, чем код в файле Pici5_04.xls. Используя при записи макроса только кнопки панелей инструментов, вы получите более эффективный код.
