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

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

Excel VBA 実践レポートと題しまして、実用的なツールを作成するための試行錯誤の状況やプログラムの解説をお伝えしたいと思います。いくつかの記事に分けてお伝えするボリュームになると思いますが、VBAツールの作成練習用の題材としても使用できると思いますので、ご活用頂けたら幸いです。

その他のExcel VBA関連の教材および練習問題は下記よりご覧いただけます。

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

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

本記事の対象読者

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

仕様の検討

作成するのは、勤怠管理・給与計算ツールです。下は簡単な処理フローになります。

従業員は勤怠入力用ファイルに、月ごとの勤怠状況を入力する。
従業員は勤怠入力用ファイルを、経理担当に提出する。
経理担当は給与計算ファイルを使用し、給与明細書を作成する。

図に示すように、作成するExcelファイルは２つです。まずは勤怠管理の入力用ワークシートを検討します。

勤怠管理シートのレイアウト

勤怠管理のレイアウトは次のように検討しました。

次のような点を考慮しています。

入力セルを広めにとり、見やすさを重視。
L4セルから始まる空欄は、表示項目が増えた場合の予備として準備。
A4に収まるレイアウトで作成。ボタンはA4サイズの外。

本記事で実装する仕様

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

西暦年(A2セル)と月(D2セル)を入力し、日付設定ボタンを押すと日付と曜日(B9～B39セル)が自動入力される。
勤怠列(C9～C39セル)は、ドロップダウンリストによる選択入力とする。
開始列(F9～F39セル)、終了列(I9～I39セル)、休憩(O9～O39セル)は「HH:MM」形式で入力できる方式とする。
HHは0～23、MMには0～59が入力できる。
時間外列(O9～O39)、勤務時間列(R9～R39)は自動入力されるようにする。

実践レポート

ここから、実際の作成手順を紹介しながら、感じたことを合わせて書いていきたいと思います。基本となるExcelファイルは、こちらからダウンロードできます。

管理情報シートの作成

Excelでツールを作成する際、定数や入力リストを設定しておく管理用シートを作成すると便利です。本記事の段階では、次のように値を設定した「管理情報」シートを準備しています。

シートは定数を設定しておくエリアと入力リストを設定しておくエリアに分けています。定数や入力リストが必要になる都度、空いているセルに追加していくようにします。これらの値の具体的な使用方法は、実際に使用する箇所で説明しています。

日付と曜日の設定

日付と曜日の設定が、下図の手順で実行されるようにします。

②のボタンが押されたらマクロを実行し、処理を実現する動作とします。マクロの内容を下に示します。

Function getLastDay(year, month)
    Dim lastYmd As Date     '末日(年月日)
    Dim lastDay As Integer  '末日(数値)

    '指定年月の末日を年月日情報として取得
    lastYmd = DateSerial(year, month + 1, 0)

    '日のみを取得
    lastDay = Format(lastYmd, "d")

    '指定年月の末日日付を返す
    getLastDay = lastDay    
End 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

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

プログラムのポイントをいくつか解説します。

DateSerial(year, month + 1, 0)は、指定された年と月から末日を返しています。DateSerial関数は、３つの引数にそれぞれ年、月、日を指定することで日付型の値を返します。月にmonth + 1を指定することで指定月の翌月となります。その上で日に0を指定することで、翌月1日の前日、つまり指定月の末日を表すことができます。yearが2026、monthが5なら、2026年6月1日の前日を表しているため、2026年5月31日を返すことができます。その上でFormat(lastYmd, "d")の処理で「日」のみを取得し、戻り値として返しています。

lastDay = getLastDay(year, month)は、作成したgetLastDay関数を呼び出す処理です。引数に年(A2セル)と月(D2セル)の値を指定し、戻り値として返される日付を変数lastDayに代入しています。

Forループでは31日分の処理を行います。どの月も必ず31日まであるわけではないので、If i <= lastDay Thenで実在する日付かをチェックした上で処理を分岐しています。

Format(targetYmd, "aaa")は、日時情報を特定の書式で出力します。targetYmdは処理対象の年月日です。書式は"aaa"とすることで、曜日を漢字1文字で返してくれます。

Select Case Weekday(targetYmd) ～ End Selectでは、設定した曜日に応じてフォントの色を変更しています。Weekday(targetYmd)は、引数で指定した年月日を元に、曜日を数値で返してくれます。返される数値は日曜日を1とし、順に7(土曜日)までの値で返されます。返された値に応じて、Case 1などで処理を分岐し、それぞれに適した色を設定しています。

色の設定はRGB関数を使っています。RGB関数は、3つの引数に赤(Red)、緑(Green)、青(Blue)の色の度合いを数値(0～255)で指定します。色を表す数値は16進数で表示しています。これは、Webページの作成などで色を指定する際によく用いられる形式です。16進数で数値を指定する場合は数値の先頭に(&H)をつける必要があります。

16進数を難しく考える必要はありません。色見本を提供しているサイトから、該当する16進数をコピーして持ってきましょう。多くの場合#FF3300のように記載されています。赤、緑、青の情報は下の図のようになっていますので、それぞれをRGB関数の3つの引数に設定します。