I am doing some mple calculations using the Excel Solver Add-In called through VBA. The very first call, after opening the workbook containing the macro code, calling Solver it returns integer no 5 which is the same as message "5 Solver could not find a feasible solution.". Now, if re-enter the same input again it runs without any problem returning an accepted solution. As long as the workbook is open, additional calls will keep on returning successfully.
How do get rid of the annoying first call that fails? It seems like it has some kind of inititalization problem.
Public Sub SolveTheProblem() Dim row As Integer, rowTarget As Integer, columnTarget As Integer Dim cellTarget As String Dim vars As String, leftSide As String, rightSide As String Dim targetValue As String Dim resultInt As Integer solutionFound = False targetValue = CStr(Cells(1, 1).Value) targetValue = Replace(targetValue, ",", ".") rowTarget = Cells(2, 1).Value columnTarget = Cells(3, 1).Value If columnTarget = 2 Then cellTarget = "$B$" + CStr(rowTarget) 'vars = "$B$" + CStr(rowTarget) + "," Else cellTarget = "$C$" + CStr(rowTarget) End If vars = "$B$" + CStr(rowTarget) + "," For row = 1 To 17 If Cells(row, 4).Value > 0 Then vars = vars + "$B$" + CStr(row) + "," End If Next row vars = Left(vars, Len(vars) - 1) 'SolverReset Application.Run "Solver.xla!SolverReset" 'SolverOptions MaxTime:=100, Iterations:=400, Precision:=0.00001, AssumeLinear:= _ ' False, StepThru:=False, Estimates:=1, Derivatives:=2, SearchOption:=1, _ ' IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True 'Måste anropa Auto_Run så att Solver Add-In installeras??? 'Application.Run "Solver.xla!Auto_Open" Application.Run "Solver.xla!Solver.Solver2.Auto_open" Application.Run "Solver.xla!SolverOptions", 1000, 400, 0.00001 'Application.Run "Solver.xla!SolverOptions", 1000, 400, 0.00001, False, False, _ ' 1, 2, 1, 5, False, 0.0001, True 'SolverOk SetCell:=cellTarget, MaxMinVal:=3, ValueOf:=targetValue, ByChange:=vars Application.Run "Solver.xla!SolverOk", cellTarget, 3, targetValue, vars 'SolverAdd CellRef:="$b$1:$b$16", Relation:=3, FormulaText:="0" Application.Run "Solver.xla!SolverAdd", "$b$1:$b$17", 3, "0" 'SolverAdd CellRef:="$b$21", Relation:=1, FormulaText:="0.999" 'Application.Run "Solver.xla!SolverAdd", "$b$21", 1, "0.999" Application.Run "Solver.xla!SolverAdd", "$b$22", 1, "0.999" For row = 1 To 17 If Not row = rowTarget Then If Cells(row, 4).Value > 0 Then leftSide = "$C$" + CStr(row) rightSide = "$D$" + CStr(row) 'SolverAdd CellRef:=leftSide, Relation:=2, FormulaText:=rightSide Application.Run "Solver.xla!SolverAdd", leftSide, 2, rightSide End If End If Next row 'SolverAdd CellRef:="$c$1", Relation:=2, FormulaText:="$C$1" 'SolverSolve 'SolverSolve userFinish:=True'The results of the SolverSolve function include:' 0 Solver found a solution. All constraints and optimality conditions are satisfied.' 1 Solver has converged to the current solution. All constraints are satisfied.' 2 Solver cannot improve the current solution. All constraints are satisfied.' 3 Stop chosen when the maximum iteration limit was reached.' 4 The Set Cell values do not converge.' 5 Solver could not find a feasible solution.' 6 Solver stopped at user's request.' 7 The conditions for Assume Linear Model are not satisfied.' 8 The problem is too large for Solver to handle.' 9 Solver encountered an error value in a target or constraint cell.' 10 Stop chosen when maximum time limit was reached.' 11 There is not enough memory available to solve the problem.' 12 Another Excel instance is using SOLVER.DLL. Try again later.' 13 Error in model. Please verify that all cells and constraints are valid. resultInt = Application.Run("Solver.xla!SolverSolve", True) Cells(31, 2) = resultInt 'Equivalent to selecting options and clicking OK in the Solver Results dialog box that appears when the solution process is finished. The dialog box will not be displayed. 'VBA Syntax 'SolverFinish(KeepFinal:=, ReportArray:=) 'Macro Language Syntax '=SOLVER.FINISH(keep_final, report_array) 'KeepFinal is the number 1 or 2 and specifies whether to keep or discard the final solution. 'If KeepFinal is 1 or omitted, the final solution values are kept in the changing cells. 'If KeepFinal is 2, the final solution values are discarded and the former values of the changing cells are restored. If resultInt < 2 Then Application.Run "Solver.xla!SolverFinish", 1 Else Application.Run "Solver.xla!SolverFinish", 2 End IfEnd Sub
I use solver all the time, and did my setup a few years ago, so I've not had these types of problems in a while.
Would you provide a simple spreadsheet with a solver problem and your code? Its more difficult to solve your problem as I have to create what I think is your exact environment to do so.
PS - I believe Peltier's examples are obsolete, as well. Solver.XLA is only available for Excel 2003 and prior. Excel 2007 and 2010 use Solver.XLAM. You'd need to add additional logic to run the right routine, if you're really trying to be Excel-Version independent.
See attached jpg for input data where the rates are placed in Excel B-column and the amounts are placed in Excel C-column. The tickbox sets whether user wants to have the rate (ticked) or amount fixed.
If I fill the sheet with the values from jpg and changes B1 from 0.02 to 0.01 I manage to capture the error inside the call to Solver dll I also get very strange values (see below, all values should be > 0 for instance) in the cells if I look before return of message "Solver failed to find a feasible ..." . If I step through the calculation ´pressing the Evaluate button (see attached jpg). Strange is that Solver is using calls through Excel4Functions. I thought that this was only running in older Excel versions like 97?
Please igore my previous .xls attachement, I found some errors in the formulas.
Here's an updated version! Running this version with the same input as described earlier, I get a result that seem correct BUT I still get the same returning integer = 5 ???
As you can see, the return value in cell B1 is close to its target value of 0.01 (cell A1)
Does this version work? Did you upload the correct one? I'll look at it, but the workbook_open event calls a function that you have completely commented out (CheckSolver) so that's why I'm asking.
Yes - you uploaded this file, already. I'm not sure how I'm supposed to help with this - perhaps you did inadvertently upload the wrong one? I'm supposed to be commenting on the failure with the first solve, so I need a working mock-up so I can assist.
I have managed to get this part of my program to work again.
I'm sorry this was confusing everyone, it sure confused me. The CheckSolver Sub was out commented because at one point I ended up with the step-solver at each call and was recommended to comment out that particular call. That helped me with that issue (it was the call to Auto_Open that was causing that behaviour).
Now, since a new problem arised (the one posted here) concerning only the very first call to Solver to return 5 (as described above), I continued trying all kind of different attempts to fix this unwanted behaviour.
I still don't know what's causing this but what I did was to do a "roll back" and checking out (from our CVS) the latest working version of the program and started to re-enter all changes again, one by one, checking all the time that all is working as supposed.
Yesterday I reached the point where all new changes were re-adapted but this "new" one IS WORKING. Why??? I haven't got a clue.
I attached a new test scenario with the correct version.
I'm changing the parameter Other Special Direct Cost from 0.00% to 2.00%
Ok - I'm working on this now. I've opened your file in Excel 2003. I see there's a solver setup already there. However, I assume I'm supposed to run the "SolveTheProblem" macro, is this correct?
Please note, I don't know what you mean by "Other Special Direct Cost from 0.00% to 2.00%" as you gave a dummy spreadsheet.
When I run the macro on the sheet you uploaded, I can see nothing happening - I'm going to wade in, but would appreciate some help in understanding what you sent me.
Ok - I'm working on this now. I've opened your file in Excel 2003. I see there's a solver setup already there. However, I assume I'm supposed to run the "SolveTheProblem" macro, is this correct?Yes
Please note, I don't know what you mean by "Other Special Direct Cost from 0.00% to 2.00%" as you gave a dummy spreadsheet.
The data that should be filled in is taken from the attached pictures from where you'll find the parameter named "Other Special Direct Cost ". In reality, the program (a windows form app) provides several input forms and this particular one is using Excel Solver in the background (through OLE) to calculate each time one parameter value is changed by the user. To test only the Excel part, you'll need to fill the sheet manually with the values shown in the attached picture. There are totally 17 rows to be filled, starting with parameter "Comission external" in row 1 and ends with "EBIT" at row 17. The B-column holds the rates, C-column holds the amounts. The corresponding cells in the D-column would either be 0 or equal the amount value of that row depending on the tickbox (ticked means value = 0 and vice versa).
Now, the cells in the A-column need to be filled as
A1 = target value
A2 = target row number
A3 = 2 means that the target value is a RATE (B-column), all other values means target is an AMOUNT (C-column)
As I previously informed you, I got it working now but I still don't know why.
Once again, thanks for your interest in trying to assist me. :)
I thought that it might be interesting in finding why it's suddenly is working but at the same time, I realize that this whole issue also might a bit complex to continue dig deeper into. I mean, to do thorough investigation, we'll probably also need to bring in the C++ code and that would mean too much effort at least for me at the moment. So, ... Yes, I'll guess we could close this question now.
All the best,
Peer
PS. How do I close it? Is it by accepting my own answer as solution?
You can grant points for effort (your choice) and select your solution as the best solution (but no points to you) to close it out.
You can just accept your answer as the best solution, to close out (no points to anyone), I believe.
If neither choice works for you, you can Request Attention, explain what you want to do, and the Moderator will help.
I was looking forward to getting into this, as I'm a solver user, myself. I don't use Peltier's code (but gave you the links to his modifications to make them work beyond 2003 to 2007+, and I suggest you do that, as Solver.XLA doesn't exist in 2007+), but I do have a separate subroutine to report the error codes, I force my solver to use all the time it needs to complete, etc.
I suggest you have a separate initialize routine to set all solver parameters (# iterations, precision, etc.) and a "ShowSolverTrial" routine that solver can call which can log what's going on - e.g., taking more time, did/did not converge, etc. I have my solver routines take the outputs to a Log file, as I process literally hundreds of solvers in a "run" so don't stand by to see what's happening, anymore...
Hope those hints help you as you move forward with solver!
I put the following in a separate module (actually, I have it as an add-in, so I don't have copies of this all over the place):
Public Function ShowTrial(xReason As Integer)Dim MyMsg As StringDim myWindowOpen As StringDim x As VariantDim ModelProject As String ModelProject = Range("[DavesAddins.xlam]Sheet1!a1").Value myWindowOpen = Application.ActiveWorkbook.Name Windows(ModelProject).Activate MyMsg = "SolverResults Trial Output = "'To run a function in another workbook :-'ReturnValue = Application.Run("filename.xls!functionname")'If it needs arguments :-ReturnValue = Application.Run("filename.xls!functionname", Arg1value, Arg2value) Select Case xReason Case 1: MyMsg = MyMsg + "Function called (on every iteration) because the Show Iteration Results box in the Solver Options dialog was checked, OR function called because the user pressed ESC to interrupt the Solver." Application.StatusBar = MyMsg x = Application.Run("'" & ModelProject & "'!myStatusbar", MyMsg) 'how I do my logging (a local sub to status bar and logsheet Windows(myWindowOpen).Activate ShowTrial = False ' to display trial, or false to just continue Case 2: MyMsg = MyMsg + "Function called because the Max Time option in the Solver Options dialog was exceeded." Application.StatusBar = MyMsg x = Application.Run("'" & ModelProject & "'!myStatusbar", MyMsg) Windows(myWindowOpen).Activate ShowTrial = False ' to display trial, or false to just continue Case 3: MyMsg = MyMsg + "Function called because the Max Iterations option in the Solver Options dialog was exceeded." Application.StatusBar = MyMsg x = Application.Run("'" & ModelProject & "'!myStatusbar", MyMsg) Windows(myWindowOpen).Activate ShowTrial = False ' to display trial, or false to just continue Case Else MyMsg = MyMsg + " Code " & xReason & " not understood" Application.StatusBar = MyMsg x = Application.Run("'" & ModelProject & "'!myStatusbar", MyMsg) Windows(myWindowOpen).Activate ShowTrial = True ' to display trial, or false to just continue End SelectEnd Function
This is my solver handler - believe similar/same as yours:
Sub SolverHandler(xSolverSolve As Variant)Dim myMsg As String'Documentation here = http://www.pe.tamu.edu/wattenbarger/public_html/PETE%20685/Latest%20Excel%20Programs/mlvbaref.htm Select Case xSolverSolve Case 0: myMsg = "Solver found a solution. All constraints and optimality conditions are satisfied." Case 1: myMsg = "Solver has converged to the current solution. All constraints are satisfied." Case 2: myMsg = "Solver cannot improve the current solution. All constraints are satisfied." Case 3: myMsg = "Stop chosen when the maximum iteration limit was reached." Case 4: myMsg = "The Set Cell values do not converge." Case 5: myMsg = "Solver could not find a feasible solution." Case 6: myMsg = "Solver stopped at user's request." Case 7: myMsg = "The conditions for Assume Linear Model are not satisfied." Case 8: myMsg = "The problem is too large for Solver to handle." Case 9: myMsg = "Solver encountered an error value in a target or constraint cell." Case 10: myMsg = "Stop chosen when maximum time limit was reached." Case 11: myMsg = "There is not enough memory available to solve the problem." Case 12: myMsg = "Another Excel instance is using SOLVER.DLL. Try again later." Case 13: myMsg = "Error in model. Please verify that all cells and constraints are valid." Case Else: myMsg = "Not Sure..." End Select myStatusBar (myMsg)End Sub
The rest is just making calls to solver. I don't worry about version control (probably why I haven't used Peltier's tip - but would, if I needed to ensure that, for sure!)
The real solution to this was that I used an earlier version of the C++ program and the Excel macros. Started all over to re-enter my changes one by one to end up with a "new" working application.
0
Featured Post
New feature! Upgrade and increase expert visibility of your issues with Priority Questions.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.