Application.Iteration in UDF

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mitch Powell - 13 Dec 2007 09:36 GMT

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?

Reply to this Message

Chip Pearson - 13 Dec 2007 11:47 GMT

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.

Signature

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

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

Reply to this Message

Charles Williams - 13 Dec 2007 13:17 GMT

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

> 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
[quoted text clipped - 10 lines]
>>
>> Any idea why?

Reply to this Message

Chip Pearson - 13 Dec 2007 13:24 GMT

Charles,

I did not know that. Thanks for the info.

Signature

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

> 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
[quoted text clipped - 24 lines]
>>>
>>> Any idea why?

Reply to this Message

Mitch Powell - 13 Dec 2007 13:30 GMT

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.

> 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
[quoted text clipped - 23 lines]
> >>
> >> Any idea why?

Reply to this Message

Charles Williams - 13 Dec 2007 15:48 GMT

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

> I'm not attempting to SET the property nor is the UDF in a circular chain.
> Here's the function:
[quoted text clipped - 38 lines]
>> >>
>> >> Any idea why?

Reply to this Message