3) Отчёт о невыданной корреспонденции. Этот отчёт отображается не на ячейках книги Excel, а на компоненте ListBox формы (см. рис. 8). Список обновляется при каждом нажатии кнопки «Получить» (то есть при получении корреспонденции). При каждом нажатии кнопки «Выдать» выбранная строка удаляется из отчёта, так как выбранная корреспонденция выдаётся получателю. При разработке этого отчёта использовался механизм встроенного в Excel расширенного фильтра, а ячейки - результат этого расширенного фильтра использовались как RowSource для компоненты ListBox. Пример отчёта о невыданной корреспонденции находится на рис. 8.

Заключение

В ходе работы было разработано полноценное приложение с использованием Microsoft Excel на языке VBA, позволяющее автоматизировать деятельность почты по учёту полученных и отправленных посылок, бандеролей и писем без приобретения дополнительных специализированных программных продуктов. Разработанное приложение полностью удовлетворяет требованиям, поставленным в задаче: производится учёт корреспонденции, формируются бланки извещения и необходимые для работы почты отчёты. В ходе работы были изучены средства языка VBA, позволяющие скрывать и отображать отдельные листы и всё приложение целиком; методы и средства работы как с отдельными ячейками, так и с их диапазонами.

Дальнейшее развитие программного продукта возможно в следующих направлениях:

• расширение исходных данных и обрабатываемой корреспонденции (наложенный платёж, денежные переводы и пр.)

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

Список использованной литературы

1) Встроенная справочная система Microsoft Office Excel.

2) Тематический раздел форума city.is74.ru по программированию на VBA.

3) Справочная система MSDN

Приложение А

Схема связи форм приложения.

Вызов формы «Справка» осуществляется по нажатию кнопки «Справка» на главной форме «Почта».

В

Почта

Справка

ызов главной формы «Почта производится автоматически при открытии рабочей книги.

Приложение Б

Ниже представлены основные смысловые фрагменты кода программы.

Дублирующиеся фрагменты кода (например, сопроводительной ведомости полученной и отправленной корреспонденции) приводятся 1 раз.

'Обработка нажатия кнопки «Отправить». Добавление данных в исходную таблицу

Private Sub CommandButton11_Click()

Dim Flag As Boolean

Flag = True

If IsNumeric(TextBox6.Text) = False Or IsDate(TextBox1.Text) = False Or ComboBox1.Value = "" Or _

ComboBox1.Value = "" Or TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Or _

TextBox4.Text = "" Or TextBox5.Text = "" Or TextBox6.Text = "" Then Flag = False

If Flag = False Then

a = MsgBox("Данные введены неверно или не полностью", vbCritical, "Ошибка")

Exit Sub

End If

Sheets("Отправленная корреспонденция").Select

Range("A3").Select

Selection.CurrentRegion.Select

i = Selection.Rows.Count

j = i + 3

Range("A" & j).Value = i

Range("B" & j).Value = TextBox1.Text

Range("C" & j).Value = ComboBox1.Value

Range("D" & j).Value = ComboBox2.Value

Range("E" & j).Value = TextBox2.Text

Range("F" & j).Value = TextBox3.Text

Range("G" & j).Value = TextBox5.Text

Range("H" & j).Value = TextBox4.Text

Range("I" & j).Value = TextBox6.Text

Range("J" & j).Value = Label10.Caption

End Sub

'Обработка нажатия кнопки печати квитанции об оплате отправки корреспонденции

Private Sub CommandButton4_Click()

If IsNumeric(TextBox6.Text) = False Or IsDate(TextBox1.Text) = False Or ComboBox1.Value = "" Or _

ComboBox1.Value = "" Or TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Or _

TextBox4.Text = "" Or TextBox5.Text = "" Or TextBox6.Text = "" Then

a = MsgBox("Данные введены неверно или не полностью", vbCritical, "Ошибка")

Exit Sub

End If

Sheets("Отправленная корреспонденция").Select

Range("A3").Select

Selection.CurrentRegion.Select

i = Selection.Rows.Count - 1

Sheets("Бланки").Select

Range("Q18").Value = TextBox1.Text

Range("P19").Value = i

Range("M23").Value = TextBox2.Text

Range("M24").Value = TextBox3.Text

Range("M25").Value = TextBox5.Text

Range("M26").Value = TextBox4.Text

Range("N27").Value = ComboBox1.Value

Range("L28").Value = TextBox6.Text

Range("M29").Value = Label10.Caption

Application.Visible = True

For Each m In Sheets

If m.Name <> "Бланки" Then m.Visible = False

Next m

Application.Visible = True

Почта.Hide

End Sub

'функция, рассчитывающая стоимость отправки

Private Function DispatchCost(City As String, Kind As String, Optional weight As Double)

