Телефонный справочник

Томский межвузовский центр дистанционного образования

Томский государственный университет

систем управления и радиоэлектроники (ТУСУР)

Кафедра автоматизации обработки информации.

Курсовая работа.

по дисциплине «Компьютерная подготовка»

Выполнил:

студент ТМЦДО

гр.: з-472-27б

специальности 061000

Маркелова А.А.

5 .01.2005г

г. Абакан.

Задание.

Вариант 9

Написать программу, которая формирует базу «Телефонный справочник», содержащую следующие сведения: ФИО, адрес, телефон. Просмотр базы и обработка должна выполняться в программе Excel.

Программа должна удовлетворять следующим требованиям:

1. Приложение выполнить с использованием языка программирования VBA для MS Excel.

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

3. Проект должен обеспечивать следующие режимы работы:

• ввод исходных данных и формирование базы;

• корректировка данных (исправление, добавление, удаление);

• работа с данными (поиск, сортировка, просмотр);

• формирование статистики:

  1. общее количество абонентов телефонной сети;

  2. количество телефонов на указанной улице;

  3. количество телефонов в указанном доме.

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

5. На экране разместить одну панель инструментов, обеспечивающую работу проекта. Остальные панели скрыть.

6. Данные проекта сохранять в файле.

7. При вводе числовых данных обеспечить обработку ошибок некорректного ввода.

Содержание

Введение

Цель работы: закрепление знаний, полученных при изучении курса «Компьютерная подготовка», а также приобретение новых навыков создания завершенных программных приложений для MS Excel.

В дополнение к огромным возможностям MS Excel, входящего в состав Microsoft Office квалифицированному пользователю доступен полноценный язык программирования Visual Basic for Applications (VBA), позволяющий оживить страницы электронных таблиц, превратив их в сложное Windows – приложение, осуществляющее многогранную обработку данных под управлением оператора. При этом, большинство часто используемых операций программируется заранее, что упрощает работу конечного пользователя и значительно снижает вероятность возникновения ошибки как на этапе ввода исходных данных, так и при их последующей обработке.

В настоящей работе используются наиболее часто употребляемые элементы проектирования приложений для MS Excel. В том числе: процедуры (подпрограммы и функции), модули и формы. В коде VBA применялись различные типы данных (включая определенные пользователям описания типов), многоуровневые условные операторы и операторы циклов. Для реализации хранения информации во внешнем файле использованы встроенные функции работы с файлами (последовательный доступ).

Диалог с пользователем реализован при помощи функций ввода/вывода информации InputBox и MsgBox, а также ряда специально созданных форм. В указанных формах применены элементы управления типа: надпись, поле, рамка, переключатель, кнопка.

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

MS Excel имеет богатую объектную модель. При решении поставленной задачи использовались объекты следующих типов: Application, Workbook, Worksheet, Range, CommandBar, а также коллекции Workbooks и Worksheets.

Работа выполнена в среде Microsoft Excel 2002.

Структура программного комплекса

Состав программного комплекса

Программный комплекс «Телефонный справочник» состоит из двух файлов, располагаемых в каталоге “c:\tmp\”:

• "Телефонный справочник.xls" – основной файл, содержащий таблицу просмотра базы данных и инструменты, необходимые для работы с ней.

• "phones.db" – вспомогательный текстовый файл, используемый для хранения базы данных.

Работа программы осуществляется под управлением Microsoft Excel, входящего в состав пакета Microsoft Office, поэтому для ее использования необходимо наличие указанного пакета на клиентском компьютере.

Иерархия объектов

Описываемое Excel-приложение в своем составе содержит:

1. Рабочую книгу Workbook «Телефонный справочник» + программный код VBA, состоящую из двух листов:

   1. Worksheet Лист1 (Старт) + программный код VBA

   2. Worksheet Лист2 (База данных) + программный код VBA

2. Пять форм + программный код VBA:

   1. addRowForm – для режима добавления новой записи

   2. delRowForm – для режима удаления записи

   3. editRowForm – для режима редактирования записи

   4. reportForm – для режима формирования статистики

   5. sortForm – для режима сортировки базы данных

3. Модуль Module1, содержащий описание типа данных Record для одной записи об абоненте, а также функции работы (чтения/записи) с такими данными и внешним файлом.

4. Панель инструментов “Phones”, обеспечивающую работу программы и состоящую из девяти кнопок:

   1. чтение базы данных;

   2. запись базы данных;

   3. добавление записи;

   4. корректировка записи;

   5. удаление записи;

   6. поиск;

   7. сортировка;

   8. отчет (статистика);

   9. выход из программы.

