Проверка дат в VBA на основе первоначального заполнения табеля рабочего времени.
На текущем занятии рассмотрим, как происходит работа с датами в VBA на основе первоначального заполнения табеля. Необходимо провести заполнение табеля рабочего времени шаблонными значениями с учетом выходных дней конкретного месяца.
На практике, если не учитывать посменную работу, существует два варианта рабочей недели – 5 и 6 дневная. Необходимо сделать так, чтобы при вызове макроса весь диапазон дат в табеле автоматически заполнялся эталонными значениями с учетом режима работы и выходных дней.
Зададим начальные условия. При шестидневной неделе работа происходит с понедельника по пятницу по 7 часов, а в субботу необходимо отработать 4 часа. Выходной только воскресенье. При пятидневной недели с понедельника по пятницу отрабатывается по восемь часов, а суббота и воскресенье – выходные.
Приступаем к работе. Прежде всего требуется обеспечить запуск макроса в любом файле, на любом листе Excel. Поэтому придется отказаться от использования элементов управления на листе. Кроме этого, не совсем удобно использовать и форму, так как в нашем случае она будет дублировать функции. Чтобы этого избежать, мы создадим два макроса, привязав каждый к отдельной кнопке на настраиваемой панели.
Определимся с алгоритмом.
- Пользователь вводит начальную дату.
- Макрос проходит построчно диапазон табеля
- Происходит формирование даты из индекса ячейки в строке, а также месяца и года введённой даты.
- Для всех полученных дат выполняется проверка даты на валидность (существование).
- Рассчитывается номер дня недели для сформированной даты, если она существует. Если даты нет, то выводится пустое значение.
- Выполняется проверка полученного дня недели на соответствие выбранному типу рабочей недели.
- Результаты проверки заносятся в очередную ячейку.
Переходим непосредственно к созданию процедуры макроса. Определяемся с переменными. Понадобятся:
- Переменные – счетчики. Так как обрабатываться будут и строки, и столбцы выбранного диапазона, то таких переменных будет две. Обозначим их как I и J и присвоим им тип integer;
- Переменные для определения координат диапазона табеля. Присвоим таким переменным тоже тип Integer и зададим имена StRow и LRow для строк, StCol и LCol для столбцов;
- Для определения координат текущей ячейки укажем переменные iR и iC для номеров строки и столбца текущей ячейки соответственно. Тип тоже integer;
- Для дат применим переменные StartDate и NDate. В первую занесем первоначальную дату расчета, во вторую внесем сформированную дату для поверяемой ячейки;
- Далее понадобятся две отдельные переменные типа Integer. Переменную Days назначим для хранения индекс ячейки в строке, а в MyDay буде хранить номер дня недели для текущей ячейки.
- Наконец, потребуется переменная типа String для хранения сформированной строки даты. Обозначим ее как MyDate. Кроме этого, понадобится строковая переменная DsT для хранения текущего разделителя элементов дат.
Открываем редактор VBA, нажав в Excel сочетание клавиш Alt+F8. Вставляем новый модуль. Командой Insert → module и создадим каркас новой процедуры. Начнем с пятидневной рабочей недели, так как в ней условие проще.
Sub TabelList_5Days()
End Sub
Работаем внутри процедуры.
Определяемся с переменными. Конечно, их можно не объявлять, но не будем создавать код, забивающий память.
Dim StRow As Integer, LRow As Integer
Dim StCol As Integer, LCol As Integer
Dim i As Integer, j As Integer, Ir As Integer, Ic As Integer
Dim Days As Integer, MyDay As Integer
Dim StartData As Date, Mydate As Date
Dim DAteStr as string, MyDay as string,DsT as String
Узнаем системный разделитель для дат и получаем начальную дату с помощью InputBox
DsT = Format(Date, “/”)
StartData = InputBox(“Ведите начальную дату”, “Дата расчета табеля”)
Находим координаты табеля, предварительно выделив нужный диапазон.
StRow = Selection.Row: LRow = StRow + Selection.Rows.Count – 1
StCol = Selection.Column: LCol = Selection.Columns.Count + StCol – 1
Начинаем перебирать строки. После выбора строки присваиваем индексу первой ячейки в проверяемой строку значение 1 и переходим к перебору ячеек в строке.
For Ir = StRow To LRow
Days = 1
For Ic = StCol To LCol
Сформируем дату в виде строки из индекса ячейки, месяца и года даты, введенной в начале с помощью InputBox:
DateStr = Days & DsT & Month(StartData) & DsT & Year(StartData)
Проверяем дату на существование и если дата существует, то преобразуем значение в реальную дату и находим день недели для этой даты. Если полученное значение меньше 6, то записываем в ячейку число 8, иначе записываем букву «В», после чего увеличиваем значение Days на единицу и переходим к следующей ячейке в строке
If IsDate(DAteStr) Then
Mydate = DateValue(DAteStr)
MyDay = Weekday(Mydate, vbMonday)
If MyDay
Теперь проверяем записанное данные. Если это число, то присваиваем числовой формат без дробной части а если текст ( буква «В»), то назначаем текстовый формат с полужирным шрифтом и зеленой заливкой. Конечно, все это не обязательно, но позволяет сразу выделить визуально выходные дни.
If IsNumeric(Cells(Ir, Ic).Value) Then
Cells(Ir, Ic).NumberFormat = “0”
Else
Cells(Ir, Ic).NumberFormat = “@”
Cells(Ir, Ic).Font.Bold = True
Cells(Ir, Ic).Interior.Color = RGB(0, 200, 0)
End If
Если же даты не существует, то записываем пустое значение. После проверки на существование даты и выполнения, соответствующих этому операторов, увеличиваем индекс Days на единицу и переходим к следующей ячейке в строке. Закончив строку, перемещаемся ниже и начинаем проверку заново.
End If
Days = Days + 1
Next Ic
Next Ir
Приведем еще раз код макроса полностью с пояснениями:
Sub TabelList_5Days()
Dim StRow As Integer, LRow As Integer
Dim StCol As Integer, LCol As Integer
Dim i As Integer, j As Integer, Ir As Integer, Ic As Integer
Dim Days As Integer, MyDay As Integer
Dim StartData As Date, Mydate As Date
Dim DAteStr As String, DsT As String
StartData = InputBox(“Введите начальную дату”, “Дата расчета”)
‘узнаем системный разделитель в датах
DsT = Format(Date, “/”)
‘находим координаты табеля, предварительно выдельв нужные ячейки
StRow = Selection.Row: LRow = StRow + Selection.Rows.Count – 1
StCol = Selection.Column: LCol = Selection.Columns.Count + StCol – 1
‘начинаем перебирать строки
For Ir = StRow To LRow
Days = 1
‘в каждой строке перебираем колонки
For Ic = StCol To LCol
‘формируем очередную дату в виде строки
DAteStr = Days & DsT & Month(StartData) & DsT & Year(StartData)
‘проверяем дату на существование. если она существует, то…
If IsDate(DAteStr) Then
‘преобразуем строку в дату
Mydate = DateValue(DAteStr)
‘узнаем день недели в виде числового значения
MyDay = Weekday(Mydate, vbMonday)
‘если он меньше 6, записываем 8, иначе записываем «В»
If MyDay
‘Делаем проверку записанных данных.
‘Если это число, то делаем числовой формат
‘Если это текст, делаем формат текствый и полужирный шрифт, а
‘затем заливаем ее зеленым цветом. Конечно, последнее не обязательно
If IsNumeric(Cells(Ir, Ic).Value) Then
Cells(Ir, Ic).NumberFormat = “0”
Else
Cells(Ir, Ic).NumberFormat = “@”
Cells(Ir, Ic).Font.Bold = True
Cells(Ir, Ic).Interior.Color = RGB(0, 200, 0)
End If
‘Если же даты не существует, записываем
‘в очередную ячейку пустое значение
Else
Cells(Ir, Ic).Value = “”
End If
Days = Days + 1
Next Ic
Next Ir
End Sub
Код шестидневной рабочей недели практически идентичено, но требует дополнительноцй проверки на номер дня недели для субботы. Сразу приведем код
Sub TabelList_6Days()
‘определяем переменные
Dim StRow As Integer, LRow As Integer
Dim StCol As Integer, LCol As Integer
Dim i As Integer, j As Integer, Ir As Integer, Ic As Integer
Dim Days As Integer, MyDay As Integer
Dim StartData As Date, Mydate As Date
Dim DAteStr As String, DsT As String
‘задаем контрольную дату
StartData = InputBox(“Введите начальную дату”, “Дата расчета”)
‘узнаем системный разделитель в датах
DsT = Format(Date, “/”)
‘находим координаты табеля, предварительно выдельв нужные ячейки
StRow = Selection.Row: LRow = StRow + Selection.Rows.Count – 1
StCol = Selection.Column: LCol = Selection.Columns.Count + StCol – 1
For Ir = StRow To LRow
Days = 1
‘в каждой строке перебираем колонки
For Ic = StCol To LCol
‘формируем очередную дату в виде строки
DAteStr = Days & DsT & Month(StartData) & DsT & Year(StartData)
‘проверяем дату на существование. если она существует, то…
If IsDate(DAteStr) Then
‘преобразуем строку в дату
Mydate = DateValue(DAteStr)
‘узнаем день недели в виде числового значения
MyDay = Weekday(Mydate, vbMonday)
‘если он меньше 6, записываем 7, если 6, то запишем значение 4 иначе запишем “В”
If MyDay
Cells(Ir, Ic).Value = 7
ElseIf MyDay = 6 Then
Cells(Ir, Ic).Value = 4
Else
Cells(Ir, Ic).Value = “В”
End If
‘Делаем проверку записанных данных.
‘Если это число, то делаем числовой формат
‘Если это текст, делаем формат текствый и полужирный шрифт, а
‘затем заливаем ее зеленым цветом. Конечно, последнее не обязательно
If IsNumeric(Cells(Ir, Ic).Value) Then
Cells(Ir, Ic).NumberFormat = “0”
Else
Cells(Ir, Ic).NumberFormat = “@”
Cells(Ir, Ic).Font.Bold = True
Cells(Ir, Ic).Interior.Color = RGB(0, 200, 0)
End If
‘Если же даты не существует, записываем
‘в очередную ячейку пустое значение
Else
Cells(Ir, Ic).Value = “”
End If
Days = Days + 1
Next Ic
Next Ir
End Sub
Все вроде бы в порядке, но макросы будут работать только в текущей книге. Необходимо же, чтобы эти макросы работали в любом файле. Мы пойдем на хитрость. На вкладке «Вид» в блоке макросы нажмем кнопку «Запись макроса», выберем сохранение «В личной книге макросов» и нажмем ОК.
После этого сразу перейдём на блок «Макросы» вкладки «Вид» и выберем команду «Остановить запись». В результате получаем доступ к личной книге макросов Personal. Открываем ее, перетаскиваем туда наш модуль, после чего удаляем его из текущей книги.
Было
Стало
Обратите внимание, что модуль Table_5_6 ( так я его назвал) теперь находится в Personal.xlsb
Подведем итог. Наши макросы готовы, теперь надо сделать их удобными для запуска. Для этого можно использовать быстрые клавиши (Alt + F8), назначить макросы кнопке, добавить их в панель быстрого доступа и так далее. Мы же создадим отдельную панель для наших макросов и поместим кнопки вызова на нее.
Переходим на вкладку «файл» и открываем параметры, после чего переходим на вкладку «Настроить ленту». Нажимаем кнопку «Создать вкладку»
Создается вкладка с новой группой. Переименовываем их по желанию. Я назвал вкладку как «Макросы», а группу как «Табель», кроме этого, задал группе иконку в виде двух папок. В дальнейшем группы и вкладки можно добавлять.
Осталось совсем немного. Выбрав добавленную группу , вверху в списке «выбрать команды» выбираем вариант «макросы», после чего перетаскиваем наши макросы в группу нажатием кнопки «Добавить»
Осталось изменить внешний вид добавленных кнопок и их названия, после чего сохранить изменения, нажав кнопки ОК дважды.
Теперь для создания табеля достаточно выделить диапазон для будущего табеля и нажать нужную кнопку на добавленной панели.
На этом текущее занятие подошло к концу. Желаем всем удачного дня и светлого настроения. До встречи на наших занятиях в учебном центре «Зерде»!
Скачать модуль, рассмотренный на уроке здесь.
