Questions About Iteration

[MDW]

1) Is it a true statement that Iteration is only relevant to allowing or
disallowing Excel to try and calculate a solution to circular reference
errors? (I have a co-worker who seems to think that if a workbook contains
VBA code where there is a loop, you need to turn Iteration on.)

2) Is this an Excel-wide setting, or does it pertain to a particular
workbook? It seems to me that if I alter the Iteration settings in a workbook
(i.e., turn iteration off), if I close that workbook and open a new one, it
turns Iteration back on.

3) It also seems like the Excel default is Iteration on, 10 max attempts. Is
that true? Is there a way to change the default?

TIA

 

[Niek Otten]

1)
Iteration has 3 fields: Iteration, Max Change and Max iterations. The first one indeed applies to circular references only. The
other two also apply to Goalseek, although setting Max iterations to something smaller than 100 has no effect for Goalseek. In
these dialogs, in HELP and in documentation MS often confuses Iteration and Goalseek.
2)
Yes. The setting doesn't save with the workbook.
3)
No. The default is Iteration off, Max iterations 100. I don't know of a way (other than a macro or maybe editing the registry) to
change the default.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| 1) Is it a true statement that Iteration is only relevant to allowing or
| disallowing Excel to try and calculate a solution to circular reference
| errors? (I have a co-worker who seems to think that if a workbook contains
| VBA code where there is a loop, you need to turn Iteration on.)
|
| 2) Is this an Excel-wide setting, or does it pertain to a particular
| workbook? It seems to me that if I alter the Iteration settings in a workbook
| (i.e., turn iteration off), if I close that workbook and open a new one, it
| turns Iteration back on.
|
| 3) It also seems like the Excel default is Iteration on, 10 max attempts. Is
| that true? Is there a way to change the default?
|
| TIA
| --
| Hmm...they have the Internet on COMPUTERS now!

 

[Niek Otten]

1)
Iteration has 3 fields: Iteration, Max Change and Max iterations. The first one indeed applies to circular references only. The
other two also apply to Goalseek, although setting Max iterations to something smaller than 100 has no effect for Goalseek. In
these dialogs, in HELP and in documentation MS often confuses Iteration and Goalseek.
2)
Yes. The setting doesn't save with the workbook.
3)
No. The default is Iteration off, Max iterations 100. I don't know of a way (other than a macro or maybe editing the registry) to
change the default.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel
| 1) Is it a true statement that Iteration is only relevant to allowing or
| disallowing Excel to try and calculate a solution to circular reference
| errors? (I have a co-worker who seems to think that if a workbook contains
| VBA code where there is a loop, you need to turn Iteration on.)
|
| 2) Is this an Excel-wide setting, or does it pertain to a particular
| workbook? It seems to me that if I alter the Iteration settings in a workbook
| (i.e., turn iteration off), if I close that workbook and open a new one, it
| turns Iteration back on.
|
| 3) It also seems like the Excel default is Iteration on, 10 max attempts. Is
| that true? Is there a way to change the default?
|
| TIA
| --
| Hmm...they have the Internet on COMPUTERS now!