85本目：請求日から入金予定日を算出

Excel&VBA解説サイト「エクセルの神髄」様出題の問題集、
「VBA100本ノック」に対する私の回答と解説のページです。

100本ノックの出題リストはこちらから
excel-ubara.com

出題：請求日から入金予定日を算出

#VBA100本ノック 85本目
請求日から入金予定日を算出するユーザー定義関数を作成。
「取引先マスタ」「支払パターン」を基に決定します。
支払パターン
・締め日：5,10,15,20,25,末
・支払月：0~6月後 ※n=0は当月、n=1は翌月
・支払日：5,10,15,20,25,末
入金日が土日祝日の場合は前日にする。

◇ 出題ページはこちら

ソースコード

定義モジュール

Option Explicit

' 支払パターン
Public Const R1st支払パターン = 2
Public Const C1st支払パターン = 1
Public Enum CNo支払パターン
    支払パターン = C1st支払パターン
    締め日
    支払月
    支払日
End Enum
Public Const CLast支払パターン = CNo支払パターン.支払日

' 取引先マスタ
Public Const R1st取引先マスタ = 2
Public Const C1st取引先マスタ = 1
Public Enum CNo取引先マスタ
    取引先 = C1st取引先マスタ
    取引先名
    支払パターン
End Enum
Public Const CLast取引先マスタ = CNo取引先マスタ.支払パターン

メインモジュール

Option Explicit

' 100本ノック085：請求日から入金予定日を算出

' メインロジック
Function Get入金予定日(ByVal 請求日 As Date, ByVal 締日 As Variant, ByVal 支払月 As String, ByVal 支払日 As Variant) As Date
    
    ' 当月の締日を取得
    Dim 当月締日 As Date
    If IsNumeric(締日) Then
        当月締日 = DateSerial(Year(請求日), Month(請求日), 締日)
    ElseIf 締日 = "末" Then
        当月締日 = Fx.EoMonth(DateSerial(Year(請求日), Month(請求日), 1), 0)
    End If
    
    ' 当月締日を過ぎているかどうかで加算月数を1調整
    Dim 加算月数 As Long
    加算月数 = IIf(請求日 > 当月締日, 1, 0)
    
    ' 支払い月から加算月数を取得
    加算月数 = 加算月数 + Val(支払月)
    
    ' 土日祝を考慮しない入金予定日を取得
    Dim 入金予定日 As Date
    If IsNumeric(支払日) Then
        入金予定日 = DateSerial(Year(請求日), Month(請求日) + 加算月数, 支払日)
    ElseIf 支払日 = "末" Then
        入金予定日 = Fx.EoMonth(DateSerial(Year(請求日), Month(請求日) + 加算月数, 1), 0)
    End If
    
    ' 土日祝日を考慮して調整
    Do
        If Format(入金予定日, "aaa") = "土" Then
            入金予定日 = 入金予定日 - 1
        ElseIf Format(入金予定日, "aaa") = "日" Then
            入金予定日 = 入金予定日 - 2
        End If
        
        If Match行番号(入金予定日, WS祝日マスタ.Columns(1)) Then
            入金予定日 = 入金予定日 - 1
        Else
            Exit Do
        End If
    Loop
    
    ' 結果を返す
    Get入金予定日 = 入金予定日
    
End Function

' 取引先からの取得
Function Get取引先別入金予定日(ByVal 請求日 As Date, ByVal 取引先 As String) As Date
    
    Dim R_取引先マスタ As Long
    R_取引先マスタ = Match行番号(取引先, WS取引先マスタ.Columns(CNo取引先マスタ.取引先))
    
    Get取引先別入金予定日 = Get支払いパターン別入金予定日(請求日 _
        , WS取引先マスタ.Cells(R_取引先マスタ, CNo取引先マスタ.支払パターン))
    
End Function

' 支払いパターンからの取得
Function Get支払いパターン別入金予定日(ByVal 請求日 As Date, ByVal 支払いパターン As String) As Date
    
    Dim R_支払いパターン As Long
    R_支払いパターン = Match行番号(支払いパターン, WS支払パターン.Columns(CNo支払パターン.支払パターン))
    
    Get支払いパターン別入金予定日 = Get入金予定日(請求日 _
        , WS支払パターン.Cells(R_支払いパターン, CNo支払パターン.締め日) _
        , WS支払パターン.Cells(R_支払いパターン, CNo支払パターン.支払月) _
        , WS支払パターン.Cells(R_支払いパターン, CNo支払パターン.支払日))
    
End Function

汎用関数モジュール

Option Explicit

' WorksheetFunctionの短縮取得
' 参考：https://www.limecode.jp/entry/utility/shortcall-worksheetfunction
Function Fx() As WorksheetFunction
    Set Fx = WorksheetFunction
End Function

' 行番号の検索
' ◆ ブログ未掲載
Function Match行番号(ByVal 検索値 As Variant, 検索エリア As Range) As Long
    On Error Resume Next
    If IsDate(検索値) Then
        Dim x: x = CDbl(検索値)
        If Err.Number = 0 Then
            Match行番号 = Fx.Match(CDbl(検索値), 検索エリア, 0) + 検索エリア.Row - 1
            Exit Function
        End If
    End If

    Match行番号 = Fx.Match(検索値, 検索エリア, 0) + 検索エリア.Row - 1

End Function

解説

日付計算に関する問題です。

メイン引数である「請求日」以外に計算に必要なパラメータとして、
「締日」「支払日」「支払月」を基に計算をする必要があります。

ただし実際の関数は「取引先」をパラメータとして動くため、

• 取引先から支払いパターンを取得する処理
• 支払いパターンから締日・支払日・支払月を取得する処理
• 締日・支払日・支払月から入金予定日を取得する処理

をそれぞれ組む必要があります。

このように階層化された処理を実装する場合は、
各階層ごとにしっかりプロシージャを分けておくのが重要です。

そうすることでコードの再利用性が格段に良くなりますし、
「この月だけ請求日をずらす」といった改修もやりやすくなります。

プロシージャ分割の参考にしてみてください。

肝心のメインロジックは愚直に分岐処理を組んでいるだけです。

途中計算に必要な日付や値がいくつか出てきますが、
これを「当月締日」や「加算月数」といった変数に入れるだけで、
コードが読みやすくなりロジックを整理しやすくなります。

日付の処理は複雑になりやすいですから、
一つ一つ丁寧に処理を進めていきましょう。