出勤簿を作る　(7)

有給の持ち時間を管理する

　有給休暇は、年間を通して取得時間を管理している場合が殆どでしょう。今回の出勤簿プログラムでは、翌月の出勤簿を自動で出力できるので有給時間の管理をする事が可能になります。
　有給休暇の持ち時間は社員リストシートに保持して、その月に使用した有給の時間数との差で次月の有給時間の持ち時間を算出するようにすれば良いでしょう。

追加するプログラム

　有給時間の管理に関するインターフェイス部分は、社員リストのC列になります。この値は前月までの有給残で、その月度で取得する有給は含まれていません。

　プログラムに関しては、社員リストにある有給残と、その月の出勤簿の有給利用時間を差し引いたものを、集計シートに反映させるようにします。
　算出した翌月の有給残は、次月度のブックを作成する際に社員リストの有給残に反映させれば良いでしょう。
　
　以下の関数（プロシージャ）の変更を行います。
　　・次月度の出勤簿ブックを生成()　Module2
　　・集計()　Module4

　まずは集計()です。

集計プロシージャの変更

　集計プロシージャはModule4にあります。

'Module4
Sub 集計()
    Dim totalSh As Worksheet
    Set totalSh = Worksheets("集計")
    
    '集計シートの値を全てクリア
    totalSh.Cells.Clear
    
    '項目名を記述
    totalSh.Range("A1") = "社員番号"
    totalSh.Range("B1") = "氏名"
    totalSh.Range("C1") = "実働時間合計"
    totalSh.Range("D1") = "欠勤時間合計"
    totalSh.Range("E1") = "有給時間合計"
    totalSh.Range("F1") = "深夜業時間合計"
    totalSh.Range("G1") = "残業時間合計"
    totalSh.Range("H1") = "交通費支給日数合計"
    totalSh.Range("I1") = "出勤日数"
    totalSh.Range("J1") = "有給残"
    
    Dim Sh, i As Long, j As Long
    Dim cnt As Long, cnt2 As Long, cnt3 As Long
    Dim totalDayCnt As Long, dayCnt As Long
    
    i = 2
    For Each Sh In Worksheets   '社員の出勤簿のみ値を取得
        If Sh.name <> "原本" _
            And Sh.name <> "社員リスト" _
            And Sh.name <> "メニュー" _
            And Sh.name <> "勤務パターン" _
            And Sh.name <> "集計" Then
            
            totalSh.Cells(i, "A") = Replace(Sh.Range("B2"), "№", "")
            totalSh.Cells(i, "B") = Sh.Range("D2")
            totalSh.Cells(i, "C") = Sh.Range("H36")
            totalSh.Cells(i, "D") = Sh.Range("I36")
            totalSh.Cells(i, "E") = Sh.Range("J36")
            totalSh.Cells(i, "F") = Sh.Range("K36")
            totalSh.Cells(i, "G") = Sh.Range("L36")
            
            totalDayCnt = 0
            cnt = 0
            cnt2 = 0
            cnt3 = 0
            For j = 5 To 35
                If Sh.Cells(j, "B") <> "" Then
                    '日付のカウント
                    totalDayCnt = totalDayCnt + 1
                    '公休日のカウント
                    If Sh.Cells(j, "E") = "" Then
                        cnt = cnt + 1
                    '全日欠勤のカウント
                    ElseIf Sh.Cells(j, "H") = 0 Then
                        cnt2 = cnt2 + 1
                    '実働時間合計＝有給
                    ElseIf Sh.Cells(j, "H") = Sh.Cells(j, "J") Then
                        cnt3 = cnt3 + 1
                    End If
                End If
            Next j
            '出勤日
            totalSh.Cells(i, "H") = totalDayCnt - cnt - cnt2
            '交通費回数
            totalSh.Cells(i, "I") = totalDayCnt - cnt - cnt3
            '有給残(社員リストにある有給時間 - 今月の有給利用時間)
            totalSh.Cells(i, "J") = getZangyoCnt(Sh.name) - Sh.Range("J36")
            
            i = i + 1
            
        End If
    Next Sh
    
End Sub

　下線部分が変更した部分です。getZangyoCntというプロシージャで社員の今月度の有給取得時間を取得します。
プログラムは以下の通りです。

'社員名から社員リストにある有給時間を返す
Function getZangyoCnt(name As String) As Long
    Dim i As Long, count As Long
    count = 0
    With Worksheets("社員リスト")
    
    For i = 2 To .Cells(Rows.count, 1).End(xlUp).Row
        If name = .Cells(i, "B") Then
           count = .Cells(i, "C")
           Exit For
        End If
    Next i
    
    getZangyoCnt = count
    
    End With
    
End Function

これで集計シートの最後の列に翌月度の有給残が記録されるようになりました。

翌月度の出勤簿ブックを作るプロシージャ

