MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Kim on December 10, 2001 9:55 AM

Our construction company uses Excel for a calculating our final bid price,it was originally done in Lotus which handled the iterations automatically when the file was first opened (no nag screen). With Excel we need to set the calculation iteration option each time the sheet is opened in a NEW session. Question, is there a way to have the program automatically calculate the iterations without having to set the calculation iterations box each time we use the file for the first time. I have tried saving the file with it checked but when I open it again after closing Excel the setting must be reset.

Thanks in advance.


Posted by Mark W. on December 10, 2001 10:12 AM

Have you considered using Solver? (nt)

Posted by Bariloche on December 10, 2001 5:32 PM


You could try entering the following into the ThisWorkBook code sheet:

Private Sub Workbook_Open()
With Application
.Iteration = True
.MaxIterations = 1000
.MaxChange = 0.0001
End With
End Sub

Then each time the workbook is opened the iterations box will be checked and the workbook will be calculated.

You might want to change the MaxIterations and MaxChange values to whatever numbers you require.