Excel 97 - Application.Iteration is always FALSE?

[Umfriend]

Dear all,

Still on Excel 97 (but wonbdering about higher versions as well) I
want to query whether Iteration is on or off. It appears that while
running VBA (In a UDF, not sure abotu Subs), Applicaiton.Iteration is
alwaqs False. I can see from the Options dialog that it is actually
on. Printing Applicaiton.Iteration before and after the UDF is called
also returns TRUE.

Is there a way to be able to query that actual status of Iteration?

Function Iter_Status()
If Application.Iteration Then
Iter_Status = "TRUE"
End If
End Function

Kind rgds,
Umf

 

[Charles Williams]

Hi Umf,

Interesting: I think what actually happens is that Excel must first do an
ordinary calculation (or 2) to find out which cells are involved in the
circular calculation before recalculating the circular chain of cells (and
their dependents).
It seems that during the "ordinary" calculation Excel switches iteration
off, then switches it on during the circular calculation.

try this function referencing one of the cells in the circular chain (view
the Immediate window for results)

Function itertest(theRange As Range)
Debug.Print Application.Iteration
itertest = theRange + 1
End Function

See http://www.DecisionModels.com/calcsecretsk.htm for more details on
circular calculation.

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

 

[Umfriend]

Hi Umf,

Interesting: I think what actually happens is that Excel must first do an
ordinary calculation (or 2) to find out which cells are involved in the
circular calculation before recalculating the circular chain of cells (and
their dependents).
It seems that during the "ordinary" calculation Excel switches iteration
off, then switches it on during the circular calculation.

try this function referencing one of the cells in the circular chain (view
the Immediate window for results)

Function itertest(theRange As Range)
Debug.Print Application.Iteration
itertest = theRange + 1
End Function

Will try this, but how do I call this from a worksheet? (I can do a
lot with spreadhseets, I can do a lot with VBA, but interfacing
between the two is, let's day, a lesser developed skill).

Thx.

 

[Charles Williams]

Hi Umf,

its just a UDF so in a worksheet cell put =itertest(c9)
where c9 is one of the cells in your circular reference chain.

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

 

[Umfriend]

Hi Umf,

its just a UDF so in a worksheet cell put =itertest(c9)
where c9 is one of the cells in your circular reference chain.

Charles

Silly me.... It prints False four times and then prints True for 100
times. It gets called 104 times although Max interations is at 100. I
think you are right on Excel switching it off and on later.

Using your method gets it to work, as in this:
Function Iter_Status(theRange As Range)
Application.Volatile
Dim dblx As Double
dblx = theRange + 1
If Application.Iteration Then
Iter_Status = "True"
Else
Iter_Status = "False"
End If
End Function

The line " dblx = theRange + 1" is crucial, without it, it is only
called in the first iteration, where it returns false (but you knew
that I guess).

It is still a bit of a prob for me, as all the iterations are made
only if iteration is on and a boolean swithc is on for each
"transaction"....which the user can turn on or off to gain speed if
they do not need all the transactions calculated in this way. So I'd
rather know whether Excel would try to iterate, given circular
references, then whether Excel actually *does* iterate...

But this is very helpful already. I'll just have to do your test on
all individual iteration-sections. If they are all turned off on the
spreadsheet I might still get a false while it is true, but I guess
I'll have to live with it...

Thanks a lot. :up: