Excel VBA Application.Iteration not able to be set programmatically?

[malcolmlyle]

I've been trying to work this out and searching multiple sources and forums to no avail.

MY APPLICATION:
We are an engineering firm. I have a "Special" Workbook that peforms calcaultions that need to have Iterations=TRUE to get past Excel throwing up an error. There is not, in fact, a circuilar reference, it just looks like there is becuase I'm searching through a network of "FROM" and "TO" node names to trace loads or fault current calcualted values either up or down the tree.

For crash protection, MaxIterations is set to 100, becuase it's possilbe to create a circular reference while doing data. If anyone is intersted in this, I can share an example, but it's not really the issue because if Iterations is turned on and the nodes are configured correctly, it works brilliantly.

THE PROBLEM:
There are conditions where when I open this workbook in a special circumstance:

1. Excel is not running yet, no other workbooks open (expecialy not one with Iterations enabled)
2. I open a plain jane workbook - without iterations enabled
3. Then I open my "Special" workbook that is saved with iterations enabled

Excel turns iterations off in this instance. I get error messages and I have to manually turn them back on. There are others in my firm that use these workbooks too and are not VBA savvy, so they get their minds blown when they see this error message. I'd perfer to prevent it altogether.

If I open the "Special" workbook first, there are no issues. You can open a plain jane workbook and it will not alter settings as long as the first WB opened is the one with Iterations=TRUE.

I've tried all sorts of Workbook and Worksheet events (open, activate, etc.) to programmatically turn it back on, but none of these actually work. I've tried putting these in this "Special" workbook and my Personal.xlb. As near as I can tell this code does nothing, no matter where it lives:

Private Sub Workbook_Open()
    With Application
        .Iteration = True
        .MaxIterations = 100
        .MaxChange = 0.001
    End With
End Sub

What on earth do I need to do to get this to set correctly?

 

[Domenic]

Your event handler needs to be placed in the workbook code module...

Visual Basic Editor (Alt+F11) >> Project Explorer (Ctrl+R) >> right click ThisWorkbook and select View Code

Is this where you placed it?

And I'm assuming that you've enabled macros, correct?

 

[Micron]

I take it that you're suggesting the plain wb sets the application setting and the special wb should then alter the setting but it does not. To test, I opened a wb that does not use that setting, then opened one with your code. Result is that the setting was applied as per immediate window:
?application.Iteration
True
False
First check was false, 2nd check pushed that down the list and was true. Either your code isn't running because it's in the wrong place, or there is some other issue. Try putting a stop statement as the first line of code in the sub body.

 

[malcolmlyle]

Your event handler needs to be placed in the workbook code module...

Visual Basic Editor (Alt+F11) >> Project Explorer (Ctrl+R) >> right click ThisWorkbook and select View Code

Is this where you placed it?

And I'm assuming that you've enabled macros, correct?

Yes, macros enabled, VBA project trusted, etc. The code sample I provided is in both the Workbook open and activate events. Still doesn't run.

 

[malcolmlyle]

I take it that you're suggesting the plain wb sets the application setting and the special wb should then alter the setting but it does not. To test, I opened a wb that does not use that setting, then opened one with your code. Result is that the setting was applied as per immediate window:
?application.Iteration
True
False
First check was false, 2nd check pushed that down the list and was true. Either your code isn't running because it's in the wrong place, or there is some other issue. Try putting a stop statement as the first line of code in the sub body.

What event or sub should be used? I have it defined in both Workbook open and activate events.

 

[Domenic]

Yes, macros enabled, VBA project trusted, etc. The code sample I provided is in both the Workbook open and activate events. Still doesn't run.

Yes, but did you place the code in the code module for your workbook called ThisWorkbook, as I described in my previous post?

 

[Micron]

What event or sub should be used?

I used yours, exactly as it you posted. Just to be clear, I opened the plain wb and tested in the immediate window and got False. Then I opened the wb with that code and tested again and got True. Just wanted to clarify the sequence of events and testing. You have it here, yes?

Doesn't seem like you tried it with the Stop statement to ensure yours is running.

Did you ensure that events have not/are not being disabled? What does this inquiry tell you:
?Application.EnableEvents

 

[malcolmlyle]

Your event handler needs to be placed in the workbook code module...

Visual Basic Editor (Alt+F11) >> Project Explorer (Ctrl+R) >> right click ThisWorkbook and select View Code

Is this where you placed it?

And I'm assuming that you've enabled macros, correct?

I finally followed your logic. Yes, I had it in the wrong place. I moved the code to the "ThisWorkbook" class module code and now it's behaving as expected.
Thanks for your answer to my question and putting me back on the right path.

 

[Micron]

Umm, I created and posted a pic to show exactly where it was supposed to be.

 

[Alex Blakenburg]

I hope you are resetting Iterations back to False when you close the workbook. Messing around with someone else's application level settings is not good programming etiquette.