エクセルVBAのFunctionプロシージャで祝日判定関数を自作する方法

みなさん、こんにちは！
フジタニ(@libartweb)です。

エクセルVBAとエクセル関数を組み合わせ、営業日を考慮したスケジュール表自動作成の方法をシリーズでお伝えしております。

前回の記事はこちら

エクセルVBAでカレンダーシートに翌月の日付を反映させる方法

エクセルVBAでスケジュール表を自動で作成する方法を連載しています。今回はカレンダーのシートにエクセルVBAで日付を自動的に反映させる方法をお伝えします。同時にシートの保護の活用などミス防止のための工夫も紹介します。

前回はカレンダーシートにエクセルVBAで自動的に日付を設定する方法をお伝えしました。

翌月の日付が設定されることで、カレンダーの形が見えてきました。

今回は、その日付設定がされたカレンダーに対して、土日祝日の行に色をつける処理を行うために、エクセルVBAのFunctionプロシージャを使って祝日判定関数を自作する方法をお伝えします。

前回までのおさらい
祝日判定関数を自作する理由
関数を自作する際に便利なFunctionプロシージャとは
Functionプロシージャを用いた祝日判定関数のサンプルコード
まとめ
連載目次：エクセルVBAで営業日を考慮したスケジュール表を自動で作成する

前回までのおさらい

前回までは以下の①～②まで紹介しました。

今回と次回で紹介するのは③の土日祝日を判定し、VBAでその行に色をつける方法です。今回はまず、祝日判定関数の自作方法ですね。

① シート名が「テンプレート」のままなのでVBAでシートをコピーして対象月の名称にしたい。例：「201811」
② A1セルに対象月の「開始日」をVBAで自動入力し、B列全体を対象月の日付として反映させたい。
③ 土日祝日を判定し、VBAでその行に色をつけたい。　⇒今回と次回で紹介します。
④ 各日付ごとのタスクをVBAで「日付計算」シートから取得して反映させたい。

祝日判定関数を自作する理由

土日祝日の行に色をつけるには当然、それをエクセルVBAで判定する必要がありますが残念ながら

エクセルVBAでは土日を判定することは可能（Weekday関数使用）なのですが祝日は判定できません。

標準ではそういった関数が用意されていないため、祝日を判定するための関数を自作する必要があります。

関数を自作する際に便利なFunctionプロシージャとは

Functionプロシージャとは、呼び出し元に値を返す事ができるプロシージャのことです。

例えばAのSubプロシージャからBというFunctionプロシージャを呼び出したとき、AのSubプロシージャに対してBのFunctionプロシージャは値を返すことができます。

SubプロシージャとFunctionプロシージャの大きな違いは、値を返すかどうか？です。

それでは、以下の最もシンプルなプログラムでイメージしてみましょう。

Sub Test()
    MsgBox (Msg)
End Sub

Function Msg() As String
    Msg = "Function説明のためのテストです"
End Function

TestというSubプロシージャでメッセージボックスを出す処理です。

MsgBox (Msg)

MsgというFunctionプロシージャを呼び出し、Msgはメッセージボックスに出力する内容を返しています。

Functionプロシージャは返り値の型を指定することができます。今回はメッセージの内容なので、String型としています。

Function Msg() As String

今回の例の処理結果は以下の通りです。

Functionプロシージャを用いた祝日判定関数のサンプルコード

ではFunctionプロシージャを応用し、祝日判定関数を自作してみましょう。

前回までの記事で作成した「日付計算」シートを使用します。

以下のシートですね。I列に祝日一覧があります。

WORKDAY関数で日付計算を行うために用意した祝日の一覧ですが、これを今回はエクセルVBAで使ってみましょう。

サンプルコードは以下の通り。Functionの返り値によって祝日かどうかを判定する方法です。

Private Sub holidayTest()
    Dim strDate As String
    strDate = "2019/4/29"
    
    If isHoliday(strDate) = True Then
        MsgBox (strDate & "は祝日です")
    Else
        MsgBox (strDate & "は祝日ではありません！")
    End If
End Sub

'---------------------------------------------------------------------------------------
'機能：取得した引数と、祝日一覧を比較して一致した場合にTrueを返す
'引数：祝日かどうか判定したい日付(文字列)
'返り値：真偽値
'---------------------------------------------------------------------------------------
Private Function isHoliday(ByVal strDate As String) As Boolean
    Dim maxRow As Long '祝日一覧の最終行
    Dim cntRow As Long 'ループカウント用変数
    
    isHoliday = False '初期化
    
    With wsDateList '日付計算シート
        maxRow = .Cells(.Rows.Count, 9).End(xlUp).Row '祝日一覧の最終行を取得する
        
        For cntRow = 3 To maxRow '祝日一覧を一行ずつ読み込むループ
            If Format(.Range("I" & cntRow), "yyyy/m/d") = Format(strDate, "yyyy/m/d") Then
                isHoliday = True '祝日一覧に引数strDateに一致する日付があればTrue
                Exit For　'一致する日付があればループを抜ける
            End If
        Next cntRow
    End With
End Function

holidayTestプロシージャ(Sub)からisHoliday(Function)を呼び出しています。

プロシージャ	種類	引数	機能
holidayTest	Sub	なし	指定の日付が祝日かどうか？をメッセージボックスでお知らせ
isHoliday	Function	strDate	祝日かどうかを真偽値で返す

If isHoliday(strDate) = True Then

isHolidayには引数としてstrDateを渡しています。このstrDateの中に、祝日かどうか？を判定したい日付を格納します。
isHolidayの返り値がTrueかFalseかで祝日を判定します。

maxRow = .Cells(.Rows.Count, 9).End(xlUp).Row '祝日一覧の最終行を取得する

日付計算シート内の祝日一覧の最終行を取得します。

こちらのI列の、最終行ですね。

最終行の求め方として以下の記事で詳細に説明しております。

【エクセルVBA入門】シートのデータがある最終行番号を求めるステートメントを徹底解説

エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズです。今回は、実行のたびにデータをシートに追加できるようにします。また、最終行番号を求めるステートメントについて徹底的に解説をしていきます。

最終行を求めたら、ループで祝日一覧の日付を1行ずつ読み込んでいき、引数strDateと一致する日付はあるか？

を探っていきます。

        For cntRow = 3 To maxRow '祝日一覧を一行ずつ読み込むループ
            If Format(.Range("I" & cntRow), "yyyy/m/d") = Format(strDate, "yyyy/m/d") Then
                isHoliday = True '祝日一覧に引数strDateに一致する日付があればTrue
                Exit For　'一致する日付があればループを抜ける
            End If
            cntRow = cntRow + 1
        Next

※比較処理をする際、Format関数でどちらも同じ書式に変換をかけています。

一致するものがあればisHolidayにTrueを返し、Exit Forでループを抜けています。

isHolidayにはTrueかFalseが返ります。これで祝日判定関数を自作することができました！