2. Systems of linear equations
Examples of solving systems of linear equations by XLPack are shown below.
Now, we will solve the following linear equations.
10x1 - 7x2 = 7 -3x1 + 2x2 + 6x3 = 4 5x1 - x2 + 5x3 = 6
These equations are conveniently written in the matrix form.
Ax = b
A = (10 -7 0) (-3 2 6) ( 5 -1 5) b = (7 4 6)T x = (x1 x2 x3)T
A is the coefficient matrix, b is the right hand vector, and x is the solution vector.
Two methods using worksheet function WDgesv and VBA subroutine Dgesv are shown below.
Solving problem with worksheet function
Enter the data of coefficient matrix A and right hand vector b into the appropriate cells in the worksheet, and select (order of the equations + 2) cells where the solution vector is to be placed (cells shown as X in the figure).
Click fx in the formula bar. Then select WDgesv worksheet function from category XLPack.
Supply the appropriate values for the requested parameters N (order of the equations, e.g. 3), Nrhs(number of columns of right hand side, e.g. 1), A() (range of coefficient matrix) and B() (range of right hand side vector).
Then press Ctrl+Shift+Enter.
Now, we’ve got the solution X=(0 -1 1)T. The next cell with the value of 0.078283 is the reciprocal of the the estimated condition number and the undermost cell with the value 0 is the return code (0 shows normal return). We selected extra 2 cells for displaying these information. You may select only the cells for the solution (3 cells in this example) if such information is not necessary.
Solving problem with VBA program
Let’s solve the same example problem by VBA program. The following is an example code using VBA library subroutine Dgesv.
Sub Start() Const NMax = 10 Dim N As Long, A(NMax, NMax) As Double, B(NMax) As Double Dim Anorm As Double, RCond As Double Dim Ipiv(NMax) As Long, Info As Long, I As Long, J As Long '--- Input data N = 3 For I = 0 To N - 1 For J = 0 To N - 1 A(I, J) = Cells(4 + I, 1 + J) Next B(I) = Cells(4 + I, 4) Next '--- Solve equation and compute condition number Anorm = Dlange("1", N, N, A()) Call Dgesv(N, A(), Ipiv(), B(), Info) If Info = 0 Then Call Dgecon("1", N, A(), Anorm, RCond, Info) End If '--- Output solution Cells(8, 5) = Info If Info = 0 Then For I = 0 To N - 1 Cells(4 + I, 5) = B(I) Next Cells(7, 5) = RCond Else MsgBox "** Error ** Info = " + Str(Info) End If End Sub
In this program, Dlange and Dgecon are used to compute a condition number. These are not necessary for just solving the equations.
After entering the data of matrix A and vector b into the cells, the following result was obtained by running the macro Start.
Symmetric coefficient matrices
If the coefficient matrix A is symmetric, WDposv and Dposv can be used. The procedure is same as above, but only the upper or lower triangular part of the matrix A (including diagonal elements) is referred. It is not necessary to set the data in the remaining triangular part.