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.
When aattempting to use CBC opensolver we receive the following message:
“Variable de objeto o bloque With no establecido”
and the solver does not give any results.
We had previously solved the LP model with Opensolver but received the given error message after making some adjustments to the model and attempting to re-solve.
Thanks in advance for any help you can provide.
Sorry it is not working. Please try the latest pre release version 2.8.2. If it still fails please email us the sheet to email hidden; JavaScript is required Cheers, Andrew
I am trying to download Open solver on my mac and it kept giving me this message “Unexpected error (485)”
Sorry it’s not working. I suggest you try a different browser or computer. Andrew
Hi
I’ve same error as Thomas at Nvember 19, 2015
I cannot define contraints whith limits <= 1. (i could type 0, i could type 2, but no 1
My workaround is refer the constraint to a cell where there is the 1 value
Lluís
This is a very strange bug that happens when you have objects such as buttons on the sheet; Excel then interprets Application.Evaluate(1) as referring to an object, not the value 1! Please replace the “1” by the address of a cell that contains a 1. Note that we have fixed this ready for the next release. Andrew
Hello!
I am creating a model whose solution might be only 0 or 1, but it offers decimal values. How can I limit the solution to 0 or 1?
I am using opensolver with VBA, so I need to code it
Thank you
Add a binary variable constraint. Andrew
Hi!
I’m trying to use Opensolver to créate a timetable with some options that I introduce.
I need that when there is not a feasible solution it lets me know.
is there any option of getting it by code in VBA so the macro can change the options established withone my help?
Thanks in advance!
We have a full VBA interface, all detailed on the website. Enjoy. Andrew
I’ve been trying to install OpenSolver on my Mac/Excel 2016, and it just blows up on initial install with “Microsoft Visual Basic: Run-time error ’94’: Invalid use of Null”. Any suggestions, or is there anything I’m missing?
You asked for suggestions: Please ask Microsoft to finish their VBA editor for the Mac so we can actually properly test OpenSolver in Excel 2016! Sorry, but their Excel 2016 release is very limited; see this for more info. We’ll make a new release available once Mac development becomes possible. Andrew
Hi,
I am running a linear model and it works fine, through I have two issues:
1. When I look at some of the hard constraints (=), the values differ by .0005 but solver still doesn’t fails the constraint. I am ok with the output but my only concern is that if the values doesn’t exactly match, solver should have picked it.
2. I looked at the tolerance value and I thought may be that’s the reason, so when I tried to change it, it’s frozen. Do you think that’s normal?
Look forward to hear from you!
Regards,
Ajitesh
Getting small numerical errors is normal. Tightening the tolerance can cause problems; checking the CBC output may show you what’s really going on when it looks “frozen”. However, I would never recommend a tolerance of 0. Andrew
Hi,
I am running a NLIP model. After sometime I get “Open solver encounter an error: Out of string space”. Thanks for your help!
Probably a big non linear model. Is that right? Andrew
Hi,
Yes it is. So the solution could be to start with a smaller instance and grow the model sequentially?
Thanks!
Yes, you could try that. Or try to simplify your formulae. if you were happy to share your model, then we could use it to improve our non-linear equation processing (which is an ongoing task). Andrew email hidden; JavaScript is required
Hi,
Sure, I will send the workbook.
Thanks.
Hi,
I have a model for which optimized value equals (-100)
For some reason if I constraint the objective function to be small or equal of higher values, I get different results.
For example if I constrsaint the objective function f(x) to be f(x)<0 I don't get (-100) as a solution but rather slightly other value.
What can be the cause?
Thank you
Alex
Set your Branch and Bound tolerance to zero; I think you are finding random good-enough solutions. Andrew
Hi
thank you very much.
It solved the problem for wide constraints.
However, the problem remained for small constraints changes.
For example:
Optimal without constraint f(x)=-100
if f(x)<=-102 the result is f(x)=-105
if f(x)<=-103 the result is f(x)=-104
Alex
Hi
what can be the cause for that?
Thank you in advance
Alex
I don’t know. Can you email your workbook to email hidden; JavaScript is required so we can look at it? Andrew
Hi, I’m trying to run a non linear solver and I get “OpenSolver 2.7.1 encountered an error: Invalid procedure call or argument” When I click on Report Issue… it says “Run-time error ‘5’:” Invalid procedure call or argument.
Thanks,
Please try our 2.8.2 pre release. It fixes lots of bugs. Andrew
Hi Andrew, I tried the 2.8.2 solver first and received errors as follows:
OpenSolver 2.8.2 encountered an error: A model was found on the sheet “xxxxxxx” but the decision variable cells “xxxxxx” …….. could not be interpreted. Please redefine the decision variable cells, and try again.
Can you please tell us what it says the decision variables are in the error msg? And perhaps email the workbook to email hidden; JavaScript is required Thanks. Andrew
Error -2147220503: A model was found on the sheet XYZ but the decision variable cells (‘XYZ’!$C$19,’XYZ’!$C$25,’XYZ’!$C$26,’XYZ’!$C$27,’XYZ’!$C$29,’XYZ’!$C$30,’XYZ’!$I$22,’XYZ’!$I$23,’XYZ’!$I$24,’XYZ’!$I$26,’XYZ’!$I$27,’XYZ’!$O$19,’XYZ’!$O$20,’XYZ’!$O$21,’XYZ’!$O$23,’XYZ’!$O$24,’XYZ’!$U$20,’XYZ’!$U$21) could not be interpreted. Please redefine the decision variable cells, and try again.
I changed the actual sheet name to XYZ. The sheet name is actually 23 characters long with a two spaces included.
I had selected the Coin-OR Bonmin (non-linear) solver.
Thanks,
I think it is too long for Excel to evaluate. Try shortening the sheet name further or creating a new set of decision cells that are contiguous and so a small range like a1:a20 (and use formulae to copy these values into the original cells where they are needed).
I changed the sheet name to XYZ and reran the problem. I now get the same error as when I was using 2.7.1. ““OpenSolver 2.7.1 encountered an error: Invalid procedure call or argument” When I click on Report Issue… it says “Run-time error ‘5’:” Invalid procedure call or argument.”
I rearranged the decision variables into a single contiguous column and I get the same error.
Thanks,
I am able to run my model with Nomad. Model does not run with Bonmin or Couenne.
Are you able to send us the workbook, to email hidden; JavaScript is required ? Then we can try to debug this issue (which is probably caused by complicated formulae that we don’t know how to convert into model constraints). Andrew
I ran my model in opensolver 2.7.1, linear, using CBC. After running for almost 1 hour, following error message appears: “OpenSolver 2.7.1 encountered an error: overflow”.
The previous file works fine, but I add more data and decision variables in this version. What does really overflow mean? Thanks
Please try v2.8.2 (the pre-release version); this should fix the error, which occurs for very large models in some of our earlier releases. Please let us know if this fixes your problem. Andrew
I have a non-linear system. I am getting the following error when I try to run any of the non-linear solvers: Error 1004: No cells were found.
Thoughts?
Not sure. Are you using v2.8.2 (the pre-release)? This fixes a few bugs in the non-linear model error reporting. If not please email your model to us at email hidden; JavaScript is required Andrew
I just tried OpenSolver for the first time, and it ignores all of my constraints. However, Solver still works just fine. What am I doing wrong?
My guess if your model is non-linear.
I have a problem where a constraint is not being followed. It is an equal reference but the solver result has it as 0 = 1, which means it didn’t follow the constraint. How can this be returned as an optimal solution but not following the constraint?
Please check the model file (View Last Model File), and see what constraints are there. My guess is your model is non-linear, and so the constraints are not being picked up correctly (assuming you are using a linear solver like CBC). Andrew
This is note in the file:
\ BE30 = BF30
\ (A row with all zero coeffs) = +0
However, it is not the case and it should be linear since the inputs are all set to binary and thus when it changes to 1 for one of the inputs it does in fact make BE30 = 1.
BINARY
Z2 Z3 Z4 Z5 Z6 Z7 Z8 Z9 Z10 Z11 Z12 Z13 Z14 Z15 Z16 Z17 Z18 Z19 Z20 Z21 Z22 Z23 Z24 Z25 Z26 Z27 Z28 Z29 Z30 Z31 Z32 Z33 Z34 Z35 Z36 Z37 Z38 Z39 Z40 Z41 Z42 Z43 Z44 Z45 Z46 Z47 Z48 Z49 Z50 Z51 Z52 Z53 Z54 Z55 Z56 Z57 Z58 Z59 Z60 Z61 Z62 Z63 Z64 Z65 Z66 Z67 Z68 Z69 Z70 Z71 Z72 Z73 Z74 Z75 Z76 Z77 Z78 Z79 Z80 Z81 Z82 Z83 Z84 Z85 Z86 Z87 Z88 Z89 Z90 Z91 Z92 Z93 Z94 Z95 Z96 Z97 Z98 Z99 Z100 Z101 Z102 Z103 Z104 Z105 Z106 Z107 Z108 Z109 Z110 Z111 Z112 Z113 Z114 Z115 Z116 Z117 Z118 Z119 Z120 Z121 Z122 Z123 Z124 Z125 Z126 Z127 Z128 Z129 Z130 Z131 Z132 Z133 Z134 Z135 Z136 Z137 Z138 Z139 Z140 Z141 Z142 Z143 Z144 Z145 Z146 Z147 Z148 Z149 Z150 Z151 Z152 Z153 Z154 Z155 Z156 Z157 Z158 Z159 Z160 Z161 Z162 Z163 Z164 Z165 Z166 Z167 Z168 Z169 Z170 Z171 Z172 Z173 Z174 Z175 Z176 Z177 Z178 Z179 Z180 Z181 Z182 Z183 Z184 Z185 Z186 Z187 Z188 Z189 Z190 Z191 Z192 Z193 Z194 Z195 Z196 Z197 Z198 Z199 Z200 Z201 Z202 Z203 Z204 Z205 Z206 Z207 Z208 Z209 Z210 Z211 Z212 Z213 Z214 Z215 Z216 Z217 Z218 Z219 Z220 Z221 Z222 Z223 Z224 Z225 Z226 Z227 Z228 Z229 Z230 Z231 Z232 Z233 Z234 Z235 Z236 Z237 Z238 Z239 Z240 Z241 Z242 Z243 Z244 Z245 Z246 Z247 Z248 Z249 Z250 Z251 Z252 Z253
When Z207 which is binary is set to 1 then it makes BE30 = 1 so it doesn’t satisfy the constraints.
Interesting. What is the formula in BE30 that sets BE30=1 when Z207=1? If it is an if() statement, then it is not linear, and because we determine the coefficients for constraint equations by trying different (& arbitrary) values for Z207, we may not get the coefficient correct. We have discussed changing this for binary variables, specifically to handle a case such as yours (if I guess as to what is happening is correct.) Andrew
=SUM(AZ$2:AZ$253)
That is the formula in BE30
Strange. What about BF30?
I see now that BE30 doesn’t depend directly on Z207. Can you try setting all the decision variable values to 0, and then step Z207 through 0, 1, 2, and show us the values in BE30 and BF30 for both Z207=0, Z207=1 and Z207=2. This is similar to the test that the code does (or will do in the next release)
Column Z is binary. It is either 0 or 1. What exactly are you wanting? I can manually go through and when it is 0 then BE30 is 0 and when it is 1 then BE30 is 1.
The issue I am trying to avoid is a duplication. Perhaps there is a better setup for it. But imagine you have a product like a tomato that can be classified as either a fruit or a vegetable. You can only have 1 tomato but it can be only one of the categories. Right now it is giving two tomatoes, one as fruit and one as vegetable.
Z triggers a formula in AZ that is counting to see if the item has already been used and returns a 1,0 and then multiples by Z. So if Z = 1 and it is a duplicate, it will make AZ = 1. BE is summing column AZ.
This seems like a non-linear model as you are multiplying Z by something that depends on other variables. You should reformulate your model to remove this non-linearity. Andrew
What do you suggest for that issue? AZ is depending on Z which is the binary field. When Z = 1, then AZ becomes 1 immediately and it doesn’t change any other cells in the column…. and that column is what BE is summing. When the binary field is 1 it should immediately recognize that it doesn’t satisfy a constraint.
I suggest you write the constraint out as a linear equation (on a piece of paper). Googling for “optimisation modelling tricks” may give you some helpful ideas. Andrew
Hi all,
I’m trying to run a non linear solver and I get “OpenSolver 2.8.2 encountered an error: An error token was found while parsing the formulae.”
I suspect you have a #REF or similar in one of your formulae. Andrew