Using Excel Solver and VBA User Defined Functions

[ExcelSolverUser]

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]

Did you try it?

TTFN

FAQ731-376

 

[ExcelSolverUser]

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]

You need to pass the parameters to the function

TTFN

FAQ731-376

 

[IRstuff]

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

TTFN

FAQ731-376

 

[ExcelSolverUser]

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]

Pass a and b to the function

TTFN

FAQ731-376

 

[ExcelSolverUser]

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.

 

[Denial]

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]

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

TTFN

FAQ731-376