Goal Seek and Solving an Equation with One Unknown Using VBA

The GoalSeek method of the Range object selects the value of a parameter (an unknown) that is the solution of an equation with one variable.

It is assumed that the equation has the form: the right-hand side of the equation is a constant (independent of the parameter), and the parameter appears only in the left-hand side of the equation, for example:

f(x)=x³−3x−5=0.

The GoalSeek method automates the Goal Seek procedure. This method computes the root using the method of successive approximations, the result of which generally depends on the initial guess. Therefore, in order to correctly find the root, you must provide a proper initial guess.

expression.GoalSeek(Goal, ChangingCell)

• expression — the cell containing the formula that represents the left-hand side of the equation being solved. In this formula, the parameter (unknown value) is referenced by the cell specified in the ChangingCell argument.
• Goal — a required parameter specifying the value of the right-hand side of the equation, which does not contain the parameter.
• ChangingCell — a required parameter specifying the cell reserved for the parameter (the unknown). The value entered in this cell before activating the GoalSeek method is considered the initial approximation of the root. The value returned to this cell after executing the method is the found approximation of the root.

The precision with which the root is found, and the maximum number of iterations used, are set by the MaxChange and MaxIterations properties of the Application object. For example, defining the root with an accuracy of 0.0001 within a maximum of 1000 iterations is set by the following code:

With Application
    .MaxIterations = 1000
    .MaxChange = 0.0001
End With

The GoalSeek method returns True if a solution is found and False otherwise.

Example

The following code (Listing 9.1a) searches for the root of the equation

f(x)=x³−3x−5=0.

using an initial guess of 1.

Solving an Equation (Standard Module)

Sub DemoGoalSeek()
    Range("A1").Name = "x"
    Range("A1").Value = 1
    Range("B1").Formula = "=x^3-3*x-5"  
    If Range("B1").GoalSeek(Goal:=0, ChangingCell:=Range("x")) Then
        MsgBox "Root: " & Range("A1").Value
    Else
        MsgBox "Root not found"
    End If
End Sub

Solving an Equation (Worksheet Module)

Private Sub CommandButton1_Click()
    DemoGoalSeek
End Sub