I agree with Niek that it is best to include all input to a UDF in the
argument list.

You may want to look at
http://www.decisionModels.com/calcsecretsj.htm for advice on efficient
coding for UDFs, and see the other pages on my website for improved
understanding of how Excel calculates etc.

regards
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"Niek Otten" wrote in message
...
<Range(...).Value2 suggests that you read values from a worksheet in a
UDF.
But the only way Excel can know in which order to calculate the cells, is
to include all input to a function in the argument list.
It seems to detect cells rat were changed, but in a very inefficient way.
Also, I have never been able to find any documentation which guarantees
that the calculation will be in the correct order.
So that is my advice: don't read from a worksheet directly in a UDF,
always pass the input as arguments to the function.

--
Kind regards,

Niek Otten

"Sinus Log" wrote in message
...
My workbook runs well in Excel97, but not in Excel2000. The problem I
have seems to be pretty difficult.

In the spreadsheet, I have (among other things):
- a set D of data (cells containing raw numbers),
- a set F of formulas (some are udf's) based on D,
- a formula G (not udf) based on both D and F,
- a formula U (a udf) based on D, F and G.
The situation is easier to comprehend by viewing a diagram, but it seems
that there can be no attachments to messages here. So I sketched it
below, but there are 5 arrows missing:
- from D to G
- from D to U
- from F to G
- from F to U
- from G to U


G


D --------- F


U


The code for formula U reads the values in all the cells of D and F
without problem: Range(...).Value2. But it can't read cell G before its
1,805th attempt (the number 1,805 depends on the number of calls to U in
the spreadsheet, of course). That's a big waste of time.

If you draw the arrows above, you'll notice that there are 2 triangles,
one inside the other. First, I thought that the problem arose because
VBA2000 considered this as a circular reference. But in that case, why
would the cell containing G be successfully read after a number of
attempts ? So I thought, maybe the problem is that the cells in F are
calculated last. But that's not the case: all their values are known to
VBA before the 900th attempt to read G.

At first sight, a solution would be to calculate the value of G in VBA
instead of calculating it in the spreadsheet. That way, cell G wouldn't
have to be read in VBA. But that's not really satisfactory. Cell G is
needed in the spreadsheet. So I have 2 choices: either calculate G in the
spreadsheet AND in VBA (messy), or call a udf in cell G to get the value
calculated in VBA. But in that case, I might very well end up with the
same problem I am having right now. Before losing any more time (I've
been working on this for a month), I thought I'd ask the specialists:
what is the cause, what is the solution ?

Thanks