Урок 7

Тема:Заполнение базы данных

Цель урока

В этом уроке разрабатывается приложение, позволяющее при помощи диалогового окна заполнять базу данных. Конструируя данное приложение, вы сможете освоить следующие операции:

Последовательное заполнение плоской базы данных

Программирование примечаний

Программирование текстовых полей на рабочем листе

Использование переключателя и флажков

Создание пользовательского заголовка окна приложения

Как на рабочем листе программно закрепить область

Практика

Для заполнения базы данных на рабочем листе с помощью редактора пользовательских форм создадим диалоговое окно регистрация туристов фирмы "Эх, прокачу'" (рис. У7.1).

Рис. У7.1. Диалоговое окно Регистрация туристов фирмы "Эх, прокачу!"

При инициализации диалогового окна программа проверяет, есть ли заголовки у полей создаваемой базы данных о регистрации туристов. Если этих заголовков нет, то программа автоматически создает их, снабжая примечаниями, имеющими пояснительный текст о содержании полей. Выбор переключателя о программе приведет к отображению на экране текстового поля с пояснениями к данной программе. Снятие этого флажка удаляет данное поле , Обратите внимание, что у окна приложения пользовательское ИМЯ Регистрация. База данных туристов.

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

UserForm Initialize	1. Активизирует диалоговое окно. 2. Назначает клавише <Esc> функцию кнопки Отмена, а клавише <Enter> — Вычислить. 3. Назначает кнопкам Вычислить, Отмена и переключателю О программе всплывающие подсказки. 4. Закрепляет первую строку так, чтобы она всегда отображалась на экране. 5. Создает заголовки полей базы данных, если они еще не были созданы. 6. Устанавливает начальное значение переключателя О программе. 7. Заполняет раскрывающийся список. 8. Устанавливает текст заголовка окна приложения.
Нажатие кнопки Вычислить запускает на выполнение процедуру CommandButton1 Click	1. Определяет номер первой пустой строки в базе данных о регистрации туристов, куда будет введена новая запись. 2. Считывает данные из диалогового окна. 3. Вводит их в первую пустую строку.
Нажатие кнопки Отмена запускает на выполнение процедуру CommandButton2 Click	Закрывает диалоговое окно. Устанавливает заголовок приложения, используемый по умолчанию, т. е. удаляет пользовательский заголовок приложения, созданный при активизации формы.
SpinButtonI Change	Вводит значение в поле Продолжительность тура.
ToggleButton1 Click	Отображает текстовое поле в выбранном со- стоянии и удаляет его — в снятом состоянии. |
Заголовок Рабочего Листа	Создает заголовки полей базы данных о ре- гистрации туристов. Эти заголовки отобра- жаются с примечаниями.

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

If .OptionButton1.Value = True Then

Пол = "Муж"

Else

Пол = "Жен"

End If

If .CheckBox1.Value = True Then

Оплачено = "Да"

Else

Оплачено = "Нет"

End If

If .CheckBox2.Value = True Then

Фото = "Да"

Else

Фото = "Нет"

End If

If .CheckBox3.Value = True Then

Паспорт = "Да"

Else

Паспорт = "Нет"

End

If ВыбранныйТур = .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

Private Sub CommandButton2_Click()

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

' Установка заголовка окна приложения по умолчанию

UserForm1.Hide

Application. Caption == Empty

ActiveSheet.DrawingObjects.Delete

End Sub

Private Sub SpinButton1_Change()

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

With UserForm1

. TextBox3. Text = CStr (.SpinButton1. Value)

End With

End Sub

Private Sub TextBox3_Change()

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

With UserForm1

.SpinButton1.Value = CInt(.TextBox3.Text)

End With

End Sub

Private Sub ToggleButton1_Click()

' Процедура отображения или удаления поля с текстом

If ToggleButton1.Value = True Then

ActiveSheet.DrawingObjects.Delete

ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, _

11.25, 44.25, 106.5, 96#).Select

Selection.Characters.Text = ""

With Selection.Font

.Name = "Arial Cyr"

.FontStyle = "обычный"

.Size » 10

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = x1UnderlineStyleNone

.Colorlndex == x1Automatic

End With

Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13

Selection.ShapeRange.Fill.Visible = msoTrue

Selection.ShapeRange.Fill.Solid

Selection.Characters.Text = _

"Программа составлена " & Chr(1O) &

"Андреем Гарнаевым для регистрации " & 'Chr(1O) &

"клиентов" & Chr(1O) & "туристической " & Chr(1O) & "фирмы"

With Selection.Characters(Start:=1, Length:=86).Font

.Name = "Arial Cyr"

.FontStyle = "обычный"

