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

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

Функциональные возможности разрабатываемой книги Microsoft Excel рассчитаны на широкую категорию пользователей. В связи с тем, что все читатели знакомы с учебным процессом по школе, колледжу или вузу, перечисленные далее основные сервисные возможности разработки должны быть понятны:

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

Ввод начальной информации

Понятно, что рассматриваемая разработка требует некоторой начальной информации — о группах, аудиторном фонде и т. д. Фактически нам необходимы справочники по группам, аудиториям, преподавателям и еще ряду категорий. Такие данные заносятся на отдельный лист книги; один из вариантов его заполнения представлен на рис. 5.15.

Рис. 5.15. Лист, содержащий справочную информацию

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

В столбце D располагаются учебные дни в течение недели, а столбец Е отводится для времени начала занятий. Так, во второй строке этого столбца отображается время начала первого занятия, в третьей строке — начало второго занятия, и т. д.

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

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

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

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

Рис. 5.16. Организация листа Заявки

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

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

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

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

Так, в каждой строке столбцы F и G отводятся для записи информации об обслуживании заявки (подобранной для нее аудитории). И если аудитория подобрана, то в столбец F программно записывается слово «да», а в следующий столбец вписывается номер аудитории. Фактически информация на первом листе представляет собой определенную базу данных.

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

В столбце Е для каждой заявки указывается количество студентов, которые учатся в данной группе. Эта информация вносится автоматически после выбора названия группы (вы уже видели, что на втором листе присутствует два связанных столбца — название группы и число студентов в ней). В столбцах, начиная с J, отмечаются недели, когда должны проводиться занятия по данной заявке. Звездочка в соответствующем столбце говорит о том, что на этой неделе занятие планируется. В дальнейшем для исключения лишних фрагментов мы ограничимся семью неделями учебного процесса.

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

Рассмотрим теперь элементы управления на рис. 5.16. Это две кнопки, которые на листе расположены в области первых двух строк. В табл. 5.1 приведены значения их свойств Name и Caption.

Таблица 5.1. Значения свойств кнопок, расположенных на листе Заявки

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

Форма для ввода заявок

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

1
2
3
4

' Листинг 5.20. Обработка щелчка на кнопке Ввод новой заявки
Private Sub Input_New_Click()
    Form_Add.Show
End Sub

Здесь Form_Add — значение свойства Name формы, которую мы далее разработаем для ввода информации. Выполняется метод Show этой формы, который загружает ее в память и отображает на экране. Фактически для пользователя щелчок на кнопке ввода новой заявки приводит к появлению формы на экране. На рис. 5.17 представлена рассматриваемая форма ввода.

Рис. 5.17. Форма ввода заявок на занятия

На форме расположено шесть элементов управления типа «Поле со списком» или ComboBox (в табл. 5.2 приведены значения их свойства Name). Для каждого подобного объекта рядом имеется поясняющая надпись. При активизации формы эти элементы автоматически заполняются информацией со второго листа книги. Скажем, при щелчке на поле со списком преподавателей мы увидим фамилии преподавателей, предварительно внесенные нами на второй лист.

Таблица 5.2. Значения свойства Name полей со списками на рис. 5.17

В правой части формы расположено текстовое окно Количество студентов (Name — Col_Stud), в которое автоматически заносится информация о количестве студентов после выбора группы. Это обеспечивает процедура, представленная в листинге 5.21.

1
2
3
4

' Листинг 5.21. Обработка щелчка в поле со списком групп учащихся
Private Sub Group_Click()
Col_Stud.Text = Worksheets(2).Cells(Group.ListIndex + 2, 2).Value
End Sub

В верхней части формы присутствует набор элементов управления тина «Флажок». Каждый флажок предназначен для указания определенного номера недели, когда должно проводиться занятие. Значения свойства Name флажков формируются как комбинация символов Ch и номера недели: Chi, Ch2 и т. д. Здесь предполагается, что в учебном блоке не больше 7 учебных недель.

В нижней части формы располагаются три важные функциональные кнопки. При щелчке на кнопке Выход (Name — Com_Out) форма просто закрывается, и никакой записи информации в базу данных первого листа не производится. В листинге 5.22 приведен текст предопределенной процедуры для обработки щелчка на кнопке Выход. Метод Hide позволяет закрыть форму и вернуться на рабочий лист.

1
2
3
4

' Листинг 5.22. Обработка щелчка на кнопке Выход
Private Sub Com_Out_Click()
    Form_Add.Hide
End Sub

Другие две кнопки позволяют перенести на рабочий лист данные, внесенные пользователем в форму. При этом одна из кнопок (Зафиксировать и ввести следующую, Name — Com_Next) после записи оставляет форму активной, а другая (Name — Com_Wr_Out) закрывает ее. При активизации формы автоматически выполняется предопределенная процедура UserForm_Activate, а ее текст приведен в листинге 5.23. Основное ее назначение заключается в заполнении списков преподавателей, групп и т. д. Данные для заполнения берутся со второго листа, о чем уже говорилось.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

' Листинг 5.23. Процедура, выполняемая при активизации формы на рис. 5.17
Private Sub UserForm_Activate()
Ch1.Value = False
Ch2.Value = False
Ch3.Value = False
Ch4.Value = False
Ch5.Value = False
Ch6.Value = False
Ch7.Value = False
'Заполнение заявителей
N = 0
While Worksheets(2).Cells(N + 2, 6).Value <> ""
    N = N + 1
Wend
Boss.Clear
For i = 1 To N
Boss.AddItem Worksheets(2).Cells(i + 1, 6).Value
Next
'Заполнение преподавателей
N = 0
While Worksheets(2).Cells(N + 2, 7).Value <> ""
    N = N + 1
Wend
Prepod.Clear
For i = 1 To N
Prepod.AddItem Worksheets(2).Cells(i + 1, 7).Value
Next
' Заполнение учебных дней
N = 0
While Worksheets(2).Cells(N + 2, 4).Value <> ""
    N = N + 1
Wend
Day.Clear
For i = 1 To N
Day.AddItem Worksheets(2).Cells(i + 1, 4).Value
Next
'Заполнение начала пар
N = 0
While Worksheets(2).Cells(N + 2, 5).Value <> ""
    N = N + 1
Wend
Time.Clear
For i = 1 To N
Time.AddItem Worksheets(2).Cells(i + 1, 5).Value
Next
' Заполнение названий групп
N = 0
While Worksheets(2).Cells(N + 2, 8).Value <> ""
    N = N + 1
Wend
Group.Clear
For i = 1 To N
Group.AddItem Worksheets(2).Cells(i + 1, 8).Value
Next
' Заполнение названий дисциплин
N = 0
While Worksheets(2).Cells(N + 2, 10).Value <> ""
    N = N + 1
Wend
Disp.Clear
For i = 1 To N
    Disp.AddItem Worksheets(2).Cells(i + 1, 10).Value
