Dependency problem

[Sinus Log]

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

 

[Niek Otten]

<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.

 

[Charles Williams]

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

 

[Sinus Log]

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

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.