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

Цель урока

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

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

Практика
Для заполнения базы данных на рабочем листе с помощью редактора пользовательских форм создадим диалоговое окно Регистрация туристов фирмы "Эх, прокачу!" ( 1).
При инициализации диалогового окна программа проверяет, есть ли заголовки у полей создаваемой базы данных о регистрации туристов. Если этих заголовков нет, то программа автоматически создает их, снабжая примечаниями, имеющими пояснительный текст о содержании полей ( 2). Выбор переключателя о программе приведет к отображению на экране текстового поля с пояснениями к данной программе. Снятие этого флажка удаляет данное поле ( 3). Обратите внимание, что у окна приложения пользовательское имя Регистрация. База данных туристов.
Обсудим, как приведенная ниже программа решает перечисленные задачи и что происходит в программе.

UserForm Initialize

  1. Активизирует диалоговое окно.
  2. Назначает клавише <Esc> функцию кнопки Отмена, а Клавише <Enter> - Вычислить.
  3. Назначает кнопкам вычислить, отмена и переключателю о программе всплывающие подсказки.
  4. Закрепляет первую строку так, чтобы она всегда отображалась на экране.
  5. Создает заголовки полей базы данных, если они еще не были созданы.
  6. Устанавливает начальное значение переключателя 0 программе.
  7. Заполняет раскрывающийся список.
  8. Устанавливает текст заголовка окна приложения.

Нажатие кнопки вычислить запускает на выполнение процедуру
CommandButton1_Click

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

Нажатие кнопки Отмена запускает на выполнение процедуру
CommandButton2_Click

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

SpinButtonl_Change

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

ToggleButtonl Click

Отображает текстовое поле в выбранном состоянии и удаляет его — в снятом состоянии.

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

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

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. Наберите в текстовом поле следующий текст:

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

  1. Выделите текстовое поле и смените цвет его заливки на желтый, нажав кнопку Цвет заливки (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
Из записанного макроса остается только скопировать нужные фрагменты в программу создаваемого приложения о базе данных регистрации туристов.
Самостоятельное задание
Разработать приложение с диалоговым окном регистрация клиентов отеля "хромая кобыла" ( 4), в котором:

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

 

 
На главную | Содержание | < Назад....Вперёд >
С вопросами и предложениями можно обращаться по nicivas@bk.ru. 2013 г. Яндекс.Метрика