Welcome to the OpenSolver on-line help.
We hope that most of your questions will be answered on the Using OpenSolver page. However, if you still have a question, please post it below.
Building Linear Programming models
Writing optimisation models that only use linear mathematical equations and inequalities is not easy. However, most of the time you want to build these “linear programming” models (and avoid non-linear models) because these are easier and more reliable to solve using packages such as OpenSolver. (OpenSolver uses CBC by default to solve these linear programmes.) Check out this Wiki page on linear programming.
The AIMMS folks have a great online chapter describing how to take non-linear requirements (local copy of pdf) and model them using linear programming. (Thanks to Stu Mitchell of PuLP fame for pointing out this resource.) The NEOS guide is also a great resource. If you still have a modelling question (e.g., how do I express this requirement using the rules for a linear program), please ask it on https://www.or-exchange.org/.
Common Software Problems
Excel can have problems dealing with add-ons. Visit http://www.add-ins.com/how_to_repair_office.htm for a guide on fixing these.
1. OpenSolver not loading – July 2016 Office update (no error message)
In July 2016, MicroSoft released an update to Office 365 which prevents OpenSolver from loading unless the .zip file is “unblocked” before the files are extracted. (We expect this change to appear in other versions of Excel.) The symptoms are simply that OpenSolver does not appear in the Data tab; there is no error message shown. To fix this, delete your old OpenSolver files (but not the downloaded .zip file), right click the downloaded OpenSolver .zip file, choose Properties, and click the Unblock button which will show if the file is blocked. Once the file is unblock, close the properties dialog, and then un-zip the files and open up OpenSolver.xlam as usual.
2. Unable to find the external solver …
If OpenSolver reports this problem, then it could not find a solver file that comes with OpenSolver. Please check that you have extracted (i.e. un-zipped or uncompressed) all the files from the OpenSolver download.
3. The solver did not create a solution file.
If this error is reported, then it is likely that OpenSolver was unable to run the solver executable. This may happen if the OpenSolver files are in an ‘untrusted’ location, and so Windows will not let them be run. First check whether the solver is shown as found in the “About OpenSolver” form. If not, go to the folder identified in the “About OpenSolver” form and double click on this solver file. You should then see a command window open that gives the solver name and version information etc. If you don’t see this, then Windows should give you an error message to help you diagnose the problem. It may help to move the OpenSolver folder (and all the files it contains) into a location such as your Documents, or even into Program Files, and try again.
4. Solver crashes; OpenSolver reports no solution file
The files in the “Solver” folder provided with OpenSolver contain the actual solver code. These solvers are being continually upgraded as bugs are reported and fixed. If OpenSolver reports that no solution file was generated, then the solver may have crashed. This can happen on big problems when the solver runs out of memory, but also on smaller problems that happen to hit a solver bug. In such cases, you should check if a newer version of OpenSolver is available since we regularly update the solvers included with OpenSolver as they are released.
With CBC, a typical OpenSolver error for thois problem message might be: ‘The CBC solver did not complete, but aborted with the error code -1073741510’
If needed, you can download the latest version of CBC, Bonmin or Couenne from AMPL. Download the either the Windows or Mac version as appropriate. For Windows, get the 32-bit version if you have 32-bit Windows, or the 64-bit version otherwise. (The 64 bit version will solve much larger problems than the standard 32 bit version included in OpenSolver, so always choose this if possible. Right-clicking on Computer, and choosing Properties, will tell you if have a 32 or 64 bit system type.) Right click on the downloaded .zip file, choose Properties, and click the Unblock button if there is one. Then, open the .zip file, and drag the files into the right location in your OpenSolver Solvers folder (the win32, win64 or osx folder as appropriate), replacing the existing files. This may fix your problem.
Report a CBC bug
If after installing the latest version of CBC, CBC still crashes on you, then you may wish to report a bug. Please be sure to include the .lp file produced by OpenSolver; you can open this file using “View Last Model .lp File”. CBC bugs can be reported by either:
- The online CBC TRAC bug reporting system (which you will have to register for), or
- The CBC mail list
Switch from CBC to Gurobi
Since August 2014, OpenSolver has also supported solving models using the Gurobi solver. You can obtain a copy of this solver at no charge if you are at a university. Gurobi is faster and more stable than CBC, and comes highly recommended.
5. Repairing Excel
Sometimes it is useful (if all else fails) to repair your Office installation:
Windows
See these Microsoft Office 2010 and later instructions (or in Excel 2007, use Excel’s application menu, then Excel Options, then Resources, then “run Microsoft Office Diagnostics”). You might also want to look at http://support.microsoft.com/kb/166273, which talks about this.
Mac
Unfortunately there is no easy way to repair the Office 2011 installation. The best option is to follow these instructions to completely uninstall Office. You can then install Office again from scratch to get a clean version installed.
6. ‘Can’t find project or library’ : Missing Reference Errors
OpenSolver uses the “RefEdit” control, which we need but which can cause problems because of missing references caused by version changes. To check for missing references, using the VBA editor (which you access using Alt-F11), select OpenSolver (openSolver.xlam) in the Project window, choose Tools… References, and look for a “Missing:” line. If you see this, un-check that line, close the dialog, and choose File … Save OpenSolver.xlam to update the file. If this doesn’t help, try the above steps for repairing Excel.
7. Error 70: Unable to delete the file: C:\Users\XXXXXX\AppData\Local\Temp\log1.tmp
This errors happens if Excel crashes while OpenSolver is running, leaving log1.tmp open. Please restart your computer, which will close this file, and try again.
8. OpenSolver was working on my Mac. Then I updated to Excel 16.16.7 (released late 2018) and OpenSolver stopped working. What do I do?
We have had numerous users report this issue. We are looking for a Mac user with VBA experience to help us diagnose this problem. Please contact us at email hidden; JavaScript is required if you can help.
9. OpenSolver does not work in Excel installed from the Windows Store
Windows Store versions of Office applications do not support Add-ins such as OpenSolver; see this link, or this support page for another add-in, or this technical article from Add-in Express. This Dell article explains a little more. If you have a Windows Store version, it will display this in the Excel Version information, such as in the following example:
Version 1807 (Build 10325.20082 Microsoft Store)
The solution is to un-install Excel, and install the click-to-run versions, in which case the Excel version will change from ‘Microsoft Store’ to ‘Click-to-Run’. Standard installs (meaning not Office 365) don’t have this issue.
Urgent help required !!
I am running an allocation problem using open solver for 30,000 variables. The model was running all fine until few days ago and now its not giving output. I gives “Error 70” as mentioned in one of the comments above. I’ve restarted my system multiple times and have tried various other options but it continues to give me the same error message or goes in an infinite sort of loop once its allocation over 30,000 variables is complete.
Even when I try to run my file on other folks’ systems, it gives the same error. Any quick response would be highly appreciated !
Regards
Raman Sehgal
Integer optimisation problems can often become much harder because of a small change in the data. You can try increasing the branch and bound tolerance under Options or set a maximum run time. Good luck. Andrew. PS You can also try Gurobi.
Hi, there. Hoping you can help. How do I run a sensitivity report in Open Solver for Google Sheets?
You can’t, yet, sorry. On our ToDo list… Andrew
I am using CBC but not getting optimal results. I am getting near optimal results, but I can manually put in a better result in some cases. I don’t know if it is an issue with version 2.8.2 or CBC not being accurate but the result it is showing is .2 lower than the optimal result.
Have you set the Branch and Bound tolerance to 0% in the Options dialog? Andrew
That appears to have fixed the issue. Thanks for the quick response.
Hi,
I am not getting a correct solution for my LP in excel 2016. I did not have a problem with Excel 2013. Please let me know when a new version is available.
Thanks and regards,
hello, I am working on OpenSolver for my thesis. this is the first time I use this tool and almost the first to use an optimization tool, so it is difficult for me to understand what is going wrong.
I have created a model on Open Solver and it worked fine last week. in the mean time I sent it to my professor via email and add it on dropbox and never worked with it until yesterday. I added some more relations to my sheet which doesn’t relate to solver cell, at least not in the model! by pressing the “solve” button I got the following message: “no solver model with decisions variables was found on sheetTHESIS”. Because I couldn’t fix this, I made a new excel document and although the cells formulation and numbers are the same as in the previous xls file, the new one cannot find a feasible solution!
if there is an opinion of what should I do let me know,
thanks in advance
Hi,
I’m building a MILP with OpenSolver, as far as now it works just fine. Now, I’m wondering, which formulas are supported by the parser? I’m planning to built a model using HLOOKUP, VLOOKUP, INDEX, SUMIFS – references made by the formulas are fixed, so they do not change during optimization process. It would be just for the ease of implementing the model in Excel. I used this formulas for a very simple problem instance, it worked without any problem. Just want to have quick feedback on how well OpenSolver is working with these formulas for larger problem instance, before I invest to much time in modelling which will not work with OpenSolver.
Thank you!
If the sheet recalculates quick then our linear solvers will work fine. (They need to recalculate the sheet once for each decision variable.) Our non linear solvers need to parse the formulae and will fail on vlookups and similar. Hope this helps. Andrew
Hi Andrew,
I am experiencing the same exact problem as Coach and after setting the Branch and Bound tolerance to 0%, it still does not give me the optimal solution.
– I am using CBC linear
– The solution I know of is more optimal than the one given
Also, would the function work fine with the AND, MAX, MIN formulas in excel?
It is almost certainly an error in your model where you have a formula that is not linear (eg if, of lookup, or max, etc). And, no, you cannot use MIN, MAX or AND. Andrew
Hi there!
First of all thanks for your effort and work to build this fantastic application. I’m right now working on a transportation problem, where you choose between different transport concepts (=binary decision variables), within each, one shipping frequency out of a set of frequencies is selected (=binary decisions variables) and a then the quantity of units is allocated to the shippings (=integer decision variables). Besides that everthing else is linear. Since I’m rather new to the field, I have some questions on which solver I should or have to use for solving this problem. Is a linear solver ok? Or do I need to solve the problem with non-linear solvers? Up to now I tried both, but I’m not getting optimal results (e.g. I see right away that if i would use another transport concept I would get a beter solution than the solver calculates). Now I’m wondering is this a problem of which solver I should use or are the solvers just finding a local optimum? Are there options to define some kind of MIP gap (like passing such values to Gurobi solver?) and relax the integer constraints on the DVs? Or would you suggest to reduce the optimization problem to just allocating quantities while keeping shipping concept and frequency constant and then changing them and running the optimization by VBA code, so that I would run an optimization problem for every combination of concept and frequency, finally choosing the best value out of these solutions?
Thank you very much!
Pleased you are starting to use OpenSolver. I suggest you track down a modelling guide; the free online AMPL book is a good example. Andrew PS: You cannot get local optima for linear programmes, only for integer ones.
Hi,
I’m running a Linear CBC model on Open Solver 2.7.1 and I’m getting the following error message
‘The CBC solver did not complete, but aborted with the error code -1073741510’
When I click on ‘More details’ I get the following message:
‘Error -2147220501: The CBC solver did not complete, but aborted with the error code -1073741510’
Any idea what this issue could be?
Many Thanks in advance and Thanks for creating such a great solution!
Diego
Just adding on my previous comments, some of my variables are integers and it is a 144 variables problem.
Thanks
Diego
This indicates that the CBC solver is crashing. Please see http://opensolver.org/help/ for some ideas. Hope you get it working. Andrew
I am using version 2.7.1 and trying to use “set quick solve parameters”. However, the function for “set quick solve parameters” is not working in my laptop, after I select and click the menu, nothing shows up for me to select a cell in my spreadsheet as the parameter.
Please try the v2.8.2 pre release; that should fix your problem. Andrew
hi!
i would appreciate if anyone can help me on this problem.
I am a supply chain student, and i am doing the master thesis on distribution planning using the open solver.
However, when i tried to solve the model all the decision variables are shown as 0. But there is no report on ‘no feasible solution’ or any other issues. So i wonder what does this indicate?
Thanks in advance!
Paris
Hi, I would appreciate if you can help me with the following problem I met.
I stopped my opensolver run in the middle. Then when I tried again, an error message window popup saying “Open
Solver – User Interupt Occurred
You have pressed the escape key. Do you wish to cancel?”
This message keeps popup after I restart “excel” and after I reinstall the “open solver”. This message occurs to the old spreadsheet with my data and model and new spreadsheet without any data and model.
Thank you and look forward to your advice.
Never heard of that before. Have you tried re-starting the PC? If you can reproduce it with the same workbook on another PC, then we’d love to see the workbook. Andrew
Thanks for the reply. After I restarted my PC, the problem has gone.
Hi!
First of all, congrats for this amazing tool! OpenSolver has been a much more flexible for me than Cplex/Concert.
So, I’m developing a hybrid strategy to solve large combinatorial problems and I need to set up an initial solution in a MIP model. There are some specific function for this in OpenSolver?
Best regards!
Our upcoming release 2.8.3 (coming very soon) adds support for passing warm start solutions to Gurobi. The way it works is that if the solution in the sheet is integer-feasible when the solve is started, it will be used as the warmstart.
Passing a feasible solution to CBC is more complicated and will be added in a later release
I’m getting an “Overflow” error when using OpenSolver. It occurs at the end of the solve. I fear I’m hitting some sort of limitation, because if I reduce the number of the data points I’m solving for – and don’t change any constraints, it works. My problem has 35,325 variables and 42,267 rows. Any thoughts?
This is a bug that shows up for large problems in the
latest pre-release version 2.8.22.7.1 release. Goingback to version 2.7.1to the pre release 2.8.2 should fix this.The bug will be fixed in the next release. (You can also edit the vba code to change Cint to Clng on the error line of you prefer.)Andrew (with the versions corrected… thanks to Jack for pointing this out).Hi!
I’m trying to build a model with this fantastic tool but an error shows when i use the function add constraint. It says that i dont have the same type as required. Might it be a problema with the >=?
I have written it as follows:
AddConstraint Range(“rngManana”).Cells(i, j), “>=”, Range(“rngManana”).Cells(i, j), , Worksheets(“Manana”)
It should make the solver to leave the cell as it was before when it already contained a 1. But, if it had a 0 it offers the possibility of changing it.
Thank you!
Hi Maria, please see the following example of how to use the API, you need to use our constants for the relation types rather than “>=” etc.
https://gist.github.com/JackDunnNZ/c4eda47f3dfda205274553b4b3b3f057
First… Opensolver is awesome! Thank you for building and supporting it.
I’m having a problem with the performance of a model build. An older version (that was, in fact, bigger) builds super fast. As best I can tell, everything is the same (minus the size). Internally, formulas are the same. The model itself is the same (values are different, but this shouldn’t affect the model build, right?). We made lots of changes to other worksheets and code in the workbook. We have probably rebuilt the model several times, but in it’s final form, it’s basically the same.
Can you point me to place to investigate the potential problem? I could submit the worksheet, but it would take some real time to scrub it of sensitive data.
Thanks in advance,
Chris
I used name manager to review all of the hidden names. Best I can tell, all of the Opensolver named ranges are the same, save the number of rows. To put in perspective, the current version builds at a rate of about 1 variable per second. The old version at about 40 per second.
Wow… that really has slowed down. I know that a really complicate workbook can break Excel’s quick recalculate, and force a full recalculate on. Maybe this has happened to you? Andrew
The build speed depends on the speed Excel can recalculate at. For example, simply adding a plot of the decision variables or using the constraint left hand side values in formulae on another sheet can slow things down. Could this explain your slow-down? Andrew
Andrew, that’s exactly what happened. We had calcs on other sheets referencing inputs from the solving sheet. Thanks!
Great; pleased you could identify the problem. We have a plan for these sorts of workbooks, but it is in the future. Andrew