В начало

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

18-й час. Диаграммы

Построение диаграмм - одно из самых популярных средств Excel. Разработчики часто используют это средство Excel построения диаграмм в своих приложениях. В этом часе мы запишем макрос, создающий диаграмму, а затем исследуем и изменим код этого макроса в редакторе Visual Basic.

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

• Запись процесса создания диаграммы.
• Исследование кода макроса.
• Изменение кода макроса.
• Усовершенствование процедуры построения диаграмм.

Создание диаграмм

Любой пользователь Excel знаком с процессом построения диаграмм. А если он знает, как построить диаграмму, то может и автоматизировать процесс построения диаграмм. "Как?", - спросите вы. Очень просто: запишите макрос, выполняющий весь процесс создания диаграммы, а затем усовершенствуйте код этого макроса.

Запись макроса построения диаграмм

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

Для чистоты эксперимента закройте все открытые рабочие книги и откройте новую рабочую книгу. В новой книге создайте таблицу данных, показанную на рис. 18.1. По этим данным построим круговую диаграмму.

Выделите диапазон А1:В6 и выполните команду Сервис > Макрос > Начать запись. В диалоговом окне Запись макроса введите имя макроса КруговаяДиаграмма, а в списке Сохранить в выберите пункт Эта книга. Теперь все готово к началу записи макроса. Щелкните на кнопке ОК.

Рис. 18.1. Таблица данных, на основе которой будет построена круговая диаграмма

Рис. 18.2 Диаграмма, созданная с помощью мастера диаграмм

Запустите мастера диаграмм и в первом диалоговом окне мастера выберите круговую диаграмму. Пропустите второе окно мастера, щелкнув на кнопке Далее. В третьем диалоговом окне мастера перейдите на вкладку Подписи данных и установите переключатель Доля. Щелкните на кнопке Далее. В последнем окне мастера укажите, что диаграмму следует поместить на существующем рабочем листе Лист1. Закройте диалоговое окно мастера, щелкнув на кнопке Готово.

На этом построение диаграммы не заканчивается. Теперь отформатируем заголовок диаграммы. Дважды щелкните на заголовке - откроется диалоговое окно Формат названия диаграммы. Прейдите на вкладку Шрифт, задайте полужирное курсивное начертание шрифта и установите размер шрифта 14 пунктов. Закройте диалоговое окно Формат названия диаграммы и остановите запись макроса. Построенная диаграмма показана на рис. 18.2.

Теперь исследуем код записанного макроса и посмотрим, как можно его усовершенствовать.

Код макроса

Нажав комбинацию клавиш <Alt+F11>, перейдите в редактор Visual Basic. В окне проектов откройте текущую рабочую книгу, а затем папку Modules (Модули), в которой найдете процедуру КруговаяДиаграмма. Полный код этой процедуры приведен в листинге 18.1.

Листинг 18.1. Процедура КруговаяДиаграмма

1: Sub КруговаяДиаграмма()

2:  Charts.Add

3:  ActiveChart.ChartType = xlPie

4:  ActiveChart.SetSourceData _

5:   Source:=Sheets("Лист1").Range("A1:B6"), PlotBy:=xlColumns

6:  ActiveChart.Location Where:=xlLocationAsObject, Name:="Лист1"

7:  ActiveChart.ApplyDataLabels _

     Type:=xlDataLabelsShowPercent, LegendKey:=False, _

8:   HasLeaderLines:=True

9:  ActiveChart.ChartTitle.Select

10:  Selection.AutoScaleFont = True

11:  With Selection.Font

12:   .Name = "Arial"

13:   .FontStyle = "полужирный курсив"

14:   .Size = 14

15:   .Strikethrough = False

16:   .Superscript = False

17:   .Subscript = False

18:   .OutlineFont = False

19:   .Shadow = False

20:   .Underline = xlUnderlineStyleNone

21:   .ColorIndex = xlAutomatic

22:   .BackGround = xlAutomatic

23:  End With

