Заполнение табеля в VBA. Формирование и проверка дат.

Проверка дат в VBA на основе первоначального заполнения табеля рабочего времени.

табель в VBA - 1

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

На практике, если не учитывать посменную работу, существует два варианта рабочей недели – 5 и 6 дневная. Необходимо сделать так, чтобы при вызове макроса весь диапазон дат в табеле автоматически заполнялся эталонными значениями с учетом режима работы и выходных дней.

Зададим начальные условия. При шестидневной неделе работа происходит с понедельника по пятницу по 7 часов, а в субботу необходимо отработать 4 часа. Выходной только воскресенье. При пятидневной недели с понедельника по пятницу отрабатывается по восемь часов, а суббота и воскресенье – выходные.

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

Определимся с алгоритмом.

  1. Пользователь вводит начальную дату.
  2. Макрос проходит построчно диапазон табеля
  3. Происходит формирование даты из индекса ячейки в строке, а также месяца и года введённой даты.
  4. Для всех полученных дат выполняется проверка даты на валидность (существование).
  5. Рассчитывается номер дня недели для сформированной даты, если она существует. Если даты нет, то выводится пустое значение.
  6. Выполняется проверка полученного дня недели на соответствие выбранному типу рабочей недели.
  7. Результаты проверки заносятся в очередную ячейку.

Переходим непосредственно к созданию процедуры макроса. Определяемся с переменными. Понадобятся:

  • Переменные – счетчики. Так как обрабатываться будут и строки, и столбцы выбранного диапазона, то таких переменных будет две. Обозначим их как 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 < 6 Then Cells(Ir, Ic).Value = 8 Else Cells(Ir, Ic).Value = “В”

Теперь проверяем записанное данные. Если это число, то присваиваем числовой формат без дробной части а если текст ( буква «В»), то назначаем текстовый формат с полужирным шрифтом и зеленой заливкой. Конечно, все это не обязательно, но позволяет сразу выделить визуально выходные дни.

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 < 6 Then Cells(Ir, Ic).Value = 8 Else Cells(Ir, Ic).Value = “В”

‘Делаем проверку записанных данных.

‘Если это число, то делаем числовой формат

‘Если это текст, делаем формат текствый и полужирный шрифт, а

‘затем заливаем ее зеленым цветом. Конечно, последнее не обязательно

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 < 6 Then

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

табель в VBA - 2

Все вроде бы в порядке, но макросы будут работать только в текущей книге. Необходимо же, чтобы эти макросы работали в любом файле. Мы пойдем на хитрость. На вкладке «Вид» в блоке макросы нажмем кнопку «Запись макроса», выберем сохранение «В личной книге макросов» и нажмем ОК.

табель в VBA - 3

После этого сразу перейдём на блок «Макросы» вкладки «Вид» и выберем команду «Остановить запись». В результате получаем доступ к личной книге макросов Personal. Открываем ее, перетаскиваем туда наш модуль, после чего удаляем его из текущей книги.

Было

табель в VBA - 4

Стало

табель в VBA - 5

Обратите внимание, что модуль Table_5_6 ( так я его назвал) теперь находится в Personal.xlsb

Подведем итог. Наши макросы готовы, теперь надо сделать их удобными для запуска. Для этого можно использовать быстрые клавиши (Alt + F8), назначить макросы кнопке, добавить их в панель быстрого доступа и так далее. Мы же создадим отдельную панель для наших макросов и поместим кнопки вызова на нее.

Переходим на вкладку «файл» и открываем параметры, после чего переходим на вкладку «Настроить ленту». Нажимаем кнопку «Создать вкладку»

табель в VBA - 6

Создается вкладка с новой группой. Переименовываем их по желанию. Я назвал вкладку как «Макросы», а группу как «Табель», кроме этого, задал группе иконку в виде двух папок. В дальнейшем группы и вкладки можно добавлять.

табель в VBA - 7

Осталось совсем немного. Выбрав добавленную группу , вверху в списке «выбрать команды» выбираем вариант «макросы», после чего перетаскиваем наши макросы в группу нажатием кнопки «Добавить»

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

табель в VBA - 8

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

На этом текущее занятие подошло к концу. Желаем всем удачного дня и светлого настроения. До встречи на наших занятиях в учебном центре «Зерде»!

Скачать модуль, рассмотренный на уроке здесь.

29880cookie-checkЗаполнение табеля в VBA. Формирование и проверка дат.