Using Tools, Options, Calculation, I set Iterations to TRUE. When my
user-defined function retrieves the value of Application.Iteration, it is
FALSE, even though I just set it to TRUE using the user interface.

Any idea why?

In general, a user defined function (UDF) cannot change any part of the
Excel environment. It can do nothing but return a value to the cell from
which it was called. Normally, attempting to change anything else will cause
the UDF to terminate immediately and return a #VALUE error to the calling
cell. I don't know why setting the Iteration property doesn't cause a #VALUE
error but it doesn't surprise me that you cannot set its value in a UDF.
Functions called from worksheet cells have much more restrictions than code
executed directly.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Mitch Powell" <(E-Mail Removed)> wrote in message
news:9294A7DC-85F3-452A-B06D-(E-Mail Removed)...
> Using Tools, Options, Calculation, I set Iterations to TRUE. When my
> user-defined function retrieves the value of Application.Iteration, it is
> FALSE, even though I just set it to TRUE using the user interface.
>
> Any idea why?

If you write a UDF that is either in or dependent on the circular chain it
will be executed repeatedly. You can detect whether the UDF is being
calculated during the first or second step of the calculation process by
checking Application.Iteration. This will be False during the first step and
True during the second step.

regards
Charles
_________________________________________
FastExcel 2.3
Name Manager 4.0
http://www.DecisionModels.com

"Chip Pearson" <(E-Mail Removed)> wrote in message
news:E0C80071-AD3E-470B-8075-(E-Mail Removed)...
> In general, a user defined function (UDF) cannot change any part of the
> Excel environment. It can do nothing but return a value to the cell from
> which it was called. Normally, attempting to change anything else will
> cause the UDF to terminate immediately and return a #VALUE error to the
> calling cell. I don't know why setting the Iteration property doesn't
> cause a #VALUE error but it doesn't surprise me that you cannot set its
> value in a UDF. Functions called from worksheet cells have much more
> restrictions than code executed directly.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel, 10 Years
> Pearson Software Consulting
> www.cpearson.com
> (email on the web site)
>
>
> "Mitch Powell" <(E-Mail Removed)> wrote in message
> news:9294A7DC-85F3-452A-B06D-(E-Mail Removed)...
>> Using Tools, Options, Calculation, I set Iterations to TRUE. When my
>> user-defined function retrieves the value of Application.Iteration, it is
>> FALSE, even though I just set it to TRUE using the user interface.
>>
>> Any idea why?
>

Charles,

I did not know that. Thanks for the info.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Charles Williams" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> If you write a UDF that is either in or dependent on the circular chain it
> will be executed repeatedly. You can detect whether the UDF is being
> calculated during the first or second step of the calculation process by
> checking Application.Iteration. This will be False during the first step
> and True during the second step.
>
> regards
> Charles
> _________________________________________
> FastExcel 2.3
> Name Manager 4.0
> http://www.DecisionModels.com
>
> "Chip Pearson" <(E-Mail Removed)> wrote in message
> news:E0C80071-AD3E-470B-8075-(E-Mail Removed)...
>> In general, a user defined function (UDF) cannot change any part of the
>> Excel environment. It can do nothing but return a value to the cell from
>> which it was called. Normally, attempting to change anything else will
>> cause the UDF to terminate immediately and return a #VALUE error to the
>> calling cell. I don't know why setting the Iteration property doesn't
>> cause a #VALUE error but it doesn't surprise me that you cannot set its
>> value in a UDF. Functions called from worksheet cells have much more
>> restrictions than code executed directly.
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel, 10 Years
>> Pearson Software Consulting
>> www.cpearson.com
>> (email on the web site)
>>
>>
>> "Mitch Powell" <(E-Mail Removed)> wrote in message
>> news:9294A7DC-85F3-452A-B06D-(E-Mail Removed)...
>>> Using Tools, Options, Calculation, I set Iterations to TRUE. When my
>>> user-defined function retrieves the value of Application.Iteration, it
>>> is
>>> FALSE, even though I just set it to TRUE using the user interface.
>>>
>>> Any idea why?
>>
>
>

I'm not attempting to SET the property nor is the UDF in a circular chain.
Here's the function:

Function Iteration() As Boolean
Iteration = Application.Iteration
End Function

Pressing F2 then Enter returns the correct value (TRUE). Pressing F9
returns FALSE. I have verified that the function only executes ONCE
regardless of execution method. I'm still lost.


