Как сделать в excel расписание


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

При необходимости можно легко самостоятельно скорректировать приведенные процедуры на большую продолжительность.

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

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

Name Caption Input_New Ввод новой заявки Input_Auditoria Подбор аудитории

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

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

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

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

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

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

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

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

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

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

Name Подпись Boss Декан Prepod Преподаватель Day День Time Время Group Группа Disp Дисциплина

В правой части формы расположено текстовое окно Количество студентов (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

' Листинг 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

' Листинг 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.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

' Листинг 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

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

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

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)

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

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.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.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.26. Обработка щелчка на кнопке Подбор аудитории на первом листе Private Sub Input_Auditoria_Click() Form_Podbor.Show End Sub

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

Рис. 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 для указания параметров заявок

Name Подпись Boss Декан Prepod Преподаватель Day День Times Время Group Группа Disp Дисциплина Col_Stud Количество студентов

Программа обнаруживает совместные заявки на одно занятие для нескольких групп и подбирает им одну (достаточно вместительную) аудиторию. Так, при выборе в списке 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

' Листинг 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

' Листинг 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

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. Общие переменные формы подбора аудитории

Рис. 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

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.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 показано, что вместе excel с текущей заявкой есть еще одна — совместная (одно занятие для нескольких групп). В списке аудиторий приведен перечень подходящих для указанного занятия аудиторий (результат щелчка на кнопке Начать — в этом случае подбираются аудитории подходящие по вместимости для двух групп).

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

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

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

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

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,

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.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.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.22. Организация листа Потоки

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

Рис. 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.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. Формирование листа Расписание

Рис. 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

' Листинг 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

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

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

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

' Листинг 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.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.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.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.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. Организация листа Загрузка

Рис. 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.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. Просмотр загрузки аудиторного фонда

Рис. 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

' Листинг 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

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


Источник: http://excelexpert.ru/kak-pri-pomoshhi-excel-avtomatizirovat-process-sostavleniya-raspisaniya-zanyatij-uchebnom-zavedenii



Рекомендуем посмотреть ещё:


Закрыть ... [X]

Как сделать список в Excel 2007 Дорога к Бизнесу за Сумка из остатков кожи своими руками


Как сделать в excel расписание Как сделать в excel расписание Как сделать в excel расписание Как сделать в excel расписание Как сделать в excel расписание Как сделать в excel расписание Как сделать в excel расписание Как сделать в excel расписание Как сделать в excel расписание