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,
I’m really struggling to determine what is generating this error message:
Open Solver could not find an optimal solution, and report: Infeasible.
The model contains an Excel constraint $C$202 > = $E$202 for which instance 1 does not depend on the decision variables and is not satisfied.
Can you please point me in the right direction or provide a way I could provide my file to you?
This constraint cannot be satisfied because it does not change as the decision cells change.
Hi! im also encountering this error. the model ive been working consists of 1200 Decision variables and 200 constraints. i dont know but after ive rechecked the model so many times the problem still persists. i did a smaller version of my problem and it worked pretty well. is it because my model is so big that open solver cant solve it? what are the limitations of the opensolver in number of decision variables and constraints? thank you so much
I’m having trouble running the model. It says,
The specified solver, CBC could not be found. Make sure it is correctly installed or try another solver (at line 2810)
Please help!
Check that the OpenSolver Solvers folder path has no non-American characters in it. Andrew
No it doesn’t! could there be any other issue /?
Please email me the About Opensolver text. A dot mason @ auckland.ac.nz. Andrew
I’m having trouble running the model. It says,
The specified solver, CBC could not be found. Make sure it is correctly installed or try another solver (at line 2810)
Please help!
Check you have everything unzipped in the folders they came in. And make sure you have no non-American chaarcters in your path. Hope this helps, Andrew
I’m using 2.6.2 (but had the same error using 2.6). Model creation works fine. It’s non-linear, and I’ve selected the engine NEOS using Couenne. When I solve, I get this error message:
OpenSolver2.6.2 Error
OpenSolver2.6.2 encountered an error:
Mthod ‘ProcessSolverModel’ of object ‘CModelParsed’ failed (at line
(7144) (at line 2816)
Source = OpenSolver, ErrNumber=-2147220504
Your model contains a formula we don’t know how to translate, and our code is very poor at reporting whats gone wrong. The next release will fix this. In the mean time, try removing any Excel-specific formulae. sqrt() and sin() are ok; index(), indirect(), lookup(), offset() etc are not! Andrew
Thank you, Andrew!
Hello,
I am using opensolver to run through a number of different cases using VBA. Sometimes these cases will be infeasible due to problems with the input data.
What I would like do to is that when there is an error, the code will skip that step and move onto the next iteration. I have had tried using the code on the “Using OpenSolver” page:
Visual Basic
Dim Result as OpenSolverResult
Result = RunOpenSolver(False, True) ‘ do not relax IP, do hide dialogs
When I use this with an infeasible model, Result=-5. I was expecting it to return something like “OpenSolverResult.Infeasible”
Thanks for raising this; we’ll look into it. Andrew
We have checked the code (thanks, Jack), and it all looks fine. An infeasible result will give a Result of 5 (not -5 as you report?), which is the value defined for OpenSolverResult.Infeasible. Cheers, Andrew
Thanks for your reply – What do the other numbers returned for Result mean (5 being OpenSolverResult.Infeasible)?
I cannot remember the details, sorry; searching the OpenSolver source will find all of these for you. Andrew
If you have a reference to OpenSolver set, you should be able to type “OpenSolverResult.” and see the list of possible options in autocomplete. Note that you shouldn’t ever need to worry about what the numeric values are for each option, you can do the following instead:
Dim Result as OpenSolverResult
Result = RunOpenSolver(False, True)
If Result = OpenSolverResult.Infeasible Then
‘ do something
End If
Andrew, Jack – thank you for your assistance, I have got this working.
Many thanks for your wonderful solver – warmest regards, Adam
Hello,
Hopefully this isn’t a waste of your time, but I have open solver installed, and I have an academic license for gurobi. However, open solver says there is no Gurobi installation detected. My Gurobi installation works, I am able to solve the examples that come with it from Gurobi. Why does opensolver not detect my Gurobi?
Thank you for your time,
Richard
Please check if GUROBI_HOME (an environment variable) is set, and gurobi.bat exists in the location indicated by GUROBI_HOME. Hope this helps, andrew
Hi Andrew,
Thank you for the help, it turns out that the python script was in the wrong directory, I must have moved it by accident. On a separate note, I am currently working on a project which has 140,000 variables and 150,000 rows….the problem takes about 5 hours to set up but only a few minutes to solve, its a version of a p median problem. Any tips? I have solver studio but I prefer the way that the problem can be set up and constraints applied in open solver.
Your help is much appreciated,
All the best,
Richie
Pl;eased you fixed the missing file issue. To speed up OpenSolver, make sure nothing depends on your model cells, eg don’t have any plots or summary tables in the same workbook. Or, even better, move to SolverStudio…. the model is not too hard to write…. eg see https://github.com/JuliaOpt/JuMP.jl/blob/master/test/perf/speed.jl Hope this helps, Andrew
Hi Andrew,
I am a new user of OpenSolver, and I am using it with excel 2010 (64 bit).
I downloaded the things, added the corresponding reference into the VBA (success).
Now, I want to use the function RunOpenSolver as on the page, and adding that code, I have a compile error in VBA for the line OpenSolver = new COpenSolver.
Also, VBA cannot find the BuildModelFromSolverData method.
Do You hava any idea ?
Thanks : adam
Nothing comes to mind. Have you tried just calling RunOpenSolver False? That’s the easiest way… Andrew
This could be caused by a missing reference other than OpenSolver. Can you check to make sure that none of the references are marked MISSING? See http://www.cpearson.com/excel/missingreferences.aspx
Hi all
I am using the open solver 2.6.2 advannced for a non linear problem. When I try the bonmin or couenne solver i get the following message:
Type mismatch (at line 5405) ((at line 5104)(at line 4840)(at line 7144)(at line 2816)
Source = OpenSolver , ErrNumber = 13
The nomad engine works just fine. I have greek windows xp and greek excel 2007
Any help will be appreciated.
Sorry to hear it is not working for you. You are using features that are very new, and so less well tested. Are you able to send your spreadsheet to me at a dot mason at auckland dot ac dot nz. Thanks, Andrew
Andrew having an issue with my relatively simple model. It used to solve in around 38 seconds or so, and recently it just jumped up to over 240 seconds to solve. I havent drastically changed anything that should account for that. I deleted Opensolver and then reinstalled in and it is still slow, Any ideas on what would cause the jump in time?
Is the extra time being spent preparing the model (indicated by a count in the Excel status bar at the bottom left), or in solving (i.e. actually running the solver)? If the former, then maybe you have added plots etc to your s/sheet which are slowing things down. If the latter, then I guess you have integer or binary variables; such problems can take much longer given very small problem changes. Hope this helps. Andrew
Hi Andrew, I also have noticed a very large increase in preparation time for a relatively simple model…The preparation and solving used to take perhaps 30s. Now the preparation takes 10mins+….There has been no change to the model setup – the only thing that has changed is a slight change to the data. In the past i have noticed this and i was able to fix the problem by restarting excel. That has not helped this time however.
Has this change in preparation time happened as a result of upgrading OpenSolver to a new version, or only as a result of you changing your spreadsheet? Adding a slow formula to a spreadsheet could slow it down, but 30s to 10 minutes seems very odd. Have you added other sheets to the workbook that depend on your model cells? This can also slow things down. If we have a made a change that’s increased your solve time, then we’d like to follow this up. Andrew
Hi,
I am very enthusiastic of changing my existing VBA-Solver Model to OpenSolver. I use Solver/OpenSolver for Timeseries Optimization (with about 24 variable cells and 30 Constraint cells for a whole year… so its quite some variables…). Because I would run into a limit of max. constraints and variable cells I use VBA to optimise the timeseries step by step and change the variable-range accordingly in a loop. Each cycle I adjust the Range by resetting the solver with “SolverReset”, setting the target cell with “SolverOk” and changing the Constraints with SolverAdd.
In principle it works with the conventional solver, but requires quite some time, and the result also seems to be not optimal –> Because of some weird reason Solver uses only 5 iteration steps per optimisation cycle, even if I define max. 150 in solverOptions (“solverOption Iterations:=150”).
Now I was trying to change the macro to use OpenSolver in order to solve this issue and gain some computing speed on the way. Conveniently It seems that I only have to change one line in the VBA code (replacing “SolverSolve True” by “RunOpenSolver False, True”). However, it appears to me that OpenSolver doesnt use the Constraints that I Introduce with “SolverAdd” — The results look clearly different from what they should look like. When I use the conventional OpenSolver Interface to build the model (for a short part of the timeseries) everything works fine (although OpenSolver complains that he cant
Any suggestions why OpenSolver doesnt accept my constraints? I was digging through the Internet and the OpenSolver VBA Code the whole day but couldnt find a solution!! Would be greatful for a hint in the right direction!!
Best Regards
I am puzzled that OpenSolver is not working for you. I would suggest you look at the model (using the Model dialog, or, even better, the NameManager add-in) to see what’s going on. Please let us know how you get on. Andrew
Dear Andrew,
Thanks for the Hint with Name Manager. Not sure how it works, but it doesent work for the variables of the regular solver, does ist?
Anyway, I installed the Name Manager Plugin. I figured out that all names that start with “solver_” are created by opensolver, and that “lhs” stands for the lefthand side of a constraint, and “rhs” for the right hand side of the formula.
I compared the
– two Ranges handed over to the SolverAdd function in VBA (Using the variable monitor of VBA) and
– the solver_lhs1 and solver_rhs1 variable in the name manager.
the VBA variables were both ok.
solver_lhs was ok (although there were $, even if I didnt add them in the code)
solver_rhs was NOT on the correct range, and without $
Adding $ to the range in VBA seems to have fixed the problem, but some more testing to do!!
Thanks for the hint again, I will report back if everything works fine!
regards!
lukas
I am pleased you are making progress. Name Manager will show you a model built using either Solver or OpenSolver; they both store the model structure using Excel ‘Names’. Using relative references (i.e. no dollar signs) for a right hand side is a bad idea. I am surprised that Solver works in this case. Can you confirm that I have correctly understood this? Thanks, Andrew
Dear Andrew,
yes, you correctly understood this. In VBA I was adding the left hand side and right hand side arguments without absolut reference with ‘SolverAdd’. Checking with NameManager I learned that the left hand side argument will automatically be changed to the absolute version (with ‘$’), but not the right hand side argument which stays relative (Without ‘$’).
With the regular solver it worked fine. Or, at least it was leading to plausible results — I didnt check with NameManager, might be that the plausible results are just coincidence… This might also be the reason why the regular solver did only 5 iteration loops (even if I defined 150)… I will check when I make a trial run with the normal Solver again!
Thanks again for pointing me in the right direction, I will come back to you!
PS: might be worht mentioning in a Manual / “Using Solver”- section of this page that the right hand side argument (or actually ALL cell references!) should always be refered to in its absolut version — would have saved me a lot of headache ;D
Thanks for clarifying that. You will notice that both Solver and OpenSolver create models with absolute references for the right hand side. (Indeed, Solver forces relative references to become absolute.) I will add an issue to our TODO list to report any relative references in right hand sides; thanks for alerting us to this. Andrew PS: I have now added the following to our using VBA notes – “If using VBA, we strongly suggest all references in your constraints are absolute (not relative) to avoid unpredictable behaviour. (Thanks to Lukas for this feedback.)”
CPU is only at 15% utilization while solving with OpenSolver? All 6 cores go to 100% when solving with CPLEX. Why is OpenSolver not using more processing power? I tried threads=6 and it had no effect.
We provide a version of CBC that is compiled for single-core usage only, and hence only one of your six cores are being used. This is the standard recommended version of CBC. However, if you are feeling intrepid, you can (with a bit of work!) compile a multi-threaded version of CBC, which you can download from https://projects.coin-or.org/Cbc. I have never done this, and so cannot be of any help, sorry. Andrew
Hi all,
I hope you’re doing well. I’m very glad that you guys share such a wonderful tool as an open source.
I’m working on a school course project about optimization. I ran the model but it didn’t provide a feasible solution. I tried to find a feasible solution by myself and made sure that it satisfied every constraint I set. I’m wondering why the solver didn’t work.
And another question is that can the solver solve more than 10,000 variables problem?
Thank you very much. Look forward to your reply.
Best regards,
Jack
Hello,
I’m having an issue. I want to model a constraint where my variables are <= a list of cells.
How can I easily model the constraint "variable <= list of cells" ?
I am currently seeing just one solution : I2:I71 <= K2:K71
where my variables are in I
and my cells K are defined as following : K2 = MIN(J2:J$71) (my real constraints are in J)
But that is no more linear…
Do you have any idea how to fix it ?
Regards,
Hi all,
I hope you’re doing well. ‘m very glad to use such a wonderful tool as an open source. I ran the model but it didn’t provide a feasible solution. However, I tried to find a feasible solution by myself and made sure that it satisfied every constraint I set. I’m wondering why the solver didn’t work.
And another question is that can the solver solve more than 10,000 variables problem?
Thank you very much. Look forward to your reply.
Best,
Jack
I get the following error message @ download – from a company firewall
HEUR/Macro.VBA5
We have had 1 other report of the latest OpenSolver triggering a false alarm from antivirus software (BitDefender in this case). The mainstream virus checkers all confirm OpenSolver to be virus free. We have tracked these issues down to some of our new code… see my last post which discusses this. Andrew