7. Solution of nonlinear equation

Example of solving a single variable nonlinear equation, i.e. finding a root of arbitrary one-dimensional nonlinear function by XLPack is shown below.

Example

Now, we consider the following equation.

    f(x) = x3 - 2x - 5 = 0

To check the shape of f(x), let’s draw the graph of the function.

07-01

Since we see that zero point is located near x=2 from the graph, we will use [1, 3] as the interval including zero.

Solving problem with VBA program (1)

The following is an example code using VBA library subroutine Dfzero.

Function F(X As Double) As Double
    F = X ^ 3 - 2 * X - 5
End Function

Sub Start()
    Dim Ax As Double, Bx As Double, Rx As Double, Re As Double, Ae As Double, Info As Long
    '--- Input data
    Ax = 1
    Bx = 3
    Rx = Ax
    Re = 0.000000000000001 '1e-15
    Ae = Re
    '--- Compute zero of equation
    Call Dfzero(AddressOf F, Ax, Bx, Rx, Re, Ae, Info)
    '--- Output zero
    MsgBox Str(Ax) & " (Info = " & Str(Info) & ")"
End Sub

Dfzero can be used easily. Users only need to define the object function and call Dfzero with specifying the interval and the tolerance. However, users should supply the interval which surely includes a zero.

The result X = 2.0945514815 was obtained by this program.

Solving problem with VBA program (2)

The following is an example code using the reverse communication version VBA library subroutine Dfzero_r.

Sub Start()
    Dim Ax As Double, Bx As Double, Rx As Double, Re As Double, Ae As Double, Info As Long
    Dim XX As Double, YY As Double, IRev As Long
    '--- Input data
    Ax = 1
    Bx = 3
    Rx = Ax
    Re = 0.000000000000001 '1e-15
    Ae = Re
    '--- Compute zero of equation
    IRev = 0
    Do
        Call Dfzero_r(Ax, Bx, Rx, Re, Ae, Info, XX, YY, IRev)
        If IRev <> 0 Then YY = XX ^ 3 - 2 * XX - 5
    Loop While IRev <> 0
    '--- Output zero
    MsgBox Str(Ax) & " (Info = " & Str(Info) & ")"
End Sub

Instead of defining the object function as the external function, when returned with IRev = 1, users need to compute the object function value at XX, enter it in YY and call Dfzero_r again. For further details of reverse communication version, see here.

The same result with above is obtained by this program.

Top