Next
    Col_Stud.Text = ""
End Sub

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161

' Листинг 5.24. Процедура щелчка на кнопке Зафиксировать и ввести следующую
Private Sub Com_Next_Click()
' Массивы для фиксации пересечений в занятиях
' по группам и преподавателям
Dim Mass(10) As Integer
Dim Mass2(10) As Integer
' Поле заявителя необходимо заполнить
If Boss.ListIndex = -1 Then
    MsgBox ("Выберите заявителя")
    Exit Sub
End If
' Поле преподавателя необходимо заполнить
If Prepod.ListIndex = -1 Then
    MsgBox ("Выберите преподавателя")
    Exit Sub
End If
' Поле для указания дня недели должно быть заполнено
If Day.ListIndex = -1 Then
    MsgBox ("Выберите день")
    Exit Sub
End If
' Поле для указания начала занятий должно быть заполнено
If Time.ListIndex = -1 Then
MsgBox ("Выберите время")
Exit Sub
End If
' Поле для указания группы должно быть заполнено
If Group.ListIndex = -1 Then
    MsgBox ("Выберите группу")
    Exit Sub
End If
' Необходимо выбрать дисциплину
If Disp.ListIndex = -1 Then
    MsgBox ("Выберите дисциплину")
    Exit Sub
End If
' Необходимо указать количество студентов
If IsNumeric(Col_Stud.Text) = False Then
    MsgBox ("Не указано количество студентов")
    Exit Sub
End If
' Подсчет числа заявок на первом листе
N = 0
While Worksheets(1).Cells(N + 4, 1).Value <> ""
    N = N + 1
Wend
' Запись информации на первый лист
Worksheets(1).Cells(N + 4, 1).Value = Boss.Text
Worksheets(1).Cells(N + 4, 2).Value = Prepod.Text
Worksheets(1).Cells(N + 4, 3).Value = Day.Text
Worksheets(1).Cells(N + 4, 4).Value = Time.Text
Worksheets(1).Cells(N + 4, 5).Value = Col_Stud.Text
Worksheets(1).Cells(N + 4, 8).Value = Group.Text
Worksheets(1).Cells(N + 4, 9).Value = Disp.Text
If Ch1.Value = True Then
    Worksheets(1).Cells(N + 4, 10).Value = "*"
    End If
If Ch2.Value = True Then
    Worksheets(1).Cells(N + 4, 11).Value = "*"
End If
If Ch3.Value = True Then
    Worksheets(1).Cells(N + 4, 12).Value = "*"
End If
If Ch4.Value = True Then
    Worksheets(1).Cells(N + 4, 13).Value = "*"
End If
If Ch5.Value = True Then
    Worksheets(1).Cells(N + 4, 14).Value = "*"
End If
If Ch6.Value = True Then
    Worksheets(1).Cells(N + 4, 15).Value = "*"
End If
If Ch7.Value = True Then
    Worksheets(1).Cells(N + 4, 16).Value = "*"
End If
Key = 0 ' Переменная-индикатор при наличии накладок в расписании
Ch_mass = 0
' Счетчик накладок по группам
Ch_mass2 = 0
' Счетчик накладок по преподавателям
' Цикл по заявкам
For it = 4 To 4 + N - 1
' Проверка преподавателя
    q1 = Worksheets(1).Cells(it, 2).Value 'Преподаватель
    q2 = Worksheets(1).Cells(it, 3).Value 'День
    q3 = Worksheets(1).Cells(it, 4).Value 'Время
    Zan_Pr = 0 ' Сброс индикатора занятости преподавателя
    If q1 = Worksheets(1).Cells(N + 4, 2).Value And _
    q2 = Worksheets(1).Cells(N + 4, 3).Value And _
    q3 = Worksheets(1).Cells(N + 4, 4).Value Then
    For Ned = 10 To 16
    If Worksheets(1).Cells(N + 4, Ned).Value <> "" _
        And Worksheets(1).Cells(it, Ned).Value <> "" Then
    Zan_Pr = 1 ' Преподаватель занят
    Exit For
End If
Next
If Zan_Pr = 1 Then
    Mass(Ch_mass) = it
    Ch_mass = Ch_mass + 1
    Key = 1 ' Отмечается факт противоречия в данных
    End If
    End If
'Проверка группы
    q1 = Worksheets(1).Cells(it, 8).Value
    q2 = Worksheets(1).Cells(it, 3).Value
    q3 = Worksheets(1).Cells(it, 4).Value
    Zan_Gr = 0
    If q1 = Worksheets(1).Cells(N + 4, 8).Value And _
    q2 = Worksheets(1).Cells(N + 4, 3).Value And _
    q3 = Worksheets(1).Cells(N + 4, 4).Value Then
    For Ned = 10 To 16
        If Worksheets(1).Cells(N + 4, Ned).Value <> "" And _
        Worksheets(1).Cells(it, Ned).Value <> "" Then
        Zan_Gr = 1
        Exit For
    End If
    Next
        If Zan_Gr = 1 Then
        Mass2(Ch_mass2) = it
        Ch_mass2 = Ch_mass2 + 1
        Key = 1 ' Отмечается факт противоречия в данных
        End If
        End If
    Next ' Завершение цикла по заявкам
If Key = 1 Then
    If Ch_mass > 0 Then ' Если есть противоречия по преподавателю
    Inform_str = "" ' Формирование информационной строки
    For i = 0 To Ch_mass - 1
    If Inform_str <> "" Then
    Inform_str = Inform_str + ","
    End If
    Inform_str = Inform_str + CStr(Mass(i))
    Next
    Inform_str = "Преподаватель занят. Заявка в стр. " + Inform_str
    MsgBox (Inform_str)
    End If
    If Ch_mass2 > 0 Then ' Накладка по группе
    Inform_str = "" ' Формирование информационной строки
        For i = 0 To Ch_mass2 - 1
            If Inform_str <> "" Then
                Inform_str = Inform_str + ","
                End If
                Inform_str = Inform_str + CStr(Mass2(i))
        Next
        Inform_str = "У группы уже есть занятие. Заявка в стр. " + Inform_str
        MsgBox (Inform_str)
        End If
' Выбор варианта действия пользователя
Inform = MsgBox("Если все равно записать заявку - Да. " + _
        " Если продолжить редактирование - Отмена. " + _
        " Если удалить, то нажмите - Нет. ", _
        vbYesNoCancel)
If Inform = vbNo Or Inform = vbCancel Then
' Удаление только что введенных данных
    For i = 1 To 16
    Worksheets(1).Cells(N + 4, i).Value = ""
    Next
End If
End If
End Sub

В начале процедуры подсчитывается количество уже имеющихся заявок на первом листе:

1
2
3
4

N = 0
While Worksheets(1).Cells(N + 4, 1).Value <> ""
    N = N + 1
Wend

