Scope limit of user written Excel Functions

[Guest]

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

 

[Guest]

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

 

[Guest]

Thanks Tom,

I appreciate the confirmation.

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

 

[Guest]

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?

 

[Tim Williams]

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

 

[Tim]

Typo:

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

Tim