VBA遍历含空单元格工作表 存储合并工时数据至类变量

问题背景 #

现有员工工时记录表包含Full name、Work Begin、Break、Work End、Total Hours5个字段，同一员工单日工时记录分散在多行，行内夹杂大量空单元格。需提取有效单元格值，合并同一段工作周期的分散记录后输出规整结果：

• 同一工作时段的姓名、工作开始时间、累计休息时长、工作结束时间、总工时合并为单行
• 无休息记录的时段休息时长默认填充00:00
• 同周期内多段休息时长累加计算

现有方案问题 #

现有参考公开方案编写的VBA代码存在两个问题：

1. 空单元格会导致部分计算值为0
2. 存在数据重叠计算错误
   现有代码如下：

Sub OTHours()
    Dim c As Collection
    Set c = New Collection
    Dim e As Collection
    Set e = New Collection
    On Error GoTo RowHandler
    Dim i As Long, r As Range
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        Set r = Range("M" & i)
        c.Add r.Row, r.Offset(0, -12) & "£" & r
    Next i

    For i = 1 To c.Count
        If i <> c.Count Then
            Dim j As Long
            j = c.Item(i)

            Dim m As Merged
            Set m = New Merged

            m.Name = Range("A" & c.Item(i))
            m.Dates = Range("M" & c.Item(i))

            Do Until j = c.Item(i + 1)
                m.Hours = m.Hours + Range("L" & j)
                m.Row = j
                j = j + 1
            Loop
        Else
            Dim k As Long
            k = c.Item(i)
            
            Set m = New Merged

            m.Name = Range("A" & c.Item(i))
            m.Dates = Range("M" & c.Item(i))
           
            Do Until IsEmpty(Range("A" & k))
                m.Hours = m.Hours + Range("L" & k)
                
                m.Row = k
                k = k + 1
            Loop
        End If
        e.Add m
    Next i

    For i = 1 To e.Count
        Debug.Print e.Item(i).Name, e.Item(i).Dates, e.Item(i).Hours, e.Item(i).Row
        Range("P" & e.Item(i).Row) = IIf(e.Item(i).Hours - 7.7 > 0, e.Item(i).Hours - 7.7, vbNullString)
    Next i

    PrintOvertime e

    Exit Sub

RowHandler:
    Resume Next
End Sub


Private Sub PrintOvertime(e As Collection)
    Application.DisplayAlerts = False
    Dim ws As Worksheet
    For Each ws In Sheets
        If StrComp(ws.Name, "Time Only", vbTextCompare) = 0 Then ws.Delete
    Next
    Application.DisplayAlerts = True
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Time Only"
    Set ws = Sheets("Time Only")
    With ws
        Dim i As Long
        .Range("A1") = "Applicant Name"
        .Range("B1") = "Date"
        .Range("C1") = "hours"
        For i = 1 To e.Count
            If (e.Item(i).Hours - 0 > 0) Then
                .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row + 1) = e.Item(i).Name
                .Range("B" & .Range("B" & Rows.Count).End(xlUp).Row + 1) = e.Item(i).Dates
                .Range("C" & .Range("C" & Rows.Count).End(xlUp).Row + 1) = e.Item(i).Hours - 0
            End If
        Next i
        .Columns.AutoFit
    End With
End Sub

代码编写规则 #

最终输出的VBA代码需满足以下要求：

• 遍历整张表格，处理完成的数据存储到自定义类组件（Classcomponent）的变量中
• 计算逻辑忽略时间重叠问题
• 同一工作开始到结束区间内的多段休息时长做累加计算
• 休息时长单元格为空时，变量中默认存储00:00
• 适配表格筛选后Full name字段动态变化的场景

内容的提问来源于stack exchange，提问作者radiokonntas