3. Eigenvalue problems

Note – The example worksheets are available for download. See 1. How to obtain and use example worksheets.

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.

  
        ( 10 -3  5 )
    A = ( -3  2 -1 )
        (  5 -1  5 )

 
Two solving methods using worksheet function WDsyev and VBA subroutine Dsyev are described below. These function and subroutine can find all eigenvalues and eigenvectors, or all eigenvalues.

Solving problem with worksheet function

Enter the data of symmetric matrix A into the appropriate cells (orange cells) in the worksheet, and select cells where the solution is to be entered and additional one row (green cells) and enter woeksheet function WDsyev.

The parameters required by WDsyev are Jobz, Uplo, N and A. Jobz = “N” if find eigenvalues only, and “V” if find eigenvalues and eigenvectors. Uplo = “U” if upper triangular part of A is used, and “L” if lower triangular part of A is used. N is the order of the equations (e.g. 3). A is the cell range of the symmetric matrix A.

In this example, all elements of symmetric matrix A 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.

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(5 + I, 1 + J)
        Next
    Next
    '--- Compute eigenvalues and eigenvectors
    Call Dsyev("V", "L", N, A(), W(), Info)
    '--- Output result
    For I = 0 To N - 1
        For J = 0 To N - 1
            Cells(5 + I, 5 + J) = A(I, J)
        Next
        Cells(5 + I, 4) = W(I)
    Next
    Cells(8, 4) = Info
End Sub

After entering the data of symmetric matrix A into the appropriate cells (orange cells), the following result was obtained by running the macro “Start”. Unlike the example using worksheet functions, you cannot obtain results by simply entering the value of matrix A, and you need to run a VBA program.

In this program, all the elements are read into the array A(). However, as in the worksheet case, Dsyev uses only specified upper or lower triangular part and diagonal elements.