Scope limit of user written Excel Functions

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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?
 
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
 
Typo:

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

Tim
 
Back
Top