Sheets("Стоимость отправки").Select

Range("A2").Select

Selection.CurrentRegion.Select

n = Selection.Rows.Count

For i = 3 To n Step 1

If InStr(1, Sheets("Стоимость отправки").Range("A" & i).Value, City, vbTextCompare) > 0 Then

If Kind = "посылка" Then DispatchCost = weight * Range("B" & i).Value

If Kind = "бандероль" Then DispatchCost = weight * Range("E" & i).Value

If Kind = "заказное письмо" Then DispatchCost = weight * Range("H" & i).Value

End If

Next i

End Function

'Пересчёт стоимости отправки корреспонденции при изменении города. Аналогичный код обрабатывает изменение веса и типа корреспонденции

Private Sub ComboBox2_Change()

If IsNumeric(TextBox6.Text) And ComboBox1.Value <> "" And ComboBox2.Value <> "" And TextBox6.Text <> "" _

Then Label10.Caption = DispatchCost(ComboBox2.Value, ComboBox1.Value, CDbl(TextBox6.Text)) _

Else Label10.Caption = ""

End Sub

'Отчёт по направлениям (отправка)

Private Sub CommandButton5_Click()

Worksheets("Отчёты").Activate

Cells(3, 1).Select

Selection.CurrentRegion.Select

n = Selection.Rows.Count + 2

Worksheets("Отправленная корреспонденция").Select

Cells(3, 1).Select

Selection.CurrentRegion.Select

n2 = Selection.Rows.Count + 2

For i = 4 To n Step 1

Sheets("Отчёты").Select

CurrentCity = Range("A" & i).Value 'перебор городов

Count1 = 0

Count2 = 0

Count3 = 0

Sum1 = 0

Sum2 = 0

Sum3 = 0

Sheets("Отправленная корреспонденция").Select

For j = 4 To n2 Step 1 'перебор отправленной корреспонденции

If Range("D" & j).Value = CurrentCity Then

If Range("C" & j).Value = "посылка" Then

Count1 = Count1 + 1

Sum1 = Sum1 + Range("I" & j).Value

End If

If Range("C" & j).Value = "бандероль" Then

Count2 = Count2 + 1

Sum2 = Sum2 + Range("I" & j).Value

End If

If Range("C" & j).Value = "заказное письмо" Then

Count3 = Count3 + 1

Sum3 = Sum3 + Range("I" & j).Value

End If

End If

Next j

Sheets("Отчёты").Select

Range("B" & i).Value = Count1

Range("C" & i).Value = Sum1

Range("D" & i).Value = Count2

Range("E" & i).Value = Sum2

Range("F" & i).Value = Count3

Range("G" & i).Value = Sum3

Next i

For Each m In Sheets

If m.Name <> "Отчёты" Then m.Visible = False

Next m

Application.Visible = True

Почта.Hide

End Sub

'формирование сопроводительной ведомости (получение)

Private Sub CommandButton8_Click()

data = InputBox("Укажите дату", "Сопроводительная ведомость (получение)")

While IsDate(data) = False

data = InputBox("Укажите дату", "Сопроводительная ведомость (получение)")

Wend

Sheets("Сопроводительная ведомость").Select

Range("K4").Select

Selection.CurrentRegion.Select

n = Selection.Rows.Count + 4

If n > 4 Then Range("K5:S" & n).Clear

Sheets("Полученная корреспонденция").Select

Range("A3").Select

Selection.CurrentRegion.Select

n = Selection.Rows.Count + 2

c = 5

For i = 4 To n Step 1

Sheets("Полученная корреспонденция").Activate

If Range("B" & i).Value = data Then

Range("A" & i & ":I" & i).Copy

Sheets("Сопроводительная ведомость").Activate

Range("K" & c).Select

ActiveSheet.Paste

c = c + 1

End If

Next i

For Each m In Sheets

If m.Name <> "Сопроводительная ведомость" Then m.Visible = False

Next m

Application.Visible = True

Почта.Hide

End Sub

'обработка нажатия кнопки «Выдать»

Private Sub CommandButton13_Click()

i = ListBox1.ListIndex

j = ListBox1.List(i, 0) + 3

Sheets("Полученная корреспонденция").Select

Range("J" & j).Value = "ВЫДАНО"

Range("A3").Select

Selection.CurrentRegion.Select

n = Selection.Rows.Count + 2

Range("N3").Select

Selection.CurrentRegion.Select

Selection.Clear

Range("A3:J" & n).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _

"L3:L4"), CopyToRange:=Range("N3"), Unique:=False

Range("N3").Select

Selection.CurrentRegion.Select

n = Selection.Rows.Count + 3

ListBox1.RowSource = "N4:V" & n

End Sub