Офисное программирование - Ирина Фризен

i = 1

Do

'Замена в введенной формуле аргумента х на ссылку $A1

If Mid(УрГрафика, i, 1) = «x» Or Mid(УрГрафика, i, 1) = «X» Then

n = Len(УрГрафика)

If (1 < i) And (i < n) Then

УрГрафика = Left(УрГрафика, i – 1) & «$A1» & Right(УрГрафика, n – i)

End If

If i = 1 Then

УрГрафика = «$A1» & Right(УрГрафика, n – 1)

End If

If i = n Then

УрГрафика = Left(УрГрафика, n – 1) & «$A1»

End If

End If

i = i + 1

Loop While i <= Len(УрГрафика)

ActiveSheet.Cells.Select

Selection.Clear

'Очистка на активном листе ранее введенных данных

ActiveSheet.Range(«A1»).Select

'Заполнение диапазонов значениями аргумента

With ActiveSheet

Range(«A1»).Value = х_нз 'Ввод в ячейку A1 начального значения

'Создание арифметической прогрессии по столбцу с указанным шагом и начальным значением

Range(«A1»).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=х_шаг, Stop:=х_пз, Trend:=False

End With

'Заполнение диапазона значениями функции

With ActiveSheet

nx = Range(«A1»).CurrentRegion.Rows.Count

'Определение числа строк в диапазоне заполнения

Range(«B1»).FormulaLocal = УрГрафика

'Ввод уравнения поверхности в ячейку B1

If IsError(Evaluate(УрГрафика)) = True Then

MsgBox «Ошибка в формуле», vbExclamation, «График»

Exit Sub

End If

'Заполнение диапазона Range(Cells(1, 2), Cells(nx, 2))

'начиная с ячейки B1, что эквивалентно протаскиванию маркера

'заполнения ячейки B1 на диапазон Range(Cells(1, 2),

Cells(nx, 2))

Range(«B1»).AutoFill Destination:=Range(Cells(1, 2), Cells(nx, 2)), Type:=xlFillDefault

End With

ActiveSheet.ChartObjects.Delete

'Удаление с рабочего листа всех ранее построенных диаграмм

ActiveSheet.Range(Cells(1, 2), Cells(nx, 2)).Select

'Выбор диапазона, по которому строится график

ActiveSheet.ChartObjects.Add(20, 19.5, 192, 192).Select

'Задание и выбор области на рабочем листе, где будет построен график,

'размер графика должен соответствовать размеру объекта Image1

Application.CutCopyMode = False

'Построение графика

ActiveChart.ChartWizard Source:=Range(Cells(1, 1), Cells(nx, 2)), Gallery:=xlLine, Format:=2, PlotBy:=xlColumns, CategoryLabels:=1, SeriesLabels:=0, HasLegend:=False, Title:="График",

CategoryTitle:="Аргумент", ValueTitle:="Функция y" & TextBox1.Text

ActiveSheet.ChartObjects(1).Activate

ActiveChart.Axes(xlValue).AxisTitle.Select

With Selection

HorizontalAlignment = xlCenter

VerticalAlignment = xlCenter

Orientation = xlUpward

End With

'Запись диаграммы в файл и загрузка картинки в Imagel

ActiveChart.Export Filename:="Graph.jpg",

FilterName:="JPEG"

UserForm1.Image1.Picture = LoadPicture(«graph.jpg»)

ActiveSheet.Range(«A1»).Select

End Sub

Private Sub CommandButton2 Click()

'Процедура закрытия диалогового окна

UserForm1.Hide

End Sub

Private Sub UserForm Initialize()

'Рисунок масштабируется с учетом относительных размеров

так, чтобы он помещался в объекте Imagel

With Imagel

PictureAlignment = fmPictureAlignmentTopLeft

PictureSizeMode = fmPictureSizeModeStretch

End With

End Sub

8.2. Построение круговых диаграмм и гистограмм

Пример 55. В диалоговое окно «Построение графика» (рис. 104) вводятся начальное, конечное значения аргумента и его шаг изменения. Уравнение графика жестко регламентировано. Программа сама переведет аргумент в ссылку на ячейку. После табуляции введенной функции программой и построения поверхности на рабочем листе этот график также отображается в объекте управления image, расположенном в диалоговом окне Построение графика (рис. 106, 107).

Пример 56. В диалоговое окно «Построение графика» (рис. 108) вводятся начальное, конечное значения аргумента и его шаг изменения. Уравнение графика вводится в текстовое окно. Программа сама переведет аргумент в ссылку на ячейку. Предусматривается выбор типа графика при построении графика функции. После табуляции введенной функции программой и построения поверхности на рабочем листе этот график также отображается в объекте управления image, расположенном в диалоговом окне Построение графика (рис. 108, 109, 110).

Рис. 106. Построение графика в диалоговом окне при выборе у=cos(x)

Рис. 107. Построение графика в диалоговом окне при выборе у=sin(x)

Рис. 108. Построение графика в диалоговом окне при выборе линейного типа

