Implementing targeting and optimization algorithms in VBA subroutines

Loading...

En provenance du cours de University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 1

460 notes

University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 1

460 notes

Cours 1 sur 3 dans Specialization Excel/VBA for Creative Problem Solving

"Excel/VBA for Creative Problem Solving, Part 1" is aimed at learners who are seeking to augment, expand, optimize, and increase the efficiency of their Excel spreadsheet skills by tapping into the powerful programming, automation, and customization capabilities available with Visual Basic for Applications (VBA). This course is the first part of a three-part series and Specialization that focuses on the application of computing techniques in Excel/VBA to solve problems. In this course (Part 1), you will: 1) create macros to automate procedures in Excel; 2) define your own user-defined functions; 3) create basic subroutines to interface with the user; 4) learn the basic programming structures in VBA; and 5) automate Excel’s Goal Seek and Solver tools and use numerical techniques to create “live solutions” to solve targeting and optimization problems. New to computer programming? The extremely intuitive and visual nature of VBA lends itself nicely to teaching and learning - what a fun way to learn to code! No prior knowledge in programming nor advanced math skills are necessary yet seasoned programmers will pick up new and creative spreadsheet problem solving strategies. After you have learned the basics of VBA, each module will introduce foundational and broad problems inspired by situations that you might encounter in the real world. To pass each module, you'll need to pass a mastery quiz and complete a problem solving assignment. This course is unique in that the weekly assignments are completed in-application (i.e., on your own computer in Excel), providing you with valuable hands-on training.

À partir de la leçon

(OPTIONAL) Numerical techniques and live solution strategies

Week 5 is OPTIONAL! Those who complete the Week 5 assessments (Quiz 5 and Assignment 5) will earn an Honors designation on their Course Certificate (see the first reading "WEEK 5 IS OPTIONAL" below). Week 5 delves into some important numerical techniques that can be used in Excel without the use of VBA. You will also explore ways in which the targeting tools in Excel (Goal Seek and Solver tools) can be automated in VBA. Finally, you'll explore some exciting "live solution" methods that have distinct advantages, especially for case studies where you have multiple scenarios that must be solved. Week 5 represents the most math-heavy module of either part of "Excel/VBA for Creative Problem Solving" but will provide you with some extremely useful tools that you can implement in solving a variety of real world problems. Even if you choose to skip/forego Week 5, you can always use this material as reference in the future!

Week 5 preview1:47

How to use the Goal Seek and Solver tools to solve targeting problems7:55

How to solve optimization problems using the Solver tool9:11

Fuel tank example and limitations of the Goal Seek and Solver Tools7:33

Automating the Goal Seek and Solver Tools8:29

Circular Calculations in Excel7:39

Implementing the bisection method in Excel8:59

Implementing a live solution of the bisection method to solve a case study8:55

Using the Golden Section search technique for optimization problems in Excel7:56

Live solution of the Golden Search technique for solving an optimization problem7:25

Solving a case study for friction factor using a circular calculation6:18

Implementing targeting and optimization algorithms in VBA subroutines8:45

Bisection method in a VBA function5:14

Preview of Part 2 of the course4:42

Rencontrer les enseignants

Charlie Nuttelman
Instructor
Chemical and Biological Engineering

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.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2018 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play