24: End Sub

Рассмотрим код этой процедуры. Оказывается, первым действием, выполненным при записи макроса, было применение метода Add (Добавить) к коллекции Charts (Диаграммы):

Charts.Add

Итак, мы узнали тип созданного объекта и элементов какой коллекции он является. Все остальное в этой процедуре - задание свойств этого объекта и применение к нему методов. ActiveChart (Активная диаграмма) является свойством объекта Application (Приложение), которое возвращает текущую диаграмму. В строке 3 свойству ChartType (Тип диаграммы) присвоено значение xlPie, что в Excel обозначает круговую диаграмму. В следующей строке кода с помощью метода SetSourceData (Установка источника данных) задается диапазон ячеек с данными, на основе которых строится диаграмма. Метод Location (Положение) помещает активную диаграмму в указанный рабочий лист (или на отдельный лист диаграмм, если необходимо). Наконец, метод ApplyDataLabels (Применить подписи данных) выполняет те установки, которые вы сделали в третьем диалоговом окне мастера диаграмм на вкладке Подписи данных.

Далее процедура устанавливает свойства еще одного объекта: объекта ChartTitle (Заголовок диаграммы). Сначала этот объект выбирается (строка 9), затем задаются параметры шрифта заголовка. Отметим, что, хотя на вкладке Шрифт диалогового окна Формат названия диаграммы мы только задали размер шрифта и его начертание, в процедуре представлены все опции этой вкладки (строки 12 - 22).

Изменение кода макроса

Выполним макрос еще раз.

1. Выделите ячейки А1:B3.
2. Выполните макрос КруговаяДиаграмма. Что получилось? Есть ли какие-нибудь отличия в последней диаграмме и предыдущей, когда выделялись шесть строк данных? Отличий нет, поэтому надо как-то изменить процедуру, чтобы она строила диаграмму на основании только тех данных, которые мы выделяем.
3. Удалите все диаграммы из рабочего листа.
4. Вернитесь к процедуре КруговаяДиаграмма. Нетрудно найти место в процедуре, которое надо как-то изменить, - это оператор, задающий источник данных для построения диаграммы:

ActiveChart.SetSourceData _

 Source:=Sheets("Лист1").Range("А1:В6"), _

 PlotBy:=xlColumns

5. Внесем изменения в процедуру. В верхней части процедуры введите две строки кода с объявлением и заданием новой переменной:

Dim rCurrentRange As Range

Set rCurrentRange = Selection

6. В операторе задания источника данных замените непосредственное указание диапазона ячеек Sheets("Лист1").Range("A1:B6") на переменную rCurrentRange:

ActiveChart.SetSourceData _

 Source:= rCurrentRange, PlotBy:=xlColumns

7. Вернитесь в рабочую книгу.
8. Выделите диапазон А1:B3. Выполните макрос КруговаяДиаграмма. В результате будет построена круговая диаграмма всего с двумя секторами (рис. 18.3).

Рис. 18.3. Теперь процедура стада более гибкой и может работать с любым диапазоном данных

Усовершенствованная процедура построения диаграмм

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

1. Измените заголовок столбца В - в ячейку В1 введите 1-й квартал.
2. Заполните шесть ячеек столбца С: в ячейку С1 введите 2-й квартал, в ячейку С2 введите число 1000, в ячейку C3 - 1150, в С4 - 875, в С5 - 1270 и в ячейку С6 - 1395.
3. Удалите из рабочего листа все диаграммы.
4. Выделите диапазон А1:С6 и выполните команду Сервис > Макрос > Начать запись. В диалоговом окне Запись макроса задайте имя макроса, например Гистограмма. Щелкните на кнопке ОК для начала записи макроса.
5. Щелкните на кнопке Мастер диаграмм стандартной панели инструментов. Появится первое окно мастера диаграмм.
6. Выберите тип диаграммы Гистограмма. Щелкните на кнопке Готово для создания диаграммы.
7. Остановите запись макроса.

