Why my user function return VALUE! error and don't apply property?

Question

I have follow code:

Public Function myfunc()
    ActiveSheet.Range("A1").Interior.Color = vbGreen
    myfunc = ""
End Function

Why when I type =myfunc() in cell B1 it is return #VALUE! error and don't fill A1 cell?

Also, why it make cell A1 green, when i press function icon?

A function called as a UDF from a worksheet cell can only return a value. — Tim Williams, Sep 08 '21 at 21:19
Workaround - https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet — Tim Williams, Sep 08 '21 at 21:20
@GSerg - I think there's a some discussion in the linked question around "use at your own risk"... There's a reason why it doesn't work out of the box. — Tim Williams, Sep 08 '21 at 21:37

score 1 · Answer 1 · answered Sep 08 '21 at 21:19

1

A UDF in a cell can only return a value to the cell in which it resides. It can't effect changes to other cells. You need a SUB not a function.

answered Sep 08 '21 at 21:19

Gary's Student

so way why it is work, when i press function icon? – Your Mammy Sep 08 '21 at 21:26
@WaleronTrot - seems like that function icon runs your function outside of the "worksheet calculation" sandbox. It would also work if you called the function from a Sub instead. See related: https://stackoverflow.com/questions/20866484/can-i-disable-a-vba-udf-calculation-when-the-insert-function-function-arguments – Tim Williams Sep 08 '21 at 21:31

1 Answers1