Error writing value to cell - continued

[Steven M (remove wax and invalid to reply)]

I have a continuing perplexing problem. Using Excel 2000, I created a
test routine, in a module in Personal.xls:

Sub TestAssign()
ActiveSheet.Cells(17, 10).Value = "TT"
End Sub

When I run it in my currently open spreadsheet, it changes the value
of cell J17 to "TT" and the subroutine finishes normally. That's as
expected.

I have another routine, a function defined in a module in the
spreadsheet. It includes the same statement:

ActiveSheet.Cells(17, 10).Value = "UU"

There is a breakpoint on this line. Before execution, the arguments
have the values 17 and 10, same as above. When I press F8 to execute
that step, execution does NOT continue to the same line. Nothing
changes in the active sheet (there is only one sheet in the file.)
There's no error, it just stops.

The only thing that seems to be different is that the assignment to
..Value works if the function is stored in a module in Personal.xls,
and does NOT work if the function is in a module in the current
spreadsheet.

Is this some sort of bug in VB?

 

[kassie]

Argument seems to work fine for me!

 

[Dave Peterson]

You write function in your text.

Do you really mean that it's a function

Function Test(somethingpassed as sometype)
End function

If you do mean that, then how is this function executed? If you're calling it
from a worksheet cell, then these functions can only return values to the cell
that holds the formula.

If you're calling it from a Sub, then this isn't the problem.

 

[Tom Ogilvy]

Just like I answered in the original thread:

A user defined function called by a worksheet 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 (remove wax and invalid to reply)]

Thanks, it's much clearer now. And thinking about it this way, I can
see why -- If the worksheet 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

 

[Tom Ogilvy]

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
worksheet (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)