Это делается для того, чтобы записать новую заявку в очередную свободную строку. Однако до этого проверяется, все ли данные введены: заявитель, преподаватель, группа и т. д. Что касается полей со списками, то для них индикатором выбора элемента в списке является значение свойства ListIndex (номер выбранного элемента): -1 означает, что в списке не выбран ни один элемент. Например, если не указан день, то следующая конструкция выводит соответствующее сообщение и процедура на этом завершается:

1
2
3
4

If Day.ListIndex = -1 Then
MsgBox ("Выберите день")
Exit Sub
End If

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

1
2
3
4

If IsNumeric(Col_Stud.Text) = False Then
MsgBox ("Введите число студентов")
Exit Sub
End If

Функция IsNumeric возвращает значение False, если входной параметр не является числом. Таким образом, если вместо количества студентов пользователь введет строку с включением букв или других символов, то процедура воспримет подобную информацию как некорректную.

Итак, мы рассмотрели так называемый блок предварительной проверки данных. Далее в процедуре располагается фрагмент, позволяющий выявить противоречия с уже имеющимися данными. Для этой проверки вводится переменная Key, которая в начале проверки принимает значение 0, а если в процессе просмотра имеющихся заявок обнаруживается накладка (по преподавателю или по группе), программно устанавливается в значение 1. Это является индикатором для вывода информационного сообщения в конце процедуры. Сама проверка технически достаточно проста — проверяется, есть ли в указанное время занятия в выбранной группе и у данного преподавателя. Однако несколько деталей мы все же поясним.

Так, в начале процедуры определены массивы Mass и Mass2. При вводе новой заявки процедура просматривает все предыдущие, проверяя, нет ли накладок в расписании по преподавателю и группе. Допустим, преподаватель может вести занятие для нескольких групп. Тогда при вводе новой заявки, если у преподавателя уже есть заявка на это время, в массиве Mass запоминается ее строка на первом листе книги, а в счетчик заявок Ch_mass добавляется 1. Например, если в результате просмотра всех имеющихся заявок в переменной Ch_mass будет число 5, это говорит о том, что у преподавателя в это время уже есть 5 других занятий (такое возможно при проведении занятия для нескольких групп одновременно). В конце процедуры на экран будет выведено одно сообщение с упоминанием обо всех этих заявках.

Массив Mass2 предназначен для проверки накладок по группе. Если у рассматриваемой группы yжe есть заявка на это время, то в массиве Mass2 запоминается номер строки этой имеющейся заявки, а в счетчик заявок Ch_mass2 добавляется 1. Например, если в результате просмотра всех имеющихся заявок в переменной Ch_mass2 будет число 1, это говорит о том, что у группы в это время уже есть другое занятие. В конце процедуры на экран выводится окно сообщения с упоминанием обо всех этих заявках. Фактически предназначение массивов — служить буфером для хранения информации, который очищается в начале процедуры.

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

1
2
3

Inform = MsgBox("Если все равно записать, то нажмите — Да. " + _
" Если продолжить редактировать, то нажмите — Отмена. " + _
" Если удалить заявку, то нажмите — Нет. ", vbYesNoCancel)

В этом месте процедуры на экран выводится стандартное диалоговое окно с кнопками Да, Нет и Отмена. В случае выбора варианта Отмена либо Нет выполняется очистка только что введенных данных на листе:

1
2
3
4
5

If inform = vbNo Or inform = vbCancel Then
For i = 1 To 16
Worksheets(1).Cells(N + 4, i).Value = ""
Next
End If

Таким образом, пользователь может отредактировать имеющуюся заявку в форме либо создать новую. А если накладок нет, то набранная заявка просто остается записанной на первом листе. На рис. 5.18 показан результат ввода одной из заявок на занятие.

Рис. 5.18. Заполненная заявка на проведение занятий

Процедура обработки щелчка на кнопке Зафиксировать и выйти принципиально не отличается от только что рассмотренной. Различия лишь в заключительном фрагменте программного кода, который отдельно приведен в листинге 5.25.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

' Листинг 5.25. Фрагмент процедуры щелчка на кнопке Зафиксировать и выйти
Private Sub Com_Wr_Out_Click()
If Key = 1 Then
    If Ch_mass > 0 Then ' Если есть противоречия по преподавателю
    Inform_str = "" ' Формирование информационной строки
    For i = 0 To Ch_mass - 1
    If Inform_str <> "" Then
    Inform_str = Inform_str + ","
    End If
    Inform_str = Inform_str + CStr(Mass(i))
    Next
    Inform_str = "Преподаватель занят. Заявка в стр. " + Inform_str
    MsgBox (Inform_str)
    End If
    If Ch_mass2 > 0 Then
' Накладка по группе
    Inform_str = "" ' Формирование информационной строки
        For i = 0 To Ch_mass2 - 1
            If Inform_str <> "" Then
                Inform_str = Inform_str + ","
            End If
            Inform_str = Inform_str + CStr(Mass2(i))
        Next
    Inform_str = "У группы уже есть занятие. Заявка в стр. " + Inform_str
    MsgBox (Inform_str)
End If
' Выбор варианта действия пользователя
Inform = MsgBox("Если все равно записать заявку - Да. " + _
    " Если продолжить редактирование - Отмена. " + _
    " Если удалить, то нажмите - Нет. ", _
    vbYesNoCancel)
If Inform = vbNo Or Inform = vbCancel Then
' Удаление только что введенных данных
    For i = 1 To 16
    Worksheets(1).Cells(N + 4, i).Value = ""
    Next
Else
Form_Add.Hide
End If
Else
Form_Add.Hide
End If
End Sub

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

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

Форма для подбора аудитории

В этом разделе мы рассмотрим один из наиболее интересных компонентов разработки — форму для подбора аудиторий для заявок на проведение учебных занятий. Сама форма с разнообразными элементами управления приведена на рис. 5.19, а для ее активизации на первом листе имеется кнопка с соответствующей надписью. В листинге 5.26 приведен текст процедуры, с помощью которой рассматриваемую электронную форму можно вывести на экран. Из текста видно, что в качестве значения свойства Name для формы подбора аудитории выбрано Form_Podbor.

1
2
3
4

' Листинг 5.26. Обработка щелчка на кнопке Подбор аудитории на первом листе
Private Sub Input_Auditoria_Click()
    Form_Podbor.Show
End Sub

Рис. 5.19. Форма подбора аудитории

Рассмотрим последовательно все основные расположенные на форме (рис. 5.19) элементы. В левом верхнем углу находится элемент управления типа ListBox (Name — Num_Str), а чуть выше располагается поясняющая подпись к нему — Номер строки. Этот список при активизации формы автоматически заполняется заявками, для которых еще не подобрана аудитория. Точнее не самими заявками, а номерами строк первого листа, где эти заявки расположены. В дальнейшем, выбрав (выделив) в списке номер заявки, мыс помощью специально разработанного алгоритма будем подбирать для нее подходящую аудиторию.

