2 семестр / vba_2002

Рис. 17.5. Создание набора сводных таблиц позволяет представить данные опроса в более понятном виде

Рис. 17.6. Эти сводные таблицы созданы с помощью процедуры, написанной на VBA

ЧастьV.Совершенныеметодыпрограммирования

449

Код VBA, который позволил создать эти сводные таблицы, показан в листинге 17.4.

Листинг 17.4. Создание нескольких сводных таблиц наоснове сложной внешней (базы данных

Sub MakePivotTables()

1Процедура создает 14 сводных таблиц Dim PTCache As PivotCache

Dim PT As PivotTable

Dim SummarySheet As Worksheet Dim ItemName As String

Dim Row As Integer, i As Integer Application.ScreenUpdating = False

1Удаление листа сводных таблиц, если он существует On Error Resume Next

Application.DisplayAlerts = False Sheets("СводныеТаблицы").Delete On Error GoTo 0

' Добавление листа сводных таблиц Set SuminarySheet = Worksheets .Add ActiveSheet.Name = "СводныеТаблицы"

1Создание объекта PivotCache

Set PTCache = ActiveWorkbook.PivotCaches.Add( _ SourceType:=xlDatabase, _ SourceData:=Sheets("Данные"}.Range("Al"). _

CurrentRegion.Address 1

Row = 1

For i = 1 To 14

ItemName = Sheets("Данные").Cells(1, i + 2)

1Создание сводной таблицы

Set PT = PTCache.CreatePivotTable _ (TableDestination:=SummarySheet.Cells(Row, 1), _

TableName:=ItemName)

Row = Row + 1 1

'Добавление полей

With PT.PivotFieldsdtemNamei

.Orientation = xlDataField

.Name = "Кол-во"

End With

With PT.PivotFields{ItemName)

.Orientation = xlDataField

.Name = "Процент"

.Calculation = xlPercentOfTotal End With

PT.AddFields RowFields:=Array(ItemName, "Данные")

PT.PivotFields("Пол").Orientation = xlColumnField

PT.PivotFields("Данные").Orientation = xlColumnField

Next i

450 Глава 17. Работа со сводными таблицами

1

End

Замена оценок описательным текстом SummarySheet.Activate

With Columns{"A:A")

.Replace "1", "Категорически не согласен" -Replace "2", "He согласен"

.Replace "3", "Затрудняюсь ответить"

.Replace П 4 " , "Согласен"

.Replace "5", "Полностью согласен" End With

Настройка ширины столбцов Columns("A:G").EntireColumn.AutoFit Sub

Обратите внимание, что сводные таблицы создаются в цикле на основе одного и того же объекта P i v o t C a c h e . Переменная Row отслеживает начало каждой сводной таблицы. После создания сводных таблиц код заменяет числовые категории на текст в первом столбце таблицы (например, 1 заменяется на Полностью согласен) . Наконец, производится изменение ширины столбцов.

Модификация сводных таблиц

Сводные таблицы Excel разрабатывались для предоставления максимальной гибкости при отображении данных. Например, пользователи легко могут менять поле строки на поте столбца и скрывать некоторые элементы сводной таблицы, которые не являются важными в данный момент. Вы вправе предоставить собственный интерфейс, который позволит облегчить пользователям выполнение операций изменения сводных таблиц. Пример, приведенный в этом разделе, представляет сводную таблицу, которая управляется набором элементов Ор- t i o n B u t t o n s и двумя элементами CheckBox (рис. 17.7).

Сводная таблица состоит из четырех дополнительных вычисляемых значений <Кв1, К Е 2 , КвЗ и Кв4), которые содержат квартальные суммы. Код VBA, который выполняется при щелчке на переключателе O p t i o n B u t t o n l , покачан в листинге 17.5. Данная процедура проста. Она напоминает процедуры обработки событий остальных элементов управления O p t i o n B u t t o n .

Рис. 17.7. Пользователь может применять элементы управлениядляизмененияпараметровсводнойтаблицы

ЧастьУ.Совершенныеметодыпрограммирования

451

Листинг 17.5. Ответ на изменение параметров сводной таблицы

Private Sub OptionButton2_Click()

1Только по кварталам Application.ScreenUpdating = False

With ActiveSheet.PivotTables(1).PivotFields("Месяц")

.PivotltemsC'KBl") .Visible = True

.Pivotltems("Кв2")-Visible = True

.PivotIterns("КвЗ")-Visible = True

.Pivotltems("Кв4"}.Visible = True

.Pivotltems("Январь").Visible = False

.Pivotltems("Февраль").Visible = False

.Pivotltems{"Март").Visible = False

.Pivotltems{"Апрель").Visible = False

.Pivotltems("Май").Visible = False

.Pivotltems("Июнь").Visible = False

.Pivotltems("Июль").Visible = False

.Pivotltems("Август"}.Visible = False

.Pivotltems("Сентябрь").Visible = False

.Pivotltems("Октябрь").Visible = False

.Pivotltems("Ноябрь").Visible = False

.Pivotltems("Декабрь"\-Visible = False

End With

End Sub

Элемент управления CheckBox дает установку отображать общую сумму. Ниже приведеныпроцедурыобработкисобытийдля этихэлементовуправления.

Private Sub CheckBoxl_Click()

1Итоги по столбцам Application.ScreenUpdating = False

ActiveSheet.PivotTables(1)-ColumnGrand = CheckBoxl.Value

End Sub

Private Sub CheckBox2_Click{)

1Итоги по строкам Application.ScreenUpdating = False

ActiveSheet.PivotTables(1).RowGrand = CheckBox2.Value End Sub

Сводные таблицы могут изменяться и др;/тими способами. Как упоминалось ранее, самый простой способ создания кода VBA, который вносит изменения в сводную таблицу, — это запись макроса при ручном внесении изменений в сводную таблицу. После этого необходимо внести изменения в код ископироватьего в процедуруобработкисобытияэлементовуправления.

Резюме

Вэтой главе представлены примеры создания и модификации сводной таблицы с помощью кода VBA.

ВследующейглавеприведеныметодикиnpiшенениякодаVBАдляуправлениядиаграммами.

452	Глава 17. Работа со сводными таблицами

Управление

диаграммами

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

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

О диаграммах

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

Расположение диаграмм

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

•В качестве встроенного объекта листа. Лист может содержать любое количество встроенных диаграмм.

•На отдельном листе диаграммы. Такой лист может содержать только одну диаграмму.

Встроенная диаграмма иногда располагается на диалоговом листе, если используется документ Excel 5/95. Как показано далее в этой главе (см. раздел "Сортировка диаграмм на листе диаграммы"), на листе диаграммы можно хранить встроенные диаграммы.

Большинство диаграмм создаются вручную с помощью мастера диаграмм. Их также можно создавать с помощью кода VBA. Кроме того, код VBA используется для модификации уже существующих диаграмм.

Самым быстрым способом создания диаграммы на новом листе является выделение данных и нажатие клавиши <F11>. Excel создаст новый лист диаграммы и воспользуется типом диаграммы, принятым по умолчанию.

Ключевой при работе с диаграммами является концепция "активной диаграммы". Когда пользователь щелкает на встроенной диаграмме или выделяет лист диаграммы, то активизируется объект C h a r t . В VBA свойство A c t i v e C h a r t возвращает активный объект C h a r t (если такой существует). Можно создать код, который будет управлять этим объектом (он будет подобен коду для работы с объектом Workbook, возвращаемым свойством ActiveWorkbook).

Ниже приведен пример. Если диаграмма активизирована, то следующий оператор отобразит свойство Name активного объекта C h a r t ;

MsgBox A c t i v e C h a r t . Ы а т е

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

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

Объектная модель диаграммы

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

Предположим, что необходимо изменить заголовок, отображаемый на встроенной диаграмме. Объект верхнего уровня— это объект Application (Excel). Объект Application содержит объект Workbook, в котором находится объект Worksheet. Объект Worksheet содержит объект ChartObject, а в нем расположен объект Chart. Объект Chart содержит объект ChartTitle, а объект ChartTitle — объект Characters. Свойство Text объекта Characters представляет объект, который отображается на заголовке диаграммы. Другими словами, объект Characters находится на седьмом уровне иерархии объектной модели.

Ниже в графическом виде представлена иерархия объектов встроенной диаграммы, Application

Workbook Worksheet

ChartObject

Chart

ChartTitle Characters

Подробно тема создания диаграмм с помощью Excel рассмотрена в книге Диаграммы в Excel, выпущенной издательством "Диалектика".

454	Глава 18. Управление диаграммами

Код VBA должен, конечно же, следовать этой объектной модели. Например, чтобы установить заголовок диаграммы в значение Ежегодные продажи, можно создать следующий оператор VBA:

Worksheets("Лист1и).ChartObj ects(1).Chart.ChartTitle. __ Characters.Text = "Ежегодные продажи"

Также можно обратиться к свойству Text объекта ChartTitle:

Worksheets("Лист1").ChartObjects(1).Chart.ChartTitle. _ Text = "Ежегодные продажи"

Использование объекта Characters обязательно только в случае, если требуется форматировать отдельные символы текстовой строки заголовка.

Последний оператор предполагает, что активная рабочая книга предоставлена объектом Workbook. Оператор работает с первым элементом в коллекции ChartObj e t s на листе Лист1. Свойство Chart возвращает объект Chart. Свойство ChartTitle возвращает объект ChartTitle, а свойство Characters — объект Characters. В данном случае нас интересует свойство Text объекта Characters.

Для диаграммы на листе диаграммы объектная модель несколько отличается, так как она не содержит объекта Worksheet или объекта ChartObj ect. Например, ниже представлена объектная модель для диаграммы на листе диаграммы.

Workbook

Chart

ChartTitle Characters

Воспользовавшись этой моделью в VBA, можно использовать следующий оператор для установки заголовка диаграммы в значение Ежегодные продажи; Sheets("Диаграмма1").ChartTitle.Characters.Text = "Ежегодные продажи"

Можно также непосредственно изменить свойство Text объекта ChartTitle: Sheets("Диаграмма1").ChartTitle.Text = "Ежегодные продажи"

Другими словами, лист диаграммы является объектом Chart и не содержит объект ChartObj ect. Если попытаться рассмотреть ситуацию в общем, то легко понять, что родительским объектом для встроенной диаграммы является объект ChartObj ect, а для диаграммы на отдельном листе диаграммы родительским объектом выступает объект workbook,

Ниже приведены операторы, которые отображают окно сообщения со словом Chart.

MsgBox TypeName{Sheets("Лист1").ChartObjects(1).Chart) Msgbox TypeName(Sheets("Диаграмма1"))

При создании новой встроенной диаграммы в объектную модель добавляется коллекция ChartObjects, которая содержится в объекте определенного листа (для листа не создается коллекция charts). При создании листа диаграммы новый объект всего лишь добавляется в коллекции charts и sheets определенной рабочей книги.

Запись макроса

Возможно, самым лучшим способом ознакомления с объектной моделью объекта C h a r t является запись макроса при создании и внесении изменений в диаграммы. Лаже если при записи макросов создается большое количество лишнего и неэффективного кода, этот код можно с успехом использовать в ознакомительных целях — для получения информации об

Часть V. Совершенные методы программирования

4S5

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

Команда записи макросов в Excel генерирует код, в котором используется только активная диаграмма, а для предоставления самого объекта C h a r t применяется свойство A c t i v e C h a r t . В Excel не обязательно выделять объект (или активизировать диаграмму) для того, чтобы управлять им (ею) в коде VBA. Кроме того, как отмечалось ранее, при записи макроса создается большое количество лишнего кода. Таким образом, если эффективность кода является одной из первостепенных задач, то вам придется редактировать код, полученный после записи макроса (особенно, если этот код используется для управления диаграммами).

Результат записи макроса

Команда записи макросов была включена при создании простой диаграммы (показанной на рис. 18.1). После создания диаграммы (но еще в процессе записи макроса) ее немного изменили.

Рис.18,1.ЭтадиаграммасоздаваласьиредактироваласьвпроцессезаписимакросавExcel

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

Sub MacrolO

1 Записанный макрос Range("Al:F2"}.select Charta.Add

ActiveChart.ChartType * xlColumnCluetered ActiveChart.SetSourceData _

Source•.«Sheets СЛист1М .Range("A1-.F2") , _ PlotBy:=xlRows

ActiveChart.LocaCion _ Where:=xlLocationAsObject, Name:=мЛист1"

ActiveChart.HasLegend = False

ActiveChart.ApplyDataLabels _ Type:=xlDataLabelsShowValue, LegendKey;=False

ActiveChart.HasDataTable = False ActiveChart.Axes(xlCategory).Select Selection.TickLabels.Orientation = xlHorizontal

456	Глава 18. Управлениедиаграммами

ActiveChart.ChartTitle.Select Selection.Font.Bold = True

Selection.AutoScaleFont = True With Selection.Font

.Name = "Arial"

.Size = 12

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone -Colorlndex = xlAutomatic

.Background = xlAutomatic End With ActiveChart.PlotArea.Select Selection.Top = 18 Selection.Height = 162 ActiveChart.ChartArea.Select ActiveChart.Axes(xlValue).Select With ActiveChart.Axes(xlValue)

.MinimumScalelsAuto = True

.MaximumScale = 0.6

.MinorUnitlsAuto = True

.MajorUnitlsAuto = True

.Crosses = xlAutomatic

.ReversePlotOrder = False

.ScaleType = xlLinear End With

End Sub

Подкорректированный код

Большая часть кода, полученного при записи макроса в предыдущем разделе, не так важ-
на, как кажется на первый взгляд. Она используется для установки значений свойств, которые
в дальнейшем использоваться не будут. Ниже приведен листинг отредактированного кода
макроса. Этот код выполняет те же действия, что и код из предыдущего раздела, однако он
намного короче и эффективнее. Установка свойства S c r e e n U p d a t i n g в значение F a l s e
предотвращает обновление экрана.
Sub CleanedMacro()
Application.ScreenUpdating			=	False
Charts.Add
ActiveChart.Location _
Where:=xlLocationAsObj ect, Name: ="Лист1"
With ActiveChart
.SetSourceData		Range("Al:F2")
.HasTi tie = True
.ChartType	= xlColumnClustered
-HasLegend	= False
•ApplyDataLabels Type:=xlDataLabelsShowValue
-Axes(xlCategory).TickLabels.Orientation = xlHorizontal
-ChartTitle.Font.Bold			=	True
.ChartTitle.Font.Size			*	12
.PlotArea.Top		= 1 8
.PlotArea.Height = 162
Часть V. Совершенные методы программирования					457

.Axes(xlValue).MaximumScale = 0.6

.Deselect End With

Application.ScreenUpdating = True End Sub

При создании диаграммы с помощью метода Add коллекции charts вы всегда будете использовать лист диаграммы. В предыдущем коде метод Location перемещаетдиаграммунарабочийлист.

Метод Location объекта Chart вызывает особый интерес, так как он формально создает новый объект, а не перемешает существующий. Для того чтобы в этом убедиться, выполните следующий код.

Sub Test()

Charts.Add

MsgBox ActiveChart.Name

ActiveChart.Location _ Where:=xlLocationAsObject, Name:="J]MCTl"

MsgBox ActiveChart.Name End Sub

Эта процедура добавляет диаграмму (лист диаграммы) и представляет окно сообщения, которое отображает имя активной диаграммы. Метод L o c a t i o n перемещает диаграмму на рабочий лист. Следующее окно сообщения демонстрирует имя активной диаграммы, которое отличается от имени предыдущей активной диаграммы. Первоначальный объект Chart прекращает свое существование и заменяется новым объектом Chart, который расположен в объекте ChartObject.

Рабочая книга, которая содержит оба макроса (записанный и подкорректированный), находится на Web-узле издательства. Загрузив эту рабочую книгу, можно самостоятельно сравнить производительность обеих версий макроса.

Распространенные методы управления диаграммами в VBA

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

Активизациядиаграммы

Когда пользователь щелкает на встроенной диаграмме, она активизируется. В VBA можно активизировать встроенную диаграмму с помощью метода Activate. Ниже приведен пример применения этого метода:

ActiveSheet.ChartObjects("Диаграмма1"}.Activate

Если диаграмма находится на листе диаграммы, то можно воспользоваться следующим оператором:

Sheets("Диаграмма!"}.Activate

458	Глава 18. Управление диаграммами