[Excel VBA] 年度に関わらず営業日数を算出する方法について紹介

Mar 29, 2021 · この記事は約5分で読めます · Excel VBA ·

Share on:

概要

この記事について

About

Excel VBAで営業日数を計算する場合、
自作関数で細かく営業日条件を設定して計算する以外に、
組み込み関数のNetworkDaysを使用する方法がある。

書式：

WorksheetFunction.NetworkDays(開始日, 終了日, [祝日])
(NetworkDaysの仕様)

使用例

使用例としては、通常、次のようになる。

祝日（土日以外）のリストをいずれかのシート上に作成する。
VBAコード上で、上記リストのセル範囲を祝日引数として指定し、営業日数を算出する。

祝日リスト

 1'******************************************************************************************
 2'*機能      ：営業日の計算　祝日としてセル範囲使用
 3'*引数      ：
 4'******************************************************************************************
 5Public Sub CalcWorkDayUseRange()
 6    
 7    '定数
 8    Const FUNC_NAME As String = "CalcWorkDayUseRange"
 9    
10    '変数
11    
12    '営業日：祝日を除く月～金曜日とする
13    'ex.1)2019/12/29から2020/1/6までの営業日を計算する
14    Debug.Print WorksheetFunction.NetworkDays(#12/29/2019#, #1/6/2020#, ThisWorkbook.Worksheets("祝日").Range("B2:B9")) '=3
15    'ex.2)2020/6/14から2020/6/16までの営業日を計算する
16    Debug.Print WorksheetFunction.NetworkDays(#6/14/2020#, #6/16/2020#, ThisWorkbook.Worksheets("祝日").Range("B2:B9")) '=1
17    
18    'note)祝日シートには2020年の祝日カレンダーしかないため、
19    '   他の年の祝日をまたぐ営業日の計算するためには、対象年のカレンダーを追加する必要がある
20    '   ex)2021/6/14から2021/6/16までの営業日を計算すると、6/15（会社設立記念日）が祝日判定されず、3が返る
21    Debug.Print WorksheetFunction.NetworkDays(#6/14/2021#, #6/16/2021#, ThisWorkbook.Worksheets("祝日").Range("B2:B9")) '=3
22    
23    
24ExitHandler:
25
26    Exit Sub
27    
28End Sub
29

課題点

上のコードのハイライトで示されているように、
上記の方法だと、
祝日シートに記述されていない年（2019年以前、2021年以降）の祝日またぎの計算は苦手である。

各年ごとの祝日をシートに準備しなければならないが、
それが作業量とミスの可能性を増やすため、
この記事では年度に関わらず営業日数を算出する方法を紹介したい。

方法

NetworkDays関数の[祝日]引数はセル範囲だけではない

NetworkDays関数の第三引数（[祝日]）として
セル範囲の他に、配列引数や Date型の配列変数をとることができる。

VBAにおいては、
Date型の配列変数を利用すると、年に依らない日数の計算が容易になるかと考えられる。

よって、以下では、
シートに祝日を記述するのではなく、
配列としてVBA上にハードコードすることを基本方針としている。

配列変数取得用のClassを作成

まず、それぞれの祝日を格納している配列変数を取得するClassを作成した。

 1'@Folder("Class")
 2Option Explicit
 3
 4'**************************
 5'クラス名：ClsSpecialHoliday
 6'*祝祭日の設定・取得
 7'**************************
 8
 9'定数欄
10Private Const SOURCE_NAME As String = ""
11
12'変数欄
13Private lArrHoliday() As String '祝祭日（月日のみ）格納先
14
15
16'******************************************************************************************
17'*getter/setter欄
18'******************************************************************************************
19
20'******************************************************************************************
21'*引数      ：対象年
22'******************************************************************************************
23Public Property Get arrHoliday(ByVal yy As String) As Date()
24    Dim dateArr() As Date
25    Dim i As Long
26    ReDim dateArr(0 To UBound(lArrHoliday))
27    
28    For i = 0 To UBound(lArrHoliday)
29        dateArr(i) = CDate(yy & "/" & lArrHoliday(i))
30    Next i
31    arrHoliday = dateArr
32End Property
33
34
35
36'******************************************************************************************
37'*機能      ：Class_Initialize
38'*引数      ：
39'******************************************************************************************
40Private Sub Class_Initialize()
41
42    '定数
43    Const FUNC_NAME As String = "Class_Initialize"
44    
45    '変数
46    
47    '***ここで通年の祝祭日を設定します（○月/○日）***
48    ReDim lArrHoliday(0 To 7)
49    lArrHoliday(0) = "1/1"
50    lArrHoliday(1) = "1/2"
51    lArrHoliday(2) = "1/3"
52    lArrHoliday(3) = "4/29"
53    lArrHoliday(4) = "5/3"
54    lArrHoliday(5) = "5/4"
55    lArrHoliday(6) = "5/5"
56    lArrHoliday(7) = "6/15"
57    '*************************************
58    
59
60ExitHandler:
61
62    Exit Sub
63    
64        
65End Sub
66
67

クラスの利用時は、
インスタンスを生成した後に
配列変数 = object.arrHoliday(string: 対象年)というように利用する。

もし祝日を追加/削除したければ、
上のコードでハイライトした配列の設定部分を追加したり削除したりすればいい。

営業日の計算

営業日の計算用の関数を書き直すと、
以下のようになる。

 1'******************************************************************************************
 2'*機能      ：営業日の計算　祝日としてハードコードされた値を使用
 3'*引数      ：
 4'******************************************************************************************
 5Public Sub CalcWorkDay()
 6    
 7    '定数
 8    Const FUNC_NAME As String = "CalcWorkDay"
 9    
10    '変数
11    Dim objSpecialHoliday As New ClsSpecialHoliday
12    
13    '営業日：祝日を除く月～金曜日とする
14    'ex.1)2019/12/29から2020/1/6までの営業日を計算する
15    Debug.Print WorksheetFunction.NetworkDays(#12/29/2019#, #1/6/2020#, objSpecialHoliday.arrHoliday("2020")) '=3
16    'ex.2)2020/6/14から2020/6/16までの営業日を計算する
17    Debug.Print WorksheetFunction.NetworkDays(#6/14/2020#, #6/16/2020#, objSpecialHoliday.arrHoliday("2020")) '=1
18    
19    'ex.3)2021/6/14から2021/6/16までの営業日を計算する
20    '   引数として2021を渡せば2021年の祝日として配列を取得できるため、
21    '   6/15（会社設立記念日）が祝日判定され、2が返る
22    Debug.Print WorksheetFunction.NetworkDays(#6/14/2021#, #6/16/2021#, objSpecialHoliday.arrHoliday("2021")) '=2
23    
24    
25ExitHandler:
26
27    Exit Sub
28    
29End Sub
30

書き直し前とは異なり、
2021/6/14から2021/6/16までの営業日がかんたんに計算できている。

Furthermore

今回ClsSpecialHolidayクラスにハードコードした祝日配列の内容を
ファイルから取得したりデータベースを利用したりすれば、
さらにメンテナンス性は向上するかと思う。