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.
Hi! I have a relatively simple question I can’t find the answer to anywhere… is Open Solver single or multi threaded? Or does it depend? Specifically, I’m asking in relation to a linear optimization model I’ve built with about 500 rows and 70 constraints, using Coin-OR CBC.
If I want to solve my model as quickly as possible, am I looking for the best single-threaded performance (a good i5/i7) or the best multi-threaded (e.g. Xeon dual CPU) setup?
Good question, there are a few different things that come into play. First, we need to know the distinction between building the model and solving, and how much time is spent in each.
When we are building the model, this is using VBA which is single-threaded. However, part of the workload during this phase is recalculating the sheet values in Excel, which will multi-thread if configured to do so. The amount of time spent in sheet recalculations will depend on the “complexity” of the sheet, e.g. how many different cells have to be recalculated after changing the values in the variable cells.
Once the model is built, the solver starts running (you can see exactly when this happens by enabling the option to show optimization progress in OpenSolver). CBC supports multithreading, although you may have to enable it explicitly using the `threads` option as an extra solver parameter. However, the multi-threading performance in MILP solvers does not usually scale linearly with the number of cores, particularly after passing 4-8 cores.
So in summary, there are a lot of pieces of the workflow that can/do run using multi-threading, but I’d recommend a smaller number of high performance cores because the benefit of additional cores drops off pretty rapidly past an i5/i7 and the time spent in single-threaded parts of the code is also non-neglible.
Amazing. Thanks for the answer. The setup part it definitely the “long” part of the problem… so single-threaded performance it is. I had Excel configured to multi-thread already but figured that had little effect on Open Solver.
Thanks again.
Greetings from Estonia. I’m trying to execute command “RunOpenSolver” from VBA, which only causes Excel to freeze and crash. What could be the issue?
Can you give more information on the error you are seeing? Have you added a reference to OpenSolver in your VBA?
I am using NOMAD to optimize a quadratic objective function implemented inside a macro that runs another simulation package to update the objective function internally. For constraints I only have upper and lower bound on my decision variable for now.
I used your NOMAD_Callback.xlsm as a guide and implementing similar approach. However I am consistently getting the following error:”One of the parameters supplied to NOMAD was invalid. This usually happens if the precision is too large. Try adjusting the values in the Solve Options dialog box.” I change the precision variable from 1e-3 to 1e-20 but that didn’t help and I don’t understand what is the problem. Your help will be appreciated.
There might be useful information about what is wrong in the NOMAD log file (“View last solve log file” in the menu)
How can I find a list containing the names for NOMAD solver parameters so that I can use it in OpenSolver.SetSolverParameters? OpenSolver.GetSolverParameters “NOMAD”, Sheet:=optsheet does not seem to be working.
Is there any way to set the correlation (or directionality (increasing or decreasing)) between the decision variables and the objective function from prior knowledge so that the optimizer only moves in that direction? One way I can think of, is to add an extra constraints, in which the relation depends on the current value of the objective function. This needs a constant updating of that constraints in every optimization iteration which I am not sure how to do.
There are parameter listings for all the solvers here: http://opensolver.org/using-opensolver/#extra-parameters
We don’t support adding extra constraints in the middle of a solve.
Thanks for your reply! I got the impression that the parameter names are different in the OpenSolver and original NOMAD implementation, for example BB_EXE is OpenSolver_Callback, right? What about other parameters?
Is it possible to add all the possible constraints at the beginning and deactivate some during run? Is it possible to update the constraints during run, for example change the relation from ?
The parameter names are unchanged from NOMAD. `OpenSolver_Callback` is our own custom parameter that we invented to allow calling your own macro inside the black box function, this is not passed to NOMAD itself at all.
There isn’t a way to disable or update the constraints like you say, because this would affect the results at all the points that NOMAD has already searched. The best bet would probably be to terminate the solve from within the callback, modify the model as you want, and then start the solve again, which you could automate using VBA
Hi, I get an error “OpenSolver 2.8.4 encountered an error: Mismatched parentheses” when trying to solve with Bonmin. Running on Win 7 & Excel 2010. Any thoughts?
Thanks,
Mike
This is probably caused by some of the formulae in your sheet that we don’t support (most commonly IF). We’d recommend trying to stick to only basic mathematical formulae like SUM and SUMPRODUCT when using the non-linear solvers as this is what they work best with. It’s usually possible to reformulate the model to avoid IF functions (potentially using binary variables).
Is there a version of this software that can take more than 300 decision variables?
There are no limits other than what your computer can handle!
Hi All,
Excited to give Opensolver a workout. Having a problem with my model using Bonmin nonlinear. I receive an error that says “Out of stack space”. Any thoughts on how to correct it?
thanks!
-Drew
This can happen in the nonlinear solvers if there are formulae that reference a large number of cells (e.g. a big SUM or SUMPRODUCT). This is due to a limitation in how we create the nonlinear model that we plan to revise in the future. For now, you might be able to work around it by breaking the sum into smaller pieces in different cells and then adding the results in a final cell.
Hi, I am using a Windows 10 with Excel 2013 version. I can’t use Open Solver, always says “Cannot Run the macro ‘OpenSolver_modelClick’. The macro may not be available in this workbook or all macros may be disabled.”.
Thanks in advance for the help guys.
Have you tried downloading OpenSolver again to see if that works? Also make sure that you have trusted OpenSolver and enabled macros when you open it.
Hello,
I successfully downloaded the Gurobi solver engine and obtained a license. When I go to solve a model using this engine through OpenSolver, however, I am receiving the error message “Error 1004: Method ‘LoadResultsToSheet’ of object ‘COpenSolver’ failed.” Any advice on why this error may be occurring and how to fix it? Thank you!
Can you please send a copy of the sheet to me (email hidden; JavaScript is required) and I will check it out
I am using OpenSolver in a VBA to loop and try and solve many different problems sometimes it cant come to a solution witch is fine but it brings up a box saying “No Feasible Solution” how can one avoid the box coming up and stopping the macro from running?
You should set the `MinimiseUserInteraction` parameter of `RunOpenSolver` to True: http://opensolver.org/opensolver-api-reference/#RunOpenSolver
Hi
I am using OpenSolver to optimize a supply chain network design. I have around 500 demand centres and 10 warehouses to service to them. I am trying to reduce the number of warehouses. but every time i run a box appears with “OpenSolver could not find an optimal solution, and reported: no feasible solution” written on it. The openSolver still works for some of the demand centres but completely ignores half of the warehouses. Let me know what to do
It is saying that your model is infeasible with the constraints you have added. If you know this is not the case and that there is a feasible solution then let us know
Hi
First, you did a great job with this solver.
Then when solving my problem, the set up phase is quite slow (around 1 minutes for 1000 variables, 4000 rows) while the resolution is immediate.
What should i do in order to reduce the set up phase ?
Thanks for your help
Regards
Mathieu
The time taken depends on two things: the number of variables and the time it takes to recalculate your sheet. Other than reducing the size of the model, the only way to reduce the building time is to ensure that there is nothing unrelated to the model on the sheet that is updated when the variable cells are changed (e.g. if you are plotting results from the model, the plot will be redrawn at each update which will slow down the build time).
Failing that, we would suggest trying out solverstudio.org which has a steeper learning curve but is much more powerful and fast.
Hi
Many thanks for your answer, I had several try but no success.
Actually, it’s the very first phase “setting up problem” that is very slow, i believe formulas should not be updated during this phase.
Is there any other possibility to speed up this phase (even in modifying the code) ?
Thks for your help
If there is nothing else you can do to reduce the sheet calculation time, then there isn’t much chance to make the model faster other than moving to Solver Studio
Hello,
I have a problem, I am trying to use opensolver from vba using solveradd, I need my variables to be binary and I use this in my code:
SolverAdd CellRef:=Range(“B25”, Range(“B25”).End(xlToRight)), Relation:=5
or
SolverAdd CellRef:=Range(“B25”, Range(“B25″).End(xlToRight)), Relation:=5, FormulaText:=”binary”
But in any case the solution is binary, I proved doing the model with the open solver window and it worked, so I know there is a feasible solution.
Can yo help me please?
Thak you so much.
I’m not quite following what you are saying sorry. Can you send through a copy of the sheet and a more detailed explanation of what isn’t working to email hidden; JavaScript is required?
Hello,
First of all the solver is amazing and with out you I would have a lot of difficulties in completing my thesis. However I have on final problem in my portfolio optimization and I thought you could help.
I would like to only have positive weights (no -short selling) but at the same time I would like a minimum investment of 2 % of each stock. When I add both constraints the solver only takes into consideration only one.. Can you help ?
Many Thanks
Felix
Felix, can you send a copy of the sheet to me at email hidden; JavaScript is required and I will have a look into it
First off I love this solver! It’s fantastic!
However, I’m now unable to update any constraints in an existing model or add constraints to a new model. I’ve uninstalled and reinstalled OpenSolver and continue to have the same issue.
Any ideas?
Same here. I basically can’t click on the constraints fields of the UI. One work around I found is to use the tab key until you get in the field you want (use the arrow keys to then select the operator you want = etc…).
Thank you both for reporting this. We have confirmed that this bug occurs on the October update to Excel on Windows and hope to release an update with the fix soon. In the meantime, using the tab key as Jeremy suggests will get around the problem
The tab key solution isn’t working for me.
I’ve managed to work around the problem using the Excel built-in solver add-in to add constraints; these show up in the OpenSolver model.
Note that if the solver dialogue crashes while adding a constraint you may end up with a named range “OpenSolver_Temp” that causes future problems so you may need to go into the Name Manager to delete that as well.
To all affected, we’ve just released version 2.8.5 which fixes this clicking problem. Thanks Dan for letting us know about the “OpenSolver_Temp” range, we will make the changes to clean that up properly.
Hi there.
Great product!
When I try to Solve a model I get the following error:
Error -2147220504: Unable to access the Bonmin solver at “C:\Users\Richard\AppData\Roaming\Microsoft\AddIns\OpenSolver2.8.3_AdvancedWin\Solvers\win64\bonmin.exe”
As far as I can tell I’ve followed all the instructions. And bonomin.exe is at the above location.
Cheers
Richard
Hi Richard, please try the new 2.8.4 release which fixes this error
Hi JAck, I have the same problem with the version 2.8.4
Best regards,
Santiago
Can you please confirm the problem is that it cannot start Bonmin? What operating system and what excel version do you have? Does CBC work ok? Andrew
Hi Jack,
I have the same Bonmin.exe problem – error: can’t find Bonmin message appears.
I’m using Excel 2013 on Windows 10.
Many thanks,
Alex.
Please try v2.8.4 which fixes this for some users. If you are already using 2.8.4 then please confirm bonmin.exe runs from the command line. Andrew
Hello,
I have a model that runs just fine with Windows version 2.7.1. I recently tried the version 2.8.3 And I get the following error: “Unable to access the CBC solver at […]\Open Solver\Solvers\win64\cbc.exe”.
I unzipped the Open Solver folder, I can see a cbc.exe at that location and in About OpenSolver… it shows:
“OpenSolver is correctly installed” and “CBC v (64-bit) at […]\Open Solver\Solvers\win64\cbc.exe”
What should I do?
Thanks in advance!
Sorry it is not working. We are trying to diagnose an issue that is impacting a very small number of users. Do you mind testing to see if the 2.8.2 release works for you? Also, can you please email a copy of the About OpenSolver text to email hidden; JavaScript is required? Thanks, Andrew