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. Thanks for your positive feedback. Yes, you can run VBA macros (include OpenSolver’s) from within a .net language by (1) first loading the OpenSolver add-in, and then (2) calling a macro in the workbook, using code such as Application.Run(“OpenSolver.RunOpenSolver”). (This comes from a VSTO add-in, where Application is defined as: internal Microsoft.Office.Interop.Excel.Application Application.) Please let me know how you get on; I’m sure other users would be interested. Andrew

      Reply

  2. I have reached the limit of the standard Excel LP solver and OpenSolver seems to be very promising. One small question – Can OpenSolver be manupulated by VBA?

    Reply

  3. Can your tool handle three subscripts for variables? It looks very helpful and I love that you can do large problems.

    Thanks!

    Reply

    1. OpenSolver just sees a list of variables on your spreadsheet, so it doesn’t care how many subscripts there are. If you are thinking about subscripts, then it sounds like you are using a modelling language – maybe you should look at OpenSolver’s companion product, http://opensolverstudio.org, which supports modelling languages such as PuLP (and, in a coming release, AMPL). Andrew

      Reply

  4. We are using the opensolver to optimize water conservation. The problem uses the number of water using accounts at varying levels of consumption to apply water conserving best management practices, to get the most water conservation for the cost of the BMP. However, when we try to apply the model to individual accounts, we will end up with ~4M variables, including constraints. Opensolver seems to begin solving the problem, but it looks like it will take 155+ of processing time. Is there any way to speed this up. I am using a 32 bit machine and the latest version of opensolver. I think the premium solver has an option which disregards all of the obvious solutions to shorten the run-time. Any ideas?

    Reply

    1. Max: That sounds like a really interesting problem you are solving. Can you please explain it in a little more detail. Can I confirm that you have a spreadsheet with 4 million decision cells on it? Is it an LP or an IP that you are solving? When you say OpenSolver starts solving the problem, I assume it is in the building phase (when you can see it stepping thru all the decision cells, as displayed in the Excel status bar), not in the running the external optimizer phase? Perhaps if you email me the spreadsheet, I can take a look… a dot mason at auckland dot ac dot nz. Thanks, Andrew

      Reply

    2. A bit more info. OpenSolver performs a slow tedious process to determine the model. The Frontline products that include their Psi engine can by-pass this step, and so will be faster. However, if you have a problem with 4 million variables, then I’d suggest you look at OpenSolver Studio (http://opensolverstudio.org), which allows you to build a model separately from the data. This should be much faster for large problems. OpenSolver Studio will be adding more modelling languages as it matures; at the moment it comes with PuLP. By the way, can you confirm that your model is a linear one? Are there really 4 million cells selected as decision cells (ie adjustable cells) in your model? Are these binary or continuous variables? How many constraints are there? Andrew

      Reply

      1. Andrew,

        I am sending a copy of our spreadsheet that contains fewer decision variables. Most of our utilities have ~20,000 accounts. Each account currently has 14 decision variables. That’s 280,000 total. Our largest utility has almost 300,000 accounts, which would result in about 4M decision variables.

        Reply

        1. Andrew,

          we sent the data yesterday. We have been playing around with PuLP and ran a smaller model with ten accounts, successfully. We were trying to incorporate the Open Solver – Model Data Items tool into the PuLP set of instructions, and were unable to make it work. We will be working towards accomplishing this while waiting for your response. Great tool!

          Thanks in advance!

          Max

          Reply

  5. I want to use OpenSolver for a big model, so I was thinking if I can put the variables on one sheet and the constraints on the other sheet. I tried and it did not work. It seems that OpenSolver can only handle one spreadsheet. Please let me know if there is a way to do it. Thanks.

    Reply

    1. OpenSolver can solve models with decision variables on the active sheet, and constraints on another sheet. But, as you have pointed out, our new Model dialog does not let you set up models like this. The changes to make this work properly are almost finished (and allow decision variables to be on any sheet); keep an eye out for an update shortly. In the meantime, you can try using Solver to set up the problem, or use NameManager to edit the hidden names to set up the model the way you want it. Thanks for your feedback, Andrew

      Reply

  6. hi i am working on a routine with a lot of data in the background but only a few variables to optimize. What I have setup works fine with the Solver add-in, but in OpenSolver, while all the settings seem to be the same, I continue to quickly get this error after hitting quicksolve: “Model Build Failed: The objective cell does not appear to contain a numeric value. Please fix this, and try again.”

    Thanks

    Reply

    1. Fil. Thanks for sending me your model. However, as it is non-linear, OpenSolver cannot solve your problem. Cheers, Andrew

      Reply

  8. I am trying to solve a minimization problem in spreadsheet. I want to optimization a scheduling problem, try to minization shortage. I have 108 people, three kinds of shifts (4hr, 8hr, 12hr). I have set the max time and iterations as limit (32767) in solver. I can use open solver solve the model up to 4 days. If I increase to 5 days, means increase 108*3*24=7776 variables, open solver cannot solve it. The time is definitely less than 32767 seconds. The error message is “cbc.exe has stopped working A problem caused the program to stop working correctly. Please close the program. ” If I click “Close the program”, then it says “Model solve failed: The CBC solver did not create a solution file. No new solution is available.”
    I am pretty sure the model is correct because if I just reduce to 4 days, open solver can solve it. If I cut of half people (reduce to 54) open solve can also solve it.
    I checked online, the iterations limit for solver is 32767. I guess it’s because it reached iterations limit. How do you think please? But 32767 is kind of low, is it possible that open solver overwrite this limit please?

    Reply

    1. OpenSolver ignores the maximum iterations, but does respect the time limit. You can set this time limit in Solver (as you have done), and also using the “more options” button (or a similar name – I forget the exact name) in the OpenSolver Model dialog. You can also use this same button to turn on display of OpenSolver’s CBC solver during the solve. (Turning on “Show Iteration Results” in Solver will also cause CBC to show its output during the solve.) If you do this, you’ll be able to see what’s going on. Let me know what happens – if CBC is crashing, then we’d like to know. Cheers, Andrew

      Reply

      1. Hello Andrew,

        Thank you very much for your help. I am so sorry for so late reply, but the problem still not solved.
        Actually I cut off half nurses, I only have 54 nurses now, and I can solve for 7 days. My model is like this: I try to determine start time for each nurse, then I can get working hours for each nurse, then I can get the number of working nurses for each hour, I call this scheduled. I know the number of nurses demanded for each hour, I call this demand. Then I have two other decision variables O and P, both O and P are non-negative integers, let demand – scheduled = O – P . So O means shortage, and P means surplus.
        The problem is I can minimize shortage or surplus, but when I lock shortage then minimize surplus, it cannot solve it. Could you please help me a little bit?
        If I did not say the problem clearly, I’d like to explain more for you, or I’d like to send you my spreadsheet to take a look. My email address is email hidden; JavaScript is required. Thank you and hope to hear from you soon. Lenny

        Reply

        1. Lenny: Please feel free to email your sheet so I can check that it is not a bug. However, it seems as tho the model is simply taking a long time to solve. Cheers, Andrew (email hidden; JavaScript is required)

          Reply

        2. Lenny,

          Thanks for your spreadsheet.
          As far as I can tell, OpenSolver is working fine. Your model is a big
          one. You can see CBC running by opening the CBC command line with the
          last model (after trying to run your model), and doing something like
          the following:

          Coin:slog 2
          slogLevel was changed from 1 to 2
          Coin:log 2
          logLevel was changed from 1 to 2
          Coin:solve
          Coin0506I Presolve 27438 (-433) rows, 27384 (-168) columns and 1328838 (-127452) elements
          Clp0014I Perturbing problem by 0.001 % of 3.4641016 – largest nonzero change 4.796578e-007 (% 1.3846528e-005) – largest
          zero change 9.9994598e-005
          Clp0006I 0 Obj 0 Primal inf 1580 (276)
          Clp0006I 362 Obj 6.0003774 Primal inf 25625.951 (2925)
          Clp0006I 724 Obj 6.0004198 Primal inf 73307.25 (3901)
          Clp0006I 1071 Obj 6.0007168 Primal inf 756021.91 (4737)
          Clp0006I 1433 Obj 6.0012547 Primal inf 2219757 (6574)
          Clp0006I 1795 Obj 6.0015303 Primal inf 1996370.8 (6064)
          Clp0006I 2157 Obj 6.0018235 Primal inf 1899107.3 (6102)
          Clp0006I 2519 Obj 6.0020243 Primal inf 9832227.1 (5697)

          You can also change the slog and log parameters by setting these up on the spreadsheet before you solve; I have documented this somewhere on the OpenSolver web site under “Advanced CBC Options” at http://opensolver.org/using-opensolver.

          You have a hard problem. You can try solving with a bigger optimality tolerance, or perhaps simplify your model. Or switch to SolverStudio, which will let you try a different solver.

          Hope this helps, Andrew

          Reply

  9. Hi,

    I’m trying to run Open Solver 1.5 with Excel 2003, but I can’t make it work. I’m familiar with Open Solver i Excel 2007, but now I have to use it together with Excel 2003 and in 2003 I can’t seem to find any toolbar to run the Open solver from. Is it only previous versions (1.4) that will work with Excel 2003? If not, what do I have to do to make the Solver run in Excel 2003?

    Thanks!

    Reply

    1. Petter: Sorry to hear of your Excel 2003 problem. OpenSolver 1.5 should work in Excel 2003. Have you tried OpenSolver v1.4, and did that work? I’m sorry, but I don’t have Excel 2003, so can’t test it. Even if the menu does not appear, you can still control OpenSolver using VBA – if you are developing a VBA program. Looking forward to getting this resolved, Andrew

      Reply

  10. I have built a model that is too big for solver. When I run it with OpenSolver I get the following message:

    Error: Unable to run the external program: C:Users???OpenSolver15cbc.exe

    I also get some of the model parameters.

    Reply

    1. I’m not sure that you have a problem with size… the error message does not indicate that. Does it work ok with small problems? Have you unzipped the .zip file before opening OpenSolver from the unzipped folder? Does cbc.exe work ok (ie bring up a command line console) when you double click on it? I’m not sure what you mean by getting some of the parameters, sorry. Please let me know how you get on and I’ll see if I can help further. Cheers, Andrew

      Reply

      1. I don’t think the problem is with size. It’s just too big for Excel’s solver. cbc.exe works fine on its own; it just gives me this error message when I run it in Excel. If you would like, I could send you my model.

        Reply

        1. Strange. Yes, I’d like to look at the spreadsheet, please. a dot mason at auckland dot ac dot nz. Cheers, Andrew

          Reply

          1. I figured it out. I placed the OpenSolver folder on my desktop. I guess Windows has a problem with that. When I moved the OpenSolver folder to C:, it worked fine.

            Reply

  11. Hi Andrew,
    It’s been a while since you released this new version of your solver! I wanted to thank you for your responsitivity and concern!
    The new version allowed me to solve my problem (even though it takes me +1h to initialize the QuickSolve problem).

    Thanks again and good luck for your future research!

    Vincent

    Reply

  12. Hi,

    thank you so much for programming the OpenSolver. It really helped me out when I had reached the limits of the standard solver.

    Best Regards,

    Richard

    Reply

  13. Hi Andrew,
    Nice tool, thanks a lot! Nevertheless I’m now facing an error message (“OpenSolver encountered error6: Overflow, Source=openSolver”) when initializing a QuickSolve for a model with 52,000 decision variables (hence binary).
    From reading previous help messages, I however think this should work.

    Any idea? I can for sure send the spreadsheet (Excel 2010) would you need it!

    Thanks in advance, and once again great work!!

    Reply

    1. Vincent: Great to hear that someone is using the Quick Solver feature. (I must confess to not having tested this for a while as I didn’t think it was being used by anyone apart from me!) 50,000 binary variables is a large problem! I’ve never tried it on anything so big. Yes, please email me the spreadsheet to a dot mason at auckland dot ac dot nz, and I will have a look. Cheers, Andrew

      Reply

      1. I am pretty sure that many many people use the tool! Do you think it is because of the variables being binary? I know that Mark (19th July) aked a question about a 70k decision variables problem, so 52k should not be a problem, right?

        Anyway, I’m sending my spreadsheet with some comments!

        Thanks for your quick answer BTW!!

        Reply

        1. Vincent. I have now looked at your model; thanks for sending it. The model solves fine (albeit slowly). However, the QuickSolve was crashing because I needed a ‘long’ instead of an ‘int’ to handle your large problem. That is now fixed (and I’ve changed Quick Solve to use sparse matrix handling to make it less memory hungry). I’ll send you the new version to test. Cheers, Andrew

          Reply

  15. Yours is a very usefull tool!
    I might suggest that it is important to save models and, later, load them…
    When variables are too many, they fills the space of “variable cells” field. Often, the name of sheet, inside constraint fields, could fire some exception.
    These problems, however, are all surmountable.

    Reply

    1. Thanks for your comments. Your feedback on the variable cells has been most useful; the next version will have a larger space for these. Can you please give more details on the exception firing problem? We would like to fix that. You can still use Solver to save and load models if you need to do that. Cheers, Andrew

      Reply

      1. Thank you for your fast reply.

        About error due to the sheet name: suppose “a b” the sheet name. So, if I use the wizard of OpenSolver to create constraints I select cells with the mouse… Inside the LHS I have, for example, ‘a b’!$C$14, and inside RHS I have ‘a b’!$C$15 (with operator <=). If I click on "Add Constraint" an error message appears: "The formula or value for RHS is not valid. Please check and try again.". So, I usually delete " 'a b'! " from both fields and the adding action work.

        Reply

      2. Sorry for my double reply (and for my hazardous English)! But, I just thought that Cbc solver, with an appropriate configuration of the logging system, writes how many nodes have been opened and their information, like the lower bound, the timestamp, ecc…
        So, if I wanted to evaluate, for example, the trend of solution value, I would read log…
        What do you think about?

        Reply

        1. Andrea, Sorry; I missed your post before. The log is written to the C “standard out”; you would need to capture this (or pipe it to a file) if you want to analyse or display the log information. Let me know if you succeed in making this work. Cheers, Andrew

          Reply

Leave a Reply

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