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.
Hello everyone,
I need your help, I’m trying to solve a quadratic optimization problem with a large number of binary variables.
Is it possible to use gurobi to solve my quadratic objective function? Currently Open Solver only call the linear solver of gurobi.
Thank you for your help and sorry for my English.
You are correct. This is something we will add to our to do list but in the meantime, we cannot use Gurobi for non linear models. Andrew
Hi everyone, hope you can help me.
I try to solve a non-linear problem with Bonmin but when I press “Solve” I have this message:
Error 1004: La méthode ‘Range’ de l’objet ‘_Worksheet’ a échoué (in English: Error 1004: the methode “Range” of the object ‘_worksheet’ failed)
Do you know what is the problem and how to fix it?
Thank’s!
Probably a formula OpenSolver cannot translate into a Bonmin model. Are you using OpenSolver 2.7.1 (the latest version)? If not, please try this new version. If you are, please send your model to us at email hidden; JavaScript is required Thanks, Andrew
I am having the same problem and I am only using SUM() and SUMPRODUCT() functions. The Problem seems to originate from input data ranges coming from other worksheets. I am running a rather large problem instance so I am using several sheets for different data in order to maintain some form of readability. I have, of course, all my variables and constraints on the same page, which is rather limiting already.
However some of the constraints get data from other sheets. The error disappears when I copy all data from all sheets on to my main sheet where the solver is ran from. So the problem seems to lay with ranges on other sheets, like ‘Worksheet2’!CELLXX:CELLYY. Is there a way to fix this? It works fine using CBC but both BONMIN and COUENNE seem to have trouble interpreting ranges on other sheets. I am currently thinking about introducing some non-lin constraints but so far it is not possible since the non-lin solvers do not work for my model.
Best regards and thanks a lot for your ongoing effort with this great software!
Chris
Thanks for alerting us to this, and for sending us an example spreadsheet. Fixing this is now on our to-do list. Andrew
Hello
I got a simple linear program like this;
Private Sub CommandButton1_Click()
Worksheets(“Blad1”).Activate
SolverReset
SolverOk SetCell:=B63, MaxMinVal:=1, ByChange:=Range(“B45:B62”)
SolverAdd CellRef:=B45, Relation:=1, FormulaText:=P24
SolverAdd CellRef:=B45, Relation:=3, FormulaText:=O24
SolverAdd CellRef:=L45, Relation:=1, FormulaText:=L3
SolverAdd CellRef:=L46, Relation:=3, FormulaText:=L4
SolverAdd CellRef:=B63, Relation:=3, FormulaText:=B42
SolverSolve
End Sub
which is constructed as a somewhat trivial program in order to see if the program runs and fulfills all constraints. The Solver actually runs but does not fulfill the constraints, which I’ve tracked down as the cells containing commma: “,” as a decimal point is read as a String rather than a decimal. I’ve tested programs which takes integers as an input to the program, execute a program, and shows the results in commas if Application.DecimalSeparator = “,” is incorporated in my VBA Macro. It seems however that SolverSolve (from what I’ve read on Forums and my own impressions) can’t handle commas in its operations. Some people argue that it is possible to adapt the PC Global Language settings to English, but that would mess up all formulae etc. I’m using in all Workbooks, and since it’s for Work that wouldn’t allow interaction with other collegues’ workbooks…
Is there a workaround for this issue, allowing SolverSolve among others to be able to use comma as a decimal, or will this be patched in anytime soon? I’m using the Free Solver add-in at the moment.
Thanks in advance
This sounds like a question for Frontline. Or, you could try OpenSolver using a new VBA API; I would be very interested to know if we have got it right. You can also try building the model as above but solving it with SolverStudio from VBA. We try very hard to work in all languages, but don’t do much testing of scenarios such as yours. Please let me know how you get on. Andrew
Hi all,
For my thesis I need to solve a non-linear non-convex problem in inventory management.
The standard Evolutionary solver in Excel did not give the optimal solutions. Now, I used NOMAD, the solver gives an outcome, but it doesn’t fulfill all constraints, although I do not get any message or error.
Why are not all constraints fulfilled? Or where can I see what goes wrong?
I hope you can help.
Thanks,
Marije
I assume you only have a few constraints (perhaps 1 or 2)? NOMAD should be able to satisfy them; I am puzzled it has not. Please check you have the latest version (2.7.1). If so, can you please send your spreadsheet to email hidden; JavaScript is required so we can see if this is an OpenSolver or a NOMAD issue. I’d strongly suggest you avoid NOMAD, if at all possible, and try one of our other non-linear solvers (starting form lots of different starting solutions if you think there are lots of local optima). They are much more reliable if your model’s equations are straightforward. Hope this helps. Andrew
Good afternoon!
I really need help, but rather would like to congratulate the great work.
I’m no expert in mathematical models and due to need this much of a help. I do not know how I could restrict my solution within a range of values only.
I need all solution variables found to comply with a minimum amount, ie, would not appreciate as a variable solution when your result is between (0 to 9), so all solution variables must have a value equal to or greater than 10, otherwise remain with zero. Thus all variables that receive the value from 0 to 9 need not be suggested as a valid result.
How can devise such a solution?
Thank you so much!
Hey,
My name is Mia, currently I am using opensolver for my research internship. I need to develop a VBA program using CBC solver in order to automatize my model every time when I generate random numbers for several contraints. I try to extract the CBC Solver of Opensolver in order using it, but it is not very easy….
I would like to know if it exists a cbc solver that we can download and use it without using opensolver?
thank you very much.
Try Googling COIN-OR CBC. OpenSolver will not work with models that contain formulae that give a different random number on each spreadsheet recalculation. Andrew
Hi
I have a big model with about 40000variables which definitey used to solve but now I get an error message immediately. when I look at the details of the error report it seems to point to gurobi.
Your emailed error messages suggest Excel or CBC has crashed; please restart your PC and try again. Your model may be crashing CBC because of bugs in CBC; if that is the case, then please let us know so we can report these. Andrew
Hi,
I am sent to the VBA module:
Sub OpenSolver_ModelClick(Optional Control)
Dim frmModel As FModel
Set frmModel = New FModel
2853 frmModel.Show
Unload frmModel
2854 DoEvents
AutoUpdateCheck
End Sub
When I click the “Model” button. The 2853 frm.Model.Show line is highlighted yellow (VBA is in break mode).
How do I work around this?
Cheers, Ben
If you tell VBA to stop on every error (even the ones that are handled using On Error), then you can get this behaviour. Please check your VBA settings. Please also check that you haven’t set a breakpoint on this line. If this is not the issue, then is there an error message being shown? Is it Mac or Windows? What happens if you tell VBA to continue?
Hello,
When the sheet is protected the Solver menu is blocked from view but the OpenSolver menu is not, any way to block it?
Thank you
PS by the way, I am using 2.7.1 in Windows 10 and is working great.
Regards
There is no reason why you cannot optimise a protected sheet, and so I don’t think OpenSolver should be blocked. You can always protect the decision cells if you don’t want changes being made; then OpenSolver will (shoud?) fail with an error. Andrew
Hi,
I’m using OpenSolver to run a model with more than 1000 variables, and I would like to use the QuickSolve option to have the results for my optimization faster. But every time I close my excel spreadsheet and open it again, I have to redo the Initialize step and it takes almost 2 hours. Is there a faster option where I can save my model, and run it with the QuickSolve anytime I want without doing the Initialize step every time or doing it just one single time valid for all the following times that I run my model?
Thanks in advance,
Richard
Pleased QuickSolve is useful for you. Unfortunately Opensolver cannot save the intermediate Quick SolverStudio data. If you are keen, you could add this feature using VBA. We have addressing this on our long term Too list. Sorry I cannot offer a more positive answer (yet). Andrew
Thanks for your answer Andrew.
It’s great to hear that you are already thinking about adding this feature on the future. It could be very useful.
Do you have more information about how this could be done using VBA?
Thank you very much.
We build data on how the right hand sides of constraints vary with the parameters plus the model constraint equations. You could save this to disc and reload it later. Andrew
Hi Andrew,
Can you explain why it takes so long to “set up the problem”? After 1000 variables, it takes about one second per variable! We have about 14000 variables…
Is there any way to avoid this long time? by writing the constraint directly in VBA?
Thanks for your help.
Emmanuel
Emmanuel: We do the same thing for all variables, and so the slow-down is caused by Excel struggling in some way, perhaps with memory issues. There are no shortcuts apart from using QuickSolve (but this cannot save the model information when you close the spreadsheet), waiting for our ongoing research programme to address this (which it will, eventually), or move to SolverStudio (http://solverstudio.org). (We have colleagues who can help organisations with the move to SolverStudio, if you want to pursue this route.) You could always try a better PC as well. To speed things up, please remove any bits of the spreadsheet (such as graphs) that are not part of the model.
If you are willing to send your spreadsheet to email hidden; JavaScript is required, then we can include it as part of the testing in our ongoing R&D program; it sounds like a good challenging problem.
Sorry I cannot be of more help. Andrew
Hello,
I’m using OpenSolver to run a model with 7842 variables, then QuickSolve would be a better option. But I have a problem, every time I close my excel sheet, when I open it again, I have to redo the initialize step all over again and it takes almost 2 hours.
Is there an easier way to do this without this reinitializing step?
Some method that skips this Initialize step, where I can do it one time and save my model so I can just run it anytime I want?
Thanks,
Richard
We have no way, currently, of saving Quick Solve data when Excel quits. For big models like yours I think you will find http://solverstudio.org is much more efficient. Andrew
Hi
The old version worked fine. I have downloaded updated version but when I go to solve I immediately get a message ‘could not find optimal solution, reported:stopped on iteration limit”
any thoughts
thanks
Please check your iteration limit in the OpenSolver Options dialog. Maybe this has been reduced by accident? Depending on your solver, we may also be respecting this where we ignored it before. Sorry about the hassle, but hope this work for you. Andrew
That has worked. thank you
I am trying to use quick solver, but when I click “set quick solve parameters” nothing happens, no box shows up or anything.
Sorry it is not working. Please send us the text contents of your About OpenSolver in an email to: help at OpenSolver dot org. Then we can make a start at debugging this. Andrew
Thanks for alerting us to this bug. The next release will fix it. Andrew
I have just downloaded OpenSolver and am trying to use Quick Solve. When I try to click “Set Quick Solve Parameters…” nothing happens. No dialogue box pops up.
Sorry to hear it is not working. Can you please send us the contents of your About OpenSolver window (via copy/paste of the text, emailed to email hidden; JavaScript is required) so we can see what system you running? Thanks, Andrew
I am building an awesome fuel efficiency model for ships (540 variables) but want to be able to email it to my buddies on other ships who are not as fluent in excel / VBA. Is there a way to embed Open Solver into my file so I have a single file – or is there a auto detect and install VBA subroutine that you may have? I would love to make this idiot proof.
Any suggestions?
I have not tried it, but you could convert OpenSolver from an add-in to a stand-alone workbook. Then you could put your model in this workbook, and distribute it (and the associated OpenSolver files). Let me know if you try this and make it work; I’m sure others would be interested. Good luck. A>
I would put the OpenSolver folder in the same folder with the workbook then add this routine:
Private Sub Workbook_Open()
OSDirectory = ActiveWorkbook.Path & “\OpenSolver2.9.0_LinearWin\OpenSolver.xlam”
Set myAddIn = Application.AddIns.Add(OSDirectory)
myAddIn.Installed = True
End Sub
Hope it helps.
Cheers,
Thanks… good suggestion. Andrew
We have been using OpenSolver for a while now and have started to use the Gurobi solver via their cloud service. We have paid for the service via Amazon Web Services. We wanted to know if there are some “best practices’ when initiating the Gurobi Cloud service programatically.
Thanks for your interesting comment. Can I ask how you have found the experience of using Gurobi via Amazon? Is it working well? I am assuming that you are not using OpenSolver with the cloud Gurobi (or does Gurobi do something clever that I don’t know about?). As for best practices, I think you know more than we do, so please do tell us more about your approach. Thanks, Andrew
I’ve got experience scripting and using Gurobi on AWS, so I’m happy to help with any questions you might have. Is there anything in particular you were wondering about?
@Andrew: It’s very easy to hook up a local instance of Gurobi to any server/cluster of servers running Gurobi Remote Services (AWS or otherwise). It just requires you to drop in a new gurobi.lic file that points to the server. They’ve done a very good job making it easy to use!
Jack: I didn’t know it was so straight forward, and so OpenSolver+GurobiInTheCloud is an easy option. We should advertise this more, and perhaps add solver-specific menu items to support this (like SolverStudio does for each language). Andrew