Help

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:

  1. The online CBC TRAC bug reporting system (which you will have to register for), or
  2. 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.

2,138 thoughts on “Help”

  1. Hello everyone,

    using OpenSolver I have constructed a model with a relatively large amount (~ 2400) of decision variables. Trying to define more variables, an error message popped up saying “Cell Range specified invalid – must be valid range that does not exceed excels internal character count limits” – does that mean that, although OpenSolver could potentially process more, excel has reached its limits and if so, is there a way around it?

    Thanks very much in advance!

    Moritz

    Reply

    1. OpenSolver can handle bigger problems, but you will need to create a smaller (eg contiguous) range that contains the decision variables so that you do not exceed the character limits. (You can put formulae into the current decision variable cells on your sheet to copy their values from the new decision variable range.) Hope this helps. Andrew

      Reply

      1. Works perfectly – thanks a lot! Maybe this is a general LP/MIP-design-advice to obey; define large continous ranges, instead of numerous smaller ones!

        Reply

  2. Hello,

    My name is Aline Takahashi and I am a student of Industrial Engineering.
    I am working on a project about mathematical programming (blending problem). For this project, I am required to solve the problems using VBA and the Solver plugin for Excel.
    For the next phase of my project, I need to use VBA and the ‘Open Solver’, but I am not familiarized with this tool yet. I haven’t understood how to use the functions of the ‘Open Solver’.
    I would like to know if there is any possibility of receiving a sample problem utilizing the Open Solver, or if you have any material that could help me to understand better the tool.
    Thank you very much for the attention,

    Aline Takahashi.

    Reply

    1. Pleased you are using OpenSolver. If you know Solver, using OpenSolver is easy. For using VBA, please see

      Reply

  3. Hi
    I have recently downloaded Opensolver. I am using Mac Excel 2011 and have copied contents into Add-ins folder and have included Opensolver.xlam into my excel>Tools>add-ins. I restarted my excel several times. But I can’t see Opensolver in Data tab. Is there any problem with my process of installation.

    Thanks

    Reply

    1. Try opening it manually to get it to load into Excel. Once it’s loaded, you can go to “OpenSolver > About OpenSolver” and check the option to start automatically with Excel, which tends to be more reliable than using the Addins folder

      Reply

  4. Hi,
    I would like to know if it’s possible to set up the variable cells as Integers ?
    Thanks in advance for your help !

    Reply

  5. The Open Solver tool has proved to be a great decision support tool for a dedicated fleet that I manage. Recently we came across a solution from this tool that violated a constraint that defined the Solver change cells area to be integers and greater than zero. We’ve decreased the tolerance to 0 but still has one cell as a negative 3 value and not executable in real operations. Any suggestions? Thanks.

    Reply

    1. The >=0 lower bounds are only applied to variables that do not have another lower bound applied to them. Could you perhaps have another bound applied to this variables (via a constraint directly on the decision variable)? It seems unlikely, given the problem you have described. Assuming not, can you please check the model .lp file to see what it specifies for the rogue cell? If you see “A6 FREE” under BOUNDS then we are allowing A6 to go negative. No entry for A6 would mean A6>=0. Please let me know what you find. Andrew

      Reply

      1. Thanks for the quick reply. We will provide the information you referred to tomorrow. We do have multiplie constraints defined for this named range, first as “int” and then as >=0 set up dynamically within a VBA macro. Thanks. Tom

        Reply

        1. Thanks. Please also check the model that results after running the VBA (check this using the model dialog). Thanks, Andrew

          Reply

          1. Hi Andrew,
            I looked at the model.lp file and all the Open Solver variable cells are set up as “free”. Any suggestions you can provide would be appreciated.

            Thanks,

            Tom Bauer

            Reply

            1. Tom: Please confirm you have “Make unconstrained variables non-negative” turned on, and that this is giving the error in our latest v2.8.2 pre-release version. Assuming so, do you mind sending the spreadsheet and the .lp file to us at email hidden; JavaScript is required? Then we can see if it is our bug. Thanks, Andrew

              Reply

          2. Hi Andrew,

            We looked at the model.lp file and the “rogue” cell is showing as free along with all the solver variable cells. We added an additional constraint to have the “rogue” cell = to 0 just to see what happened and a new rogue cell was created . . . . the old wack a mole phenom. Although it’s really not a rogue cell if the boundary status is free based on your previous comments. Do you have any suggestions on getting the boundaries in the optimization to set up to no negative variables in the solution? I know this has to do with a lot of constraints “must ship” in the model that is creating this issue or at least that is what I suspect. I love the tool and using for planning fleet operations has proved to be very beneficial. Thanks, Tom Bauer

            Reply

  6. Hi All!

    I’ve got a quick question regarding the newest version of Open Solver. What is the maximum amount of variables it can cope with? I’m currently using Basic Excel Solver on my Mac Excel 2016 and it can only take 200 variables. I would need to optimise 4000+ variables. Is that possible with the open Solver?

    Thanks!

    Diego

    Reply

  7. Hey there all,

    Wonderful product, love everything. Quick question, is there a way to take the current solver constraints and turn those into code easily? I have roughly ~200 and really don’t want to re-code it all 😛

    Thanks!

    Chris

    Reply

    1. Yes. You can export to AMPL code because this is what we send to NEOS; solve it using NEOS+CBC and then choose “View all opensolver files” (using version 2.8 2). However you cannot export to VBA code unfortunately. Looking at the model (.LP) file produced solving with CBC (and not using NEOS) might also be helpful. Andrew

      Reply

  8. Hello Andrew,
    First let me congratulate you with the fantastic tool you have created, and thank you for your continuous efforts. I work in the steel industry, and I use OpenSolver for a wide range of rather big LP problems.
    Since this week I get an error on models that were working fine last week :
    “OpenSolver 2.7.1 encountered an error : Unable to run the external program: “C:\Users\etc\Local\Temp\cbc.bat” > “C:\Users\etc\Local\Temp\log1.tmp” 2>&1
    Error 5: Access is denied.
    I can see that the model.lp has been generated.
    Any idea what has happened ?
    Thanks,
    Guy

    Reply

      1. Thank you for your quick reply, Andrew.
        I have tried restarting, but it doesn’t help, I get the same error.
        The “Details” button on the error message gives me the following error log :

        07 apr 16 12:26:53 [OpenSolver.xlam] OpenSolverExternalCommand.ReadExternalCommandOutput: Line 0
        07 apr 16 12:26:53 [OpenSolver.xlam] OpenSolverExternalCommand.RunExternalCommand: Line 0
        07 apr 16 12:26:53 [OpenSolver.xlam] SolverCommon.RunLocalSolver: Line 0
        07 apr 16 12:26:53 [OpenSolver.xlam] SolverCommon.SolveModel: Line 0
        07 apr 16 12:26:53 [OpenSolver.xlam] OpenSolverAPI.RunOpenSolver: Line 0

        Error -2147220493: Unable to run the external program: “C:\Users\U019446\AppData\Local\Temp\cbc.bat” 2>&1 | “L:\My_Archives\My Downloads\OpenSolver 2 7 1\Utils\mtee\mtee.exe” “C:\Users\U019446\AppData\Local\Temp\log1.tmp”.

        Error 5: Access is denied.

        Thanks,
        Guy

        Reply

        1. Hi,
          I can add that I can run the cbc.bat file manually, and then the file modelsolution.txt is created containing the optimal solution.
          Thanks,
          Guy

          Reply

      2. Hello Andrew,
        I tried restarting both Windows and Excel, to no avail.
        I forgot to mention that I could run the cbc.bat file manually, and this created the modelsolution.txt file, containing the correct solution.
        I tried v2.6.2 … same problem.
        I tried v2.1 … this worked.
        I now tried the v2.8.2 prerelease, it also works.
        Best regards,
        Guy

        Reply

        1. I am very puzzled; how odd that it should just stop working. However, I am pleased that the 2.8.2 version fixes the issue; we run our solvers very differently in this new version (thanks to great work by Jack Dunn), and must avoid whatever access issues arose for you. Andrew

          Reply

          1. Thank you Andrew.
            Our IT has looked to see if there was a change in security settings, maybe preventing .bat files from being run from Excel, they could not find changes.
            The “Error 5 : Access is denied.” message in the error log in my previous post does not give a clue ?
            Best regards,
            Guy

            Reply

            1. It still puzzles me. The error message is too generic to help, unfortunately. A few other users have reported this issue as well, and we never worked out why. At least the latest 2.8.2 version fixes this problem for you. Andrew

              Reply

  9. The Opensolver addin was working wonderfully with google spreadsheets up until a few days ago. Now every model, including ones that solved last week with no problems, gives the same answer. 0 for every decision variable except the last one which is a 1. I cannot find any information about the google spreadsheet add on.

    Reply

    1. Google have broken the Solver that we use. We have filed a bug report and are waiting for them to fix it. Sorry about the inconvenience. Andrew

      Reply

  10. I am still using Excel 2003 and have been unable to access OpenSolver in the data tab or elsewhere after extracting. Can OpenSolver be run on this version of excel and, if so, how might I go about opening it? Thank you.

    Reply

    1. Sorry… we don’t test on Excel 2003 any more. Try an older version of OpenSolver… you will have to experiment to see which versions work. Andrew

      Reply

  11. Hello

    I’m trying to use binary variables to set the minimum constraints of my continuous variables to implement “zero tolerances” (e.g. a variable can be zero, but must otherwise be greater than X), which I thought was a fairly common technique. However, I get a warning that OpenSolver can’t solve my model, because I’m trying to set “non-decision” cells as binary/integer variables. Is there a way around this?

    Reply

    1. Your binary cells have to be decision variables. Reformulated it in this way and it should be fine. Andrew

      Reply

      1. I presume you mean that I have to define my binary variables as “variable cells” in the model definition window. Is it possible to specify multiple ranges for variable cells, or must they be in one contiguous range?

        Reply

      2. I worked it out. Thanks for the guidance!

        Would it be an idea to transform this “help” collection into a Google Group, to make it more searchable? See, for example, the ExcelDNA group that Govert van Drimmelen administers. Wonderful for searching old questions/answers and to avoid repetitive questions 🙂

        Cheers!

        Reply

        1. Pleased it worked. We have considered some other options for support. I will look at the ExcelDNA example; they always do great things. Thanks for the suggestion. Andrew

          Reply

          1. ExcelDNA guys also offer a few useful printable documents with basic (and even some more advanced) help, tips and tricks. Extremely useful to get people going and could probably save you quite a bit of time answering similar questions over and over.

            I’m about to write a bit of VBA code to run a Sequential MILP model (for which I should use QuickSolve, I presume), but I would bet that others have done the same before and it would be great to base my own code on some existing proven code.

            Are there help documents or sample code resources available somewhere, perhaps? I could only find the Aeschbacher thesis on SourceForge.

            Many thanks, again, for OpenSolver!

            Reply

  12. Hey,

    Thanks a lot for a great tool. This has really helped me tremendously!

    Just a quick question/clarification:

    I would like to do the following:
    1) Set quicksolve parameters
    2) Initialize quick solve model
    3) Run quick solve
    4) Save the quick solve model for further use

    This works splendidly for one worksheet (I am running it from VBA).

    However, I have a need for three different quick solve models on three different worksheets. For each of the models, the quicksolve parameters (i.e. the parameter range) are the same but the models are a bit different from one another.

    Since the step two (initialization of the quick solve model) takes most of the time (several minutes per model) and actual solving only one second per data set, I would like to be able to save the quick solve models for each of the worksheets to save time.

    Currently, once I have run the initialization process for the first sheet and advance to the second sheet, the initialized quick solve model for the first worksheet is lost/destroyed.

    In summary: Is there any possiblity to somehow save different quick solve models (e.g. Model1.lp & Model2.lp) and call for them from the VBA code as I know that the model itself does not need changing, just the quick solve parameter data? Or is it so that Opensolver, in its current state, can only handle one quick solve model per time?

    I am currently running the version 2.8.2 of Opensolver and operating it from Excel 2010.

    Kind regards,
    Leo

    Reply

    1. Sorry, but we only remember one Quick Solve dataset. Maybe in the future we will do this better. In the meantime can you run them in different copies of Excel? That is launch Excel 3 times? Andrew

      Reply

      1. Hey Andrew,

        Thank you for the answer. I reckon that the use of different copies of Excel is a workaround solution to this for now.

        -Leo

        Reply

  13. Hello,

    first of all, thank you for this great solver.

    I have a question regarding AllDifferent constraints defined with the standard Excel solver. As far as I know, models with these AllDifferent constraints can’t be solved with opensolver (COIN-OR CBC, respectively). Are there any plans to include this type of AllDifferent contraints in opensolver/COIN-OR CBC in the future?

    Regards,
    Michael

    Reply

    1. No immediate plans for AllDifferent, sorry. But if CBC implements it, then we would add it to OpenSolver. Andrew

      Reply

      1. I could seriously use it ASAP. I have a real world problem with n=1..130. Fingers crossed. I suppose I could try to implement it as a 130×130 binary integer programming problem or 8000+ constraints. Probably not. 😉

        Reply

  14. Hey there – Jose here from DFSHowTo.com (you guys linked to my NBA spreadsheet – thanks for that btw). For some reason, OpenSolver stopped working today in Google Sheets. It just leaves a 1 on the last option and stays there. This was reported by someone that was working on my tutorial and then I tried it on my own spreadsheet and saw the same problem (without changing anything whatsoever).

    Is something going on with the add-on on your end?

    Look forward to hearing from you.

    Thanks!
    Jose

    Reply

  15. Hey guys, I’m having an issue with the OpenSolver add-on. I’ve been using the add-on for the FanDuel DFS Optimizer that you guys made mention about a while ago. Whenever I run the model to solve and optimize the lineup, it stops at the last zero/player and it doesn’t build a lineup, or a solve a model I would say, at all. The process runs through smoothly, as it had been for the past month. This started happening today, and me and Jose (the guy that came up with the optimizer idea) have been trying to figure it out. Any ideas?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *