In this screencast, I'm going to show you how we can make a subroutine that's going to solve the bisection method. So let's first make flow chart. We start, and we're going to obtain the low and high, either from the user or we can set that internally in the subroutine. We need to start with a low and a high, that bounds our solution. Then we enter into a for loop, because what we're doing is we're doing this iterative process. The bisecting technique I'm going to do 20 times, so we've set, we go into a for loop. The first thing that happens in each iteration is we calculate the midpoint, which is the average of low and high, then we calculate the function values at low and mid. Next, we take the product of f(low) and f(mid). If that product is negative that means we either go from positive to negative in the left half or we go from negative to positive. In either case, that means our solution is in that left half. And if the solution is in the left half, then we want to zoom in on that interval and we make the high equal the old mid. Otherwise, if this product is not negative then that means the left half does not contain the solution and the upper half contains the solution. And in that case, the new low is the old mid, and then we wrap back around and we do another round of iteration. And finally, after performing 20 iterations in this case, we're going to display the results and we're going to end. So let's go ahead and do this in VBA. I've set up a sub here the things that we're need to dim or low, high, index I, mid, flow and fmid. So I dimmed all of those variables, then the user can define what the inital low and high bounds are, using two input boxes. Next, we're going to move on to this for loop, and inside the for loop, we have three lines of code plus a two way if then, and that's it. So I've got my for loop, I've got my calculation for the mid point. Now, we need to calculate the function values at low and mid. So what I'm going to do is I'm going to subcontract out this work to a function. So I've defined a function here, this is the function that we're trying to find the 0 of. And then I can easily calculate flow and fmid by using that function that I defined down here, implementing low and mid as the argument. And finally, I've implemented by two way if then, so if flow times fmid is less than 0, then the new high equals the old mid, otherwise the new low equals the new mid. And finally outside the for loop, I have a message box solution is, and I'm formatting the result to the hundredths place, using the formatnumber function. After the 20th iteration, we haven't recalculated a mid. So I'm just recalculating a last mid and that would be displayed in a message box. So let's go ahead and run through this using F8, enter initial low, I'm going to do 0. Enter high, 4 and then we go into our loop. Mid, we calculate the mid, we calculate flow by using this function, we calculate fmid by using our function. If flow times fmid is less than 0, so in this case flow is -17, fmid is -7. So we should skip this and go into the L statement and we keep going, looks like it's working. So what I'm going to do is just press the continue button here and it spits out our solution of 2.44. Which is what we got earlier in the course using the bisection method on this equation. Instead of defining the function internally, I'm going to adapt this code so the user can define the function. In order to do this, the first step is we need to dim a variable as a string, so I defined fxn, then we obtain that function using an input box. Now down here, instead of using the func function down here, we're going to use a couple of functions built int VBA, the evaluate and replace. We can solve the function that the input fxn for variable low. This is a little bit more advanced VBA, but we can use the evaluate replace whenever in our string fxn, wherever there's the string x. We're going to replace it with the low value, that's how we calculate flow. We're going to do the same for f mid, so now this should work if I step through this. We enter the function, so I'm going to do x cubed + x-17, just like we would enter in Excel. And we go through low bound of 0, upper bound of 4. And now it cranks through here, and instead of using the function as we did before, it's using the evaluate peplace. Flow down here is equal to -17, which is what we got using the other process. And then we can go through and I can just continue and it just give us a result of 2.44. So this is a way that the user can input a function that wish to find the zero of. We can also use kind of the same technique for iterative solving. The flowchart for iterative solving is shown here, it's very easy compared to bisection. Inside the for loop, all we're doing is to calculate the new x. We take f of the previous x and we do that 20 times in this case, and then we display the result. So we can modify our code to implement iterative solving. Now again thes is x = f(x) type of a problem. So I've modified a bisection sub, this is an iteration sub, we only need to dim i and x, and fxn as a string. We obtain fxn in the input box, the form is x = f(x). The user inputs an initial guess and then we do 20 rounds of iterative solving, and we output the result. So when we run through this, the function, I'll just put in 1. So this is x = 1/sin(x), that's our function. We're going to do an initial guess of, I'll just do 2, and we go through, we evaluate that, and we keep going. And I'm just going to continue, and it finds a solution of 1.11. Which is what we got earlier in this course when we did the screencast on circular solving. And finally, we can do this for the golden search technique, we start, we get a and b from the user. We calculate the golden ratio, we enter into a for loop, where we're going to iterate 20 times, just like we did in the other two techniques. Inside each iteration, we're going to calculate the new d, which is golden ratio times b-a, x1 a + d, x2 b-a. We're going to calculate the function values at x1 and x2, that's going to be fx1 and fx2. And then we go into a two way if then, if the function value at x1 is less than the function value x2, we're going to set the new a as the old x2. Otherwise, we're going to set the new b = x1, and we go back here. And after 20 rounds of iteration, we're going to display the result. So I've modified the sub, this is now golden search sub. We have to dim i is integer a, b, golden ratio, d, x1, x2, the function is a string, fx1 and fx2 is double. We calculate the golden ratio, we obtain the function, we obtain a and b from the user, then we enter into our for loop here. Inside the for loop, we define a new d, the new x1, x2, we use the evaluate replace to calculate the function values. We do our comparison between fx1 and fx2, and after 20 iterations, we output the results. So let's just go ahead and run this, I'm just going to use the run button up here. We run this, enter the function that you wish to minimize. So I could put in something that I've used before, x squared-6x+15. Enter initial low, 0, wnter initial high bound would be 10. And then it calculates the minimum function value of 6 occurs at an x value of 3. So you could use this algorithm to calculate the minimum of any function as long as you know the function and you know approximately in what bound your minimum is bound. So this screencast has shown you how you can implement by section, iteration, and the golden search technique into a sub in VBA.
