Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to use EXCEL SOLVER in ACCESS VBA code?
- Thread starter wascom
- Start date
There are lots of options that can be set in solver in VBA, which are set using a number of commands. Either play around with "record macro" to see what VBA writes, or look in the help system (make sure you are looking in "Microsoft Excel Visual Basic Help", rather than the general VBA help that sometimes come up in the VB editor as a default)
SolverOk is used to set the basic parameters, so that e.g.
SolverOk SetCell:="$B$7", MaxMinVal:=3, ValueOf:="15", ByChange:="$B$4"
tells solver to make cell B7 equal to 15 by changing B4. (If I'd wanted to maximise B7, MaxminVal would be 1, or 2 to minimise.)
SolverAdd CellRef:="$E$1", Relation:=1, FormulaText:="$F$1"
tells it I want to add a condition (in this case e1 has to equal F1)
SolverOptions MaxTime:=100, Iterations:=1000, Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.001, AssumeNonNeg:=False
sets all of the various options (you'll have to look in the help for the meaning of all these, usually you can just omit the statement completely)
and
SolverSolve Userfinish:=True
actually runs the thing. The userfinish:=True is so that the macro doesn't need to stop to tell me what it's done and then wait for me to say OK.
One little hiccup - if you do a record macro, you may find that solver works fine when you do it in Excel, but the macro keeps falling over with a "Sub or Function not defined" error. This is because the solver add-in to VBA is separate from the solver add-in to VBA. To get the VBA version to run, you need to go into the menu tools/references and tick the "solver.xls" option.
Hope this helps a little - as I say you'll to experiment a bit to get exactly what you want. (Have fun!)
SolverOk is used to set the basic parameters, so that e.g.
SolverOk SetCell:="$B$7", MaxMinVal:=3, ValueOf:="15", ByChange:="$B$4"
tells solver to make cell B7 equal to 15 by changing B4. (If I'd wanted to maximise B7, MaxminVal would be 1, or 2 to minimise.)
SolverAdd CellRef:="$E$1", Relation:=1, FormulaText:="$F$1"
tells it I want to add a condition (in this case e1 has to equal F1)
SolverOptions MaxTime:=100, Iterations:=1000, Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.001, AssumeNonNeg:=False
sets all of the various options (you'll have to look in the help for the meaning of all these, usually you can just omit the statement completely)
and
SolverSolve Userfinish:=True
actually runs the thing. The userfinish:=True is so that the macro doesn't need to stop to tell me what it's done and then wait for me to say OK.
One little hiccup - if you do a record macro, you may find that solver works fine when you do it in Excel, but the macro keeps falling over with a "Sub or Function not defined" error. This is because the solver add-in to VBA is separate from the solver add-in to VBA. To get the VBA version to run, you need to go into the menu tools/references and tick the "solver.xls" option.
Hope this helps a little - as I say you'll to experiment a bit to get exactly what you want. (Have fun!)
- Status
Similar threads
- Locked
- Question
- Locked
- Question
