通过 VBA 宏实现 Excel 工时自动统计

Sub work_calc()
    Dim my_rows As Integer
    Dim my_cols As Integer
    Dim str_temp As String

    Dim read_arr As Variant
    Dim time_arr As Variant
    Dim result_arr As Variant

    my_rows = ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row
    my_cols = UsedRange.Columns.Count

    sum_total = 0

    For index_row = 3 To my_rows
        If Range("I" & index_row).Value <> "" Then
            str_temp = Range("I" & index_row).Value

            read_arr = read_cell(str_temp)

            result_arr = time_calc(read_arr)

            Range("E" & index_row) = sum_result(result_arr)

            sum_total = sum_total + sum_result(result_arr)
        End If
    Next

    Range("E" & my_rows + 1) = sum_total
End Sub

Function read_cell(cell_content As String) As Variant

    Dim var_split As Variant

    If InStr(cell_content, Chr(10)) > 0 Then
        var_split = Split(cell_content, Chr(10))
    Else
        var_split = Array(1)
        var_split(0) = cell_content
    End If

    read_cell = var_split

End Function

Function time_calc(time_arr As Variant) As Variant
    Dim time_split As Variant
    Dim result() As String
    k = 0

    For i = 0 To (UBound(time_arr) - LBound(time_arr))
        time_split = Split(time_arr(i), "-")
        ReDim Preserve result(0 To k)
        result(k) = Abs(DateDiff("n", TimeValue(time_split(1)), TimeValue(time_split(0))))
        k = k + 1
    Next

    time_calc = result

End Function

Function write_result(result_arr As Variant)
    Dim content As String
    content = ""

    For i = 0 To (UBound(result_arr) - LBound(result_arr))

        content = content & "" & CStr(result_arr(i))
        If i < (UBound(result_arr) - LBound(result_arr)) Then
            content = content + Chr(10)
        End If
    Next

    write_result = content

End Function

Function sum_result(result_arr As Variant)
    sum_temp = 0

    For i = 0 To (UBound(result_arr) - LBound(result_arr))
        sum_temp = sum_temp + result_arr(i)
    Next

    sum_result = sum_temp

End Function