В левой части рис. 5.19 расположена группа элементов типа Label — они предназначены для отображения параметров заявки, выбранной в списке Num_Str. Эти элементы выделяются светлым цветом на фоне формы и снабжены поясняющими подписями. В табл. 5.3 приведены значения свойства Name элементов типа Label и соответствующие подписи.

Теперь перейдем к элементу управления Frame с подписью Поиск аудитории, который включает в себя несколько объектов. В верхней части этого условного контейнера объектов располагается кнопка Начать (Name — Begin_Poisk), щелчком на которой запускается алгоритм подбора аудитории для указанной в списке Num_Str заявки.

Таблица 5.3. Элементы управления Label для указания параметров заявок

Программа обнаруживает совместные заявки на одно занятие для нескольких групп и подбирает им одну (достаточно вместительную) аудиторию. Так, при выборе в списке Num_Str заявки для обслуживания, возможна ситуация, когда у указанного преподавателя аналогичное занятие есть и в других группах. Эту ситуацию программа «видит» (при выделении одной из таких фупповых заявок в списке Num_Str) и запоминает во внутренней структуре данных. В дальнейшем по щелчку на кнопке Начать производится поиск общей аудитории для всех этих групп.

Продолжим обсуждение элементов, находящихся в рамке Поиск аудитории на форме. Элемент управления типа ListBox (Name — SpAyd), куда программная процедура записывает подходящие для заявки аудитории, расположен ниже кнопки Начать. Действия пользователя заключаются в указании конкретной аудитории в этом списке (щелчком мышью следует выделить одну из подходящих аудиторий). Элемент управления типа Label (Name — Inf_vmest), расположенный рядом с подписью Вместимость, используется для отображения вместимости выбранной аудитории в списке SpAyd. Теперь пользователю следует нажать кнопку Записать аудиторию (Name — Com_Write), что позволяет зафиксировать аудиторию, выбранную в списке, в строке заявки на первом листе.

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

В правой нижней части формы расположен элемент «Надпись» (Name — Inf_z), который предназначен для вывода информационных сообщений о наличии групповых заявок. Заполнение этого элемента производится при щелчке на списке Num_Str. В этом случае указывается, какие заявки комбинируются с выбранной. Таким образом, мы рассмотрели основные элементы управления, присутствующие на форме, и теперь можем перейти к описанию программного кода.

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

' Листинг 5.27. Процедура, выполняемая при активизации формы подбора аудиторий
Private Sub UserForm_Activate()
' Подсчет числа заявок на первом листе
N = 0
While Worksheets(1).Cells(N + 4, 1).Value <> ""
N = N + 1
Wend
' Подсчет числа аудиторий
N_Rooms = 0
While Worksheets(2).Cells(N_Rooms + 2, 1).Value <> ""
N_Rooms = N_Rooms + 1
Wend
' Заполнение списка заявок
Num_Str.Clear
For i = 1 To N
Obslz = Worksheets(1).Cells(i + 3, 6).Value
If Obslz = "" Then
Num_Str.AddItem Worksheets(1).Cells(i + 3, 1).Row
End If
Next
'Заполнение списка аудиторий
If Num_Str.ListCount > 0 Then
Num_Str.ListIndex = Num_Str.ListCount - 1
End If
Com_Write.Enabled = False
End Sub

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

' Листинг 5.28. Процедура, выполняемая при щелчке на списке Num_Str
Private Sub Num_Str_Click()
Inf_z.Caption = "" 'Очистка информационного поля
Select_vibor = CInt(Num_Str.Text) 'Номер выделенной заявки
' Перенос информации о параметрах заявки на форму
Boss.Caption = Worksheets(1).Cells(Select_vibor, 1).Value
Prepod.Caption = Worksheets(1).Cells(Select_vibor, 2).Value
Day.Caption = Worksheets(1).Cells(Select_vibor, 3).Value
Times.Caption = Worksheets(1).Cells(Select_vibor, 4).Value
Col_Stud.Caption = Worksheets(1).Cells(Select_vibor, 5).Value
Group.Caption = Worksheets(1).Cells(Select_vibor, 8).Value
Disp.Caption = Worksheets(1).Cells(Select_vibor, 9).Value
Ned.Caption = ""
For i = 10 To 16
    If Worksheets(1).Cells(Select_vibor, i).Value <> "" Then
    Ned.Caption = Ned.Caption + CStr(i - 9) + ","
    End If
Next
' Подсчет числа заявок на первом листе
N = 0
While Worksheets(1).Cells(N + 4, 1).Value <> ""
    N = N + 1
Wend
N_Group = 0 ' Переменная для подсчета разных групп в одной аудитории
For i = 1 To N ' Основной цикл по заявкам
    Num = i + 3
    If Num <> Select_vibor Then ' Пропускаем выбранную заявку
    Prep = Worksheets(1).Cells(Num, 2).Value
    Day_Ned = Worksheets(1).Cells(Num, 3).Value
    Vre = Worksheets(1).Cells(Num, 4).Value
    Ob = Worksheets(1).Cells(Num, 6).Value
' Если у необслуженной заявки совпадают — день, время и преподаватель
    If Prep = Prepod.Caption And Day_Ned = Day.Caption And _
    Vre = Times.Caption And Ob = "" Then
    Zan = 0
    For j = 10 To 16
    If Worksheets(1).Cells(Num, j).Value = "*" And Worksheets(1).Cells(Select_vibor, j).Value = "*" Then
    Zan = 1 ' Если есть пересечение занятий
    Exit For
    End If
Next
If Zan = 1 Then
' Отмечаем информацию о совместных занятиях
    N_Group = N_Group + 1
    Mass_Group(N_Group) = Num
    End If
End If
End If
Next ' Завершение цикла по заявкам
' Выбранная завка сохраняется в элементе с нулевым индексом
Mass_Group(0) = Select_vibor
' Вывод информации о совместных заявках
If N_Group > 0 Then
    Inf_z.Caption = "Совместные заявки в строках "
    For i = 1 To N_Group
' Корректировка числа студентов
        Col_Stud.Caption = CInt(Col_Stud.Caption) + _
        Worksheets(1).Cells(Mass_Group(i), 5).Value
' Перечисление строк групповых занятий на 1-м листе
        Inf_z.Caption = Inf_z.Caption + Str(Mass_Group(i)) + ","
    Next
End If
End Sub

В начале процедуры в совокупность надписей на форме записывается информация о параметрах выбранной заявки. Например, строка Boss.Caption = Worksheets(1).Cells(Select_vibor, 2).Value заполняет поле заявителя на форме — элемент Label с именем Boss. Далее в цикле

1
2
3
4
5

For i = 10 To 16
    If Worksheets(1).Cells(Select_vibor, i).Value <> "" Then
    Ned.Caption = Ned.Caption + CStr(i - 9) + ","
    End If