"Charles Williams" wrote:

> If you write a UDF that is either in or dependent on the circular chain it
> will be executed repeatedly. You can detect whether the UDF is being
> calculated during the first or second step of the calculation process by
> checking Application.Iteration. This will be False during the first step and
> True during the second step.
>
> regards
> Charles
> _________________________________________
> FastExcel 2.3
> Name Manager 4.0
> http://www.DecisionModels.com
>
> "Chip Pearson" <(E-Mail Removed)> wrote in message
> news:E0C80071-AD3E-470B-8075-(E-Mail Removed)...
> > In general, a user defined function (UDF) cannot change any part of the
> > Excel environment. It can do nothing but return a value to the cell from
> > which it was called. Normally, attempting to change anything else will
> > cause the UDF to terminate immediately and return a #VALUE error to the
> > calling cell. I don't know why setting the Iteration property doesn't
> > cause a #VALUE error but it doesn't surprise me that you cannot set its
> > value in a UDF. Functions called from worksheet cells have much more
> > restrictions than code executed directly.
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel, 10 Years
> > Pearson Software Consulting
> > www.cpearson.com
> > (email on the web site)
> >
> >
> > "Mitch Powell" <(E-Mail Removed)> wrote in message
> > news:9294A7DC-85F3-452A-B06D-(E-Mail Removed)...
> >> Using Tools, Options, Calculation, I set Iterations to TRUE. When my
> >> user-defined function retrieves the value of Application.Iteration, it is
> >> FALSE, even though I just set it to TRUE using the user interface.
> >>
> >> Any idea why?
> >
>
>
>

I agree that it returns false (not sure why but probably because its being
calculated in normal mode (first step) not iterative mode (second step)).

If you want to show the status of Application.Iteration in a worksheet cell
after each calculation I think you would have to do it using a sub executed
by a Calculate event.

The False then True behaviour in a UDF is useful because it enables a UDF
which is involved in the iterative calculation to know the iteration number.

regards
Charles
_________________________________________
FastExcel 2.3
Name Manager 4.0
http://www.DecisionModels.com

"Mitch Powell" <(E-Mail Removed)> wrote in message
news:10B1FD5A-6E06-475F-B61B-(E-Mail Removed)...
> I'm not attempting to SET the property nor is the UDF in a circular chain.
> Here's the function:
>
> Function Iteration() As Boolean
> Iteration = Application.Iteration
> End Function
>
> Pressing F2 then Enter returns the correct value (TRUE). Pressing F9
> returns FALSE. I have verified that the function only executes ONCE
> regardless of execution method. I'm still lost.
>
>
> "Charles Williams" wrote:
>
>> If you write a UDF that is either in or dependent on the circular chain
>> it
>> will be executed repeatedly. You can detect whether the UDF is being
>> calculated during the first or second step of the calculation process by
>> checking Application.Iteration. This will be False during the first step
>> and
>> True during the second step.
>>
>> regards
>> Charles
>> _________________________________________
>> FastExcel 2.3
>> Name Manager 4.0
>> http://www.DecisionModels.com
>>
>> "Chip Pearson" <(E-Mail Removed)> wrote in message
>> news:E0C80071-AD3E-470B-8075-(E-Mail Removed)...
>> > In general, a user defined function (UDF) cannot change any part of the
>> > Excel environment. It can do nothing but return a value to the cell
>> > from
>> > which it was called. Normally, attempting to change anything else will
>> > cause the UDF to terminate immediately and return a #VALUE error to the
>> > calling cell. I don't know why setting the Iteration property doesn't
>> > cause a #VALUE error but it doesn't surprise me that you cannot set its
>> > value in a UDF. Functions called from worksheet cells have much more
>> > restrictions than code executed directly.
>> >
>> >
>> > --
>> > Cordially,
>> > Chip Pearson
>> > Microsoft MVP - Excel, 10 Years
>> > Pearson Software Consulting
>> > www.cpearson.com
>> > (email on the web site)
>> >
>> >
>> > "Mitch Powell" <(E-Mail Removed)> wrote in message
>> > news:9294A7DC-85F3-452A-B06D-(E-Mail Removed)...
>> >> Using Tools, Options, Calculation, I set Iterations to TRUE. When my
>> >> user-defined function retrieves the value of Application.Iteration, it
>> >> is
>> >> FALSE, even though I just set it to TRUE using the user interface.
>> >>
>> >> Any idea why?
>> >
>>
>>
>>