Создание подписей к данным диаграммы
Опытным пользователям Excel известно, что порядок добавления подписей на диаграммы оставляет желать лучшего. В данном разделе мы рассмотрим прием, который позволяет быстро создать подписи к точкам диаграммы.
Диаграмма, а также исходные данные, на основании которых она построена, приведены на рис. 4.9.
Рис. 4.9. Диаграмма без подписей
Для управления отображением подписями к точкам диаграммы нужно в стандартном модуле редактора VBА написать следующий код (листинг 4.12).
Sub ShowLabels()
Dim rgLabels As Range ' Диапазон с подписями
Dim chrChart As Chart ' Диаграмма
Dim intPoint As Integer ' Точка, для которой добавляется
подпись
' Определение диаграммы
Set chrChart = ActiveSheet.ChartObjects(1).Chart
' Запрос на ввод диапазона с исходными данными
On Error Resume Next
Set rgLabels = Application.InputBox _
(prompt:="Укажите диапазон с подписями", Type:=8)
If rgLabels Is Nothing Then Exit Sub
On Error GoTo 0
' Добавление подписей
chrChart.SeriesCollection(1).ApplyDataLabels _
Type:=xlDataLabelsShowValue, _
AutoText:=True, _
LegendKey:=False
' Просмотр диапазона и назначение подписей
For intPoint = 1 To chrChart.SeriesCollection(1).Points.Count
chrChart.SeriesCollection(1). _
Points(intPoint).DataLabel.Text = rgLabels(intPoint)
Next intPoint
End Sub
Sub DeleteLabels()
' Удаление подписей диаграммы
ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1). _
HasDataLabels = False
End Sub
В результате написания данного кода будут созданы два макроса: ShowLabels (для включения подписей) и DeleteLabels (для их выключения). После выполнения макроса ShowLabels откроется диалоговое окно, в котором нужно указать диапазон исходных данных для создания подписей.
На рис. 4.10 показана диаграмма с подписями.
Рис. 4.10. Диаграмма с подписями
В данном случае в качестве исходных данных для создания подписей был использован диапазон А2:А9.
Глава 5Создание полезных программ
В данной главе приведено несколько конкретных примеров создания приложений для дальнейшего их использования в Microsoft Excel.
Программа для составления кроссвордов
В этом разделе мы рассмотрим, каким образом, используя механизм пользовательских форм и механизм макросов, можно создать программу для быстрого составления кроссвордов.
Написание макросов
В первую очередь нам необходимо создать рабочую книгу с листом. В стандартном модуле следует написать код, который приведен в листинге 5.1. В дальнейшем мы подробнее рассмотрим назначение макросов, которые будут созданы после написания кода.
Const dhcMinCol = 1 ' Номер первого столбца кроссворда
Const dhcMaxCol = 35 ' Номер последнего столбца кроссворда
Const dhcMinRow = 1 ' Номер первой строки кроссворда
Const dhcMaxRow = 35 ' Номер последней строки кроссворда
Sub Clear()
' Выделение и очистка всех используемых для кроссворда ячеек
Range(Cells(dhcMinRow, dhcMinCol), _
Cells(dhcMaxRow, dhcMaxCol)).Select
Selection.Clear
' Удаление сетки всего кроссворда
ClearGrid
Range(«A1»).Select
End Sub
Sub ClearGrid()
' Удаление сетки кроссворда (в выделенных ячейках)...
' Возврат прежнего цвета ячеек
Selection.Interior.ColorIndex = xlNone
' Задание начертания границ ячеек по умолчанию
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub
Sub DrowCrosswordGrid()
' Процедура начертания сетки кроссворда
' Задание цвета всех ячеек кроссворда
Selection.Interior.ColorIndex = 35
' Линии по диагонали не нужны
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
' Задание начертания границ всех диапазонов, входящих _
в выделение, а также границ между соседними ячейками _
всех диапазонов
On Error Resume Next
' Левые границы
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
' Правые границы
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
' Верхние границы
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
' Нижние границы
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
' Вертикальные границы между ячейками
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
' Горизонтальные границы между ячейками
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub
Sub DisplayGrid()
' Включение сетки на листе
ActiveWindow.DisplayGridlines = True
End Sub
Sub HideGrid()
' Выключение сетки на листе
ActiveWindow.DisplayGridlines = False
End Sub
Sub AutoNumber()
' Нумерация клеток, являющихся началом слов
Dim intRow As Integer ' Текущая строка
Dim intCol As Integer ' Текущий ряд
Dim cell As Range ' Текущая ячейка (с координатами _
(intRow, intCol))
Dim fTop As Boolean ' = True, если cell имеет соседей сверху
Dim fBottom As Boolean ' = True, если cell имеет соседей снизу
Dim fLeft As Boolean ' = True, если cell имеет соседей слева
Dim fRight As Boolean ' = True, если cell имеет соседей справа
Dim intDigit As Integer ' Текущий номер слова в кроссворде
intDigit = 1 ' Нумерация слов с 1
' Проходим по всем клеткам диапазона, используемого _
для кроссворда, сверху вниз слева направо и анализируем _
каждую угловую и крайнюю (левую и верхнюю) ячейки
For intRow = dhcMinRow To dhcMaxRow
For intCol = dhcMinCol To dhcMaxCol
' Текущая ячейка
Set cell = Cells(intRow, intCol)
' Проверка, входит ли ячейка в кроссворд (по ее цвету)
If cell.Interior.ColorIndex = 35 Then
fLeft = False
fRight = False
fTop = False
fBottom = False
On Error Resume Next
' Определение наличия соседей у ячейки...
' сверху
fTop = cell.Offset(-1, 0).Interior.ColorIndex = 35
' снизу
fBottom = cell.Offset(1, 0).Interior.ColorIndex = 35
' слева
fLeft = cell.Offset(0, -1).Interior.ColorIndex = 35
' справа
fRight = cell.Offset(0, 1).Interior.ColorIndex = 35
On Error GoTo 0
' Анализ положения ячейки
If (Not fTop And Not fLeft) Or _
(Not fBottom And Not fLeft And fRight) Or _
(Not fLeft And fRight) Or _
(Not fTop And fBottom) Then
' Ячейка подходит для начала слова
SetDigit intDigit, cell
intDigit = intDigit + 1
End If
End If
Next intCol
Next intRow
End Sub
Sub SetDigit(intDigit As Integer, cell As Range)
' Вставка цифры intDigit в ячейку, заданную параметром cell
cell.Value = intDigit
' Изменение настроек шрифта так, чтобы было похоже _
на настоящий кроссворд
' Маленький размер шрифта
cell.Font.Size = 6
' Выравнивание текста по левому верхнему углу ячейки
cell.HorizontalAlignment = xlLeft
cell.VerticalAlignment = xlTop
End Sub
Sub ToPrint()
' Удаление цветовой подсветки кроссворда
Cells.Interior.ColorIndex = xlNone
End Sub
Sub ToNumber()
' Закрытие первой формы и переход ко второй
UserForm1.Hide
UserForm2.Show
End Sub
Листинг 5.1 состоит из девяти макросов (семь первых можно запускать вручную):
• DrowCrosswordGrid – рисует сетку кроссворда для выделенных ячеек;
• Clear – удаляет кроссворд с рабочего листа;
• Clear Grid – удаляет рамку кроссворда в выделенных ячейках;
• AutoNumber – записывает номера в ячейки кроссворда;
• DisplayGrid – показывает сетку рабочего листа;
• Hide Grid – убирает сетку рабочего листа;
• ToPrint – удаляет цветовую подсветку ячеек кроссворда;
• SetDigit – помещает нужное число в указанную ячейку (этот макрос используется макросом AutoNumber для записи номеров в ячейки);
• ToNumber – переход от основной формы ко второй форме (см. ниже).
Вызывать все эти макросы вручную довольно неудобно. Их можно запускать посредством элементов управления, которые можно поместить прямо на рабочий лист или на пользовательскую форму. О создании пользовательской формы рассказывается в следующем подразделе.
Создание пользовательских форм
Для создания основной формы программы необходимо воспользоваться вкладкой Разработчик, отображение которой включается в настройках программы в разделе Основные с помощью флажка Показывать вкладку «Разработчик» на ленте. На данной вкладке нужно по обычным правилам создать форму, изображенную на рис. 5.1.
Рис. 5.1. Первая форма программы
К элементам формы привяжем макросы (все привязываемые макросы входят в состав кода, который приведен выше, и доступны в окне выбора макросов):
• переключатель Сетка присутствует– макрос DisplayGrid;
• переключатель Сетка на поле отсутствует – макрос HideGrid;
• кнопка Новый кроссворд – макрос Clear;
• кнопка Нарисовать рамку – макрос DrawCrasswordGrid;
• кнопка Стереть рамку – макрос ClearGrid;
• кнопка Дальше – макрос ToNumber.
Теперь аналогичным образом (с помощью вкладки Разработчик) создадим еще одну форму, которая показана на рис. 5.2.
К элементам данной формы привяжем следующие макросы (они также присутствуют в коде и доступны в окне выбора макросов):
• кнопка Автонумерация – макрос AutoNumber;
• кнопка Очистить все – макрос Clear;
• кнопка Вывести на печать – макрос ToPrint.
Следует отметить, что можно не создавать пользовательские формы, а размещать все элементы управления прямо на рабочем листе. Если так поступить, то кнопка Далее в первой (главной) форме становится ненужной.
Рис. 5.2. Вторая форма программы
Итак, у нас все готово для составления кроссвордов. О порядке использования программы рассказывается в следующем подразделе.
Порядок использования программы
С помощью созданной программы можно быстро составлять и нумеровать сетку кроссворда. Рассмотрим конкретный пример.
На листе создадим несколько выделенных областей, соединив их между собой (рис. 5.3).
Рис. 5.3. Выделение нескольких областей
Теперь нажмем кнопку Нарисовать рамку – результат представлен на рис. 5.4.
Рис. 5.4. Рамка кроссворда
Нажимаем кнопку Дальше – будет отображена вторая форма программы (см. рис. 5.2). В этой форме следует нажать кнопку Автонумерация – в результате сетка кроссворда будет быстро пронумерована (рис. 5.5).
Теперь нажимаем кнопку Вывести на печать – и на листе отобразится готовая сетка кроссворда (рис. 5.6).
С помощью кнопки Очистить все с листа удаляется рамка кроссворда.
Рис. 5.5. Нумерация сетки кроссворда
Кнопка Стереть рамку, которая находится на главной форме, позволяет удалить всю рамку (для этого ее нужно выделить) либо ее выделенный фрагмент.
При нажатии кнопки Новый кроссворд удаляется все содержимое документа, после чего можно приступать к составлению нового кроссворда.
Рис. 5.6. Готовая сетка кроссворда