Руководство пользователя

Запуск программы

Для запуска программы необходимо в MS Excel открыть книгу:

“Телефонный справочник.xls”

Вы увидите один лист с рекламной надписью и двумя кнопками. Для отображения листа с базой данных нажмите на кнопку «Начать работу».

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

Чтение / запись базы данных

Поскольку вся информация хранится во внешнем файле, то для загрузки базы данных на лист Excel таблицы необходимо на панели инструментов нажать кнопку «Открыть базу данных». При этом существующие на листе данные будут заменены информацией из внешнего файла “phones.db”.

Для сохранения результатов работы с базой данных нажмите кнопку «Сохранить базу данных» на панели инструментов. Вся текущая информация, отображаемая на листе, будет записана во внешний файл “phones.db”.

Режимы корректировки данных

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

При нажатии на любую из них, будет предложено диалоговое окно, в котором в соответствующие поля нужно занести новую (откорректировать существующую) информацию и нажать кнопку подтверждения операции.

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

Внимание. В режиме добавления/корректировки записи обязательны для заполнения поля: Фамилия, Имя, Отчество, Улица, Дом, Телефон. Кроме того, в поле Телефон допускается ввод только числовых данных (не более 10 цифр).

Поиск информации

Для поиска нужной информации нажмите кнопку «Найти» на панели инструментов. Будет запущен стандартный механизм поиска информации по листу MS Excel.

Режимы сортировки

В программе предусмотрено три режима сортировки данных:

• По абоненту (фамилия + имя + отчество);

• По адресу (улица + дом + квартира);

• По телефону.

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

Формирование статистики

Согласно заданию программа позволяет рассчитать следующую статистику:

• Общее количество абонентов телефонной сети;

• Количество телефонов на указанной улице;

• Количество телефонов в указанном доме.

Войдите в режим «Отчет», выберите требуемый отчет и, при необходимости, задайте параметры его формирования. Количество абонентов по заданным реквизитам будет посчитано и отображено в диалоговом окне.

Завершение работы с программой

Для завершения работы с программой нажмите кнопку «Выход» на панели инструментов. Лист с базой данной будет скрыт, а появится лист с рекламной заставкой. Для подтверждения выхода повторно выберите кнопку «Выход». Если «Телефонный справочник» был единственной открытой книгой, приложение MS Excel будет полностью закрыто, в противном случае – закроется только книга с описываемой программой.

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

Дополнение

Все стандартные панели инструментов скрываются и восстанавливаются при открытии / закрытии книги «Телефонный справочник», а также при переключении между окнами. Во избежание проблем с восстановлением стандартного набора панелей инструментов не рекомендуется самостоятельно менять набор отображаемых панелей инструментов пока описываемая книга остается открыта.

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

Заключение

В ходе выполнения работы были закреплены знания по работе в MS Excel и основам программирования на VBA, а также приобретены практические навыки создания завершенных программных приложений для MS Excel.

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

Разумеется, выполненный проект не является завершенным в полной мере. В качестве направлений для развития проекта можно упомянуть, например, более конкретизированный механизм поиска информации или реализация оптимальных методов сортировки (что может быть более эффективным на больших объемах информации).

Список литературы

1. Потахова И.В. Компьютерная подготовка. Офисное программирование: Учебное пособие. – Томск: Томский межвузовский центр дистанционного образования, 2004. – 181с.

2. Справочноеруководствопо MS Excel и Visual Basic for Applications: Microsoft Corp., 2001.

3. Демидова Л.А., Пылькин А.Н. Программирование в среде Visual Basic for Applications: Практикум – М.: Горячая линия – Телеком, 2004. – 175с.

Приложение. Листинг программ VBA

Рабочаякнига

Dim oldBars(20) As Long, kol As Integer PrivateSub Workbook_Activate() kol = 0Dim bar As CommandBar ForEach bar In Application.CommandBars If bar.Visible AndNot (bar.Protection = msoBarNoChangeVisible) _ And (bar.Type = msoBarTypeNormal) AndNot (bar.Name = "Phones") Then kol = kol + 1 oldBars(kol) = bar.index EndIfNext bar For i = 1To kol Application.CommandBars(oldBars(i)).Visible = FalseNext If ThisWorkbook.ActiveSheet.Name = "Базаданных"Then showTools EndIfEndSub PrivateSub Workbook_Deactivate() Dim i As Integer For i = kol To1Step -1 Application.CommandBars(oldBars(i)).Visible = TrueNext hideTools EndSub PrivateSub Workbook_Open() ThisWorkbook.Worksheets("Старт").Visible = True' спрятатьстартовыйлист ThisWorkbook.Worksheets("Старт").Activate ' сделатьактивнымлистсБД ThisWorkbook.Worksheets("Базаданных").Visible = False' показатьбазуданныхEndSub

