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

<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

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

Charles Williams wrote:
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




Until December 27, I used to pass almost all values to my
udf's as arguments, and I let the udf's read only 4 values
in the worksheet. I don't even remember why. It was stupid
of me not to think to add them to the parameters. Instead, I
took out most of the parameters and made the udf's read them
in the worksheet too, making things worse. Fortunately, I
keep backups of my work.

Many thanks to both Niek and Charles. And Charles, I already
read a large part of the documents on your site some time
ago. I would recommend it to everyone.