在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"。我做得对吗，还是有解决办法?谢谢你。

Answer 1

从工作表中，函数只能返回值(和其他一些东西)。也就是说，它们只能更改调用它们的Range的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

我不确定这个逻辑是否正确，因为我不太明白你想把文本写在哪里，但我相信你能弄清楚那部分。