.Size = 10

.Strikethrough == False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = x1UnderlineStyleNone

.ColorIndex = x1Automatic

End With

End If

If ToggleButton1.Value = False Then

ActiveSheet.DrawingObjects.Delete

End If

End Sub

Private Sub UserForm_Initialize()

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

' и задание элементов раскрывающегося списка

ЗаголовокРабочегоЛиста

' Задание пользовательского заголовка окна приложения

Application.Caption = "Регистрация. База данных туристов"

' Закрытие строки формул окна Excel

Application.DisplayFormulaBar = False

' Задание элементов раскрывающегося списка

With CommandButton1

.Default = True

.ControlTipText = "Ввод данных в базу данных"

End With

With CommandButton2

.Cancel = True

.ControlTipText = "Кнопка отмены"

End With

OptionButton1.Value = True

With ToggleButton1

.Value = False

.ControlTipText = "Информация о программе"

End With

With ComboBox1

.List = Array("Лондон", "Париж", "Берлин")

.ListIndex = 0

End With

' Активизация диалогового окна

UserForm1.Show

End Sub

Sub Заголовок Рабочего Листа()

' Процедура создания заголовков полей базы данных

' Если заголовки существуют, то досрочный выход из процедуры

If Range("A1").Value = "Фамилия" Then

Range("A2").Select

Exit Sub

End If

' Если заголовки не существуют, то создаются заголовки полей

ActiveSheet.Cells.Clear

Range("A1:H1").Value = Array("Фамилия", "Имя", "Пол", _

"Выбранный Тур", "Оплачено", "Фото", "Паспорт", "Срок") Range("А:А").ColumnWidth = 12

Range ("D:D") .ColumnWidth =14.4

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

' отображалась на экране

Range("2:2").Select

ActiveWindow.FreezePanes = True

Range("A2").Select

' К каждому заголовку поля базы данных

' присоединяется примечание

Range ("Al") . AddCoroment

Range("Al").Comment.Visible = False

Range("Al").Comment.Text Text:="Фамилия клиента"

Range("Bl").AddComment

Range("Bl").Comment.Visible = False

Range("Bl").Comment.Text Теxt:="Имя клиента"

Range("Cl").AddComment

RangeC'Cl") .Comment.Visible = False

Range("Cl").Comment.Text Теxt:="Пол клиента"

Range("PI").AddComment

Range("Dl").Comment.Visible = False

Range("Dl").Comment.Text Text:="Направление" & Chr(10) & _

"выбранного тура"

Range("El").AddComment

Range("El").Comment.Visible = False

Range("El").Comment.Text Text:="Путевка оплачена?" & Chr(10) &

"(Да/Нет)"

Range("Fl").AddComment

Range("Fl").Comment.Visible = False

Range("Fl").Comment.Text Тех1:="Фото сданы" & Chr(10) & _

"(Да/Нет)"

Range("Gl").AddComment

Range("Gl").Comment.Visible = False

Range("Gl").Comment.Text Text:="Наличие паспорта" & Chr(lO) &

"(Да/Нет)"

Range("HI").AddComment

Range("HI").Comment.Visible = False

Range("HI").Comment.Text Text:="Продолжительность" & Chr(10) & _

"поездки"

End Sub

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

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

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

Процедура Заголовок Рабочего Листа выглядит немножко устрашающе. При ее написании лучше всего воспользоваться MacroRecorder, который переведет производимые действия по созданию примечаний пользователем вручную на язык VBA. Итак, для активизации MacroRecorder выберите команду Сервис, Макрос, Начать запись (Tools, Macro, Record New Macro) и запустите MacroRecorder на запись. После задания всех параметров в появившемся диалоговом окне Запись макроса (Record Macro) и нажатия кнопки ОК появится плавающая панель инструментов с кнопкой Остановить запись (Stop Recording). Теперь все производимые действия будут записываться до тех пор, пока не будет нажата эта кнопка. Постройте примечания по следующему алгоритму. Кроме того, для того чтобы разобраться, как программируется закрепление области на рабочем листе, в этот алгоритм входит также и закрепление первой строки рабочего листа.

1. Щелкните кнопку заголовка второй строки. Вторая строка выделится. Выберите команду Окно, Закрепить области (Window, Fieeze Panes)

2. Выделите ячейку a1 и нажмите кнопку Надпись (Text box) панели инструментов Рисование (Drawing). В появившееся текстовое поле введите текст Фамилия клиента.

3 Выделите ячейку b1 и нажмите кнопку Надпись (Text Box) панели инструментов Рисование (Drawing). В появившееся текстовое поле введите текст Имя клиента и т. последовательно для ячеек от С1до Н1 .