Теперь у вас есть два макроса-процедуры для создания диаграмм: КруговаяДиаграмма и Гистограмма. Скомбинируем эти две процедуры в одну, которая бы строила диаграмму того или иного типа в зависимости от количества выделенных столбцов. Нажмите <Alt+F11> для перехода в редактор Visual Basic. Создайте новую процедуру с именем МояДиаграмма. Введите следующий код в эту процедуру:

Dim rCurrentRange As Range

Set rCurrentRange = Selection

If Selection.Columns.Count = 2 Then

Перейдите в процедуру КруговаяДиаграмма и скопируйте следующие строки кода:

Charts.Add

ActiveChart.ChartType = xlPie

ActiveChart.SetSourceData Source:= rCurrentRange, PlotBy:=xlColumns

ActiveChart.Location Where:=xlLocationAsObject, Name:="Лист1"

ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowPercent, _

 LegendKey:=False, HasLeaderLines:=True

He забывайте, что в редакторе Visual Basic для копирования, вырезания и вставки кода можно использовать стандартные комбинации клавиш: <Ctrl+C>, <Ctrl+X> и <Ctrl+V> соответственно.

Вставьте скопированные строки в процедуру МояДиаграмма после оператора If. После вставленных строк в новой строке введите код

ElseIf Selection.Columns.Count > 2 Then

Перейдите в процедуру Гистограмма и скопируйте оттуда строки

Charts.Add

ActiveChart.ChartType = xlColumnClustered

ActiveChart.SetSourceData Source:=Sheets("Лист1").Range("A1:C6")

ActiveChart.Location Where:=xlLocationAsObject, Name:="Лист1"

Вставьте эти строки в процедуру МояДиаграмма после оператора ElseIf. В операторе ActiveChart.SetSourceData замените Sheets("Лист1").Range("А1:С6") на rCurrentRange. Далее с новой строки введите следующее:

Else

 MsgBox "Выделенный диапазон не подходит для построения диаграммы"

 Exit Sub

End If

With ActiveChart

 .HasTitle = True

 .ChartTitle.Characters.Text = "Объемы продаж"

 .ChartTitle.Select

End With

Из процедуры КруговаяДиаграмма скопируйте следующий код:

Selection.AutoScaleFont = True

With Selection.Font

 .Name = "Arial"

 .FontStyle = "полужирный курсив"

 .Size = 14

 .Strikethrough = False

 .Superscript = False

 .Subscript = False

 .OutlineFont = False

 .Shadow = False

 .Underline = xlUnderlineStyleNone

 .ColorIndex = xlAutomatic

 .BackGround = xlAutomatic

End With

Вставьте этот код в процедуру МояДиаграмма после оператора End With. Полный код процедуры показан в листинге 18.2.

Листинг 18.2. Процедура МояДиаграмма

1: Sub МояДиаграмма()

2:  Dim rCurrentRange As Range

3:  Set rCurrentRange = Selection

4:

5:  If Selection.Columns.Count = 2 Then

6:   Charts.Add

7:   ActiveChart.ChartType = xlPie

8:   ActiveChart.SetSourceData Source:=rCurrentRange, _

9:    PlotBy:=xlColumns

10:   ActiveChart.Location Where:=xlLocationAsObject, Name:="Лист1"

11:   ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowPercent, _

12:    LegendKey:=False, HasLeaderLines:=True

13:  ElseIf Selection.Columns.Count > 2 Then

14:   Charts.Add

15:   ActiveChart.ChartType = xlColumnClustered

16:   ActiveChart.SetSourceData Source:=rCurrentRange

17:   ActiveChart.Location Where:=xlLocationAsObject, Name:="Лист1"

16:  Else

19:   MsgBox "Выделенный диапазон не подходит для построения _

20:    диаграммы"

21:   Exit Sub

22:  End If

23:

24:  With ActiveChart

25:   .HasTitle = True

26:   .ChartTitle.Characters.Text = "Объемы продаж"

27:   .ChartTitle.Select

