Solver Operating on VBA values?

[Phod]

Solver can be used for example to fit a model like a line (y=mx+b) to a data set (X,Y) using solver to minimize the sum of the square of the difference between the model Y's and the data Y's by changing the slope and intercept, m and b. Here the model calculations are made on a sheet using Ymodel = m*X+b at every X.

But what if VBA was used to generated the model data? Can solver still be exploited for its utility?

Reason:

I have VBA code that generates model data but its too complicated to easily do in a spreadsheet so I run the macro to generate and output these numbers. That is, if I changed a parameter sort of equivalent to the "m" and "b" above, I must run the VBA code to generate new Ymodel values.

I'm curious if solver can be used in a macro when the relationship between "the values its changing" and the value its "minimizing, maximizing, or driving to value" has to be updated thru a VBA routine since they are not linked in a direct way through expressions on a sheet?

Hope the inquiry is clear enough. Thanks.


-- removed inline image ---

 

[symbot]

I came here with the same exact question! Does anybody have an answer?

 

[shg]

You need to put the data on a worksheet and automate Solver in the usual way.

 

[symbot]

You need to put the data on a worksheet and automate Solver in the usual way.

I think you are suggesting to re-code the equations that are currently in the VBA code into a spreadsheet... That would work in theory. But in my case it isn't possible because the VBA code is quite complicated and includes a step-wise Eulerian integration that cannot be readily translated to equations in a worksheet.

Any other suggestions?

 

[shg]

The SetCell and the changing cells need to be on the worksheet; that's how Solver works.

You can have UDFs (but not Subs) in the SetCell calculation chain.

 

[symbot]

Ah ha! A User-Defined Function... That might just work...

I'll give it a try and report back. Thanks for the idea!

 

[shg]

You're welcome.

Make sure that all relevant arguments are passed to the UDF, so that Excel sees all dependencies.

Good luck.