Лист1 (Старт)

PrivateSub ExitButton_Click() ExitProject EndSub PrivateSub StartButton_Click() 'Commandbars ThisWorkbook.Worksheets("Базаданных").Visible = True' показатьбазуданных ThisWorkbook.Worksheets("Базаданных").Activate ' сделатьактивнымлистсБД ThisWorkbook.Worksheets("Старт").Visible = False' спрятатьстартовыйлистEndSub

Лист2 (Базаданных)

PrivateSub Worksheet_Activate() showTools EndSub PrivateSub Worksheet_Deactivate() hideTools EndSub Sub addRecord() If (ActiveCell.row < 5) Or (Len(ActiveCell.EntireRow.Cells(, 1).Value) = 0) Then Range("A5").Activate EndIf ThisWorkbook.ActiveSheet.Unprotect addRowForm.Show vbModal ThisWorkbook.ActiveSheet.Protect EndSub Sub delRecord() If (ActiveCell.row < 5) Or (Len(ActiveCell.EntireRow.Cells(, 1).Value) = 0) ThenExitSubEndIf ThisWorkbook.ActiveSheet.Unprotect If Selection.Rows.count = 1Then delRowForm.Show vbModal ElseDim response response = MsgBox("Отмеченозаписей: " + Str(Selection.Rows.count) + Chr(13) + "Удалитьвсе?", vbYesNoCancel, "Внимание!") If response = vbYes Then Selection.EntireRow.Delete EndIfEndIf ThisWorkbook.ActiveSheet.Protect EndSub Sub editRecord() If (ActiveCell.row < 5) Or (Len(ActiveCell.EntireRow.Cells(, 1).Value) = 0) ThenExitSubEndIf ThisWorkbook.ActiveSheet.Unprotect editRowForm.Show vbModal ThisWorkbook.ActiveSheet.Protect EndSub Sub sort() ThisWorkbook.ActiveSheet.Unprotect sortForm.Show vbModal ThisWorkbook.ActiveSheet.Protect EndSub Sub report() Dim oldCell As Range ThisWorkbook.ActiveSheet.Unprotect Set oldCell = ActiveCell reportForm.Show vbModal oldCell.Activate ThisWorkbook.ActiveSheet.Protect EndSub

addRowForm

PrivateSub UserForm_Activate() FamBox.Value = "" ImBox.Value = "" OtBox.Value = "" StreetBox.Value = "" NoBox.Value = "" FlatBox.Value = "" PhoneBox.Value = "" FamBox.SetFocus EndSub PrivateSub CancelButton_Click() addRowForm.Hide EndSub PrivateSub OKButton_Click() ' проверкаинформацииDim box As Variant, boxes As Variant boxes = Array(FamBox, ImBox, OtBox, StreetBox, NoBox, PhoneBox) ForEach box In boxes IfLen(Trim(box.Value)) = 0Then box.SetFocus ExitSubEndIfNext box IfLen(Trim(PhoneBox.Value)) > 10Then MsgBox "Более 10 цифрвномеретелефона" PhoneBox.SetFocus Else' заполнениезаписиизформыDim myRecord As Record myRecord.Fam = FamBox.Value myRecord.Im = ImBox.Value myRecord.Ot = OtBox.Value myRecord.street = StreetBox.Value myRecord.no = NoBox.Value myRecord.Flat = FlatBox.Value myRecord.Phone = Val(PhoneBox.Value) ' добавлениестрокиналистиеезаполнение ActiveCell.EntireRow.Insert putRecord ActiveCell.EntireRow, myRecord ' скрытиеформы addRowForm.Hide EndIfEndSub PrivateSub PhoneBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If (KeyAscii < Asc("0")) Or (KeyAscii > Asc("9")) Then MsgBox "Допускаетсявводтолькоцифр!" KeyAscii.Value = 0EndIf

EndSub

delRowForm

