Re: Error writing value to cell - continued

You can't trick excel by having your function call a sub - that is correct.

Possibly you could have a static array (10x10) and store the values there.

You could then possibly use the Calculate event to write that array to the
work*** (turn off events while writing to eliminate recursion).

The reason this restiction is imposed is so Excel can build an optimal
calculation sequence. If it calculated A1, then later calculated your
cell/function which changed a value A1 was dependent on, then it would have
to keep calculating until nothing else changed.

The Excel calculation engine doesn't go in and analyze what your function
does to figure out how to calculate it.

Another alternative is to calculate your 10x10 result matrix with the
calculate event (but turn off events while writing to eliminate recusion)

--
Regards,
Tom Ogilvy


"Steven M (remove wax and invalid to reply)" <unspam@xxxxxxxxxxxxxxxxxxxxx>
wrote in message news:0l82425frqusnu1uudls5jht44ablod2hq@xxxxxxxxxx
Thanks, it's much clearer now. And thinking about it this way, I can
see why -- If the work*** had 100 cells that called this function,
and any of them could change the value of cell A1, all of the values
would be overwritten except the last one. Excel uses its own
algorithms to set the order in which cells are recalculated, and the
user can't control this, correct? So the results would be
unpredictable and essentially random.

Let me back up a level of abstraction. There is a range of variable
size (now 10 x 10 for testing), with a value in each cell. Another
10x10 range contains a function that computes a result, which depends
on the values in the first range, plus some other user-entered
information.

The function that is called 100 times in the second range generates
some intermediate results. I wanted to "write" that data as a string
into a third range and display it visually, but it doesn't work for
the reason you describe.

(I'm assuming that the restriction on changing values or the
environment extends to all functions or subroutines that are called by
the user-defined function, correct?)

In effect, what I need is to return a matrix or array, containing more
than one value as a result of the function call. The only alternative
I see now is to write a second function and call it from the 100 cells
in the third range.

I hoped to take advantage of the intermediate calculations that are
performed in the first function. Repeating these calculations in two
functions would be less efficient.

Can you suggest another way to store these intermediate results?

Thanks again,

Steven



Je Sat, 15 Apr 2006 11:01:10 -0400, "Tom Ogilvy" <twogilvy@xxxxxxx>
skribis:

Just like I answered in the original thread:

A user defined function called by a work*** cell can not change the
value
of any cell or alter the environment in any way. It can only return a
value.
(it can read other values and so forth, but can't change anything that
could
be visible).


--
Steven M - unspam@xxxxxxxxxxxxxxxxxxxxx
(remove wax and invalid to reply)

A fool and his money are soon elected. -- Will Rogers


.