Using Excel Solver and VBA User Defined Functions

Question

Using Excel Solver and VBA User Defined Functions

ExcelSolverUser (Computer)

(OP)

7 Jul 08 11:52

I would like to use Excel's Solver to minimize the sum of the square errors between observed data and a model. I know how to do this if the model is an Excel function in cells in the worksheet. However, I am working with a very complicated series of functions that I would like to be user-designed functions (UDF) written in VBA. Is it possible to use Solver to optimize a cell in an Excel file that is a function of a VBA UDF? I have attached a very simple model illustrating what I am trying to do, where "model" is a VBA UDF, and I would like to use Solver to minimize D9 by changing the parameters G4 and G5.

Thanks very much for any help you can provide.

IRstuff (Aerospace) · Answer 1 · 2008-07-07 13:44:35

Did you try it?

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

ExcelSolverUser (Computer) · Answer 2 · 2008-07-07 13:58:36

Yes, I have tried it using the file attached to my first post, but Solver returns the original input values, not the optimized values. If anyone knows how I can get solver to work with a VBA UDF, I would really appreciate the help. Thanks!

IRstuff (Aerospace) · Answer 3 · 2008-07-07 15:03:29

You need to pass the parameters to the function

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

IRstuff (Aerospace) · Answer 4 · 2008-07-07 15:05:36

You are aware, hopefully, that there is a built-in linear regression routine?

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

ExcelSolverUser (Computer) · Answer 5 · 2008-07-07 15:29:32

Thanks for your help, IRstuff. Could you please further explain "You need to pass the parameters to the function"? I realize that with the simple example that I have included that there are many ways to find the solution. However, this is just a very simplified example of what I am trying to do. The real function that I want to use is not linear.

IRstuff (Aerospace) · Answer 6 · 2008-07-07 15:31:53

Pass a and b to the function

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

ExcelSolverUser (Computer) · Answer 7 · 2008-07-07 16:02:15

Thanks, IRstuff! That worked.

For anyone who might be interested, here is the working file. The parameters that Solver is going to change must be passed to the function as arguments.

http://files.engineering.com/getfile.aspx?folder=5ef34efc-209a-49b7-87c1-d3

Denial (Structural) · Answer 8 · 2008-07-07 18:08:22

Glad you've got it working.
However I think that your approach would be more "robust" if you set the Solver to minimise your target cell rather than trying to force it to zero.

IRstuff (Aerospace) · Answer 9 · 2008-07-07 22:23:54

That'll get rid of the error message, but Solver will still have the closest solution, even with the error message.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

Come Join Us!

Posting Guidelines

Using Excel Solver and VBA User Defined Functions