PrivateSub CancelButton_Click() delRowForm.Hide EndSub PrivateSub OKButton_Click() ' удалениетекущейстроки ActiveCell.EntireRow.Delete ' скрытиеформы delRowForm.Hide EndSub PrivateSub UserForm_Activate() Dim myRecord As Record myRecord = getRecord(ActiveCell.EntireRow) FamBox.Value = myRecord.Fam ImBox.Value = myRecord.Im OtBox.Value = myRecord.Ot StreetBox.Value = myRecord.street NoBox.Value = myRecord.no FlatBox.Value = myRecord.Flat PhoneBox.Value = myRecord.Phone OKButton.SetFocus EndSub

editRowForm

PrivateSub UserForm_Activate() Dim myRecord As Record myRecord = getRecord(ActiveCell.EntireRow) FamBox.Value = myRecord.Fam ImBox.Value = myRecord.Im OtBox.Value = myRecord.Ot StreetBox.Value = myRecord.street NoBox.Value = myRecord.no FlatBox.Value = myRecord.Flat PhoneBox.Value = myRecord.Phone FamBox.SetFocus EndSub PrivateSub CancelButton_Click() editRowForm.Hide EndSub PrivateSub OKButton_Click() ' проверкаинформацииDim box As Variant, boxes As Variant boxes = Array(FamBox, ImBox, OtBox, StreetBox, NoBox, PhoneBox) ForEach box In boxes IfLen(Trim(box.Value)) = 0Then box.SetFocus ExitSubEndIfNext box IfLen(Trim(PhoneBox.Value)) > 10Then MsgBox "Более 10 цифрвномеретелефона" PhoneBox.SetFocus Else' заполнениезаписиизформыDim myRecord As Record myRecord.Fam = FamBox.Value myRecord.Im = ImBox.Value myRecord.Ot = OtBox.Value myRecord.street = StreetBox.Value myRecord.no = NoBox.Value myRecord.Flat = FlatBox.Value myRecord.Phone = Val(PhoneBox.Value) ' добавлениестрокиналистиеезаполнение putRecord ActiveCell.EntireRow, myRecord ' скрытиеформы editRowForm.Hide EndIfEndSub PrivateSub PhoneBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If (KeyAscii < Asc("0")) Or (KeyAscii > Asc("9")) Then MsgBox "Допускаетсявводтолькоцифр!" KeyAscii.Value = 0EndIfEndSub

reportForm

PrivateSub UserForm_Activate() AllOption.Value = True OKButton.Caption = "Расчет" OKButton.SetFocus EndSub PrivateSub AllOption_Click() OKButton.Caption = "Расчет"EndSub PrivateSub StreetOption_Click() OKButton.Caption = "Параметры..."EndSub PrivateSub HouseOption_Click() OKButton.Caption = "Параметры..."EndSub PrivateSub CancelButton_Click() reportForm.Hide EndSub PrivateSub OKButton_Click() Dim myRecord As Record Dim counter As Long Dim street AsString, no AsString, title AsStringIf AllOption.Value Then counter = count() MsgBox "Общееколичествоабонентов: " + Str(counter) Else myRecord = getRecord(ActiveCell.EntireRow) If StreetOption.Value Then title = "Отчетпоулице" street = InputBox("Задайтенаименованиеулицы:", title, myRecord.street) IfLen(street) > 0Then street = Trim(street) counter = count(street) MsgBox "Количествотелефоновнаулице '" + street + "': " + Str(counter) EndIfElse title = "Отчетподому" street = InputBox("Задайтенаименованиеулицы:", title, myRecord.street) IfLen(street) > 0Then street = Trim(street) no = InputBox("Улица '" + street + "'" + Chr(10) + "Задайтеномердома:", title, myRecord.no) IfLen(no) > 0Then no = Trim(no) counter = count(street, no) MsgBox "Количествотелефоноввдоме '" + street + " " + no + "': " + Str(counter) EndIfEndIfEndIfEndIf reportForm.Hide EndSub PrivateFunction count(Optional street, Optional no) As Long Dim myRecord As Record Dim data As Range, curRow As Range Dim doCalc As Boolean, counter As Long counter = 0 Range("A5").Activate Set data = ActiveCell.CurrentRegion ForEach curRow In data.Rows myRecord = getRecord(curRow) doCalc = False If IsMissing(street) Then' все абоненты doCalc = TrueElseIf IsMissing(no) Then' по улице doCalc = (Trim(myRecord.street) = street) Else' по дому doCalc = (Trim(myRecord.street) = street) And (Trim(myRecord.no) = no) EndIfEndIf If doCalc Then counter = counter + 1 Next curRow count = counter EndFunction

