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

confirmed. It is not allowed. As such, there is no setting that makes it
possible.

Suspect you are using some combination of Events and embedded functions.


http://support.microsoft.com/kb/170787/en-us
Description of Limitations of Custom Functions
--
Regards,
Tom Ogilvy


"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

Thanks Tom,

I appreciate the confirmation.

"Tom Ogilvy" wrote:

confirmed. It is not allowed. As such, there is no setting that makes it
possible.

Suspect you are using some combination of Events and embedded functions.


http://support.microsoft.com/kb/170787/en-us
Description of Limitations of Custom Functions
--
Regards,
Tom Ogilvy


"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

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

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

Typo:

If it allowed updating of "random" cells from inside a UDF then it might
*not* be possible to calculate a sheet correctly

Tim



"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
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