Avoiding a 'circular reference' popup window when opening a spreadsheet?

[Ranger_j]

I have a worksheet with some references that Excel sees as circular. Actually, it is just some conditional if statements that calculate quantities from the front if front information is given, and from the back if back information is given. If I change the options to iterate the calculation, it takes Excel maybe 1 or 2 to get it right.

The problem is, I have other users of this sheet, so I want to turn the iteration option on when the spreadsheet is opened. I do this in VBA:

Private Sub Workbook_Open()
Application.CellDragAndDrop = False
Application.Iteration = True
Application.MaxIterations = 10000
Application.MaxChange = 0.0001
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CellDragAndDrop = True
Application.Iteration = False
End Sub

This does the job, but when the spreadsheet is opened, Excel gives me the standard warning box telling me I have a circular reference. No matter what choice I go with, the sheet is then set to iterate and problem solved.

How can I set this up so I DON'T have to click though a warning box every time I open the sheet?

Thanks for your help, whoever you are that has the answer!

 

[Sal Paradise]

Private Sub Workbook_Open()
    Dim prvCalcMode As String

    prvCalcMode = Application.Calculation
    Application.Calculation = xlCalculationManual
    Application.CellDragAndDrop = False
    Application.Iteration = True
    Application.MaxIterations = 10000
    Application.MaxChange = 0.0001
    Application.Calculation = prvCalcMode
End Sub

Try that. It will turn off calculation until you set your iteration, so hopefully it won't try to calculate your sheet and hit errors until you do something else in the sheet that causes it to calculate.

 

[Ranger_j]

Nice idea...but didn't work It's as if Excel is trying to calculate the sheet before addressing any open/startup commands I specify.

 

[Sal Paradise]

Have you considered storing the values as text before close, and then re-converting them to formulas on open?

 

[Ranger_j]

I never knew you could do that. Does excel store the formulas as text strings so it can retrieve them later?

I hate to ask...but how is this done?

 

[SydneyGeek]

Start the macro recorder and name the new macro something like StoreFormulas
Select the first formula cell, press F2, then press Tab to go across or Enter to go down.
Repeat until you have recorded the last formula in the row/column, then stop the recorder.

To recreate the formulas play the macro. You could then fill down and/or across.

That's the quick and dirty method. It can be cleaned up once you know a bit of VBA but it should get you up and going.

Denis