　集計シートに記録した、翌月の有給残を翌月度の出勤簿シートに記録できるようにします。次月度の出勤簿ブックを生成するプロシージャはModule2にあります。

'Module2
Sub 次月度の出勤簿ブックを生成()
    '次月の年と月を得る
    Dim nextY As Long, nextM As Long
    Dim nextMonthBook As String
    Dim sCurrentPath As String
    
    With Worksheets("メニュー")
    
    nextY = .Range("B4")
    nextM = .Range("D4")
    If nextM = 12 Then
        nextY = nextY + 1
        nextM = 1
    Else
        nextM = nextM + 1
    End If
    
    End With

    '確認メッセージ
    Dim result As Long
    Dim myMessage As String
    myMessage = nextY & "年" & nextM & "月の出勤簿を作成します。よろしいですか？"
    result = MsgBox(myMessage, vbYesNo + vbQuestion + vbDefaultButton2, "確認")
    
    If result = vbNo Then Exit Sub
    
    '集計シートを更新(現在の有給残を得るため)
    Call 集計
    
    
    '出勤簿のファイル名を生成  yyyymm出勤簿.xlsm
    nextMonthBook = nextY & Format(nextM, "00") & "出勤簿" & ".xlsm"
    sCurrentPath = ActiveWorkbook.Path & "\"

    '次月度の出勤簿が存在しているかどうかのチェック
    If Dir(sCurrentPath & nextMonthBook) <> "" Then
        MsgBox "既に　" & nextMonthBook & "が存在しますので中止します。"
        Exit Sub
    End If
    
    'ブック（自分自身）をコピー
    ThisWorkbook.SaveCopyAs sCurrentPath & nextMonthBook
    
    '次月度勤務表ブックを開く
    Dim nWb
    Set nWb = Workbooks.Open(sCurrentPath & nextMonthBook)
    
    'このファイルから次月の出勤簿を作成
    '　社員のシートを削除
    Dim Sh
    For Each Sh In nWb.Worksheets
        If Sh.name <> "原本" _
            And Sh.name <> "社員リスト" _
            And Sh.name <> "メニュー" _
            And Sh.name <> "勤務パターン" _
            And Sh.name <> "集計" Then
                
            Application.DisplayAlerts = False
            Sh.Delete
            Application.DisplayAlerts = True
        End If
    Next Sh
    
    '  新しい年、月をセットして社員分のシートを生成
    Dim i As Long, shName As String
    
    nWb.Worksheets("メニュー").Range("B4") = nextY
    nWb.Worksheets("メニュー").Range("D4") = nextM
    nWb.Worksheets("原本").Range("B1") = DateSerial(nextY, nextM, 1)
    
    With nWb.Worksheets("社員リスト")

    For i = 2 To .Cells(Rows.count, 1).End(xlUp).Row
        '有給残を更新（集計->社員リスト）
        .Cells(i, "C") = nWb.Worksheets("集計").Cells(i, "J")
        
        shName = .Cells(i, "B")
        'シートを追加
        nWb.Worksheets("原本").Copy after:=nWb.Worksheets(nWb.Worksheets.count)
        ActiveSheet.name = shName
        ActiveSheet.Range("B2") = "№ " & .Cells(i, "A")
        ActiveSheet.Range("D2") = shName
    Next i
    
    End With
    
    nWb.Worksheets(1).Select
    
    '新しいファイルを保存
    Workbooks(nextMonthBook).Save
    Workbooks(nextMonthBook).Close SaveChanges:=False
    
    MsgBox "翌月の出勤簿ファイルを作成しました。"
End Sub

　下線部分が変更した所です。このプログラムでは、一旦集計プロシージャを実行して最新の情報を得るようにしています。
　次に新しいブックを生成してから、有給残を記録しています。

有給休暇の追加処理

　有給休暇は年に一度追加される会社が多いと思います。今回のプログラムでは、新規有給休暇の追加処理は手作業で行って頂く事になります。
　自動で行う方法を模索したのですが、昨年以前の有給が消滅したり、新しい有給の追加のタイミングなど、プログラムにすると複雑になるため見送る事にしました。
　
　有給休暇の新規追加については、社員リストの有給残の項目に直接入力してください。

最後に

　出勤簿は、その月の処理だけでなく有給消化のように年間を通してカウントしたい要素もあります。
　翌月の出勤簿を自動で作成する事で、このような処理にも対応できるようになります。

有給残の取り扱い仕様

　今回のプログラムで有給の時間数が3つ登場する事になります。これらの値を定義しておきます。
　
　・社員リストの有給残　（今月利用する有給を含まない有給残）
　・出勤簿シートの有給利用時間数　（今月利用した有給時間数）
　・集計の翌月有給残　（上記2つの差で、翌月の有給残）
　
　社員リストの有給残は、その月度の始まりの時点の時間数を示しているという点に注意してください。