In order to calculate worksheet formulae Excel needs to know which cells
"depend" on which other cells. If it allowed updating of "random" cells
from inside a UDF then it would might be possible to calculate a sheet
correctly (since it would need to be able to scan your UDF code to determine
dependent cells).

So this restriction is not unreasonable.

Tim


"AnnapolisStar" wrote in message
...
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