Excel VBA自定义函数TotalHours修改单元格值时报应用定义错误

你遇到的报错是Excel自定义函数（UDF）的默认限制导致的：被单元格公式调用的自定义函数，无法直接修改其他单元格的数值、格式等属性，这是Excel的安全机制，和你引用rCell的写法无关。

方案1：改为Sub过程执行（适合手动/按钮触发场景） #

如果不需要通过单元格公式调用计算，直接把逻辑改成子过程即可，示例代码如下：

Sub CalculateTotalHours()
    Dim myRange As Range, rOT As Range, rTotal As Range
    Dim sngHours As Single, sngNormal As Single, sngOT As Single
    Dim rCell As Range
    
    ' 选择计算区域、OT输出单元格、总时长输出单元格
    Set myRange = Application.InputBox("选择一周工时区域（1行7列）", Type:=8)
    Set rOT = Application.InputBox("选择加班时长输出单元格", Type:=8)
    Set rTotal = Application.InputBox("选择总工时输出单元格", Type:=8)
    
    sngHours = 0
    sngNormal = 0
    sngOT = 0
    For Each rCell In myRange
        If rCell.Value > 8 Then
            sngOT = sngOT + rCell.Value - 8
            sngNormal = sngNormal + 8
        Else
            sngNormal = sngNormal + rCell.Value
        End If
    Next rCell
    If sngNormal > 40 Then
        sngOT = sngOT + (sngNormal - 40)
        sngNormal = 40
    End If
    sngHours = sngNormal + sngOT
    ' 直接赋值不会报错
    rOT.Value = sngOT
    rTotal.Value = sngHours
    
    Set myRange = Nothing
    Set rOT = Nothing
    Set rTotal = Nothing
    Set rCell = Nothing
End Sub

方案2：修改为返回数组的自定义函数（适合保留公式调用场景） #

如果需要通过单元格公式调用，可让函数返回包含总时长、加班时长的数组，同时在两个单元格接收结果即可：

Function TotalHours(myRange As Range) As Variant
    Dim sngHours As Single, sngNormal As Single, sngOT As Single
    Dim rCell As Range
    Dim result(1 To 2) As Single
    
    sngHours = 0
    sngNormal = 0
    sngOT = 0
    For Each rCell In myRange
        If rCell.Value > 8 Then
            sngOT = sngOT + rCell.Value - 8
            sngNormal = sngNormal + 8
        Else
            sngNormal = sngNormal + rCell.Value
        End If
    Next rCell
    If sngNormal > 40 Then
        sngOT = sngOT + (sngNormal - 40)
        sngNormal = 40
    End If
    sngHours = sngNormal + sngOT
    
    ' 第一个元素返回总工时，第二个返回加班时长
    result(1) = sngHours
    result(2) = sngOT
    TotalHours = result
End Function

调用方法：选中相邻的两个空白单元格，输入公式=TotalHours(你的一周工时区域)，如果是Excel 2021之前的版本，按Ctrl+Shift+Enter数组公式确认即可，两个单元格会分别显示总工时和加班时长。

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