Excel VBA中函数调用子过程时Offset触发#Value错误的原因与解决

Excel的**用户定义函数（UDF）**有严格的运行限制：它只能返回计算结果到调用该函数的单元格，绝对不允许修改其他单元格的内容。你在CalDailyPay这个UDF里调用了CalDailyHour子过程，而该子过程尝试通过cOut.Offset(1,0).Value = TotalHour修改其他单元格，直接违反了UDF的设计规则，因此触发#VALUE!错误。

根据你的需求场景，推荐两种合规的处理方式：

方案1：拆分逻辑，用两个独立函数分别返回结果 #

将CalDailyHour改为函数返回时长值，让用户在不同单元格分别调用两个函数获取日薪和时长：

' 返回单日时长的函数
Public Function CalDailyHour(ByVal cIn As Range, ByVal cOut As Range) As Single
   Dim TotalHour As Single
   If cIn.Value = "off" Then
      TotalHour = 0
   Else
      TotalHour = (cOut.Value - cIn.Value) * 24
   End If
   CalDailyHour = TotalHour
End Function

' 返回单日薪资的函数
Public Function CalDailyPay(ByVal cIn As Range, ByVal cOut As Range) As Single
      Dim DailyPay As Single
      If cIn.Value = "off" Then
          DailyPay = 0
      Else
          DailyPay = CalDailyHour(cIn, cOut) * 15
      End If
      CalDailyPay = DailyPay
End Function

使用方式：

• 在目标单元格输入=CalDailyPay(A1,B1)获取日薪；
• 在下方单元格输入=CalDailyHour(A1,B1)获取当日时长。

方案2：改用宏（子过程）完成批量写入操作 #

如果希望自动同时写入日薪和时长，不要用UDF，直接编写宏通过按钮或快捷键触发：

' 单条数据计算逻辑
Public Sub CalculateDailyData(ByVal cIn As Range, ByVal cOut As Range)
    Dim DailyPay As Single
    Dim TotalHour As Single
    
    If cIn.Value = "off" Then
        DailyPay = 0
        TotalHour = 0
    Else
        TotalHour = (cOut.Value - cIn.Value) * 24
        DailyPay = TotalHour * 15
    End If
    
    ' 日薪写入cOut右侧单元格（位置可按需调整）
    cOut.Offset(0, 1).Value = DailyPay
    ' 时长写入cOut下方单元格
    cOut.Offset(1, 0).Value = TotalHour
End Sub

' 批量处理A、B列的多行数据（假设第1行是表头）
Public Sub BatchCalculate()
    Dim lastRow As Long
    Dim i As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRow
        CalculateDailyData Cells(i, "A"), Cells(i, "B")
    Next i
End Sub

使用方式：直接运行BatchCalculate宏，或给它绑定一个工具栏按钮，点击即可自动计算并写入所有结果。

不推荐的特殊技巧（仅作参考） #

如果一定要在UDF里修改其他单元格，可以借助Application.OnTime延迟执行写入操作，但这种方法不稳定，容易引发数据混乱，仅用于临时场景：

Public Function CalDailyPay(ByVal cIn As Range, ByVal cOut As Range) As Single
      Dim DailyPay As Single
      If cIn.Value = "off" Then
          DailyPay = 0
      Else
          DailyPay = ((cOut.Value - cIn.Value) * 24) * 15
      End If

      ' 延迟执行单元格写入操作
      Application.OnTime Now, "'WriteHour """ & cOut.Address & """," & DailyPay / 15 & "'"

      CalDailyPay = DailyPay
End Function

Private Sub WriteHour(cOutAddr As String, TotalHour As Single)
    Range(cOutAddr).Offset(1, 0).Value = TotalHour
End Sub

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