Hi. Your code is almost correct. Your SolverAdd & SolverOk lines have
"extra" stuff that are not part of the syntax. However, your Test()
subroutine never calls SolveProblem(), so that is why Solver never got
reset, and probably worked a little from previous attempts. It looks like
you want to generate a report also based on Report:=True.
This method did not work very well in previous versions of Excel. It just
didn't work, and had a few other bugs as well. In addition, the
documentation had a few errors as well. Most of the big issues are fixed in
the latest version of Excel, however, I see that most of the documentation
has disappeared. I want to point you to some articles, but they no longer
exists.
Anyway, here's the difference between the variables. Result is a variable
that holds the "result" of Solver. A return value of 0,1,or 2 means that
Solver found a solution. Count holds the number of times that Solver called
your macro.
If reason = 1 Then 'reason = 1 means that the function will be
called on each iteration.
Actually, the above statement is not totally correct. However, I can no
longer find any documentation on this feature.
Anyway, if your macro is running correctly, your function is going to be
called anyway. The variable that we are passing to our function (we are
using the variable 'Reason') holds a number that indicates "Why" we are
calling this function. A value of 1 means that Solver called this function
instead of showing the iteration message box. We would see this box
normally when we select the solver option "Show iteration results."
This function is not called on each iteration, but is called after an small,
unknown number of iterations. The other two reasons the function is called
is listed below in the code example.
I included a small example code below to give you some ideas. Run this code
("Demo") with a blank active worksheet. The code will set it up for you.
You should see about 5 iteration results on the sheet after it runs.
Write back if you have any questions. I included one test for an
undocumented bug. I've never been able to figure out why Solver's code
breaks the workbook name if there's a space in the name.
Option Explicit
Public Count As Long ' Counter
Sub Demo()
Dim Results
Count = 1
[A2:A4] = 1
[D:F].Clear
'// Spaces in the workbook name is a major undocumented bug with this
method:
If InStr(1, ThisWorkbook.Name, Space(1)) > 0 Or _
InStr(1, ActiveWorkbook.Name, Space(1)) > 0 Then
MsgBox "IMPORTANT: Remove all Spaces from workbook names",
vbCritical
End
End If
'// Use a random Polynomial equation:
[A6].Formula = "=A2+ 2 * (A3)+ 3 * (A4^ 2) + 10"
SolverReset
SolverOk SetCell:="A6", MaxMinVal:=3, ValueOf:="310", ByChange:="A2:A4"
SolverAdd CellRef:="A2:A4", Relation:=3, FormulaText:=4 'A2:A4 >= 4
SolverAdd CellRef:="A2:A4", Relation:=1, FormulaText:=10 'A2:A4 <= 10
SolverOptions StepThru:=True
Results = SolverSolve(True, "ShowTrial")
Select Case Results
Case 0, 1, 2
' Keep final values & generate answer report
Cells(Count, 4) = Count
Cells(Count, 6) = Range("A6")
SolverFinish KeepFinal:=1, ReportArray:=Array(1)
Case 4
'Target does not converge
'Perhaps stop, or give different starting values and do again
Case 5
'Solver could not find a feasible solution
'Your code here
Case Else
'Your code
End Select
End Sub
Function ShowTrial(Reason As Integer)
'// = = = = = = = = = = = = = = = = = = = = = = = = = = = =
'// Why was this function called while Solver was running?
'// = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Const xContinue As Boolean = False 'Excel XP
Const xStopRunning As Boolean = True 'Excel XP
'
Select Case Reason
Case 1
''// = = = = = = = = = = = = = = = = = = = = = = = = = = = =
' ' The Show Iteration Results box in the Solver Options dialog is
checked, '
' ' OR called because the user pressed ESC to interrupt the Solver.
''// = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Cells(Count, 4) = Count
Cells(Count, 5) = Reason
Cells(Count, 6) = Range("A6")
Count = Count + 1
ShowTrial = xContinue
Case 2
''// = = = = = = = = = = = = = = = = = = = = = = = = = = = =
' ' The Max Time option in the Solver Options dialog was exceeded.
''// = = = = = = = = = = = = = = = = = = = = = = = = = = = =
'// Is answer close enough, or do we want to keep going?
'// We'll quit for now...
ShowTrial = xStopRunning
Case 3
''// = = = = = = = = = = = = = = = = = = = = = = = = = = = =
' 'The Max Iterations option in the Solver Options dialog was exceeded
''// = = = = = = = = = = = = = = = = = = = = = = = = = = = =
ShowTrial = xStopRunning
End Select
End Function