Рис. 109. Построение графика в диалоговом окне при выборе гистограммы

Рис. 110. Построение графика в диалоговом окне при выборе круговой диаграммы

Глава 9

Базы данных в Excel

9.1. Заполнение базы данных

При разработке приложений, кроме рассмотренных ранее элементов управления, можно использовать элемент «полоса прокрутки» и «счетчик». Рассмотрим эти элементы.

Элемент управления ScrollBar

(полоса прокрутки) и элемент управления SpinButton

(счетчик) по своим функциональным возможностям аналогичны друг другу. Поэтому свойства их рассматриваем совместно.

Value – возвращает текущее значение полосы прокрутки (только целые неотрицательные числа);

Min – минимальное значение полосы прокрутки (только целые неотрицательные числа);

Max – максимальное значение полосы прокрутки (только целые неотрицательные числа);

SmallChange – устанавливает шаг изменения значения при щелчке по одной из стрелок полосы прокрутки;

Enabled – допустимые значения: True (пользователь может изменить значение полосы) и False (в противном случае);

Visible – допустимые значения: True (полоса прокрутки отображается во время выполнения программы) и False (в противном случае).

При разработке формы будет необходимо выполнять программирование примечаний ячеек листа. Данная процедура выполняется с помощью метода AddComment объекта Range. Этот метод имеет следующий синтаксис:

AddComment (Text),

где Text – текст комментария.

Пример 57.[7] Для заполнения базы данных на рабочем листе excel с помощью редактора пользовательских форм создать диалоговое окно Регистрация клиентов (рис. 111).

С помощью этого диалогового окна вводятся данные в базу (таблицу базы), расположенную на рабочем листе Excel (рис. 112). Нажатие кнопки ОК должно приводить к добавлению новой записи в таблицу.

Рис. 111. Вид рабочей формы примера 57

Рис. 112. База данных о туристах на рабочем листе

Технология выполнения

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

UserForm_Initialize

1. Активизирует диалоговое окно.

2. Назначает клавише <Esc> функцию кнопки Отмена, а клавише <Enter> – Вычислить.

3. Назначает кнопкам Вычислить, Отмена и переключателям всплывающие подсказки.

4. Закрепляет первую строку так, чтобы она всегда отображалась на экране.

5. Создает заголовки полей базы данных, если они еще не были созданы.

6. Устанавливает начальное значение переключателя «0 программе».

7. Заполняет раскрывающийся список.

8. Устанавливает текст заголовка окна приложения.

CommandButton1_Click

1. Определяет номер первой пустой строки в базе данных о регистрации туристов, куда будет введена новая запись.

2. Считывает данные из диалогового окна.

3. Вводит их в первую пустую строку.

CommandButton2_Click

1. Закрывает диалоговое окно.

2. Устанавливает заголовок приложения, используемый по умолчанию, т. е. удаляет пользовательский заголовок приложения, созданный при активизации формы.

SpinButton1_ Change

Вводит значение в поле Продолжительность тура.

Private Sub CommandButton1_Click()

' Процедура считывания информации из диалогового окна

' и записи ее в базу данных на рабочем листе

' Смысл переменных однозначно определен их названиями

Dim Фамилия As String * 20

Dim Имя As String * 20

Dim Пол As String * 3

Dim ВыбранныйТур As String * 20

Dim Оплачено As String * 3

Dim Фото As String * 3

Dim Паспорт As String * 3

Dim Срок As String * 3

Dim НомерСтроки As Integer

'НомерСтроки – номер первой пустой строки рабочего листа НомерСтроки = Application.CountA(ActiveSheet.Columns(1)) + 1

'Считывание информации из диалогового окна в переменные With UserForm1

Фамилия =.TextBox1.Text

Имя =.TextBox2.Text

Срок =.TextBox3.Text

Пол = IIf(.OptionButton1.Value, «Муж», «Жен»)

Оплачено = IIf(.CheckBox1.Value, «Да», «Нет»)

Фото = IIf(.CheckBox2.Value, «Да», «Нет»)

Паспорт = IIf(.CheckBox3.Value, «Да», «Нет»)

ВыбранныйТур =.ComboBox1.List(.ComboBox1.ListIndex, 0)

End With

'Ввод данных в строку с номером НомерСтроки рабочего листа

With ActiveSheet

Cells(НомерСтроки, 1).Value = Фамилия

Cells(НомерСтроки, 2).Value = Имя

Cells(НомерСтроки, 3).Value = Пол

Cells(НомерСтроки, 4).Value = ВыбранныйТур

Cells(НомерСтроки, 5).Value = Оплачено

Cells(НомерСтроки, 6).Value = Фото

Cells(НомерСтроки, 7).Value = Паспорт

Cells(НомерСтроки, 8).Value = Срок

End With

End Sub

В приведенной процедуре для определения первой пустой строки в заполняемой базе данных о туристах используется инструкция

НомерСтроки = Application.CountA(ActiveSheet.Columns(1)) + 1,

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