スケジュール管理表を作成 ③ 列名をEnumで管理

先日から、スケジュール管理表をExcelで作成している。
infoment.hatenablog.com
今日も、昨日の続きから。

昨日作成したユーザー定義関数で、一つ大きな抜けがあった。
関数自体に抜けがあったわけではなく、関数に使用したEnumを掲載して
いなかったのだ。

例えば

Enum 列名
　実施内容=2
End Enum

としておくと、以下の二つは同じ列を指しているが、後者の方が圧倒的に
読み易い（※個人の感想です）。

　Columns(2).Select

　Columns(列名.実施内容).Select

テーブルに於いては、ラベル名称を変えられてもエラーにならない。ただし、
列順の入れ替えには追従しないので、Enum側の編集が必要となる。逆に

　Dim Tb As ListObject
　Set Tb = ActiveSheet.ListObjects(1)
　    Tb.ListColumns("実施内容").DataBodyRange.Select

のようにラベル名称で選択することも可能で、この場合は列の入れ替えに
コードが左右されない利点がある。ただし、ラベル名称を変更されると
途端にエラーになるのが難点だ。

どちらを選ぶも一長一短があって、今回はEnumを選択した。

Enum 列名
    enNo = 1
    en実施内容
    en作業予定時間
    en●
    en実際作業時間
    en終了予定時刻
    en残り時間
    en終了時刻
    en予実比率
    en備考
    [_eLast]
End Enum

なお今回はD列として、前回に無かった列「●」を追加しており、Enumにも
それが反映されている。これについては、次回紹介する。

ということで、現時点で最新のレイアウトがこちらになる。

先日述べたように、開始時刻を外出しにしてみた。会社によっては９時開始
だったり、午後から出社する日もあると考え、柔軟に対応できるようにして
みた。

終業予定については、定時に帰ることもあれば、残業することもある。毎日
決まった時間にはならないだろうから、終業「予定」とした。

なお、具体的な時間が入っているセルは、各々「開始時刻」「終業予定」と
名前をつけてある。

それでは、今回新たに作成した「実際作業時間」を求めるためのユーザー定義
関数と併せて、今まで作成した関数を紹介する。
※開始時刻の外出し対応済み。

' 実際作業時間
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終了時刻) - Range("始業時刻")
                
            Else
                tm実際作業時間 = .Range(列名.en終了時刻) _
                                 - Tb.ListRows(RowIndex - 1).Range(列名.en終了時刻)
            End If
        End With
        
End Function

' 終了予定時刻
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作業予定時間) + Range("始業時刻")
                
            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

' 残り時間。
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

予実比率は、予定と実際の比率を計算している。例えば１時間を見込んだ作業が
30分で終われば「50％」、２時間で終われば「200％」と表示することで、進捗を
はかる目安となる。また自分が行った見積もりの精度を振り返るための材料にも
なる。さほど複雑な計算ではないため、こちらはセルに直接計算式を入力した。

実際に案件を入力してみると、このような感じになる。

必要な情報が大分揃ってきたが、機能としてはまだまだ足りない。

ということで次回は、今回追加した「●」の説明などを行います。

参考まで。