Перечисленные выше действия будут переведены MacroRecorder в следующий макрос.

Sub Макрос1()

' Макрос1 Макрос

' Макрос записан 28.04.99 (Андрей)

Range("2:2").Select

ActiveWindow.FreezePanes == True

Range("A1").AddComment

Range("A1").Comment.Visible = False

Pange("A1").Comment.Text Техt:="Фамилия клиента"

Range("B1").AddComment

Range("B1").Comment.Visible = False

Range("B1")-Comment.Text Text:="Имя клиента"

Range("C1").AddComment

Range("C1").Comment.Visible = False

Range("C1").Comment.Text Техt:="Пол клиента"

Range("D1").AddComment

RangeC'D1").Comment.Visible = False

Range("D1").Comment.Text Техt:="Направление" & Chr(10) & _

"выбранного тура"

Range("E1").AddComment

Range("E1").Comment.Visible = False

Range("E1").Comment.Text Text:="Путевка оплачена?" & Chr(10) & _

"(Да/Нет)"

Range ("F1") . AddCornment

Range("F1").Comment.Visible = False

Range("F1").Comment.Text Text:="Фото сданы" & Chr(10) & _

"(Да/Нет)"

Range("G1").AddComment

Range("G1").Comment.Visible = False

Range("G1").Comment.Text Text:="Наличие паспорта" & Chr(10) & _

"(Да/Нет)"

Range("H1").AddComment

Range("H1").Comment.Visible = False

Range("H1").Comment.Text Text:="Продолжительность" & Chr(10) &

"поездки"

End Sub

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

Аналогично, при написании фрагмента программы, связанного с созданием текстового поля, лучше всего воспользоваться MacroRecorder, который переведет производимые пользователем вручную действия по созданию текстового поля на язык VBA. Итак, для активизации MacroRecorder выберите команду Сервис, Макрос, Начать запись (Tools, Macro, Record New Macro) и запустите MacroRecorder на запись. После задания всех параметров в появившемся диалоговом окне Запись макроса (Record Macro) и нажатия кнопки ОК появится плавающая панель инструментов с кнопкой Остановить запись (Stop Recording). Теперь все производимые действия будут записываться до тех пор, пока не будет нажата эта кнопка. Постройтe текстовое поле по алгоритму:

1 Нажмите кнопку Надпись (Text box) панели Рисование (Diawing) и создайте на рабочем листе текстовое поле

2 Наберите в текстовом поле следующий текст:

Программа составлена Андреем Гарнаевым для регистрации клиентов туристической фирмы.

3. Выделите текстовое поле и смените цвет его заливки на желтый, нажав кнопку Цвет заливки (Fill Color) панели инструментов Рисование (Drawing).

Перечисленные выше действия будут переведены MacroRecorder в следующий макрос.

Sub Макрос4()

' Макрос4 Макрос

ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal,

9.75, 45#, _ 108.75, 96#).Select

Selection.Characters.Text =

"Программа составлена " & Chr(10) &

"Андреем Гарнаевым для регистрации " & Chr(10) __

& "клиентов" & Chr(10) & "туристической " & Chr(10) & "фирмы"

With Selection.Characters(Start:=1, Length:=86).Font

.Name = "Arial Cyr"

.FontStyle = "обычный"

.Size = 10

.Strikethrough = False

.Superscript == False

.Subscript = False

.OutlineFont = False

.Shadow == False

.Underline = x1UnderlineStyleNone

.ColorIndex = x1Automatic

End With

Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13

Selection.ShapeRange.Fill.Visible = msoTrue

Selection.ShapeRange.Fill.Solid

End Sub

Из записанного макроса остается только скопировать нужные фрагменты в программу создаваемого приложения о базе данных регистрации туристов.

Самостоятельное задание

Разработать приложение с диалоговым окном Регистрация клиентов отеля "Хромая кобыла" , в котором:

Счетчик управляет вводом продолжительности проживания

В раскрывающемся списке выводятся три типа номеров: Одноместный, Двухместный, Люкс, стоимость проживания в которых равна 150, 100 и 250 руб. в сутки

Если постоялец заказывает завтраки в номер, то суточная оплата возрастает на 15 руб.

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

Рис. У7.2. Диалоговое окно Регистрация клиентов отеля

Другие варианты:

Разработать табличные базы данных:

1. Абитуриенты университета
2. Видеофильмы
3. Товары, поступающие на склад
4. Прием больных в поликлинике
5. Компьютерные игры
6. Клиенты банка
7. Продажа авиабилетов
8. Книги издательства и их авторы
9. Автомобильный магазин
10. Библиотека