VBA how to write to/modify cells in a function

Question

I am quite surprised that such a simple functionality seems to be unsupported in VBA. Say I want to write value 0.5 to the cell E7 inside a function (not a sub!):

Public Function writeToCell() As Integer
    ActiveSheet.Range("E7").Value = 0.5
    writeToCell = 0
End Function

And when I call the function writeToCell in another cell say F9, there is a value error, and the writing statement fails to be executed.

Why can we not write to cells inside a function? Is there any workaround? My problem is that the values I will write to some pre-designated cells depend on some intermediate results calculated inside the function. So I will have to write to the cells inside this function when I call the function from the target cell in my worksheet.

That sounds really contradictory with the idea of Excel, and the performance hit will be serious, since you will write to cells every time your sheet recalculates. — Patrick Honorez
To build on @PatrickHonorez's comment, it's probably better if you just use a sub that fires when a button is pressed (or a hotkey). UDFs aren't really designed to edit other cells — Tim Stack
@PatrickHonorez I would say recalculating every time on refresh is feature of Excel, e.g. allows for "real-time" monitoring while you update the parameters. Besides this, I will never use Excel for any data analysis task because VBA and its native editor are so ugly to use... — Vim
@TimStack yes I am thinking about that. A button+sub is much easier, although not robust. — Vim
Not robust? Sure it is. You could even make use of a Selection_Change or Worksheet_Change event, or Application.OnTime to make the updating automatic and more frequent — Tim Stack

Tim Stack Tim Stack · Accepted Answer · 2019-07-18T08:45:05

It is possible, although a tad different then when doing the conventional way (that is, not through a UDF)

Function writeToCell() As Integer
    Evaluate "performwrite(" & Range("E7").Address(0, 0) & ")"
    writeToCell = 0
End Function

Sub performwrite(dest As Range)
    dest.Value = 0.5
End Sub

VBA how to write to/modify cells in a function

1 Answers