Scribd
Upload
661 views7 pages

Bisection Method in VBA for Root Finding

This document discusses using Excel and Visual Basic for Applications (VBA) to develop an algorithm to find the root of a nonlinear equation using the bisection method. It includes developing an Excel spreadsheet prototype to test the bisection method, writing VBA code to implement the method as a user-defined function, debugging the code, and documenting the process. Key steps involve flowcharting the bisection method, coding the root-finding function and equation evaluation function in VBA, testing the function using the Excel spreadsheet, single-stepping through the code while debugging, and adding comments to document the code.

Uploaded by

Ser Gibo
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
661 views7 pages

Bisection Method in VBA for Root Finding

This document discusses using Excel and Visual Basic for Applications (VBA) to develop an algorithm to find the root of a nonlinear equation using the bisection method. It includes developing an Excel spreadsheet prototype to test the bisection method, writing VBA code to implement the method as a user-defined function, debugging the code, and documenting the process. Key steps involve flowcharting the bisection method, coding the root-finding function and equation evaluation function in VBA, testing the function using the Excel spreadsheet, single-stepping through the code while debugging, and adding comments to document the code.

Uploaded by

Ser Gibo
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 7

Excel & Visual Basic for Applications (VBA)

Algorithm development: bisection


Spreadsheet prototyping: bisection
VBA programming: bisection
Debugging and documentation

Algorithm Design
Statement of the objective
Written step-wise description of the method
Flowchart the method
Prototyping
Develop an Excel spreadsheet to test the method
and demonstrate its effectiveness & limitations
Program Development
Write, code and test VBA program
Documentation
Commenting of code
Nomenclature tables
Users guide
2

Case Study Problem


Develop a user-defined function in VBA that solves for a root
of a nonlinear equation, given the evaluation of the equation
on the spreadsheet based on input cells, and using the
bisection method.

f(x)
f(x2)

x1

xmid
x2

f(xmid)
f(x1)

Start with a picture of the method

The Bisection Method


finding a root of f(x)=0
1) obtain two starting estimates for the root, x1 and x2
2) evaluate f(x1) and f(x2)
3) check that f(x1) and f(x2) are of opposite sign. If
not, the starting estimates are not appropriate,
stop; otherwise, go on
4) compute the midpoint between x1 and x2: xmid,
and f(xmid)
5) if this is not the first xmid, check to see whether
the change between this xmid and the last one
is small, below some tolerance threshold. if it
is below the tolerance, stop and return xmid as
the solution; otherwise, continue
6) if f(xmid) and f(x1) have the same sign, let x1 become
xmid; otherwise let x2 become xmid
7) return to step 4)

begin

xmid =

get values for


x1 and x2 and
evaluate
f(x1) and f(x2)

x1 + x2
2

evaluate
f(xmid)

xold = x1

xmid + xold
< tol
xmid

F
F
bad initial
x1 and x2
values

do f(x1)
and f(x2) have
different
signs?

end

xold = xmid

do f(x1)
and f(xmid) have
the same sign
?

end

x2 = xmid

T
x1 = xmid

Flowchart the method

h 3 3Rh 2 +

3V

=0

or

f (h) = 0

V=
V

h 2 3R h

We will try a radius of 10 feet (3.05m) and a liquid


volume of 500 gal (1.9m3).
Initial estimates: h1 = 0

h2 = 2*R

Excel spreadsheet prototype


[ done before, see Class 10 ]

VBA Code Development

h1
h2
=Bisect(h1,h2)

Spreadsheet

Function Bisect(x1,x2)

End Function
Function f(x)

End Function

VBA Code

VBA Code for


Bisect Function

VBA Code for Equation Evaluation

Spreadsheet used to test Bisect function

10

Checking and debugging the Bisect function


Since Bisect is a function, you cant step into
it from the VBE, nor from Tools/Macro/Run
Put a breakpoint (F9) in the Function Bisect statement

Cause the Bisect function to calculate by


editing the cell where the function is used
changing one of the input cells to the function

11

Single-step the code, checking values along the way

Skip over stepping through Function f(x), once you know


it works ok, with Shift-F8 (step over) instead of F8
Skip past the repetitive loop execution by executing to
cursor position with Ctrl-F8
Run continuously to end with F5 or Run button
12

Documentation
add comments to your VBA code:
name and date
description of function
what it accomplishes
input requirements
warnings & limitations
nomenclature for variables
comments between and tagged onto
statements

13

You might also like