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

I want to write a user-defined function (defined in a workbook's module) which takes a range of cells and generate value for the next cell. It looks like this:

我想编写一个用户定义的函数(在工作簿的模块中定义),它接受一系列单元格并为下一个单元格生成值。它看起来像这样:

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

In cell A2, if I use the formula =doStuff(A1), I expect A2=1 and A3="doStuff". Somehow I always got the error "Application-defined or object-defined error" when it got to the nextCell.Value = "doStuff" line. I'm I doing it correctly or is there a workaround this? Thank you.

在单元格A2中,如果我使用公式= doStuff(A1),我期望A2 = 1并且A3 =“doStuff”。不知何故,当它到达nextCell.Value =“doStuff”行时,我总是得到错误“应用程序定义的或对象定义的错误”。我是正确地做到了还是有解决方法?谢谢。

1 个解决方案

#1

From the worksheet, functions can only return values (and a few other things). That is, they can only change the Value property of the Range from which they're called. They cannot change any other property of that Range, any property of any other Range, nor any property of Worksheet, Workbook, or Application objects.

从工作表中,函数只能返回值(以及其他一些东西)。也就是说,它们只能更改调用它们的Range的Value属性。它们不能更改该Range的任何其他属性,任何其他Range的任何属性,也不能更改Worksheet,Workbook或Application对象的任何属性。

You can use the change event to detect when that formula is entered, like so

您可以使用change事件来检测何时输入该公式,如此

Function doStuff(ByRef rInput As Range) As Long

   doStuff = rInput.Columns.Count

End Function

And in the worksheet's class module

并在工作表的类模块中

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

I'm not sure if that logic is right because I couldn't quite understand where you wanted to write the text, but I'm sure you can figure that part out.

我不确定这个逻辑是否正确,因为我不太清楚你想写文本的位置,但我相信你可以把这个部分弄清楚。

#1