sortForm

PrivateSub UserForm_Activate() OKButton.SetFocus EndSub PrivateSub CancelButton_Click() sortForm.Hide EndSub PrivateSub OKButton_Click() Dim sht As Worksheet Dim rng As Range Set sht = ThisWorkbook.ActiveSheet Set rng = sht.Range(sht.Cells(5, 1), sht.Cells(65536, 1).End(xlUp).Offset(, 7)) If NameOption.Value Then' сортировать по ФИО rng.sort Key1:=sht.Columns("A"), Order1:=xlAscending, Key2:=sht.Columns("B"), Order2:=xlAscending, Key3:=sht.Columns("C"), Order3:=xlAscending, Header:=xlNo ElseIf AddressOption.Value Then' сортировать по адресу rng.sort Key1:=sht.Columns("D"), Order1:=xlAscending, Key2:=sht.Columns("E"), Order2:=xlAscending, Key3:=sht.Columns("F"), Order3:=xlAscending, Header:=xlNo Else' сортировать по телефону rng.sort Key1:=sht.Columns("G"), Order1:=xlAscending, Header:=xlNo EndIfEndIf sortForm.Hide EndSub

Module1

PublicType Record Fam AsString Im AsString Ot AsString street AsString no AsString Flat AsString Phone As Long EndType PublicFunction dbFileName() AsString dbFileName = ThisWorkbook.Path + "\phones.db"EndFunction Sub ToolbarExitButton() If ThisWorkbook.ActiveSheet.Name = "Старт"Then ExitProject Else ThisWorkbook.Worksheets("Старт").Visible = True' спрятать стартовый лист ThisWorkbook.Worksheets("Старт").Activate ' сделать активным лист с БД ThisWorkbook.Worksheets("Базаданных").Visible = False' показать базу данныхEndIfEndSub Sub ExitProject() ThisWorkbook.Saved = TrueIf Application.Workbooks.count = 1Then Application.Quit 'завершить работу ExcelElse ThisWorkbook.Close'завершить работу проектаEndIfEndSub Sub dbRead() ThisWorkbook.ActiveSheet.Unprotect Dim myRecord As Record Dim data As Range, curRow As Range Dim row As Integer Range("A5").Activate Set data = ActiveCell.CurrentRegion data.ClearContents Open dbFileName ForInputAs #1 row = 1DoWhileNot EOF(1) Input #1, myRecord.Fam, myRecord.Im, myRecord.Ot, myRecord.street, myRecord.no, myRecord.Flat, myRecord.Phone putRecord ActiveCell.Cells(row), myRecord row = row + 1LoopClose #1 ThisWorkbook.ActiveSheet.Protect EndSub Sub dbWrite() ThisWorkbook.ActiveSheet.Unprotect Dim myRecord As Record Dim data As Range, curRow As Range Range("A5").Activate Set data = ActiveCell.CurrentRegion Open dbFileName For Output As #1ForEach curRow In data.Rows myRecord = getRecord(curRow) Write #1, myRecord.Fam, myRecord.Im, myRecord.Ot, myRecord.street, myRecord.no, myRecord.Flat, myRecord.Phone Next curRow Close #1 ThisWorkbook.ActiveSheet.Protect EndSub Function getRecord(row As Range) As Record Dim myRecord As Record myRecord.Fam = row.Cells(, 1).Value myRecord.Im = row.Cells(, 2).Value myRecord.Ot = row.Cells(, 3).Value myRecord.street = row.Cells(, 4).Value myRecord.no = row.Cells(, 5).Value myRecord.Flat = row.Cells(, 6).Value myRecord.Phone = row.Cells(, 7).Value getRecord = myRecord EndFunction Sub putRecord(row As Range, myRecord As Record) row.Cells(, 1).Value = myRecord.Fam row.Cells(, 2).Value = myRecord.Im row.Cells(, 3).Value = myRecord.Ot row.Cells(, 4).Value = myRecord.street row.Cells(, 5).Value = myRecord.no row.Cells(, 6).Value = myRecord.Flat row.Cells(, 7).Value = myRecord.Phone EndSub Sub showTools() Application.CommandBars("Phones").Enabled = True Application.CommandBars("Phones").Visible = TrueEndSub Sub hideTools() Application.CommandBars("Phones").Visible = False Application.CommandBars("Phones").Enabled = FalseEndSub