title: Circular Reference Error in VBA?
body: <p>I'm trying to write a function in Excel to calculate default probability. I have a spreadsheet with 3 columns of data. I want to be able to highlight a selection of data, and have the function return a value that's based on the data that I've highlighted . </p>
  
  <p>The function reads in data from a spreadsheet, and then executes an iterative procedure (Newton method) using the data. I'm trying to make the code refer to the first row of a selection of data on the spreadsheet, for the first "i". Then for the second i, I want it to refer to the second row, and so on. So far I've got this:</p>
  
  <pre><code>iNumRows = Table.Rows.Count
  
  maturity = Worksheets("KMV-Merton").Range("B2").Value
  For i = 1 To iNumRows
      equity(i) = SelectedRange.Cells("1").Offset(i - 1, 0).Value
      debt(i) = SelectedRange.Cells("2").Offset(i - 1, 0).Value
      riskFree(i) = SelectedRange.Cells("3").Offset(i - 1, 0).Value
  Next i
  </code></pre>
  
  <p>But this leads to a circular reference error on the spreadsheet. What am I doing wrong?</p>
  
  <p>The full code is as follows, in case the error is somewhere else. </p>
  
  <pre><code>Option Explicit
      Private Const mMax = 10000
      Public maturity As Double
      Private equity(1 To mMax) As Double
      Private debt(1 To mMax) As Double
      Private riskFree(1 To mMax) As Double
      Private iptr As Integer
      Public sigmaAssetLast As Double
  
  
  
  Function VarunModel(Table As Range, Optional EndCondition As Integer = 0) As Variant
      Dim iNumCols As Integer, iNumRows As Integer
      Dim i As Integer
  
      Dim SelectedRange As Range
      Set SelectedRange = Selection
  
      iNumCols = Table.Columns.Count
      iNumRows = Table.Rows.Count
  
      maturity = Worksheets("KMV-Merton").Range("B2").Value
      For i = 1 To iNumRows
          equity(i) = SelectedRange.Cells("1").Offset(i - 1, 0).Value
          debt(i) = SelectedRange.Cells("2").Offset(i - 1, 0).Value
          riskFree(i) = SelectedRange.Cells("3").Offset(i - 1, 0).Value
      Next i
  
      Dim equityReturn As Variant: ReDim equityReturn(2 To iNumRows)
      Dim sigmaEquity As Double
      Dim asset() As Double: ReDim asset(1 To iNumRows)
      Dim assetReturn As Variant: ReDim assetReturn(2 To iNumRows)
      Dim sigmaAsset As Double, meanAsset As Double
      Dim x(1 To 1) As Double, n As Integer, prec As Double, precFlag As Boolean, maxDev As Double
  
      For i = 2 To iNumRows: equityReturn(i) = Log(equity(i) / equity(i - 1)): Next i
          sigmaEquity = WorksheetFunction.StDev(equityReturn) * Sqr(260)
          sigmaAsset = sigmaEquity * equity(iNumRows) / (equity(iNumRows) + debt(iNumRows))
      NextItr: sigmaAssetLast = sigmaAsset
  
      For iptr = 1 To iNumRows
          x(1) = equity(iptr) + debt(iptr)
          n = 1
          prec = 0.00000001
          Call NewtonRaphson(n, prec, x, precFlag, maxDev)
          asset(iptr) = x(1)
      Next iptr
  
      For i = 2 To iNumRows: assetReturn(i) = Log(asset(i) / asset(i - 1)): Next i
  
      sigmaAsset = WorksheetFunction.StDev(assetReturn) * Sqr(260)
      meanAsset = WorksheetFunction.Average(assetReturn) * 260
      If (Abs(sigmaAssetLast - sigmaAsset) &gt; prec) Then GoTo NextItr
  
      Dim disToDef As Double: disToDef = (Log(asset(iNumRows) / debt(iNumRows)) + (meanAsset - sigmaAsset ^ 2 / 2) * maturity) / (sigmaAsset * Sqr(maturity))
      Dim defProb As Double: defProb = WorksheetFunction.NormSDist(-disToDef)
  
      VarunModel = defProb        
  End Function
  </code></pre>