Excel VBA 実践レポート（勤怠・給与管理ツール編③）

こんにちは、ECF Tech
担当 Michiharu.Tです。

Excel VBA 実践レポートと題しまして、実用的なツールを作成するための試行錯誤の状況やプログラムの解説をお伝えしています。今回は、勤怠・給与管理ツール作成の第３回です。前回から作成している勤怠入力シートをブラッシュアップして、より使いやすいシートになるよう機能追加をします。

途中、筆者の試行錯誤が見える部分もあるかもしれませんが、ひとつのケーススタディとして活用頂ければとお思います。ハンズオン教材としても使用できますので、読者の皆様の要望にあった使い方をして頂けますと幸いです。

連載目次は下のリンクから、ご確認いただけます。

【初心者向け】動かして学ぶExcel VBA入門

Excel VBAを動かしながら学べる連載をお送りしています。講座目次第１回 Excel VBAを始めようExcel VBAとはExcelマクロを動かしてみようExcelマクロの保存第２回基本操作(1)セルに値を設定セルの削除オブジェクト...

本記事の対象読者

Excelの基本的な操作知識をお持ちの方
Excel VBAの基本的な知識をお持ちの方
Excel VBAで何らかのツール（便利なしくみ）を構築したいと考えている方

本記事で実装するしくみ

本記事では、次のしくみを実装します。

カレンダー表示の祝日対応を実装する。
提出前の入力チェック機能を実装する。

実践レポート

ここから、実際の作成手順を紹介しながら、感じたことを合わせて書いていきたいと思います。

祝日対応の実装

祝日一覧表の作成

日付を設定するマクロに、祝日の曜日を赤色にする対応を追加します。祝日は、春分の日や秋分の日など、毎年少しずつ異なるものもありますので、下のような一覧表を、管理情報シートに追加しました。将来的な増加に備えて少しセルを多めに取っています。

祝日チェック関数の作成

まずは、祝日チェックの関数を作成しました。次のようになります。

Function IsHoliday(targetDay)
    Dim ControlWs

    '「管理情報」ワークシートを変数で保持
    Set ControlWs = Worksheets("管理情報")

    '祝日表と照合し、該当したらTrueを返す
    Dim i As Integer
    i = 0

    'セルが空でない間くり返す
    Do While Not IsEmpty(ControlWs.Cells(4 + i, 9).Value)
        '対象日が祝日なら
        If targetDay = ControlWs.Cells(4 + i, 9).Value Then
            'Trueを返す
            IsHoliday = True
            '関数を終了
            Exit Function
        End If
        i = i + 1
    Loop

    'すべての祝日をチェックし、該当なし
    IsHoliday = False
End Function

Do While文内では、管理情報シートにある祝日一覧を1行ずつチェックしています。祝日に該当していればTrueを返します。なお、Excel VBAでは戻り値の設定をする際関数名 = 値と記述しますが、この記述自体が関数の終了を意味しません。直後にExit Functionと記述し、関数を途中終了させます。

祝日チェック処理の追加

関数ができたら、日付設定のプロシージャ内で祝日処理を対応します。第１回で掲載済みのマクロですが、追加部分を含めて全文を示します。プログラム中の(追加①)～(追加①ここまで)までが追加部分です。

Sub SetDays()
    Dim year As Integer     '年
    Dim month As Integer    '月
    Dim day As Integer      '日
    Dim lastDay As Integer  '末日

    year = Cells(2, 1).Value    '西暦取得
    month = Cells(2, 4).Value   '月取得

    '日のみを取得
    lastDay = getLastDay(year, month)

    '日数分のくり返し
    Dim i
    For i = 1 To 31 Step 1
        If i <= lastDay Then '実在日付チェック
            '処理対象年月日
            Dim targetYmd
            targetYmd = DateSerial(year, month, i)

            '日付の設定
            Cells(8 + i, 1).Value = i
            '曜日の設定
            Cells(8 + i, 2).Value = Format(targetYmd, "aaa")

            '曜日によって文字色を設定
            Select Case Weekday(targetYmd)
                Case 1      '日曜
                    Cells(8 + i, 2).Font.Color = RGB(&HFF, &H33, &H0)
                Case 7      '土曜
                    Cells(8 + i, 2).Font.Color = RGB(&H0, &H66, &HFF)
                Case Else   '平日
                    Cells(8 + i, 2).Font.Color = vbBlack
            End Select

            '(追加①)
            '祝日の場合は文字色を赤とする
            If IsHoliday(targetYmd) Then
                Cells(8 + i, 2).Font.Color = RGB(&HFF, &H33, &H0)
            End If
            '(追加①ここまで)

        Else
            '存在しない日付なら空を設定
            Cells(8 + i, 1).Value = Empty
            Cells(8 + i, 2).Value = Empty
        End If
    Next

End Sub

追加部分のプログラムでは、IsHoliday関数で祝日かどうかをチェックし、祝日であれば文字色を日曜日と同じ色にしています。

マクロが作成できたので、日付設定ボタンを押して確認です。ゴールデンウィーク時期の祝日が反映されていることが確認できます。

入力チェック機能の実装

