3. Eigenvalue problems
Examples of solving eigenvalue problems of symmetric matrices by XLPack are shown below.
Now, we will find the eigenvalues and eigenvectors of the following symmetric matrix.
A = (10 -3 5) (-3 2 -1) ( 5 -1 5)
Two methods using worksheet function WDsyev and VBA subroutine Dsyev are described below. These function and subroutine find all eigenvalues and eigenvectors, or all eigenvalues.
Solving problem with worksheet function
Enter the data of matrix A into the appropriate cells in the worksheet, and select cells where the solution is to be entered (λ and x in the figure) and additional one row. In this example, all elements of symmetric matrix are entered. But it is not necessary to enter all elements, since WDsyev refers only specified upper or lower triangular part and diagonal elements and does not refer the remaining triangle.
Click fx in the formula bar. Then select WDsyev worksheet function from category XLPack.
Supply the appropriate values for the requested parameters Jobz (“N” if find eigenvalues only, “V” if find eigenvalues and eigenvectors), Uplo (“U” if upper triangular part of A is used, “L” if lower triangular part of A is used), N (order of the equations, e.g. 3) and A() (range of coefficient matrix).
Then press Ctrl+Shift+Enter.
Now, we’ve got three eigenvalues λ and eigenvectors x. The cell below λ with the value 0 is the return code (0 shows normal return).
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 Dsyev.
Sub Start() Const NMax = 10 Dim N As Long, A(NMax, NMax) As Double, W(NMax) As Double Dim 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 Next '--- Compute eigenvalues and eigenvectors Call Dsyev("V", "L", N, A(), W(), Info) '--- Output result Cells(7, 4) = Info If Info = 0 Then For I = 0 To N - 1 For J = 0 To N - 1 Cells(4 + I, 5 + J) = A(I, J) Next Cells(4 + I, 4) = W(I) Next Else MsgBox "** Error ** Info = " + Str(Info) End If End Sub
After entering the data of matrix A into the cells, the following result was obtained by running the macro Start.