28:  End With

29:

30:  Selection.AutoScaleFont = True

31:  With Selection.Font

32:   .Name = "Arial"

33:   .FontStyle = "полужирный курсив"

34:   .Size = 14

35:   .Strikethrough = False

36:   .Superscript = False

37:   .Subscript = False

38:   .OutlineFont = False

39:   .Shadow = False

40:   .Underline = xlUnderlineStyleNone

41:   .ColorIndex = xlAutomatic

42:   .Background = xlAutomatic

43:  End With

44:

45: End Sub

При создании этой процедуры мы использовали в основном копирование и вставку кода из ранее созданных макросов. "Изюминкой" этой процедуры является применение оператора If. Этот оператор проверяет значение свойства Columns.Count, равное количеству столбцов в выделенном диапазоне ячеек. Если это значение равно 2, то строится круговая диаграмма, если больше двух - строится гистограмма, если выделен один столбец, то выводится соответствующее окно сообщения. Работу этого оператора можно представить в виде следующей схемы:

If Selection.Columns.Count = 2 Then

 'код создания круговой диаграммы

Else IfSelection.Columns.Count > 2 Then

 'код создания гистограммы

Else

 MsgBox "Выделенный диапазон не подходит для построения диаграммы"

 Exit Sub

End If

После создания диаграммы процедура добавляет заголовок диаграммы и форматирует его. Для тестирования созданной процедуры перейдите в рабочую книгу и удалите все ранее созданные диаграммы. Выделите диапазон А1:С6 и выполните процедуру МояДиаграмма. Результат показан на рис. 18.4.

Рис. 18.4. Так как выделенный диапазон содержит более двух столбцов, то процедура построила гистограмму

Удалите гистограмму и выделите диапазон А1:В5. Снова выполните процедуру МояДиаграмма. Теперь будет создана круговая диаграмма. Сохраните рабочую книгу с именем Час18.

Резюме

Этот час был посвящен построению диаграмм. Вы узнали о коллекции Charts (Диаграммы) и объектах Chart, а также об их свойствах и методах.

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

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

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

Ответ. Если вы можете написать код такой процедуры с нуля, то такой вопрос не должен стоять перед вами: делайте так, как вам удобнее. Подход, представленный в этом часе, показывает простой путь создания сложных процедур и значительно экономит ваше время.

Вопрос. Нетрудно заметить, что в коде макросов много "лишних" операторов. Например, если в диалоговом окне задать какой-нибудь параметр, то в коде макроса записываются все опции этого окна. Можно ли избавиться от этих "липших" операторов?

Ответ. Действительно, код макросов не всегда эффективен. Но вы можете отредактировать этот код и удалить "лишнее". Но прежде чем удалять "ненужные" операторы, сделайте их комментариями (т.е. поставьте перед ними одинарную кавычку), а затем выполните макрос. Если в выполнении такого макроса вы не заметили никаких отклонений от "нормы", то только после этого удаляйте лишнее.

Практикум

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

Тесты

1. Какой оператор VBA используется для создания диаграммы?
2. Какое свойство объекта Application возвращает текущую активную диаграмму?
3. Какое свойство объекта Chart определяет тип создаваемой диаграммы?
4. Какой метод объекта Chart управляет данными, отображаемыми в диаграмме?
5. 5. Что из перечисленного не является свойством объекта Chart: ChartArea, ChartType, ChartLocation или ChartTitle?
6. Истинно или ложно следующее утверждение: процесс построения диаграмм можно записать с помощью макроса?
7. Свойство объекта Workbook возвращает активную диаграмму.

Упражнение

Создайте экранную форму с пятью переключателями (рис. 18.5):

• Круговая диаграмма
• Объемная круговая диаграмма
• Гистограмма
• Гистограмма с накоплением
• Объемная гистограмма с накоплением

Рис. 18.5. Экранная форма для упражнения

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

Совет: объявите переменную, содержащую выделенный диапазон, как глобальную (public) переменную.