在Excel用户自定义函数中，如何编辑下一个单元格的值？

Question

我希望编写一个用户自定义函数（在工作簿的模块中定义），它可以接受一系列单元格并为下一个单元格生成值。它看起来像这样：

Function doStuff(ByRef input As Range) As Integer
   Dim columnCount As Integer
   Dim nextCell As Range 

   columnCount = input.Columns.Count

   ' nextCell is 2 cells away on the right of the last cell on the first row of input
   Set nextCell = input.Cells(1, columnCount).Offset(0, 2)
   nextCell.Value = "doStuff"

   doStuff = columnCount
End Function

如果我在单元格A2中使用公式=doStuff(A1)，我希望A2=1，A3="doStuff"。但是每次执行到nextCell.Value = "doStuff"这一行时，总是出现错误"Application-defined or object-defined error"。我是否操作正确或者有解决方法？谢谢。

- Martin08

---

简单来说，在UDF内部不能更改单元格的值，即使您修复了立即出现的错误。这个答案应该会有帮助：http://stackoverflow.com/questions/4379882/excel-vba-formula-called-from-a-cell-stops-processing-vba-or-encounters-an-app/4380316#4380316 - jtolle

这是一个实现的想法：https://dev59.com/GGUo5IYBdhLWcg3w7jEq#55238072 - Przemyslaw Remin

Answer 1

从工作表中，函数只能返回值（以及其他一些内容）。也就是说，它们只能更改调用它们的范围的Value属性。它们不能更改该范围的任何其他属性，任何其他范围的属性，也不能更改工作表、工作簿或应用程序对象的任何属性。

您可以使用change事件来检测何时输入该公式，如下所示：

Function doStuff(ByRef rInput As Range) As Long

   doStuff = rInput.Columns.Count

End Function

在工作表的类模块中：

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.HasFormula Then
        If LCase(Target.Formula) Like "=dostuff(*" Then
            Target.Cells(1, Target.DirectPrecedents(1).Value).Offset(0, 2).Value = "do stuff"
        End If
    End If

End Sub

我不确定那个逻辑是否正确，因为我无法完全理解你想要写文本的位置，但我相信你可以解决这部分。

- Dick Kusleika