Asked by:
Solver and VBA

Hello everyone,
I have to maximize a function using solver. I have to do it many times (iterations) and therefore, I would like to code something in VBA. I 've came up with the following code however there is something that is not working. I can maximize my value once using the solver tool however the following loop doesnt seem to work. Here is what I want to do:
1 SetCell:="$AE$27", MaxMinVal:=3, ValueOf:="E10", ByChange:="$Z$25"
2 Write the matching value in E10
3 SetCell:="$AE$27", MaxMinVal:=3, ValueOf:="E11", ByChange:="$Z$25"
4 Write the matching value in E11
I know I'm missing the writting part but just can't figure out why. Also, should I use a string?
CODE
Sub solver_p
Dim i As Integer, s As String
For i = 10 To 15 Step 1
s = Format(i, "0")
SolverOk SetCell:="$AE$27" & s, MaxMinVal:=3, ValueOf:="E(i)", ByChange:="$Z$25" & s & ":$O$" & s
SolverAdd CellRef:="$I$10:$I$260" & s, Relation:=4, FormulaText:="2"
' SolverAdd CellRef:="$M$" & s, Relation:=2, FormulaText:="$K$" & s & "^2"
' SolverOk SetCell:="$I$" & s, MaxMinVal:=1, ValueOf:="0", ByChange:="$N$" & s & ":$O$" & s
SolverSolve (True)
SolverReset
Next i
End Sub
Thank you
Question
All replies

I did this for a case where I had some nonlinear data and was getting a local minimum. I would step through creating a new init point and search for an overall minimum. I am giving the code here. I don't have time right now to look at your code but maybe this will help.
While Not done
SolverReset 'Init solver
SolverOptions Precision:=0.001 'Set precision
ws.Cells(begRow, kVmmc) = ""
ws.Cells(begRow, kVmdp) = ""
ws.Cells(begRow, kVmap) = ""
ws.Cells(begRow, kVk) = k
'Set location to solve for. Tell it to solve for minimum, set locations to change.
SolverOK SetCell:=ws.Cells(begRow, kVsumall).Address(False, False), _
MaxMinVal:=2, _
ValueOf:="0", _
ByChange:= _
ws.Cells(begRow, kVmmc).Address(False, False) & "," & _
ws.Cells(begRow, kVmdp).Address(False, False) & "," & _
ws.Cells(begRow, kVmap).Address(False, False) & "," & _
ws.Cells(begRow, kVk).Address(False, False)
SolverSolve UserFinish:=True
SolverFinish keepFinal:=1 'Tell it to keep solution
If k < 3000 Then
If ws.Cells(begRow, kVsumall) < kVsumallVal Then
kVsumallVal = ws.Cells(begRow, kVsumall)
kVmmcVal = ws.Cells(begRow, kVmmc)
kVmdpVal = ws.Cells(begRow, kVmdp)
kVmapVal = ws.Cells(begRow, kVmap)
kVkVal = ws.Cells(begRow, kVk)
End If
k = k + zStep
Else
ws.Cells(begRow, kVmmc) = kVmmcVal
ws.Cells(begRow, kVmdp) = kVmdpVal
ws.Cells(begRow, kVmap) = kVmapVal
ws.Cells(begRow, kVk) = kVkVal
done = True
End If
Wend


I am not sure I know what you are doing but look at the SolverOK statement.
SolverOk SetCell:="$AE$27" & s, MaxMinVal:=3, ValueOf:="E(i)", ByChange:="$Z$25" & s & ":$O$" & s
When i is 10, the above becomes
SolverOk SetCell:="$AE$2710", MaxMinVal:=3, ValueOf:="E(i)", ByChange:="$Z$2510:$O$10"
It will be rejected by Solver since E(i) is not something it understands not to mention that the bychange range spans...I don't know how many cells.
And, of course, it's nothing like what you wrote you wanted, which was:
SetCell:="$AE$27", MaxMinVal:=3, ValueOf:="E10", ByChange:="$Z$25"
To get that in your code you should use something like:
SolverOk SetCell:="$AE$27", MaxMinVal:=3, ValueOf:="E" & i, ByChange:="$Z$25"
Now, when i is 10 you will get E10 and when i is 11 you will get E11.
Also, it is possible Solver is stuck at a local optimum. If your search works the first time around, see if the following works:
Before the loop save the value of Z25 in some variable. Then, each time before calling the Solver functions return Z25 to this saved value.
Hello everyone,
I have to maximize a function using solver. I have to do it many times (iterations) and therefore, I would like to code something in VBA. I 've came up with the following code however there is something that is not working. I can maximize my value once using the solver tool however the following loop doesnt seem to work. Here is what I want to do:
1 SetCell:="$AE$27", MaxMinVal:=3, ValueOf:="E10", ByChange:="$Z$25"
2 Write the matching value in E10
3 SetCell:="$AE$27", MaxMinVal:=3, ValueOf:="E11", ByChange:="$Z$25"
4 Write the matching value in E11
I know I'm missing the writting part but just can't figure out why. Also, should I use a string?
CODE
Sub solver_p
Dim i As Integer, s As String
For i = 10 To 15 Step 1
s = Format(i, "0")
SolverOk SetCell:="$AE$27" & s, MaxMinVal:=3, ValueOf:="E(i)", ByChange:="$Z$25" & s & ":$O$" & s
SolverAdd CellRef:="$I$10:$I$260" & s, Relation:=4, FormulaText:="2"
' SolverAdd CellRef:="$M$" & s, Relation:=2, FormulaText:="$K$" & s & "^2"
' SolverOk SetCell:="$I$" & s, MaxMinVal:=1, ValueOf:="0", ByChange:="$N$" & s & ":$O$" & s
SolverSolve (True)
SolverReset
Next i
End Sub
Thank you
Tushar Mehta (Technology and Operations Consulting)
www.tusharmehta.com (Excel and PowerPoint addins and tutorials)
Microsoft MVP Excel 2000Present