Using the Solver in a VBA function (not a macro)

[fm78]

Hi,

I got some kind of a standard problem but could not find any solution
for it up to now. Here the setting: I want to program a VBA function
(not a macro!). The function should optimize some expression depending
on its input parameters (a, b, and c) by using the Solver (e.g. I want
to call Solver for getting a solution rather than programing some
iterations myself). E.g. something like the following will be
required:

Function SolverInVBA(a, b, c)
x = 0 'some dummy as starting point for the optimization
y = a * x^2 + b * x + c 'just some stupid example
SolverReset
-----------------Here comes the problem-----------------
SolverOk SetCell:="y"(???), MaxMinVal:=2, ByChange:="x"(???)
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
SolverInVBA = "Solution of Solver"(???)
---------------------------------------------------------
End Function

The points where I have problems are marked with "(???)". In addition,
how do I tell the function to update once one of its input parameters
has changed? Has anybody an idea how to implement this?

Best,
Felix

 

[Tushar Mehta]

Solver works only with an XL worksheet. I suspect it is intentional.
Probably the folks who make it have a "premium" version that works with
in-memory structures. Visit solver.com

You could have the function create an XL worksheet, create and solve
the optimiation problem in the worksheet and then delete the worksheet.
Of course, that would be (a) slow and (b) still impossible with a UDF.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions