how to use Solver in Excel VBA

[mvanhelden]

Hi all,

I'm trying to use solver to minimize a cell for a least square
regression. The minimization cell is a weighted sum of square
residuals. Three variables exist that define the regression line.
Another six cells define the bounds for the variables.

I get the following error:

Compile Error: Sub or Function not defined

Here is the code:

Sub WLS_VARIO()
'
Range("F14").Select

ActiveCell.FormulaR1C1 = "=RC[-2]/1.1"
ActiveCell.Offset(1, 0).FormulaR1C1 = "=RC[-2]/1.1"
ActiveCell.Offset(2, 0).FormulaR1C1 = "=RC[-2]/1.1"

ActiveCell.Offset(0, 1).FormulaR1C1 = "=RC[-3]*1.1"
ActiveCell.Offset(1, 1).FormulaR1C1 = "=RC[-3]*1.1"
ActiveCell.Offset(2, 1).FormulaR1C1 = "=RC[-3]*1.1"

Range("F14:G16").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
'
SolverReset
SolverOk SetCell:="$D$12", MaxMinVal:=2, ValueOf:=0
ByChange:="$D$14:$D$16", _
Engine:=3, EngineDesc:="Evolutionary"
SolverAdd CellRef:="$D$14", Relation:=1, FormulaText:="$G$14"
SolverAdd CellRef:="$D$15", Relation:=1, FormulaText:="$G$15"
SolverAdd CellRef:="$D$16", Relation:=1, FormulaText:="$G$16"
SolverAdd CellRef:="$D$14", Relation:=3, FormulaText:="$F$14"
SolverAdd CellRef:="$D$15", Relation:=3, FormulaText:="$F$15"
SolverAdd CellRef:="$D$16", Relation:=3, FormulaText:="$F$16"
SolverSolve

End Sub

Any help please

 

[benmcclave]

Sounds like you haven't set the "SOLVER" reference. There is a good article about how to do so here: http://peltiertech.com/Excel/SolverVBA.html