Next

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

Для отслеживания групповых занятий в разделе General определяется счетчик количества групповых занятий (N_Group). Еще один массив предназначен для запоминания номеров строк заявок с общим одновременным занятием в одной аудитории (Mass_Group). На рис. 5.20 показана область описания общих переменных для рассматриваемой формы.

Рис. 5.20. Общие переменные формы подбора аудитории

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

В процедуре Num_Str_Click основной цикл заключается в переборе всех необслуженных заявок на первом листе. В начале этой процедуры переменная N_Group устанавливается в 0, а при обнаружении такого же занятия (у того же преподавателя) в другой группе увеличивается на 1.

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

1
2
3
4
5
6
7
8

If N_Group > 0 Then
Inf_z.Caption = "Совместные заявки в строках "
For i = 1 To N_Group
Col_Stud.Caption = CInt(Col_Stud.Caption) + _
Worksheets(1).Cells(Mass_Group(i), 5).Value
Inf_z.Caption = Inf_z.Caption + Str(Mass_Group(i)) + ","
Next
End If

Здесь производится суммирование количества студентов во всех объединяемых группах в надписи Col_Stud.

Таким образом, когда мы выделяем мышью в списке одну из заявок, то с помощью предопределенной процедуры, выполняемой по щелчку в списке Num_Str (с подписью Номер строки), выполняются следующие действия:

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

Таким образом, в результате описанных действий мы подготовились к процедуре , выполняемой по кнопке Начать, которая запускает алгоритм поиска подходящих аудиторий для указанной заявки. Эта процедура Begin_Poisk_Click приведена в листинге 5.29, а далее мы прокомментируем ее основные моменты.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

' Листинг 5.29. Процедура, выполняемая при щелчке на кнопке Начать
Private Sub Begin_Poisk_Click()
Inf_vmest.Caption = ""
SpAyd.Clear
If Num_Str.ListIndex = -1 Then
    MsgBox ("Не выбрана заявка на занятие")
    Exit Sub
End If
' Подсчет числа аудиторий
Nom = 0
While Worksheets(2).Cells(Nom + 2, 1).Value <> ""
    Nom = Nom + 1
Wend
' Подсчет числа заявок
N = 0
While Worksheets(1).Cells(N + 4, 1).Value <> ""
    N = N + 1
Wend
For i = 1 To Nom ' Цикл по аудиториям
    Ayd = Worksheets(2).Cells(i + 1, 1).Value
' Проверка вместимости
    If Worksheets(2).Cells(i + 1, 2).Value >= CInt(Col_Stud.Caption)
Then
    SpAyd.AddItem Ayd
    End If
Next
End Sub

Например, на рис. 5.21 показано, что вместе с текущей заявкой есть еще одна — совместная (одно занятие для нескольких групп). В списке аудиторий приведен перечень подходящих для указанного занятия аудиторий (результат щелчка на кнопке Начать — в этом случае подбираются аудитории подходящие по вместимости для двух групп).

Рис. 5.21. Форма подбора аудиторий

Так, в начале процедуры в листинге 5.28 проверяется, сделан ли выбор заявки, и если нет, то процедура завершается, так как обслуживать нечего:

1
2
3
4

If Num_Str.ListIndex.ListIndex = -1 Then
MsgBox ("Не выбран номер строки заявки")
Exit Sub
End If

После этого сравнивается количество мест в аудитории с числом студентов и если аудитория мала, то она также не рассматривается. Это реализуется с помощью следующей конструкции:

1

If Worksheets(2).Cells(i + 1, 2).Value >= CInt(Col_Stud.Caption) Then,

где учитывается, что во втором столбце на втором листе указано количество мест в аудитории.

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

При щелчке на списке выполняется предопределенная процедура SpAyd_Click. Основное ее назначение — фиксация (для процедуры, выполняемой по щелчку на кнопке Записать аудиторию) конкретной аудитории для заявки, а также вывод информации о ее вместимости. Сама процедура приведена в листинге 5.30.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

' Листинг 5.30. Процедура, выполняемая при щелчке на списке SpAyd
Private Sub SpAyd_Click()
' Подсчет числа аудиторий на втором листе
N = 0
While Worksheets(2).Cells(N + 2, 1).Value <> ""
    N = N + 1
Wend
For i = 2 To N + 1
    If SpAyd.Text = Worksheets(2).Cells(i, 1).Value Then
' При нахождении выбранной аудитории на втором листе
    Inf_vmest.Caption = Worksheets(2).Cells(i, 2).Value
    Exit For
    End If
Next
Com_Write.Enabled = True
End Sub

Для рассматриваемой формы осталось рассмотреть процедуру записи информации на лист Заявки (листинг 5.31).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

' Листинг 5.31. Процедура, выполняемая при щелчке на кнопке Записать
Private Sub Com_Write_Click()
If SpAyd.ListIndex = -1 Then
    MsgBox ("Не выбрана аудитория")
    Exit Sub
End If
Select_vibor = CInt(Num_Str.Text)
Inf_vmest.Caption = "" ' Очистка информационного поля
' Запись информации на первый лист
For i = 0 To N_Group
    Worksheets(1).Cells(Mass_Group(i), 6).Value = "да"
    Worksheets(1).Cells(Mass_Group(i), 7).Value = SpAyd.Text
Next
Com_Write.Enabled = False ' Запрет доступа для записи
Num_Str.Clear ' Очистка списков
SpAyd.Clear
' Заполнение списка заявок
N = 0
While Worksheets(1).Cells(N + 4, 1).Value <> ""
    N = N + 1
Wend
For i = 1 To N
If CStr(Worksheets(1).Cells(i + 3, 6).Value) = "" Then
    Num_Str.AddItem Worksheets(1).Cells(i + 3, 1).Row
End If
Next
End Sub

Лист Расписание

В этом разделе разберем одну из основных возможностей программы составление расписания. Для этого в программе используются два листа Расписание и Потоки.

Лист Потоки является вспомогательным и используется для задания потоков и групп, для которых мы намереваемся построить расписание занятий. Поэтому созданные нами ранее две группы объединим в общий поток и внесем в лист Потоки (рис. 5.22). Структура листа Потоки следующая: в первом столбце располагаются потоки, а последующих столбцах соответствующей строки — группы, входящие в поток.

Рис. 5.22. Организация листа Потоки

Далее необходимо создать лист Расписание (рис. 5.23). Здесь три элемента управления: одна кнопка и два поля со списком. В полях со списком пользователю следует выбрать интервалы недель и затем щелкнуть на кнопке Заполнить. В результате на листе отобразится расписание для групп, указанных на листе Потоки.

Рис. 5.23. Организация листа Расписание

На рис. 5.24 показан результат заполнения листа Расписание. И теперь разберем необходимые технические шаги для этого. Для работы с расписанием нам необходимо иметь заполненные списки недель. Данное заполнение мы выполним при активизации листа Расписание (листинг 5.32).

