Eliminate Excel Circular Reference Warning

**Matt W** · 04-05-2011, 04:09 PM

Hi all. I am trying to disable or deactivate the formulas in several cells as part of the workbook close event and reactive them during the workbook open event. The formulas contain an intentional circular reference and I'm trying to convert them to a text string so they won't be recognized as a circular reference when the workbook is opened again until after I enable iteration as part of the open event, after which I want to convert the text strings back to formulas. The purpose of this is to avoid the circular reference warning when the workbook is opened by users. I continue to get an error message in my code at the cell.formula lines in both of my subs below. Any help would be appreciated!

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Worksheets("sheet1").Range("B14:B2000")

    .cell.Formula = "'" & cell.Formula

End With

Application.Iteration = False

End Sub

Private Sub Workbook_Open()

    With Application
        .Iteration = True
        .MaxIterations = 1
    End With
  
With Worksheets("sheet 1").Range("B14:B2000")
  
    .cell.Formula = Right(cell.Formula, Len(cell.Formula) - 1)

End With
  
End Sub

Many thanks in advance.

**nimrod** · 04-05-2011, 04:38 PM

"Range" does not have a object, method or attribute called "cell" so you will get an error . "Range" and "Cells" is generally an either or situation .. either you refer to a cells by the "Cells" object OR the "Range" object.
... maybe this code would help ? ... it's format the cells so they are text or general ..

Public Sub MakeText()
   With Worksheets("sheet 1").Range("B14:B2000")
    .NumberFormat = "@"
   End With
End Sub
   
Public Sub MakeRegular()
   With Worksheets("sheet 1").Range("B14:B2000")
    .NumberFormat = "General"
   End With
End Sub

**Matt W** · 04-05-2011, 06:31 PM

Thanks for your help and clarification. I have replaced my code with the subs you created and have run them in my Workbook_Open() and Workbook_BeforeClose subs as shown below. They change the number format back and forth as intended. However, this still does not prevent Excel from giving the Circular Reference Warning upon opening the workbook, which is my ultimate objective.

When viewing the formulas in the formula bar after the cell format has been changed to "text" via the code, there is no evidence that the formula has been converted to a text string, which is what I may need to avoid the circular reference warning?

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call MakeText

Application.Iteration = False

End Sub

Private Sub Workbook_Open()

    With Application
        .Iteration = True
        .MaxIterations = 1
    End With
  
Call MakeRegular
  
End Sub

Again, thanks for your help.

**shg** · 04-05-2011, 07:10 PM

Try this. In ThisWorkbook,

Const sRng As String = "B14:B2000"

Private Sub Workbook_Open()
    IterationOnOff Sheet1.Range(sRng), True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    IterationOnOff Sheet1.Range(sRng), False
End Sub

... replacing Sheet1 with the CodeName of the sheet of interest.

In a code module,

Sub IterationOnOff(r As Range, bOn As Boolean)
    With r
        If bOn Then
            Application.Iteration = True
            Application.MaxIterations = 1
            .NumberFormat = "General"
            .Formula = .Value
        Else
            .NumberFormat = "@"
            .Value = .Formula
            Application.Iteration = False
        End If
    End With
End Sub

**Matt W** · 04-06-2011, 01:48 PM

Thanks Shg.

I've added the suggested code and it was successful in eliminating the circular reference warning. But it also had an unintended consequence on the circular reference formulas in the worksheet by causing them to return a "0" value or deleting the formulas altogether.

I've attached a sample worksheet with the imbedded code, the circular reference formulas, and an explanation of what I need to achieve. I'm sure there is a solution here that I'm just not seeing.

Many thanks for the help!