シートの利用者が入力を正しく行えているかをチェックする機能を実装します。入力チェック機能は、プログラミングの難しさ以上に、どこまでチェックするかを決めるのに頭を悩ませるところがあります。利用の実態にあったチェック範囲を決めるようにしなければいけません。今回は、次のような仕様とします。

年、月、勤怠列、開始列、終了列、休憩列の入力チェックを行う。
平日で勤怠列が未入力の場合、エラーとする。
勤怠列の入力に応じて以下のチェックを行う。
- 勤怠列が「出勤」の場合：開始列、終了列のいずれかが未入力ならエラーとする。
- 勤怠列が「欠勤」「有休」の場合：開始列、終了列、休憩列のいずれかに入力があればエラーとする。
開始時刻と終了時刻が前後している場合、0の場合エラーとする。

マクロの作成例

入力チェックの仕様に基づき、作成したマクロを示します。

'シートの入力内容をチェックする
Sub checkSheet()
    Dim year As Integer     '年
    Dim month As Integer    '月
    Dim lastDay As Integer  '月末日を取得
    Dim IsError As Boolean
    Dim kintaiWs

    IsError = False

    '「勤怠入力」ワークシートを変数で保持
    Set kintaiWs = Worksheets("勤怠入力")

    '背景色をクリア
    kintaiWs.Range("A2").Interior.Color = vbWhite
    kintaiWs.Range("D2").Interior.Color = vbWhite
    kintaiWs.Range("C9:N39").Interior.Color = vbWhite

    '年の入力チェック
    If kintaiWs.Cells(2, 1).Value = Empty Then
        kintaiWs.Cells(2, 1).Interior.Color = vbYellow
        IsError = True
    End If

    '月の入力チェック
    If kintaiWs.Cells(2, 4).Value = Empty Then
        kintaiWs.Cells(2, 4).Interior.Color = vbYellow
        IsError = True
    End If

    '年または月が未入力の場合、メッセージを
    '表示してマクロを終了(解説①)
    If IsError Then
        MsgBox "入力に誤りがあります。色つきの入力欄を確認してください。"
        Exit Sub
    End If

    year = kintaiWs.Cells(2, 1).Value    '西暦取得
    month = kintaiWs.Cells(2, 4).Value   '月取得

    '日のみを取得
    lastDay = getLastDay(year, month)

    '31日分のデータを確認
    Dim i
    For i = 1 To 31 Step 1
        Select Case kintaiWs.Cells(8 + i, 3).Value
            Case "出勤"
                '開始列未入力チェック
                If kintaiWs.Cells(8 + i, 6).Value = Empty Then
                    kintaiWs.Cells(8 + i, 6).Interior.Color = vbYellow
                    IsError = True
                End If

                '終了列未入力チェック
                If kintaiWs.Cells(8 + i, 9).Value = Empty Then
                    kintaiWs.Cells(8 + i, 9).Interior.Color = vbYellow
                    IsError = True
                End If

                '時間の前後不整合チェック(解説②)
                If Not IsEmpty(kintaiWs.Cells(8 + i, 6).Value) _
                    And IsEmpty(kintaiWs.Cells(8 + i, 9).Value) Then
                    If kintaiWs.Cells(8 + i, 6).Value >= Cells(8 + i, 9).Value Then
                        kintaiWs.Cells(8 + i, 6).Interior.Color = vbYellow
                        kintaiWs.Cells(8 + i, 9).Interior.Color = vbYellow
                        IsError = True
                    End If
                End If

            Case "欠勤", "有休"

                '開始列誤入力チェック
                If Not IsEmpty(kintaiWs.Cells(8 + i, 6).Value) Then
                    kintaiWs.Cells(8 + i, 6).Interior.Color = vbYellow
                    IsError = True
                End If

                '終了列誤入力チェック
                If Not IsEmpty(kintaiWs.Cells(8 + i, 9).Value) Then
                    kintaiWs.Cells(8 + i, 9).Interior.Color = vbYellow
                    IsError = True
                End If

                '休憩列誤入力チェック
                If Not IsEmpty(kintaiWs.Cells(8 + i, 12).Value) Then
                    kintaiWs.Cells(8 + i, 12).Interior.Color = vbYellow
                    IsError = True
                End If

            Case Else
                '平日に勤怠入力が無ければエラーとする(解説③)
                If kintaiWs.Cells(8 + i, 2).Font.Color = vbBlack Then
                    kintaiWs.Cells(8 + i, 3).Interior.Color = vbYellow
                    IsError = True
                End If
        End Select
    Next

    'メッセージ表示(解説④)
    If IsError Then
        MsgBox "入力に誤りがあります。色つきの入力欄を確認してください。"
    Else
        MsgBox "入力チェックが、正常終了しました。"
    End If

End Sub

いくつかのポイントを解説します。解説①～解説④の記述は、プログラム中のコメント記載の同番号と対応しています。

ワークシート変数の利用

１つのExcelブックに複数のシートが存在する場合、マクロ内ではワークシートを指定するのが良いと思います。ただその度に、Worksheets("勤怠入力")と入力してしまうと、プログラムの可読性(見やすさ)に影響します。そこで、Dim kintaiWsで変数を定義し、Set kintaiWs = Worksheets("勤怠入力")でワークシートオブジェクトを保持しています。以降は、kintaiWsと記述するだけでワークシートを指定できます。