Excel VBA自定义函数修改传入Range单元格值的问题

核心原因 #

Excel的自定义工作表函数（即你在单元格公式栏用=XXX()调用的UDF）有硬性设计限制：只能返回计算结果，不能直接修改工作表的单元格内容、格式或其他对象。这是为了避免循环引用、计算逻辑混乱，保障工作表数据的稳定性。

你在Sub过程里调用该函数时，是在VBA原生执行环境下运行，不受这个限制，所以能正常修改单元格；但在公式栏调用时，Excel会直接拦截所有修改单元格的操作，抛出值错误。

解决方案 #

方案1：用工作表事件+辅助单元格（实现公式触发修改）

如果一定要通过单元格公式触发修改操作，可以用工作表的Calculate事件间接实现：

1. 简化你的UDF，仅返回标记值：

Function ChangeCellValues(myrange As Range) As Double
    ' 只返回标记，修改逻辑移到事件中
    ChangeCellValues = 0
End Function

2. 在目标工作表的代码模块中添加Worksheet_Calculate事件：

Private Sub Worksheet_Calculate()
    Dim triggerCell As Range
    ' 指定调用UDF的触发单元格（比如J20，可自行修改）
    Set triggerCell = Me.Range("J20")
    
    ' 检测该单元格是否包含我们的自定义函数
    If triggerCell.Formula Like "*ChangeCellValues(*" Then
        ' 提取公式中的参数区域
        Dim paramRange As Range
        On Error Resume Next
        Set paramRange = Evaluate(Mid(triggerCell.Formula, InStr(triggerCell.Formula, "(") + 1, _
            InStr(triggerCell.Formula, ")") - InStr(triggerCell.Formula, "(") - 1))
        On Error GoTo 0
        
        ' 批量修改目标区域的值
        If Not paramRange Is Nothing Then
            paramRange.Value = 1
        End If
    End If
End Sub

注意：该事件会在工作表每次计算时触发，建议添加状态标记避免重复执行。

方案2：直接用宏过程（更稳定可靠）

如果不需要公式触发，直接把修改逻辑放到Sub过程中，通过按钮、快捷键调用：

Sub ChangeCellValues(targetRange As Range)
    ' 批量赋值，比循环更高效
    targetRange.Value = 1
End Sub

' 调用示例
Sub RunChange()
    ChangeCellValues Me.Range("J18:J19")
End Sub

你可以给这个Sub绑定一个工作表按钮，点击即可执行修改，完全符合Excel的设计逻辑，避免UDF的限制问题。

补充：你原代码的小错误 #

原UDF中使用的mycell未定义，应该替换为参数myrange，不过这个问题在公式调用时不会触发——因为Excel直接拦截了修改操作。

内容的提问来源于stack exchange，提问作者jtxkopt - STOP GENOCIDE