O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

9.3. Automating Tedious Problems with VBA

Problem

You now know how to use Solver and Goal Seek to solve nonlinear equations, but the trouble is you need to solve such an equation over a range of values, making manual, repeated application of Solver and Goal Seek tedious.

Solution

Automate this process using VBA, where you can programmatically call Solver or Goal Seek.

Discussion

Solver and Goal Seek are very handy tools for solving a wide variety of problems in Excel. What's even cooler is that you can use these tools directly in VBA code, without ever having to manually open their respective windows, click cells, and so on. This means you can automate many tasks that require Solver or Goal Seek, saving yourself a lot of time.

Let's reconsider the example equation from the Recipe 9.2. The equation is repeated here for convenience:

What if you wanted to plot this equation? If you wanted to plot a curve of C f versus R N , you'd have to use Solver or Goal Seek a number of times for each point along the curve you intend to plot. Maybe you don't want to plot this curve, but instead want to generate a lookup table with C f over a range of R N values. In either case, what a pain it would be to have to manually apply Solver or Goal Seek repeatedly for every value of R N . Imagine an even more complicated case where there's another parameter in the equation and you want to vary it systematically ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required