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.

Published: Oct 10, 2015 (Last update: Oct 13, 2019) | Tutorial
Top