VBA

【VBA実務で使える！！】任意の営業日を簡単に取り出す方法（第一営業日も最終営業日も！）

 
記事内に商品プロモーションを含む場合があります

このページでわかること

営業日リストを取得し、任意の営業日（第一営業日から最終営業日まで）取得することができます。

覚えること
  1. 営業日リストを格納するにはコレクションオブジェクトがおすすめ（Key省略で自動的に連番で登録してくれる）
  2. 何番目の営業日を取り出したいか、コレクションオブジェクトに数値を入れると営業日を取り出すことができる。
  3. 最終営業日はコレクションオブジェクト.Countで取得

こんにちは、hokkyokunです。
前回、第一営業日を様々な形で取得してみました。

今回は任意の営業日を取得できるようにやってみたいと思います。

例えば第三営業日とか最終営業日とかを取得できるようになります。

やってみましょう。

Contents
  1. 営業日リストの取得
  2. 最終営業日は？
  3. まとめ

営業日リストの取得

営業日リストを取得してみます。

例えば、2021年1月4日から営業が開始する場合は
第一営業日：2021/1/4、第二営業日：2021/1/5、第三営業日：2021/1/6みたいな感じで全部取得してみます。

結構簡単ですよ。

先ず、営業日カレンダーを作ってみます。

プロシージャは以下のように作りました。

Sub 営業日リスト()

Dim myYear As Long '特定の年
Dim myMonth As Long '特定の月
Dim busDate As Long '第何営業日か
Dim myDate As Date '特定の年、月の初日
Dim endMonth As Date '特定の年、月の月末
Dim myWeek As String '特定の日付の曜日
Dim table As ListObject '休日テーブル
Dim Coll As New Collection '営業日リスト
Dim r As range 'For each文で使うレンジオブジェクト


'どの月を対象とするか設定
myYear = 2021
myMonth = 1

'営業日を設定
busDate = 3


'対象となる月の1日をmyDateとする。
myDate = myYear & "/" & myMonth & "/" & 1

'対象となる月の月末をendMonthとする
endMonth = DateAdd("d", -1, DateAdd("m", 1, myDate))

'休日テーブルの変数代入
Set table = ActiveSheet.ListObjects(1)

'休日テーブルからmyDateの日付を探索
table.range.AutoFilter field:=table.ListColumns("日付").Index, Criteria1:=">=" & myDate, Operator:=xlAnd, Criteria2:="<=" & endMonth
table.range.AutoFilter field:=table.ListColumns("休み").Index, Criteria1:="<>〇"

'営業日リストを作成
For Each r In table.ListColumns("日付").DataBodyRange.SpecialCells(xlCellTypeVisible)
    Coll.Add r.Value
Next


myDate = Coll.Item(busDate)
myWeek = Format(myDate, "aaa")

MsgBox ("日付は：" & myDate & vbCrLf & _
        "曜日は：" & myWeek)


End Sub

今回はコレクションオブジェクトを使ってみました。
コレクションオブジェクトはKeyを省略すると一から順番に番号を自動的に降ってくれるので
ディクショナリーより便利です。

詳しくはこちらで解説しています。

myYearで対象となる年、
myMonthで対象となる月、
busDateで表示させたい営業日を設定すれば、どの営業日でも出せます。

今回は
myYear：2021
myMonth：1
busDate：3で
プロシージャを動かしてみると

第三営業日は2021/1/14となりました。
合っていますね。

最終営業日は？

最終営業日はどうすればいいでしょうか。

コレクションオブジェクト.Countで営業リストの最後の営業日を取り出すことができます。

Sub 営業日リスト最終営業日バージョン()

Dim myYear As Long '特定の年
Dim myMonth As Long '特定の月
Dim myDate As Date '特定の年、月の初日
Dim endMonth As Date '特定の年、月の月末
Dim myWeek As String '特定の日付の曜日
Dim table As ListObject '休日テーブル
Dim Coll As New Collection '営業日リスト
Dim r As range 'For each文で使うレンジオブジェクト


'どの月を対象とするか設定
myYear = 2021
myMonth = 1


'対象となる月の1日をmyDateとする。
myDate = myYear & "/" & myMonth & "/" & 1

'対象となる月の月末をendMonthとする
endMonth = DateAdd("d", -1, DateAdd("m", 1, myDate))

'休日テーブルの変数代入
Set table = ActiveSheet.ListObjects(1)

'休日テーブルからmyDateの日付を探索
table.range.AutoFilter field:=table.ListColumns("日付").Index, Criteria1:=">=" & myDate, Operator:=xlAnd, Criteria2:="<=" & endMonth
table.range.AutoFilter field:=table.ListColumns("休み").Index, Criteria1:="<>〇"

'営業日リストを作成
For Each r In table.ListColumns("日付").DataBodyRange.SpecialCells(xlCellTypeVisible)
    Coll.Add r.Value
Next


myDate = Coll.Item(Coll.Count)
myWeek = Format(myDate, "aaa")

MsgBox ("日付は：" & myDate & vbCrLf & _
        "曜日は：" & myWeek)


End Sub

これを使えば
例えば、最終営業日2日前はコレクションオブジェクト.Count – 2　とすれば簡単に取得できます。
何らかの社内の締め日を設定するのにとっても役に立つと思います。

まとめ

  • 特定の年、月の営業日リストを取得
  • 第一営業日から最終営業日まで任意の営業日を簡単に取り出すことができる
    コレクションオブジェクトに営業日を取り込ませ、番号で任意の営業日を取り出す
    第一営業日なら「1」をコレクションオブジェクトのKeyにいれると第一営業日を取り出せる
    最終衛秒日はコレクションオブジェクト.Countで得た数値をKeyに取り込めば取得できる

自分で言うのもなんですが、結構使えるんじゃないかと思います。
これを使って必要な営業日を取得し、社内カレンダーを充実させるのもいいと思います。

ではでは。

RELATED POST