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

M

malcolmlyle

New Member
Joined
May 20, 2024
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code: 
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?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Your event handler needs to be placed in the workbook code module...

VBA Code: 
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?
 
Upvote 0
Solution
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.
 
Upvote 0
Domenic said:
Your event handler needs to be placed in the workbook code module...

VBA Code: 
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?
Click to expand...

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.
 
Upvote 0
Micron said:
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.
Click to expand...
What event or sub should be used? I have it defined in both Workbook open and activate events.
 
Upvote 0
malcolmlyle said:
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.
Click to expand...
Yes, but did you place the code in the code module for your workbook called ThisWorkbook, as I described in my previous post?
 
Upvote 0
malcolmlyle said:
What event or sub should be used?
Click to expand...
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?
1716652583597.png

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
 
Upvote 0
Domenic said:
Your event handler needs to be placed in the workbook code module...

VBA Code: 
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?
Click to expand...

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.
 
Upvote 0
Umm, I created and posted a pic to show exactly where it was supposed to be.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Automating textbox updates in PP using VBA to read excel data
Replies
0
Views
522
noobslayer252
N
J
  • Question
VBA Script - Can't figure out one error...
2
Replies
15
Views
419
Peter_SSs
Peter_SSs
R
  • Question
VBA just isn't saving files to folder.
Replies
3
Views
517
Micron
M
I
  • Question
Microsoft Excel - REQUIRE MACROS
2
Replies
15
Views
1K
infrequentcoder
I
H
  • Solved
Excel VBA file importing data from several files located in same folder -> challenge to copy data only (rest is working perfectly)
Replies
3
Views
222
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top