スケジュール管理表を作成 ② ユーザー定義関数でIF文の多重入れ子解消

昨日から、スケジュール管理表をExcelで作成している。
infoment.hatenablog.com
今日は、昨日の続きから。
f:id:Infoment:20210112201850p:plain

昨日は、数式で以って終了予定時刻を算出した。
f:id:Infoment:20210112202005p:plain

実はこれに、もう少し条件の追加が必要だ。しかしこれ以上、IF文の多重
入れ子構造になると、ぱっと見で理解できなくなる。それは回避したい。

ということで、ユーザー定義関数を作成することにした。

【終了予定時刻】の条件

実施内容が未記載の場合、何も表示しない。
表の一行目である場合、始業時刻+作業予定時間とする。
表のｎ行目であって且つ
① ｎ－１行目の案件が終わっていない場合、
　ｎ行目の作業予定時間 + ｎ－１行目の終了予定時刻とする。
② ｎ－１行目の案件が終わっている場合、
　ｎ行目の作業予定時間 + ｎ－１行目の終了時刻とする。

ユーザー定義関数にすると、こんな感じだ。

' 終了予定時刻
Public Function tm終了予定時刻()

        Application.Volatile
    
    ' 数式がセットされたセル。
    Dim TargetRange As Range
    Set TargetRange = Application.ThisCell
    
    ' 数式がセットされたセルを含むテーブル。
    Static Tb As ListObject
        If Tb Is Nothing Then
            Set Tb = TargetRange.ListObject
        End If
    
    ' 数式がセットされたセルの、テーブルにおける行番号。
    Dim RowIndex As Long
        RowIndex = TargetRange.Row - Tb.Range(1).Row
    
        With Tb.ListRows(RowIndex)
            If .Range(列名.en実施内容) = vbNullString Then
                tm終了予定時刻 = vbNullString
                
            ElseIf RowIndex = 1 Then
                tm終了予定時刻 = .Range(列名.en作業予定時間) + TimeValue("8:00")
                
            ElseIf Tb.ListRows(RowIndex - 1).Range(列名.en終了時刻) = vbNullString Then
                tm終了予定時刻 = .Range(en作業予定時間) + _
                                 Tb.ListRows(RowIndex - 1).Range(en終了予定時刻)
                                 
            Else
                tm終了予定時刻 = .Range(en作業予定時間) + _
                                 Tb.ListRows(RowIndex - 1).Range(en終了時刻)
            End If
        End With
        
End Function

同様に、残り時間もユーザー定義関数で作成する。
なお、残り時間に関しては以下の仕様とする。

終了予定時刻前に終わった場合は、残り時間をhh:mmの書式とし、
その文字色を黒とする。
終了予定時刻超で終わった場合は、残り時間を-hh:mmの書式とし、
その文字色を赤とする。

注意点として、時間差を求めた結果がマイナスとなった場合、エラーになる。
従って、予め引かれる側が引く側より大きくなるよう、確認が必要だ。

' 残り時間。
Public Function tm残り時間() As Variant

        Application.Volatile
    
    ' 数式がセットされたセル。
    Dim TargetRange As Range
    Set TargetRange = Application.ThisCell

    ' 数式がセットされたセルを含むテーブル。
    Static Tb As ListObject
        If Tb Is Nothing Then
            Set Tb = TargetRange.ListObject
        End If
    
    ' 数式がセットされたセルの、テーブルにおける行番号。
    Dim RowIndex As Long
        RowIndex = TargetRange.Row - Tb.Range(1).Row
            
    ' 終了予定時刻と現在の時刻の大小関係から、数式を決定する。
    ' ※引いた結果がマイナスの場合、エラーになることへの処置。
        With Tb.ListRows(RowIndex)
            If .Range(列名.en終了予定時刻) = vbNullString Or _
               .Range(列名.en終了時刻) <> vbNullString Then
                tm残り時間 = vbNullString
            ElseIf .Range(列名.en終了予定時刻) >= Time Then
                tm残り時間 = Format(.Range(列名.en終了予定時刻) - Time, "hh:mm")
                TargetRange.Font.Color = vbBlack
            Else
                tm残り時間 = Format(Time - .Range(列名.en終了予定時刻), "-hh:mm")
                
                ' 負時間の色。赤より少し暗くしている（任意に変更可）。
                TargetRange.Font.Color = 192
            End If
        End With
        
End Function