I had the exact same question. It seems to me that this is one of the really
really basic things that a user would want to do in a function written for a
spread sheet i.e. modify spread sheet cells. It is hard to believe that it
is disallowed. You can view the cells but you cant modify them. I have
noticed that the exact same code placed in a macro will allow you to modify a
cell but that that code placed in a function called from a cell as you
describe will not allow you to modify the same cell. Go figure.
I am interested in how you circumvented this problem as I want to be able to
modify spread sheet cells from a user defined function as I am sure lots of
people would want to do. Can you explain further?

"mickey" wrote:

I had the requirement to write a function, which would be embedded in a cell
that could modify the values of other cells in the same worksheet. However,
the function kept returning the #Value error. I traced through the function
using Msgbox and established that the error was being caused the moment the
function tried to modify a foreign cell. After some experimentation it
appears that, just as Excel's own built-in functions only return a value to
the cell in which it is embedded and they have no constructs to modify the
contents of other cells, there must be a scope limit on user functions that
prevents a user defined function from modifing any cell but the one in which
it is embedded.

I have since found a work-around to this problem using a few programs tricks
and now the same code that caused the error works correctly, modifying other
cells. However, I would still like to know if anyone: has some familiarity
with this issue, can confirm that there is a scope limit in user defined
functions; and knows of a way to change that property to remove the scope
limit?

Thanks