In this screencast I am going to show you how to use the bisection method in VBA to
solve complicated equations involving one variable.
This spherical tank is a metallic tank, and we want to know the volume as a function of
fluid height in this glass tube.
The equation for this looks something like this.
We are trying to set this equation equal to zero.
If we know volume we know radius of this tank we can solve for the height corresponding
for that, where height is just the height in this tube of the liquid.
What we want to do is set up a flow diagram.
What we're actually trying to do is create a function here, I am going to call it spherical
tank, and we want to be able to in Excel put this formula into a cell and we're going to
give it inputs "a" and "b", and then we're going to give it a volume and radius.
So these are the arguments of our function where "a" is the lower initial bound section,
and "b" is the upper initial bound.
The output is going to be the height.
We're going to start, and the user is going to input the function, and "a", "b", V, and
R. The next thing that's going to happen is I am first going to start out with a low.
A low value is going to equal "a", "a" and "b" are in feet, and "h" is all going to be
in meters.
Similarly, V is going to be in gallons, so we're going to have to make the conversion
to cubic meters in our code at some point.
We're going to say radius is in feet, h(high) is going to be equal to "b", and then we're
going to define and h(mid) as the average, so that's just "a" + "b" divided by 2.
So these are our calculations that we're going to be doing in our code.
Bisection method uses a lot of iteration, so you're doing the bisection method quite
a few times, and we're just going to say for this example, and this is something that can
be changed, we're going to do 20 iterations of the bisection method.
We have a count control loop because we're doing this a fixed number of times.
There are other ways to do this, but I am going to do a for/next loop.
We start out with i=1, the increment is going to be i=i+1, and then we're going to be going
until i is greater than 20.
So if i is greater than 20 we're going to exit the loop.
If i is less than 20 or equal to 20 then we're going to do another iteration of the bisection
technique.
We start out the bisection technique.
The way to determine if the 0 is in an interval is by looking at the function values.
So for example here we know that the zero has to be in this region because f(x) at the
lower bound times f(x) at the high bound is a negative number.
We're going to actually put in here a conditional: Is f(h(low))*f(h(mid)) is less than 0 then
the solution has to between h(low) and h(high).
If that's true we want to keep the same h(low), but then we want to redefine h(high) as the
old midpoint.
If that's false then we actually do the opposite where we're going to say that h(low) is now
equal to h(mid), and then after we do both of those we're going to come back together,
and regardless of both those pathways there's one more thing we need to do, and that's to
redefine the new h(mid), which is just the average of low and high.
So now we've redefined that, this is a single iteration of the bisection method, and when
we're done with that we actually go all the way back here.
We increment our i by one more, so now i is going to be 2.
Is 2 greater than 20?
No, we're going to keep going, and we're going to do this 20 times and we're doing the bisection
method and zooming in on the actual root of our equation.
Now the one thing you may be wondering is in here we have our function, so how would
you actually do that in VBA code?
We're going to outsource this calculation of the function values to another function,
and this other function will be called solve function, and it will be its own separate
function, and I think you'll see more of what I mean in a few moments.
The only thing we have left of our flow chart is the output.
Once we've done those 20 iterations we would like it to display an actual height, h(low),
h(high), and h(mid) are all going to approach the same value.
We are done.
Let's take a look at this in the code, in VBA.
What you see here I've named this function spherical tank, and I've got "a", "b", V and
R as inputs, and I just put some comments here, and comments you can denote with an
apostrophe.
"a" and "b" are in feet, R is in feet, and V is in gallons.
This is going to be pretty important, because lower down we're defining h(low) as the initial
"a".
So "a" is going to be in feet and we're converting to meters, so we've done that with h(high),
we've also done that down here with radius.
We have our for/next loop here.
If solve function, this is where we're using these function values.
So we're using a function within a function, and we look down here I've defined another
function, solve function, and that's just the function that we're trying to 0.
If we scroll over here you see that I've used application worksheet function pi.
If that function at the low bound times the function value at the midpoint if that product
is negative then we know that the 0 has to lie somewhere in that range, and then this
is where we do the conditional changes where we now say h(high) is the h(midpoint).
We recalculated new h(mid), otherwise h(low) is the new mid, and then we can keep going,
and you see I have 20 iterations.
We end that with the next i, I've also dimmed i up here as an integer.
The only thing left is the output, so the output of a function is the function name.
We have spherical tank, and I am just converting it to feet here.
One more thing I forgot to mention is volume is in gallons that they input into the function,
and down here in my solve function I am actually converting volume to cubic meters by using
a conversion factor.
If we go back to Excel I've put in a bunch of different values of volume, and now, and
I can just type in here =SphericalTank, and I am putting in "a" and "b", so I am going
to use 0 and 20, again this is in feet.
The next argument is volume, so I am just going to reference this cell, and then we
have radius at the end, and I've already named cell C2 radius, and when I press enter it
automatically goes through the bisection technique, it went through 20 iterations, and I can copy
this down and fill everything in.
Let me just real quickly summarize what we did.
We first set up a flow chart.
This process is going through the iteration bisection 20 different times.
If that's true then we redefine h(high) as the mid, and we recalculate h(mid).
If that's false h(low) is the mid and we keep going, and at the end of this we display h(mid).
We're taking advantage of another function, so a function within a function.
If we look back at the code again we're using our main function is SphericalTank, but we
are using another function, and this is the function that we're trying to zero.