1
2
3
4
5
6
7
8
9
10
11
12
13

' Листинг 5.32. Процедура, выполняемая при активизации листа Расписание
Private Sub Worksheet_Activate()
N = 0
While Worksheets(2).Cells(N + 2, 3).Value <> ""
    N = N + 1
Wend
L1.Clear
L2.Clear
For i = 1 To N
    L1.AddItem Worksheets(2).Cells(i + 1, 3).Value
    L2.AddItem Worksheets(2).Cells(i + 1, 3).Value
Next
End Sub

Рис. 5.24. Формирование листа Расписание

Значения свойства Name элементов типа «Поле со списком» выберем L1 и L2 соответственно, а для кнопки Заполнить установим у аналогичного свойства значение Zapolnit. В листинге 5.33 приведена ключевая процедура, которая формирует заполнение листа Расписание.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123

' Листинг 5.33. Процедура, выполняемая при щелчке на кнопке Заполнить
Private Sub Com_Start_Click()
Dim Mass(10) As Integer
' Очистка предыдущего расписания
Range("a5:AZ100").Select
Selection.ClearContents
Selection.Interior.ColorIndex = 0
' Подсчет числа потоков
NP = 0
While Worksheets("Потоки").Cells(NP + 2, 1).Value <> _
"" Or Worksheets("Потоки").Cells(NP + 2, 2).Value <> ""
NP = NP + 1
Wend
' Подсчет количества групп, входящих в поток
For i = 1 To NP
    Mass(i) = 0
    j = 0
    While Worksheets("Потоки").Cells(i + 1, j + 2).Value <> ""
    j = j + 1
    Wend
    Mass(i) = j
Next
' Подсчет числа аудиторий
N_Rooms = 0
While Worksheets("Нач. инф.").Cells(N_Rooms + 2, 1).Value <> ""
    N_Rooms = N_Rooms + 1
Wend
Sct = 0 ' Счетчик столбцов
'Заполнение названий групп и потоков
For i = 1 To NP
    Potok = Worksheets("Потоки").Cells(i + 1, 1).Value
    For j = 1 To Mass(i)
    Cells(5, Sct + 3).Value = Potok
    Cells(6, Sct + 3).Value = Worksheets("Потоки").Cells(i + 1, 1).Value
    Sct = Sct + 1
    Next
Next
' Подсчет числа дней
N_Days = 0
While Worksheets("Нач. инф.").Cells(N_Days + 2, 4).Value <> ""
N_Days = N_Days + 1
Wend
'Подсчет числа занятий в течение дня
N_Times = 0
While Worksheets("Нач. инф.").Cells(N_Times + 2, 5).Value <> ""
    N_Times = N_Times + 1
Wend
' Количество строк в расписании
DayTimes = N_Times * N_Days
' Заполнение первых двух столбцов
Stroka = 7
For i = 1 To N_Days
    For j = 1 To N_Times
    Cells(Stroka, 1).Value = Worksheets("Нач. инф.").Cells(i + 1, 4).Value
    Cells(Stroka, 2).Value = Worksheets("Нач. инф.").Cells(j + 1, 5).Value
    Stroka = Stroka + 1
    Next
Next
' Вычисление числа записей на первом листе
N = 0
While Worksheets("Заявки").Cells(N + 4, 1).Value <> ""
    N = N + 1
Wend
' Цикл по строкам первого листа
For i = 1 To N
    Days = CStr(Worksheets("Заявки").Cells(i + 3, 3).Value)
    Times = CStr(Worksheets("Заявки").Cells(i + 3, 4).Value)
    Group = CStr(Worksheets("Заявки").Cells(i + 3, 8).Value)
    Prepod = CStr(Worksheets("Заявки").Cells(i + 3, 2).Value)
    Disp = CStr(Worksheets("Заявки").Cells(i + 3, 9).Value)
    Aud = CStr(Worksheets("Заявки").Cells(i + 3, 7).Value)
    Obs = CStr(Worksheets("Заявки").Cells(i + 3, 6).Value)
    Indic = 0
' Попадает ли заявка в указанный интервал недель?
    For j = CInt(L1.Text) To CInt(L2.Text)
If CStr(Worksheets(1).Cells(i + 3, 9 + j).Value) = "*" Then
Indic = 1
    Exit For
    End If
Next
' Если заявка попадает в указанный интервал недель
If Indic = 1 Then
' Цикл по столбцам
For im = 1 To Sct
If Group = CStr(Cells(5, im + 2).Value) Or Group = CStr(Cells(6, im + 2).Value) Then
Stolbec = im + 2
For j = 1 To DayTimes
If CStr(Cells(6 + j, 1).Value) = Days And CStr(Cells(6 + j, 2).Value) = Times Then
    If Cells(6 + j, Stolbec).Value <> "" Then
    Cells(6 + j, Stolbec).Value = _
    Cells(6 + j, Stolbec).Value + Chr(10)
    End If
    Cells(6 + j, Stolbec).Value = Cells(6 + j, Stolbec).Value + Disp + " "
    Cells(6 + j, Stolbec).Value = Cells(6 + j, Stolbec).Value + Prepod
    Cells(6 + j, Stolbec).Value = Cells(6 + j, Stolbec).Value + " Ауд." + Aud + Chr(10)
    For jj = CInt(L1.Text) To CInt(L2.Text)
    ask = CStr(Worksheets(1).Cells(i + 3, jj + 9).Value)
    If ask = "*" Then
    Cells(6 + j, Stolbec).Value = Cells(6 + j, Stolbec).Value + " " + Str(jj) + ","
    End If
    Next
    End If
    Next
    End If
    Next
    End If
Next
Cvet1 = 35 ' Оформление цветом
Cvet2 = 40
Cell1= Cells(7, 1).Value
For i = 7 To 100
    Cel12= Cells(i, 1).Value
    If Cell1 <> Cell2 Then
    Vr_cvet = Cvet1
    Cvet1 = Cvet2
    Cvet2 = Vr_cvet
    End If
        Cel11= Cel12
    Rows(i).Select
    Selection.Interior.ColorIndex = Cvet1
Next
Range("A7").Select
End Sub

Разберем некоторые конструкции процедуры, выполняемой по щелчку на кнопке Заполнить. Вначале производится очистка имеющейся старой информации на листе:

1
2

Range("A5:Z100").Select
Selection.ClearContents

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

1
2
3
4
5

NP = 0
While Worksheets("Потоки").Cells(NP + 2, 1).Value <> _
"" Or Worksheets("Потоки").Cells(NP + 2, 2).Value <> ""
NP = NP + 1
Wend

Затем в массиве Mass вычисляется, сколько групп в каждом потоке. Так, в элементе Mass (1) подсчитывается количество групп в первом выводимом в расписании потоке, в Mass (2) — во втором, и т. д. Процесс заполнения массива Mass выполняется следующим образом:

