Automatically enable iterative calculation

[deucejmp]

Anyone know how to make iterative calculations enable itself when you open a specific workbook so the user does have to do it?

I have a report I programmed that currently has a few circular references in it that are necessary. I don't want users to have to enable iterative calculations themselves when the circular reference dialog comes up--would like to add it via VBA or something so when you open the workbook, you won't need to enable it manually.

Thanks!

[MrShorty]

Scanning through VBA help, it looks like this is accessed using the Iteration property of Excel.Application object. Putting a statement like

Please Login or Register  to view this content.

into an Open event procedure should do what you want. You'd also likely want a Close event procedure that will disable iteration when the workbook is closed.

Other thoughts: Is this a wise thing to do? I'm not a "developer" (I build spreadsheets for my own use, not for other people to use), so I don't really know. It seems that there is at least some potential for this to create problems for other spreadsheets that a user might be working on while your spreadsheet is open. In any case, it seems that there will need to be some user education/training.

"This spreadsheet automatically enables iteration in Excel, so don't disable iteration while this workbook is open. This can also adversely affect other spreadsheets if you accidently create a circular reference in those spreadsheets while this one is open."

or "This spreadsheet requires iterations to be enabled. To enable iteration, ... To disable iteration... Having iteration enabled can adversely affect other spreadsheets with unintended circular references."

I don't know which is easier -- maybe it doesn't matter.

Another, completely different approach you might want to consider: Sometimes when I have an iterative calculation to perform, rather than set up the circular reference in the spreadsheet, I put the iterative part of the calculation into a VBA UDF that I can call from the spreadsheet. I find that this approach is easier to debug, eliminates the need for iteration, and I can control the convergence criteria better. Just something to consider.