Урок 7. Тема: Заполнение базы данных

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

• Последовательное заполнение плоской базы данных
  
• Программирование примечаний
  
• Программирование текстовых полей на рабочем листе
  
• Использование переключателя и флажков
  
• Создание пользовательского заголовка окна приложения
  
• Как на рабочем листе программно закрепить область
  

Практика

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

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

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

Рис. У7.3. Пояснительное текстовое поле, появляющееся при выборе переключателя О программе

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

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

Private Sub CommandButtonl_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 UserForml

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

Имя = .TextBox2.Text

Срок = .TextBox3.Text

If .OptionButtonl.Value = True Then

Пол = "Муж"

Else

Пол = "Жен"

End If

If .CheckBoxl.Value = True Then Оплачено = "Да"

Else

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

End If

If .CheckBox2.Value = True Then

Фото = "Да"

Else

Фото = "Нет"

End If

If .CheckBoxS.Value = True Then

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

Else

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

End If

ВыбранныйТур = .ComboBoxl.List(.ComboBoxl.Listlndex, 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 SpinButtonl_Change()

'

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

With UserForml

.TextBox3.Text = CStr(.SpinButtonl.Value) End With End Sub

Private Sub TextBox3_Change()

'

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

'

With UserForml

.SpinButtonl.Value = CInt(.TextBoxS.Text)

End With

End Sub

Private Sub ToggleButtonl_Click()

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

'

If ToggleButtonl.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 = xlUnderlineStyleNone

.Colorlndex = xlAutomatic End With

Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13

Selection.ShapeRange.Fill.Visible = msoTrue

Selection.ShapeRange.Fill.Solid 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 = xlUnderlineStyleNone

.Colorlndex = xlAutomatic

End With

End If

If ToggleButtonl.Value = False Then

ActiveSheet.DrawingObjects.Delete

End If

End Sub

'

Private Sub UserForm_Initialize()

'

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

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

'

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

'

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

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

'

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

Application.DisplayFormulaBar = False

'

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

'

With CommandButtonl

.Default = True

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

End With

With CommandButton2

.Cancel = True

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

End With

OptionButtonl.Value = True

With ToggleButtonl

.Value = False

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

End With

With ComboBoxl

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

.Listlndex = 0 End With

'

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

'

UserForml.Show

'

End Sub

'

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

'

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

'

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

'

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

Range("A2").Select

Exit Sub

End If

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

'

ActiveSheet.Cells.Clear

Range("Al:HI").Value = Array("Фамилия", "Имя", "Пол",

"Выбранный Тур", "Оплачено", "Фото", "Паспорт", "Срок")

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

'

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

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

Range("2:2").Select

ActiveWindow.FreezePanes = True

Range("A2").Select

'

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

'

Range("Al").AddComment

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

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

Range("Bl").AddComment

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

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

Range("Cl").AddComment

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

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

Range("Dl").AddCorament

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 Text:="OoTo сданы" & Chr(lO) &

"(Да/Нет) "

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

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

"(Да/Нет)"

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, Freeze Panes).
   
2. Выделите ячейку A1 и нажмите кнопку Надпись (Text box) панели инструментов Рисование (Drawing). В появившееся текстовое поле введите текст Фамилия клиента.
   
3. Выделите ячейку BI и нажмите кнопку Надпись (Text Box) панели инструментов Рисование (Drawing). В появившееся текстовое поле введите текст имя клиента и т. д. последовательно для ячеек от С1 до H1 .
   

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

Sub Макрос1()

'

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

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

'

Range("2:2").Select

ActiveWindow.FreezePanes = True

Range("Al").AddComment

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

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

Range("Bl").AddComment

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

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

Range("Cl").AddComment

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

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

Range("Dl").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").AddComnent

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

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

"(Да/Нет)"

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

Range("Gl").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). Теперь все производимые действия будут записываться до тех пор, пока не будет нажата эта кнопка. Постройте текстовое поле по алгоритму:

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

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

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

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

Sub Макрос4()

'

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

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

'

'

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 = xlUnderlineStyleNone,

.Colorlndex = xlAutomatic

End With

Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13

Selection.ShapeRange.Fill.Visible = msoTrue Selection.ShapeRange.Fill.Solid

End Sub

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

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

Разработать приложение с диалоговым окном регистрация клиентов отеля "хромая кобыла" (рис. У7.4), в котором:

• Счетчик управляет вводом продолжительности проживания
  
• В раскрывающемся списке выводятся три типа номеров: одноместный, Двухместный, Люкс, стоимость проживания в которых равна 150, 100 и 250 руб. в сутки
  
• Если постоялец заказывает завтраки в номер, то суточная оплата возрастает на 15 руб.
  
• При нажатии на кнопку ок в поле Стоимость проживания выводится суммарная стоимость проживания клиента, и все данные из диалогового окна должны вводится в базу данных, создаваемую на рабочем листе
  

Рис. У7.4. Диалоговое окно Регистрация клиентов отеля "Хромая кобыла"