Dependency problem

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" <tlobbosNOS-PAM@sympatico.ca> wrote in message
news:u12gtQBFGHA.648@TK2MSFTNGP14.phx.gbl...
> 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" <nicolaus@xs4all.nl> wrote in message
news:O6gknsDFGHA.3468@TK2MSFTNGP15.phx.gbl...
> <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" <tlobbosNOS-PAM@sympatico.ca> wrote in message
> news:u12gtQBFGHA.648@TK2MSFTNGP14.phx.gbl...
>> 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" <nicolaus@xs4all.nl> wrote in message
> news:O6gknsDFGHA.3468@TK2MSFTNGP15.phx.gbl...
>
>><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" <tlobbosNOS-PAM@sympatico.ca> wrote in message
>>news:u12gtQBFGHA.648@TK2MSFTNGP14.phx.gbl...
>>
>>>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.