Does VBA allows to assign a value to a cell from inside a function code?

Question

I have a VBA user defined function. Besides to assigning a value to the function, I need to assign a value to a cell which is not the cell calling the function. However, when I try to execute the code, it fails: the error #VALUE appears in the cell from which the function is called and in the other cell no value is assigned. Is it a VBA excel limitation or is there a bug in my code? Here is the code sample:

Function zz()
    zz = "OK"
    Row = ActiveCell.Row
    col = ActiveCell.Column
    Cells(Row + 1, col) = "pippi"
End Function

Many thanks.

UDF's (used in formulas in the worksheet) are not able to modify cell values directly. The following post has some responses giving work-arounds that might be helpful stackoverflow.com/q/12501759/212869 — NickSlash
– NickSlash, Commented Nov 13, 2022 at 21:32

Tim Williams · Accepted Answer · 2022-11-14 04:50:31Z

1

Instead of trying to directly update another cell, your UDF can return an array, which can "auto-spill" into an adjacent cell (depending on how you orient your array)

https://www.contextures.com/excelspillformulaexamples.html

For example:

Function zz()
    Dim rv(1 To 2, 1 To 1)
    rv(1, 1) = "OK"
    rv(2, 1) = "pippi"
    zz = rv
End Function

Note: inside a UDF you can use either Application.Caller or Application.ThisCell to refer to the cell containing the formula. If you use ActiveCell there's no guarantee that's going to be the cell you want.

answered Nov 14, 2022 at 4:50

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Guille Over a year ago

That is a clever workaround, Many thanks! Anyway I got that vba does not allow to assign a value to a cell from a function directly.

Collectives™ on Stack Overflow

Does VBA allows to assign a value to a cell from inside a function code?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related