Комп'ютерна підготовка
Курсова робота.
Виконав студент ТМЦДО гр.: з-472-27б спеціальності
061000 Маркелова А.А.
Томський державний університет систем управління
і радіоелектроніки (ТУСУР)
Кафедра автоматизації обробки інформації.
р. Абакан.
2005
Написати
програму, яка формує базу «Телефонний довідник», що містить наступні
інформація: ПІБ, адреса, телефон. Перегляд бази та обробка повинна виконуватися в
програмі Excel.
Програма
повинна задовольняти наступним вимогам:
Додаток
виконати з використанням мови програмування VBA для MS Excel.
При
відкриття книги Excel,
містить проект, забезпечити один робочий аркуш, на якому оформити рекламну
заставку проекту і помістити елемент запуску проекту на виконання.
Проект
повинен забезпечувати наступні режими роботи:
введення
вихідних даних і формування бази;
коректування
даних (виправлення, додавання, видалення);
робота
з даними (пошук, сортування, перегляд);
формування
статистики:
загальне
кількість абонентів телефонної мережі;
кількість
телефонів на вказаній вулиці;
кількість
телефонів в зазначеному будинку.
Для
кожного режиму роботи визначити кнопку запуску і призначену для користувача форму.
На
екрані розмістити одну панель інструментів, що забезпечує роботу проекту.
Решта панелі приховати.
Дані
проекту зберігати у файлі.
При
введенні числових даних забезпечити обробку помилок некоректного введення.
Введення
Мета
роботи: закріплення знань, отриманих при вивченні курсу «Комп'ютерна
підготовка ", а також придбання нових навичок створення завершених
програмних додатків для 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-програму в
своєму складі містить:
Робочу
книгу Workbook «Телефонний довідник» + програмний код VBA, що складається з двох
листів:
Worksheet Лист1 (Старт) +
програмний код VBA
Worksheet Лист2 (База даних)
+ Програмний код VBA
П'ять
форм + програмний код VBA:
addRowForm - для режиму
додавання нового запису
delRowForm - для режиму
видалення запису
editRowForm - для режиму
редагування запису
reportForm - для режиму формування
статистики
sortForm - для режиму
сортування бази даних
Модуль
Module1, що містить
опис типу даних Record
для одного запису про абонента, а також функції роботи (читання/запису) з такими
даними та зовнішнім файлом.
Панель
інструментів "Phones",
що забезпечує роботу програми і що складається з дев'яти кнопок:
читання
бази даних;
запис
бази даних;
додавання
запису;
коректування
запису;
видалення
запису;
пошук;
сортування;
звіт
(статистика);
вихід
з програми.
Керівництво користувача
Запуск програми
Для
запуску програми необхідно в MS Excel
відкрити книгу:
"Телефонний
справочнік.xls "
Ви
побачите один список з рекламною написом і двома кнопками. Для відображення аркуша
з базою даних натисніть на кнопку «Почати роботу».
В
результаті, рекламна сторінка зникне, а замість неї з'явиться основний аркуш,
що містить шапку бази даних і панель інструментів для роботи.
Читання/запис бази даних
Оскільки
вся інформація зберігається в зовнішньому файлі, то для завантаження бази даних на лист Excel таблиці необхідно на
панелі інструментів натиснути кнопку «Відкрити базу даних». При цьому існуючі
на аркуші дані будуть замінені інформацією з зовнішнього файлу "phones.db".
Для
збереження результатів роботи з базою даних натисніть кнопку «Зберегти базу
даних »на панелі інструментів. Вся поточна інформація, яка відображається на аркуші,
буде записана в зовнішній файл "phones.db".
Режими коректування даних
В
цілях безпеки, аркуші книги захищені від коригування звичайними способами.
Тому для додавання, зміни та видалення інформації передбачені
відповідні режими роботи, що ініціюються другою групою кнопок панелі
інструментів.
При
натисканні на будь-яку з них, буде запропоновано діалогове вікно, в якому в
відповідні поля потрібно занести нову (відкоригувати існуючу)
інформацію і натиснути кнопку підтвердження операції.
Якщо
перед вибором режиму видалення записи були відзначені кілька рядків бази даних,
то замість відображення форми з видаляється інформацією буде просто запропоновано
підтвердити видалення позначених елементів.
Увага.
У режимі долучення/коректування запису обов'язкові для заповнення поля:
Прізвище, Ім'я, По батькові, Вулиця, Будинок, Телефон.
Крім того, в поле Телефон допускається введення тільки числових даних (не
більше 10 цифр).
Пошук інформації
Для
пошуку потрібної інформації натисніть кнопку «Знайти» на панелі інструментів. Буде
запущений стандартний механізм пошуку інформації по аркушу MS Excel.
Режими сортування
В
програмі передбачено три режими сортування даних:
За
абоненту (прізвище + ім'я + по батькові);
За
адресою (вулиця + будинок + квартира);
За
телефону.
Для
виконання сортування досить натиснути відповідну кнопку на панелі
інструментів і вибрати один із трьох запропонованих режимів.
Формування статистики
Згідно
завданням програма дозволяє розрахувати наступну статистику:
Загальна
кількість абонентів телефонної мережі;
Кількість
телефонів на вказаній вулиці;
Кількість
телефонів в зазначеному будинку.
Увійдіть
у режим «Звіт», виберіть потрібний звіт і, при необхідності, задайте
параметри його формування. Кількість абонентів за заданими реквізитами буде
пораховано і відображено в діалоговому вікні.
Завершення роботи з програмою
Для
завершення роботи з програмою натисніть кнопку «Вихід» на панелі інструментів.
Лист з базою даної буде приховано, а з'явиться лист з рекламною заставкою. Для
підтвердження виходу повторно виберіть кнопку "Вихід". Якщо «Телефонний
довідник »був єдиною відкритою книгою, додаток MS Excel буде повністю
закрита, в іншому випадку - закриється тільки книга з описуваної програмою.
Увага.
Не забувайте зберігати інформацію в зовнішньому файлі, інакше останні
коректування можуть бути загублені.
Доповнення
Всі
стандартні панелі інструментів ховаються і відновлюються при відкритті /
закриття книги «Телефонний довідник», а також при перемиканні між вікнами.
Щоб уникнути проблем з відновленням стандартного набору панелей інструментів
не рекомендується самостійно змінювати набір відображаються панелей інструментів
поки що описується книга залишається відкритою.
При
виникненні будь-якої нестандартної ситуації слід закрити книгу «Телефонний
довідник »і виставити потрібні панелі через меню« Вид ».
Висновок
В
ході виконання роботи були закріплені знання по роботі в MS Excel і основам
програмування на VBA,
а також придбані практичні навички створення закінчених програмних
додатків для MS Excel.
Результатом
зробленої роботи є програма «Телефонний довідник», функціонально
виконує основні завдання, що стоять перед додатком такого рівня і
призначення.
Зрозуміло,
виконаний проект не є завершеним повною мірою. В якості
напрямків для розвитку проекту можна згадати, наприклад, більше
конкретизований механізм пошуку інформації або реалізація оптимальних
методів сортування (що може бути більш ефективним на великих обсягах
інформації).
Список літератури
Потахова
І. В. Комп'ютерна підготовка. Офісне
програмування: Навчальний посібник. - Томск: Томський міжвузівський центр
дистанційної освіти, 2004. - 181с.
Справочное руководство по MS Excel і Visual Basic for Applications:
Microsoft Corp., 2001.
Демидова
Л.А., Пилькин А.Н. Програмування в середовищі Visual Basic for Applications: Практикум - М.: Горячая линия - Телеком, 2004. --
175с.
Додаток. Лістинг
програм
VBA
Робоча книга
Dim oldBars (20)
As Long, kol As
Integer
Private Sub
Workbook_Activate ()
kol = 0
Dim bar As
CommandBar
For Each
bar In Application.CommandBars
If bar.Visible And
Not (bar.Protection = msoBarNoChangeVisible) _
And
(bar.Type = msoBarTypeNormal) And Not (bar.Name = "Phones")
Then
kol = kol + 1
oldBars (kol) = bar.index
End If
Next bar
For i = 1
To kol
Application.CommandBars (oldBars (i)). Visible = False
Next
If ThisWorkbook.ActiveSheet.Name = "База даних" Then
showTools
End If
End Sub
Private Sub
Workbook_Deactivate ()
Dim i As
Integer
For i = kol To
1 Step -1
Application.CommandBars (oldBars (i)). Visible = True
Next
hideTools
End Sub
Private Sub
Workbook_Open ()
ThisWorkbook.Worksheets ( "Старт"). Visible = True 'заховати стартовий лист
ThisWorkbook.Worksheets ( "Старт"). Activate 'зробити активним лист з БД
ThisWorkbook.Worksheets ( "База даних"). Visible = False 'показати базу даних
End Sub
Лист
1 (Старт)
Private Sub ExitButton_Click ()
ExitProject
End Sub
Private Sub StartButton_Click ()
'Commandbars
ThisWorkbook.Worksheets ( "База даних"). Visible = True 'показати базу даних
ThisWorkbook.Worksheets ( "База даних"). Activate 'зробити
активним лист з БД
ThisWorkbook.Worksheets ( "Старт"). Visible = False 'заховати
стартовий лист
End Sub
Лист2 (База даних)
Private Sub Worksheet_Activate ()
showTools
End Sub
Private Sub Worksheet_Deactivate ()
hideTools
End Sub
Sub addRecord ()
If (ActiveCell.row <5) Or (Len (ActiveCell.EntireRow.Cells (, 1). Value) = 0) Then
Range ( "A5"). Activate
End If
ThisWorkbook.ActiveSheet.Unprotect
addRowForm.Show vbModal
ThisWorkbook.ActiveSheet.Protect
End Sub
Sub delRecord ()
If (ActiveCell.row <5) Or (Len (ActiveCell.EntireRow.Cells (, 1). Value) = 0) Then
Exit Sub
End If
ThisWorkbook.ActiveSheet.Unprotect
If Selection.Rows.count = 1 Then
delRowForm.Show vbModal
Else
Dim response
response = MsgBox ( "Відзначено записів:" + Str (Selection.Rows.count) + Chr (13) + "Видалити все?", vbYesNoCancel, "Увага !")
If response = vbYes Then
Selection.EntireRow.Delete
End If
End If
ThisWorkbook.ActiveSheet.Protect
End Sub
Sub editRecord ()
If (ActiveCell.row <5) Or (Len (ActiveCell.EntireRow.Cells (, 1). Value) = 0) Then
Exit Sub
End If
ThisWorkbook.ActiveSheet.Unprotect
editRowForm.Show vbModal
ThisWorkbook.ActiveSheet.Protect
End Sub
Sub sort ()
sortForm.Show vbModal
ThisWorkbook.ActiveSheet.Protect
End Sub
Sub report ()
Dim oldCell As Range
ThisWorkbook.ActiveSheet.Unprotect
Set oldCell = ActiveCell
reportForm.Show vbModal
oldCell.Activate
ThisWorkbook.ActiveSheet.Protect
End Sub
addRowForm
Private Sub UserForm_Activate ()
FamBox.Value = ""
ImBox.Value = ""
OtBox.Value = ""
StreetBox.Value = ""
NoBox.Value = ""
FlatBox.Value = ""
PhoneBox.Value = ""
FamBox.SetFocus
End Sub
Private Sub CancelButton_Click ()
addRowForm.Hide
End Sub
Private Sub OKButton_Click ()
'перевірка інформації
Dim box As Variant, boxes As Variant
boxes = Array (FamBox, ImBox, OtBox, StreetBox,
NoBox, PhoneBox)
For Each
box In boxes
If Len (Trim (box.Value))
= 0 Then
box.SetFocus
Exit Sub
End If
Next box
If Len (Trim (PhoneBox.Value))
> 10 Then
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
End If
End Sub
Private Sub PhoneBox_KeyPress (ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii Asc ( "9")) Then
MsgBox "Допускається введення лише
цифр! "
KeyAscii.Value = 0
End If
End Sub
delRowForm
Private Sub CancelButton_Click ()
delRowForm.Hide
End Sub
Private Sub OKButton_Click ()
'видалення поточного рядка
ActiveCell.EntireRow.Delete
'приховування форми
delRowForm.Hide
End Sub
Private Sub 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
End Sub
editRowForm
Private Sub 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
End Sub
Private Sub CancelButton_Click ()
editRowForm.Hide
End Sub
Private Sub OKButton_Click ()
'перевірка інформації
Dim box As Variant, boxes As Variant
boxes = Array (FamBox, ImBox, OtBox, StreetBox,
NoBox, PhoneBox)
For Each
box In boxes
If Len (Trim (box.Value))
= 0 Then
box.SetFocus
Exit Sub
End If
Next box
If Len (Trim (PhoneBox.Value))
> 10 Then
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
End If
End Sub
Private Sub PhoneBox_KeyPress (ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii Asc ( "9")) Then
MsgBox "Допускається введення лише
цифр! "
KeyAscii.Value = 0
End If
End Sub
reportForm
Private Sub UserForm_Activate ()
AllOption.Value = True
OKButton.Caption = "Розрахунок"
OKButton.SetFocus
End Sub
Private Sub AllOption_Click ()
OKButton.Caption = "Розрахунок"
End Sub
Private Sub StreetOption_Click ()
OKButton.Caption = "Параметри ..."
End Sub
Private Sub HouseOption_Click ()
OKButton.Caption = "Параметри ..."
End Sub
Private Sub CancelButton_Click ()
reportForm.Hide
End Sub
Private Sub OKButton_Click ()
Dim myRecord As Record
Dim counter As Long
Dim street As String, no As String, title As String
If AllOption.Value Then
counter = count ()
MsgBox "Загальна кількість абонентів:
"+ Str (counter)
Else
myRecord = getRecord (ActiveCell.EntireRow)
If StreetOption.Value Then
title = "Звіт по вулиці"
street = InputBox ( "Задайте найменування вулиці:", title,
myRecord.street)
If Len (street)
> 0 Then
street = Trim (street)
counter = count (street)
MsgBox "Кількість телефонів на вулиці '" + street +" ':" + Str (counter)
End If
Else
title = "Звіт по будинку"
street = InputBox ( "Задайте найменування вулиці:", title,
myRecord.street)
If Len (street)
> 0 Then
street = Trim (street)
no = InputBox ( "Вулиця '" + street + "'" + Chr (10) + "Задайте номер будинку:", title, myRecord.no)
If Len (no)
> 0 Then
no = Trim (no)
counter = count (street, no)
MsgBox "Кількість
телефонів в будинку ' "+ street +" "+ no +"':
"+ Str (counter)
End If
End If
End If
End If
reportForm.Hide
End Sub
Private Function 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
For Each
curRow In data.Rows
myRecord = getRecord (curRow)
doCalc = False
If IsMissing (street) Then
'всі абоненти
doCalc = True
Else
If IsMissing (no) Then
'по вулиці
doCalc = (Trim (myRecord.street) = street)
Else
'по будинку
doCalc = (Trim (myRecord.street) = street) And (Trim (myRecord.no) =
no)
End If
End If
If doCalc Then counter = counter + 1
Next curRow
count = counter
End Function
sortForm
Private Sub UserForm_Activate ()
OKButton.SetFocus
End Sub
Private Sub CancelButton_Click ()
sortForm.Hide
End Sub
Private Sub 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
Else
If 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
End If
End If
sortForm.Hide
End Sub
Module1
Public Type Record
Fam As String
Im As String
Ot As String
street As String
no As String
Flat As String
Phone As Long
End Type
Public Function dbFileName () As String
dbFileName = ThisWorkbook.Path + "phones.db"
End Function
Sub ToolbarExitButton ()
If ThisWorkbook.ActiveSheet.Name = "Старт" Then
ExitProject
Else
ThisWorkbook.Worksheets ( "Старт"). Visible = True 'заховати стартовий лист
ThisWorkbook.Worksheets ( "Старт"). Activate 'зробити активним лист з БД
ThisWorkbook.Worksheets ( "База даних"). Visible = False 'показати базу даних
End If
End Sub
Sub ExitProject ()
ThisWorkbook.Saved = True
If Application.Workbooks.count = 1 Then
Application.Quit 'завершити роботу Excel
Else
ThisWorkbook.Close 'завершити роботу проекту
End If
End Sub
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 For Input
As # 1
row = 1
Do While
Not 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 + 1
Loop
Close # 1
ThisWorkbook.ActiveSheet.Protect
End Sub
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 # 1
For Each
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
End Sub
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
End Function
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
End Sub
Sub showTools ()
Application.CommandBars ( "Phones"). Enabled = True
Application.CommandBars ( "Phones"). Visible = True
End Sub
Sub hideTools ()
Application.CommandBars ( "Phones"). Visible = False
Application.CommandBars ( "Phones"). Enabled = False
End Sub
