Выполнение работы. Автоматизация заполнения базы данных в Microsoft Excel c помощью VBA.

Лабораторная работа №1

Автоматизация заполнения базы данных в Microsoft Excel c помощью VBA.

Цель работы: освоить в Microsoft Excel следующие приемы:

Повторить (изучить) в VBA EXCEL разработку приложения с использованием стандартных объектов VBA;

Последовательное заполнение плоской базы данных через пользовательскую форму.

Выполнение работы

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

Для заполнения базы данных на рабочем листе с помощью редактора VBA (вызывается командой Сервис/ Макрос/ Редактор VBA) создадим диалоговое окно регистрация туристов фирмы " Эх, прокачу'" (рис. 1).

Для этого:

– в редакторе VBA вставить объект UserForm1 (вызывается из строки меню Insert – команда UserForm).

– на объекте UserForm1 из панели элементов ToolBox (строки меню View – ToolBox) разместить следующие объекты так, как показано на рисунке 1: Label (надпись), TextBox (поле), SpinButton (Счетчик), ComboBox (поле со списком), CommandButton (кнопка) и Frame (фрейм или рамка). В одном из фреймов разместить объекты СheckBox (флажок), а во втором – объекты OptionButton (переключатель).

– для создания необходимых надписей на русском языке так, как показано на рисунке 1, можно воспользоваться свойством Caption, имеющемся у всех перечисленных выше объектов. Свойство Caption отображает текст, необходимый пользователю, в надписи объекта. Программа же обращается к объекту по его имени, отображаемом в свойстве Name.

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

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

3. Сохраним программу и запустим ее на выполнение

4. Заполним в Excel созданную базу данных пятью записями через пользовательскую форму.

5. Создадим кнопку «Вызов формы» в Excel для активизации пользовательской формы без загрузки редактора VBA. Для этого в меню Элементы управления найдем объект Кнопка, поместим его на активный рабочий лист, изменим надпись на кнопке на «Вызов формы» с помощью команды правой кнопки мыши Свойства – Сaption, дважды щелкнем левой клавишей мыши по Кнопке и перейдем в редактор VBA. В открывшемя листе кода запишем команду UserForm1.Show для отображения пользовательской формы, сохраним проект и запустим на выполнение.

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

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

ПРОГРАММНЫЙ КОД:

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

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 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 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

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

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

Range(" 2: 2").Select

ActiveWindow.FreezePanes = True

Range(" A2").Select

End Sub

Контрольные вопросы

1. Что такое плоская база данных?

2. Какие объекты VBA были использованы при разработке пользовательской формы?

3. Каковы основные свойства этих объектов?

4. Какие типы данных использованы при написании программного кода?

5. Как и для чего был использован объект Счетчик (SpinButton)?

6. Каково назначение в приложении объекта Поле со списком (ComboBox)? Какой командой Поле со списком заполняется данными в разработанном приложении?

7. В чем различие между инструкциями Exit Sub и End Sub?

8. Какая процедура переносит данные о туристах из пользовательской формы на рабочий лист Excel?