1
2
3
4
5
6
7
8

For i = 1 To NP
Mass(i) = 0
j = 0
While Worksheets("Потоки").Cells(i + 1, j + 2).Value <> ""
j = j + 1
Wend
Mass(i) = j
Next

Далее в пятую и шестую строки листа Расписание выводятся названия потоков и групп, для которых мы его составляем. Аналогичным образом в первые два столбца листа программно заносятся учебные дни и время начала занятий. После этого выполняется цикл но всем строкам на первом листе, и если при этом очередная заявка относится к одной из рассматриваемых групп, то она выводится в соответствующей ячейке (которая определяется днем, временем, группой либо потоком).

Если подвести некий промежуточный итог, то можно сказать следующее: мы имеем почти готовую версию расписания. На рис. 5.24 поля со списками позволяют выбрать интервал недель, для которого мы хотим составить расписание. Фактически на этом функциональные возможности листа можно было бы считать исчерпанными. Однако мы реализуем дополнительные сервисные возможности по вводу заявок. В большой доли случаев приходится копировать занятие по определенной дисциплине. Часто также приходится какие-то заявки удалять. Реализуем это с помощью дополнительных элементов управления. На рис. 5.25 добавлены:

• кнопка Копировать (Name — Com_Copy);
• кнопка Вставить (Name — Com_Paste);
• кнопка Удалить (Name — Com_Delete);
• текстовое окно (Name — Inform).

Рис. 5.25. Добавление элементов управления на лист Расписание

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

' Листинг 5.34. Процедура, выполняемая при щелчке на кнопке Копировать
Private Sub CommandButton4_Click()
NumRow = ActiveCell.Row
NumCol = ActiveCell.Column
Group = CStr(Cells(6, NumCol))
Days = CStr(Cells(NumRow, 1))
Times = CStr(Cells(NumRow, 2))
ColZaavok = 0
' Переменная для подсчета выделенных заявок
N = 0 ' Расчет числа заявок на 1-м листе
While Worksheets(1).Cells(N + 4, 1).Value <> ""
    N = N + 1
Wend
For i = 1 To N
    Day1 = CStr(Worksheets(1).Cells(i + 3, 3).Value)
    Time1 = CStr(Worksheets(1).Cells(i + 3, 4).Value)
    Group1 = CStr(Worksheets(1).Cells(i + 3, 8).Value)
' Если параметры заявки совпадают
    If Time1 = Times And Day1 = Days And Group = Group1 Then
    For j = CInt(L1.Text) To CInt(L2.Text)
        Zan = Worksheets("Заявки").Cells(i + 3, 9 + j).Value
        If Zan = "*" Then
            ColZaavok = ColZaavok + 1
            MassZaavok(ColZaavok) = i + 3
            Exit For
        End If
        Next
    End If
Next
Inform.Text = ""
' Очистка текстового окна для сообщений
If ColZaavok > 0 Then
' Вывод номеров строк заявок в окно сообщений
    For i = 1 To ColZaavok
    Number.Text = Number.Text + Str(MassZaavok(i)) + ","
    Next
Else
    Number.Text = ""
End If
End Sub

Данная процедура фиксирует в переменной ColZaavok количество заявок в выделенной ячейке (как правило, эго одна заявка, однако может быть две или три). Также в массиве MassZaavok запоминаются номера строк первого листа, в которых размещаются отмеченные заявки. Разместить эти переменную и массив необходимо в общей области (рис. 5.26)

Рис. 5.26. Описание переменной и массива в общей области

Теперь необходимо разместить процедуру обработки щелчка на кнопке Вставить. Она представлена в листинге 5.35.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

' Листинг 5.35. Процедура, выполняемая при щелчке на кнопке Вставить
Private Sub Com_Paste_Click()
NewRow = ActiveCell.Row
NewCol = ActiveCell.Column
If Inform.Text = "" Then
    Exit Sub
End If
Ngroup = 0
While Worksheets(2).Cells(Ngroup + 2, 8).Value <> ""
    Ngroup = Ngroup + 1
Wend
For i = 1 To ColZaavok ' Цикл по числу скопированных заявок
    N = 0
    While Worksheets("Заявки").Cells(N + 4, 1).Value <> ""
    N = N + 1
    Wend
    Worksheets(1).Cells(N + 4, 1).Value = _
    Worksheets(1).Cells(MassZaavok(i), 1).Value
    Worksheets(1).Cells(N + 4, 2).Value = _
    Worksheets(1).Cells(MassZaavok(i), 2).Value
    Worksheets(1).Cells(N + 4, 3).Value = CStr(Cells(NewRow, 1))
    Worksheets(1).Cells(N + 4, 4).Value = Cells(NewRow, 2)
    For j = 1 To Ngroup
    If Worksheets(2).Cells(j + 1, 8).Value = Cells(6, NewCol) Then
        Exit For
    End If
    Next
    Worksheets(1).Cells(N + 4, 5).Value = Worksheets(2).Cells(ib + 1, 9).Value
    Worksheets(1).Cells(N + 4, 8).Value = CStr(Cells(6, NewCol))
    For m = 9 To 16
    Worksheets(1).Cells(N + 4, m).Value = Worksheets(1).Cells(MassZaavok(i), m).Value
    Next
    Cells(NewRow, NewCol).Value = _
    Cells(NewRow, NewCol).Value + Worksheets(1).Cells(N + 4,
    9).Value + _
    " " + Worksheets(1).Cells(N + 4, 2).Value + Chr(10)
    For l = CInt(L1.Text) To CInt(L2.Text)
    If CStr(Worksheets(1).Cells(i + 3, l + 9).Value) = "*" Then
    Cells(NewRow, NewCol).Value = _
    Cells(NewRow, NewCol).Value + " " + Str(l) + ","
    End If
    Next
Next
End Sub

Теперь мы можем легко добавить еще одно занятие. На рис. 5.27 добавлено еще одно занятие к показанным на рис. 5.24. Для этого мы сначала выделили ячейку D14 и щелчком на кнопке Копировать зафиксировали параметры заявки во внутренней структуре данных. После этого необходимо выделить ячейку D10 и щелкнуть на кнопке Вставить. В результате на первом листе сформируется новая заявка и мы ее увидим на листе Расписание.

Рис. 5.27. Выполнение изменений н расписании занятий

На рис. 5.27 присутствует кнопка Удалить. Уже из названия следует, что она предназначена для удаления заявок. Для этого требуется лишь предварительно выделить заполненную ячейку расписания. В листинге 5.36 приведена процедура, выполняемая по щелчку на кнопке Удалить.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

' Листинг 5.36. Процедура, выполняемая при щелчке на кнопке Удалить
Private Sub Com_Delete_Click()
Dim Mass(10) As Integer
NumRow = ActiveCell.Row
NumCol = ActiveCell.Column
Group = CStr(Cells(6, NumCol))
Days = CStr(Cells(NumRow, 1))
Times = CStr(Cells(NumRow, 2))
ColZaavok = 0
N = 0
While Worksheets(1).Cells(N + 4, 1).Value <> ""
    N = N + 1
