Need a macro to enable iterative calculation

marvin_rock

Board Regular
Joined
Dec 14, 2009
Messages
62
Hey, quick and easy, I have a HUGE spreadsheet with intentional circular references. Now that it's going out to users that don't have iterative calculations enabled, it's breaking their spreadsheets.

I already have a workbook open macro in place, just need to add a line to ensure iterative calculation is turned on with the following settings

*"Enable iterative calculation"
* Maximum Iterations = 100
* Maximum Change = 0.001


thanks!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hey look @ that - macro recording was useful - for anyone else interested - here is the answer

Code:
Application.Iteration = True
 
Upvote 0
You need an open event, something like:

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

Don't forget to switch it off on workbook close and deactivate.
 
Upvote 0
I have benefited from this solution. But the problem is since in EXCEL option I have not checked "Allow iteration" Circular reference arrows are appearing in solution .How to get rid of them ?

Since I want to give these calculations to somebody else I want him to be doing this without going to checkbox for allow ietration

Regards Mukund
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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