3. Eigenvalue problems
Examples of solving eigenvalue problems of symmetric matrices by XLPack are shown below.
Example
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.