Wend
For i = 1 To N
If CStr(Worksheets(1).Cells(i + 3, 4).Value) = Times And _
Days = CStr(Worksheets(1).Cells(i + 3, 3).Value) _
And Group = CStr(Worksheets(1).Cells(i + 3, 8).Value) Then
    For j = CInt(L1.Text) To CInt(L2.Text)
    If Worksheets(1).Cells(i + 3, 9 + j).Value = "*" Then
    ColZaavok = ColZaavok + 1
    Mass(ColZaavok) = i + 3
    Exit For
    End If
    Next
End If
Next
Inform.Text = ""
For i = ColZaavok To 1 Step -1
	j = Mass(i)
    Worksheets(1).Rows(j).Delete
    Inform.Text = "Заявки удалены"
Next
Cells(NumRow, NumCol).Value = ""
End Sub

Лист Загрузка

Рассмотрим еще один лист, который дает представление о загрузке аудиторного фонда. Первый шаг заключается в несложном оформлении листа и создании двух элементов управления: поля со списком и кнопки (рис. 5.28).

Рис. 5.28. Организация листа Загрузка

Для заполнения номерок недель в поле со списком мы воспользуемся процедурой, выполняемой при активизации рассматриваемого листа.

1
2
3
4
5
6
7
8
9
10
11

' Листинг 5.37. Процедура, выполняемая при активизации листа Загрузка
Private Sub Worksheet_Activate()
N = 0
While Worksheets(2).Cells(N + 2, 3).Value <> ""
    N = N + 1
Wend
L1.Clear
For i = 1 To N
    L1.AddItem Worksheets(2).Cells(i + 1, 3).Value
Next
End Sub

Теперь пользователю необходимо выбрать интересующую его учебную неделю, после чего щелчок на кнопке Заполнить позволяет получить на листе информацию об использовании аудиторного фонда (рис. 5.29). Для этого необходимо оформить процедуру обработки щелчка на кнопке Заполнить в виде, представленном в листинге 5.38.

Рис. 5.29. Просмотр загрузки аудиторного фонда

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121

' Листинг 5.38. Процедура, выполняемая при щелчке на кнопке Заполнить
Private Sub Zapolnit_Click()
' Установка цветов для обозначения факультетов
Dim Colors(50) As Integer
Colors(1) = 4
Colors(2) = 22
Colors(3) = 19
Colors(4) = 24
Colors(5) = 26
For i = 6 To 50
    Colors(i) = 28
Next
If L1.ListIndex = -1 Then ' Выход, если не выбрана неделя
    MsgBox (" Не выбрана неделя ")
    Exit Sub
End If
Range("A5:AZ100").Select
' Очистка области данных
Selection.ClearContents
' Подсчет количества учебных дней в неделе
N_Day = 0
While Worksheets(2).Cells(N_Day + 2, 4).Value <> ""
    N_Day = N_Day + 1
Wend
' Подсчет количества занятий в течение дня
N_Times = 0
While Worksheets(2).Cells(N_Times + 2, 5).Value <> ""
    N_Times = N_Times + 1
Wend
' Подсчет количества аудиторий
N_Ayd = 0
While Worksheets(2).Cells(N_Ayd + 2, 1).Value <> ""
    N_Ayd = N_Ayd + 1
Wend
DaysTimes = N_Day * N_Times
N_Boss = 0 ' Подсчет заявителей
While Worksheets(2).Cells(N_Boss + 2, 6).Value <> ""
    N_Boss = N_Boss + 1
Wend
Range("B7:AZ100").Select
With Selection.Interior
' Заливка белым цветом области вывода
    .ColorIndex = 0
    .Pattern = xlSolid
End With
For i = 1 To 10
Cells(2, 2 + i * 2).Select
With Selection.Interior ' Установка обозначений цветов
.ColorIndex = colors(i) ' заявителей
.Pattern = xlSolid
End With
' Установка подписей заявителей для соответствующих цветов
Cells(1, 2 + i * 2).Value = Worksheets(2).Cells(i + 1, 6).Value
Next
' Подсчет количества строк с заявками на 1-м листе
N = 0
While Worksheets(1).Cells(N + 4, 1).Value <> ""
    N = N + 1
Wend
Stroka = 7
' Данные на листе размещаются начиная с седьмой строки
For i = 1 To N_Ayd ' Установка подписей аудиторий
Cells(Stroka, 1).Value = Worksheets(2).Cells(i + 1, 1).Value
Stroka = Stroka + 1
Next
St = 1
For i = 1 To N_Day ' Установка подписей занятий
    For j = 1 To N_Times
    St = St + 1
    Cells(5, St).Value = Worksheets(2).Cells(i + 1, 4).Value
    Cells(6, St).Value = Worksheets(2).Cells(j + 1, 5).Value
    Next
Next
For i = 1 To DaysTimes
    For j = 1 To N_Ayd
    Cells(6 + j, i + 1) = 0 'Инициализация ячеек
    Next
Next
For i = 4 To N + 3 ' Цикл по строкам заявок
    If CStr(Worksheets(1).Cells(i, 6).Value) = "да" Then
' Выполнение условия по обслуживанию заявки
    Stroka = 0
    For m = 1 To N_Ayd
    If CStr(Worksheets(1).Cells(i, 7).Value) = _
    CStr(Cells(m + 6, 1).Value) Then
    Stroka = m + 6
    Exit For
    End If
    Next
If Stroka > 0 And _
CStr(Worksheets(1).Cells(i, CInt(L1.Text) + 9).Value) ="*" Then
' Если есть строка с указанной аудиторией
    For m = 1 To DaysTimes
' Нахождение столбца на листе для помещения заявки
    If CStr(Worksheets(1).Cells(i, 3).Value) = _
    CStr(Cells(5, 1 + m).Value) Then
    If CStr(Worksheets(1).Cells(i, 4).Value) = _
    CStr(Cells(6, 1 + m).Value) Then
        Stolbec = 1 + m
        Exit For
    End If
End If
Next
Nomer = 1
For l = 1 To N_Boss 'Определение заявителя в заявке
    If CStr(Worksheets(1).Cells(i, 1).Value) _
    = CStr(Worksheets(2).Cells(l + 1, 6).Value) Then
    Nomer = l
    Exit For
End If
Next
Cells(Stroka, Stolbec).Value = Cells(Stroka, Stolbec).Value + Worksheets(1).Cells(i, 5).Value
Cells(Stroka, Stolbec).Select
With Selection.Interior
    .ColorIndex = colors(nomer) ' Установка заливки
    .Pattern = xlSolid ' для ячейки
End With
End If
End If
Next
End Sub

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