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

nonsense..

Could it be that you try to change iteration within the
a VBA function.. ???

Functions CANNOT change the environment when called from
the worksheet!


Sub IterTest()
Application.Iteration = False
IterFunc Range("a1:a20")
Range("b1").Formula = "=IterFunc(a1:a20)"
End Sub

Function IterFunc(rng As Range) As Double
With Application
If TypeOf .Caller Is Range Then
Debug.Print "called from worksheet!"
Else
Debug.Print "called from vba procedure"
End If
.Iteration = True
Debug.Print "INSIDE FUNCTION SET TRUE"; .Iteration
.Iteration = False
Debug.Print "INSIDE FUNCTION SET FALSE"; .Iteration
IterFunc = .WorksheetFunction.Sum(rng)
End With

End Function


Now look at the immediate screen.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(Umfriend) wrote:

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

keepITcool wrote in message . ..
nonsense..

Could it be that you try to change iteration within the
a VBA function.. ???

Functions CANNOT change the environment when called from
the worksheet!

I know *that*. The code of the UDF is as provided by me. When calling
the UDF from a cell, it *always* returns FALSE. If I debug the UDF and
use the immediate window of VBA, I get on "? applicaiton.iteration" a
TRUE bfore the UDF is called, "FALSE" as soon as it is called and as
long as it is active/in debugging" and "TRUE" again after it has
finished.

The UDF, again, always returns FALSE even if
<Tools<Option<Calculation shows iteration to be TRUE.....

Thx anyway.

Getting snappy? umf?

THIS UDF:

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

Since it's return value is UNtyped, it cannot return a
False boolean value even if it wanted too.

it MAY return an Empty Variant or a string with "TRUE"

I cant reproduce the anomaly you describe.(xl97 and xlXP)
and I'm still wondering what your real UDF's code looks like.

What does your immediate screen show when you run my example code?




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(Umfriend) wrote:

keepITcool wrote in message
. ..
nonsense..

Could it be that you try to change iteration within the
a VBA function.. ???

Functions CANNOT change the environment when called from the
worksheet!

I know *that*. The code of the UDF is as provided by me. When calling
the UDF from a cell, it *always* returns FALSE. If I debug the UDF and
use the immediate window of VBA, I get on "? applicaiton.iteration" a
TRUE bfore the UDF is called, "FALSE" as soon as it is called and as
long as it is active/in debugging" and "TRUE" again after it has
finished.

The UDF, again, always returns FALSE even if
<Tools<Option<Calculation shows iteration to be TRUE.....

Thx anyway.

Getting snappy? umf?

Yes, sorry about that. Aside from a character flaw, I was in a harsh
battle with Excel as well. Apologies.

THIS UDF:

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

Since it's return value is UNtyped, it cannot return a
False boolean value even if it wanted too.

it MAY return an Empty Variant or a string with "TRUE"

I cant reproduce the anomaly you describe.(xl97 and xlXP)
and I'm still wondering what your real UDF's code looks like.

True. It was as provided when I wrote it. Alse typed it as boolean at
some stage etc. Never got TRUE or "TRUE".

What does your immediate screen show when you run my example code?

Still need to try that one. I tried the one suggested by Charels, and
indeed it prints FALSE 4 times before it switches to True. I'll try
yours as well

Oh well, tried it right now. When called from the subroutine, it
returns TRUE aftr it is set to true. This is not the case when called
from the worksheet. Here it gets strange.

If I switch iteration on via Tools Options and I call it from the
worksheet, the output is:
called from worksheet!
INSIDE FUNCTION SET TRUEFalse
INSIDE FUNCTION SET FALSEFalse

AND: Tools Options show Iterations is STILL on....

Switching it off in Tools Options shows same output and Tools Options
afterwards shows it to be off (which I would expect in any case).

Are you saying that when you call IterFunc from the worksheet, it
prints:
called from worksheet!
INSIDE FUNCTION SET TRUETrue <- The difference
INSIDE FUNCTION SET FALSEFalse?

Thanks for your time.

hmm... Now you have confused ME :)

Sub IterTest()
Application.Iteration = False
Range("b1").Formula = "=IterFunc(a1:a20)"
Application.Iteration = True
Range("b1").Formula = "=IterFunc(a1:a20)"
Application.Iteration = False

End Sub

Function IterFunc(rng As Range) As Double
With Application
If TypeOf .Caller Is Range Then
Debug.Print "called from worksheet!"
Else
Debug.Print "called from vba procedure"
End If
Debug.Print "Iteration"; .Iteration
IterFunc = .WorksheetFunction.Sum(rng)
End With

End Function

gives:
called from worksheet!
IterationFalse
called from worksheet!
IterationFalse


which would INDEED mean that WHEN called from a worksheet
a function cannot READ the iteration setting.

I'll experiment with xl4 macro function
options.calculation later.. to see if that might help.






keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(Umfriend) wrote:

Are you saying that when you call IterFunc from the worksheet, it
prints:
called from worksheet!
INSIDE FUNCTION SET TRUETrue <- The difference
INSIDE FUNCTION SET FALSEFalse?

Thanks for your time.

keepITcool wrote in message . ..
hmm... Now you have confused ME :)

We aim to please ;)

If it is of interest to you, you might look at
http://www.google.nl/groups?hl=nl&lr...en%26h l%3Dnl
(hope the link works...)

I found that when called from a worksheet it indeed can not read
application.iteration correctly, *unless*
1. The UDF is passed a cell that is part of the iteration (or circular
reference chain), _and_
2. The UDF actually refers to that cell (does not have to do anything
meaningfull with it, just needs to "read" it.

Well "found", I learned it from Mr. C. Williams... But at least I
understand what is happening now.

P.S. What is "x14 macro function"?

that branch of the thread doesnt show up on
msnews NNTP server,strange :(

well, Charles would know as he's the resident
calculation engine expert :)


xl4 macro functions... is the old macro functions
from before VBA. Sometimes they're very usefull.
(e.g. setting a lot of PageSetup options is very
slow on VBA, but is 1 (fast) oneliner in xlm.

it's basically what you can execute with
application.ExecuteExcel4Macro

you may want to download the help file from MS,
it wont integrate well with normal help.
I've put a shortcut somewhere.

http://support.microsoft.com/default...b;en-us;109976



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(Umfriend) wrote:

keepITcool wrote in message
. ..
hmm... Now you have confused ME :)

We aim to please ;)

{SNAP}

Well "found", I learned it from Mr. C. Williams... But at least I
understand what is happening now.

P.S. What is "x14 macro function"?