title: initializing a spreadsheet, order of calculation
body: <p>I have written a VBA code that solves a set of algebraic equations whose coefficient matrix is Tridiagonal (known as Thomas algorithm).  The equations have the form</p>
  
  <p>A(i)X(i-1)+B(i)X(i)+C(i)X(i+1)=R(i)</p>
  
  <p>The values of A, B, C, and R are passed to the function that returns the values of X.  The VBA code is below.</p>
  
  <pre><code>Option Base 1
  Function TRIDI(ByVal Ac As Range, ByVal Bc As Range, ByVal Cc As Range, _
  ByVal Rc As Range) As Variant
  Dim BN As Single
  Dim i As Integer
  Dim II As Integer
  Dim A() As Single, B() As Single, C() As Single, R() As Single, X() As Single
  N = Ac.Rows.Count
  ReDim A(N), B(N), C(N), R(N), X(N)
  For i = 1 To N
  A(i) = Ac.Parent.Cells(Ac.Row + i - 1, Ac.Column)
  B(i) = Bc.Parent.Cells(Bc.Row + i - 1, Bc.Column)
  C(i) = Cc.Parent.Cells(Cc.Row + i - 1, Cc.Column)
  R(i) = Rc.Parent.Cells(Rc.Row + i - 1, Rc.Column)
  Next i
  A(N) = A(N) / B(N)
  R(N) = R(N) / B(N)
  For i = 2 To N
  II = -i + N + 2
  BN = 1 / (B(II - 1) - A(II) * C(II - 1))
  A(II - 1) = A(II - 1) * BN
  R(II - 1) = (R(II - 1) - C(II - 1) * R(II)) * BN
  Next i
  X(1) = R(1)
  For i = 2 To N
  X(i) = R(i) - A(i) * X(i - 1)
  Next i
  TRIDI = Application.WorksheetFunction.Transpose(X)
  End Function
  </code></pre>
  
  <p>The function works fine for linear equations.  If the equations are nonlinear, for example for the three equations below</p>
  
  <p>X(1)=1</p>
  
  <p>X(1)-2X(2)+X(3)=3+ X(1)^2</p>
  
  <p>X(1)+X(3)=2</p>
  
  <p>The solution is obtained iteratively using Excel’s iterative functionality, as shown below. (I can't yet post images, since this is my first post. If you email me, bumedoc@gmail.com, I can send you the spreadsheet too) </p>
  
  <pre><code>     A    B    C     D                  E
  
  1    A    B    C     R                  X
  
  2    0    1    0     =1          =TRIDI(A2:A4,B2:B4,C2:C4,D2:D4)
  
  3    1   -2    1     =3+E2^2     =TRIDI(A2:A4,B2:B4,C2:C4,D2:D4)
  
  4    1    1    0     =2          =TRIDI(A2:A4,B2:B4,C2:C4,D2:D4)
  </code></pre>
  
  <p>However, after saving the spreadsheet and opening it again, I get #value! Error in E2:E4 and D3.  I assume that upon opening the spreadsheet, Excel tries to initialize the cells that have circular reference and gets confused.  Does anyone have a fix for this problem?</p>