In Excel user-defined function, how to edit value of the next cell

Question

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.

Briefly, you can't change cell values from inside a UDF, even after you fix your immediate error. This answer should be helpful: http://stackoverflow.com/questions/4379882/excel-vba-formula-called-from-a-cell-stops-processing-vba-or-encounters-an-app/4380316#4380316 — jtolle, Jul 05 '11 at 15:30
Here is idea how to make it: https://stackoverflow.com/a/55238072/1903793 — Przemyslaw Remin, Mar 19 '19 at 10:36

score 2 · Accepted Answer · answered Jul 05 '11 at 18:13

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.

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

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.

In Excel user-defined function, how to edit value of the next cell

1 Answers1