Call Excel Solver from VBA

[MamboKing]

I'm doing some trials like with the following line in a VBA Sub:

SolverOk SetCell:=Sin(Cells(1, 30)), MaxMinVal:=2, ValueOf:="0", ByChange:=myarray()
SolverSolve

Question:
is that possible? I mean, passing to Solver functions and variables instead of Cells?

[xluser2007]

MamboKing,

Not sure directly with your example, how did it run with functions?

If it doesn;t work, just set a separe cell with the function applied.

E.g. A1 = Sin(Cells(1, 30)).

then call A1.

Also, with Solver, a general point, if you are doing many simulations, remeber to iclude the following lines at the end of teh Solver steps:

[vba]SolverSolve userFinish:=True
solverreset[/vba]
The first line automatically accpets the solver result (so you don't have to keep clicking accept, set False otherwise). the second line will ensure that the solver is reset when next simulation takes place (very important).

I've only used it once like this before, so this is the main stuff I remember using.

HTH

[MamboKing]

xluser,
Thanks, those options are really helpful.

FYI, I did assign the UDF to a cell. Not optimal but I can live with it.

The *real* problem is that the UDF receives parameters from VBA variables, not from Excel cells. I cannot change this.

Hence, Solver should change those VBA variables, not cells value.

I'm stuck...

Any idea?

[xluser2007]

xluser,
Thanks, those options are really helpful.

FYI, I did assign the UDF to a cell. Not optimal but I can live with it.

The *real* problem is that the UDF receives parameters from VBA variables, not from Excel cells. I cannot change this.

Hence, Solver should change those VBA variables, not cells value.

I'm stuck...

Any idea?

Given the above general points, you you call the UDF within VBA itself and evaluate accordingly.

E.g. if your UDF is called Solver_UDF(Argument1, Argument2),

And if you have variables defined in VBA (not sure why you can't put these in Cells and get VBA to call them separately), such as "interest_rate" (which would be Argument1 in your UDF), and "time" which could be Argument2 in your UDF above, then you could just do:

[vba] y = Solver_UDF(interest_rate, time)[/vba] and use y in your Solver.

note that y is purely called through VBA using your VBA variables.

Note, I've only used Solver using VBA a handful of times, but the above are just general ways I (a newb) could think of doing it via VBA.

I'm sure the pros can jump in here and help out.

HTH

[MamboKing]

I'm using Excel for data entry and data display only.

Users' entry into Excel are being parsed into a VBA matrix and all the computing work is done in VBA. Then, VBA results are displaied on Excel cells.

The idea of making Solver to solve y = input2Solver(var1, var2) is great but, I said, all the data are stored on matrix. Hence the code should look like:

y = input2Solver(matrix(m,n), matrix(j,k))

and the VBA compiler tells me to forget about it...

>> not sure why you can't put these in Cells and get VBA to call them separately

Well, the parameter that Solver should change for optimizing y=input2Solver() are already onto Excel, but for display only. Hence, changing those cells won't do anything at all.

Anyway, I must find a way to make Solver working on this.

If you have any other idea, I would appreciate if you reply.

Thanks

[MamboKing]

...then you could just do:
y = Solver_UDF(interest_rate, time)
and use y in your Solver.

Hence, I assume Solver will work on the variables and won't require any cell reference.
Correct?

[xluser2007]

Hence, I assume Solver will work on the variables and won't require any cell reference.
Correct?

Yeah that should work, just make sure your variables i.e. "interest_rate" and
"time" are Public variables, so you can ensure that they are picked up in any module that you use them in a UDF like this.

e.g.

[VBA]Public time as Long[/VBA] for example.