Пример 53. Функция ddb() имеет пять аргументов, последний из которых является коэффициентом амортизации. Необходимо в форме предусмотреть новое текстовое поле, куда пользователь смог бы вводить коэффициент, а в коде нужно учесть присутствие этого коэффициента.
В программе необходимо построить обработчик ошибок. Необходимость его создания обусловлена следующим фактором: если весь период эксплуатации составляет, к примеру, 120 месяцев, то невозможно вычислить амортизацию за 121 и т. д. месяцы. Таким образом, при некорректном вводе периода расчета амортизации должно появляться окно вывода с сообщением об ошибке и должен быть осуществлен новый ввод неверного параметра.
Указание: выход из процедуры осуществляется командой Exit Sub.
Глава 8
Построение диаграмм средстами VBA
8.1. Построение гладких диаграмм
Диаграммы в ExcelДиаграммы можно размещать на рабочем листе. Для этого используется коллекция chartobjects. Ее элементы – объекты класса chartobject – являются контейнерами, содержащими объект Chart, задающий непосредственно диаграмму.
Методы объекта Chart
Рассмотрим основные методы, определяющие новое поведение объекта Chart:
SubChartWizard ([Source], [Gallery], [Format], [PlotBy], [CategoryLabels], [SeriesLabels], [HasLegend], [Title], [CategoryTitle], [ValueTitle], [Extra-Title])
Этот метод позволяет построить или модифицировать существующую диаграмму. В отличие от мастера диаграмм (ChartWizard), который вызывается при построении диаграммы вручную, метод не является интерактивным, более того, он не позволяет задать все возможные свойства. С его помощью можно выполнить основную работу, а детали строятся с использованием других свойств и методов объекта Chart. Все параметры метода являются необязательными:
1) Source – объект Range, содержащий исходные данные для построения новой диаграммы. Если параметр опущен, то метод позволяет отредактировать существующую диаграмму – либо выделенную диаграмму рабочего листа, либо диаграмму активного листа диаграмм;
2) Gallery – задает тип диаграммы и может быть одной из следующих констант: xlArea, xlBar, xlColumn, xlLine, xlPie, xlRadar, xlXY-Scatter, xlCombination, xl3DArea, xl3DBar, xl3DColumn, xl3DLine, xl3DPie, xl3DSurface, xlDoughnut, или xlDefaultAutoFormat;
3) Format – задает формат для данного типа диаграммы. Каждому типу диаграммы соответствует некоторое число возможных форматов. Параметр задает номер формата, по умолчанию выбирается первый формат данного типа;
4) PlotBy – соответствует терминальному свойству PlotBy;
5) CategoryLabels и SeriesLabels – целые, указывающие число строк или столбцов с метками категорий и рядов данных в области, заданной параметром Source. Указывать эти числа нужно на единицу меньше фактического значения;
6) остальные параметры позволяют добавить легенду, задать название диаграммы и ее осей – они совпадают с соответствующими терминальными свойствами.
Sub SetSourceData (source as range, [plotby]). Устанавливает источник данных диаграммы. Второй параметр соответствует терминальному свойству plotby.
SubApplyCustomType (ChartTypeAsXlChartType, [typename]). Метод позволяет модифицировать диаграмму, применив к ней новый тип – стандартный или настраиваемый. Если этот тип стандартный, то тогда первый параметр полностью его определяет. Его возможные значения совпадают со значениями соответствующего терминального свойства charttype. Если же тип настраиваемый, то первый параметр должен иметь одно из следующих значений: xlbuiltin, xluserdefined или xlanygallery. В этом случае второй параметр задает имя типа диаграммы.
Function Export(filename as string, [filtername], [interactive]) as boolean позволяет экспортировать диаграмму, преобразуя ее в графический формат. Первый параметр задает имя файла, в который будет записана диаграмма в графическом формате, второй – задает имя графического фильтра в том виде, как оно записано в системном регистре. Булев параметр interactive должен иметь значение true, если мы хотим вызвать диалоговое окно в процессе фильтрации. Функция export возвращает значение true в случае успешного завершения работы.
Sub GetChartElement(x as long, y as long, elementid as long, argl as long, arg2 as long). Представьте себе, что пользователь щелкнул кнопку мыши где-то над диаграммой. Обработав это событие, можно получить координаты курсора мыши – x и y. Если теперь вызвать метод getchartelement с этими координатами, то он вернет значение параметра elementid – идентификатор элемента диаграммы и значения двух параметров, связанных с этим элементом. Конечно, параметры зависят от типа того элемента, чьи координаты x и y заданы.
Function Location (where as xlchartlocation, [name]) as
Chart. Передвигает диаграмму в новое местоположение. Параметр Where имеет следующие значения: xlLocationAsNewSheet, xlLocationAsObject или xlLocationAutomatic.
В первом случае диаграмма помещается на новый лист диаграммы и параметр Name задает имя этого листа. Во втором случае диаграмма помещается как встроенный объект и Name задает имя рабочего листа.
Создание VBA-программыПример 54.[6] По введенным в диалоговое окно «Построение графика» (рис. 102) начальным, конечным значениям аргументов и их шагам изменения строится график. Уравнение графика также вводится в программу из диалогового окна. Уравнение должно быть составлено в соответствии с правилами, по которым строятся функции рабочего листа, но в качестве аргументов в нем следует использовать х вместо ссылки на ячейку. Программа сама переведет аргумент в ссылку на ячейку. После табуляции введенной функции программой и построения поверхности на рабочем листе (рис. 103) этот график также отображается в объекте управления Image, расположенном в диалоговом окне Построение графика (рис. 102).
Технология выполнения
Обсудим, как приведенная ниже программа решает описанную задачу и что происходит в ней.
Рис. 102. Диалоговое окно «Построение графика» в рабочем состоянии (пример 54)
Рис. 103. Результат построения графика на рабочем листе (пример 54)
UserForm_Initialize
1. Активизирует диалоговое окно.
2. Назначает клавише <Esc> функцию кнопки Отмена, а клавише <Enter> – построение.
3. Устанавливает, чтобы отображаемая картинка графика в диалоговом окне помещалась целиком и пропорционально в пределах элемента управления Image, а также чтобы левый верхний угол рисунка совпадал с левым верхним углом элемента управления Image.
Нажатие кнопки Построение запускает на выполнение процедуру CommandButton1__C1ick
1. Проверяет, являются ли вводимые данные числами. В случае ошибки отображается соответствующее сообщение.
2. Проверяет согласованность вводимых данных. В случае ошибки отображается соответствующее сообщение (рис. 104).
3. Преобразует формулу, введенную в поле Уравнение графика, в формулу рабочего листа.
4. Проверяет корректность введенной формулы. В случае ошибки отображается соответствующее сообщение (рис. 105).
5. Используя метод DataSeries, начиная с ячейки А2, строит вниз по столбцу арифметическую прогрессию, являющуюся результатом табуляции аргумента х уравнения графика с указанными шагами (рисунок из файла graph.jpg в элементе управления Image1).
Рис. 104. Пример сообщения о несогласованности данных
Рис. 105. Сообщение о некорректном вводе формулы
Нажатие кнопки Отмена запускает на выполнение процедуру CommandButton2_C1ick
Закрывает диалоговое окно.
Рассмотрим листинг данного приложения.
Private Sub CommandButton1_Click()
' Процедура табуляции функции
Dim х_нз As Double
Dim х_пз As Double
Dim х_шаг As Double
Dim УрГрафика As String
Dim nx As Integer
'nx – число протабулированных значений аргумента х
Dim n As Integer
Dim i As Integer
'n,i – вспомогательные целые переменные
'Проверка корректности ввода данных
If IsNumeric(TextBox2.Text) = False Then
MsgBox «Ошибка в начальном значении х», vbInformation, «График»
TextBox2.SetFocus
Exit Sub
End If
If IsNumeric(TextBox3.Text) = False Then
MsgBox «Ошибка в шаге х», vbInformation, «График»
TextBox3.SetFocus
Exit Sub
End If
If IsNumeric(TextBox4.Text) = False Then
MsgBox «Ошибка в конечном значении у», vbInformation, «График»
TextBox4.SetFocus
Exit Sub
End If
'Считывание с диалогового окна значений переменных
х_нз = CDbl(TextBox2.Text)
х_шаг = CDbl(TextBox3.Text)
х_пз = CDbl(TextBox4.Text)
УрГрафика = Trim(TextBox1.Text)
'Проверка согласованности введенных данных
If х_нз >= х_пз Then
MsgBox «Начальное значение х слишком большое», vbInformation, «График»
TextBox2.SetFocus
Exit Sub
End If
If х_нз + х_шаг >= х_пз Then
MsgBox «Шаг х великоват», vbInformation, «График»
TextBox3.SetFocus
Exit Sub
End If
'Замена в введенной формуле аргумента х на ссылку $A1
i = 1
Do
'Замена в введенной формуле аргумента х на ссылку $A1
If Mid(УрГрафика, i, 1) = «x» Or Mid(УрГрафика, i, 1) = «X» Then
