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. Hi,

    When I download the OpenSolver 2.7.1 and try to access the “about OpenSolver” it gives me a compile error that says “method or data member not found”. And it gives me other errors when I try to call the model function.

    Does anyone know what is causing this? The 2.6.2 version always worked on my computer.

    Thanks

    Reply

    1. Can you email a screenshot of the error to a dot mason at auckland dot ac dot nz. Please also send any error log that is available via the error message and the full contents (as text via copy paste) of your About OpenSolver dialog. Thanks. Andrew

      Reply

    2. Can you please also try repairing your Excel installation; that could fix your issue (which must be very specific to your system setup somehow). Andrew

      Reply

      1. Hello Andrew

        I encountered the same problem on my mac, and found the problem. The property “.Width” is by default “.width” everywhere in the vba code. See code snippet taken from the form “FAbout”:

        Private Sub AutoLayout()
        AutoFormat Me.Controls

        Me.width = FormWidthAbout
        …

        As vba remembers the capitalisation of variables, i fixed the problem by running:

        sub test()
        Dim Width as String
        end sub

        This defines the variable “width” with capital “W”, changing it everywhere in the code. The code snippet above then becomes:
        Private Sub AutoLayout()
        AutoFormat Me.Controls

        Me.Width = FormWidthAbout
        …

        This makes the code runnable. I only encountered this problem on my mac, not on windows.

        / Janus

        Reply

        1. Thanks for the feedback. It’d be great if the fix was so simple (although it is still a VERY strange VBA bug…). Really appreciate you taking the time to let us know what you have observed. Andrew

          Reply

  2. Hi,

    Every time I try to run a model with COIN-OR Bonmin or Couenne, I get the error message “Invalid procedure call or argument”. CBC works fine, I only have issues with the nonlinear solvers. I’m using Open Solver 2.7.1, but the problem persists with previous versions. Could you please advise?

    Thanks!

    Reply

    1. Alex. Can you send us the full error message including line numbers and the error log. A copy of your spreadsheet would be good too. Please send it to me a dot mason at auckland dot ac dot nz. Thanks. Andrew

      Reply

      1. Hi Andrew,

        Thank you very much for this great application. I am having this same message as Alex with Couenne. Do you know what solved the problem for Alex?

        Thank you in advance,

        Roberto

        Reply

  3. Every time I try to use Solver, I get the error message “This key is already associated with an element of this collection”

    Any help would be greatly appreciated.

    Reply

    1. This should have been fixed with the latest release (2.7.1). Are you using this version? If not, please send your spreadsheet to us at a dot mason @ auckland.ac.nz so we can find the cause of this error. Thanks, Andrew

      Reply

  4. I really love the open solver!!
    I’m not very good in programming but with this great solver I can solve my model.
    However something weird is happening.
    I have a model with 2k variables en 100 contraints. When I solve it for the first time, the setting up is approximately 2 minutes.
    But…. When I solve the same model a 2nd time its suddenly takes 4 minutes. When I restart Excel the setting up is back to two minutes. Can I do something about this?

    Thanks for the reply.
    Timo

    Reply

  5. I’m having trouble solving this problem. I keep getting the error that the CBC crashed. Perhaps I’m not setting the problem up correctly. Here’s the problem.

    I know total machine tool spending in 2008 was $5.878 billion. For four different dimensions (plant size, industry, machine type, and state), I know the breakdown of this spending. For example, I have five plant sizes and I know what each plant size spent. And, for the 50 states I know what each state spent. Same for the other dimensions. The other dimensions have 23 and 34 members. What I want to do is combine the data in the four dimensions into a given set of unique combinations of the four dimensions (not every possible unique combination of the dimensions is allowable or possible). The total combinations I need to fill are just over 2,000.

    I’m really struggling to get this work out, but I’m sure it’s a fairly easy problem for someone on here.

    Reply

  6. Excuse me I have worked on your add-ins open solver Several times on the same model and now this message is appear
    the operation timed out
    (at line 6811) (at line 1446) at line (2810)

    what is that mean ?

    Reply

    1. Make sure you have a working internet connection. This will appear if OpenSolver has trouble contacting NEOS.

      Reply

      1. the internet connection is OK but the message till appear
        I changed the constraints and the full excel sheet but without any useful
        is there any another solution ?

        Reply

        1. Can you send me the sheet, and I will have a look? The only reason I can think of for that error is if your computer can’t reach NEOS.

          jdun087 at aucklanduni.ac.nz

          Reply

          1. how can I send the file to you ?
            please if there is formal email or something else attach it in your reply
            this file have my model which consist of 11640 variable and 760 constraints
            the problem is :
            the operation timed out
            (at line 6811) (at line 1446) (at line 2810)
            source = msxml3.dll,Err Number=-2147012894

            thanks in advance

            Reply

  7. Hello,

    I am getting an error saying “OpenSolver could not find an optimal solution, and reported:Neos Returned:

    Executing on neos-5.neos-server.org

    Error (2) in /opt/ampl/anok -R amplin

    No solution was available to load into the spreadsheet.

    Any suggestions? Please help!

    Reply

      1. I have tried other solver engines as well but none are able to find a solution. The NeosCBC says “No Solution Found (Unbounded)”. Is there something maybe wrong with my boundary conditions?

        Reply

        1. You need to check your formulation (i.e. your spreadsheet model) carefully, as it has a mistake. Andrew

          Reply

  8. Hi,

    With version 2.7.0 OpenSolver throws a “Type Mismatch Error” (Error 13) if I either select CBC interactive mode or I try to use BONMIN or COUENNE.
    I use Win7 (64-bit) and Excel 2010 (32-bit).
    2.7.0 works fine for me with CBC and interactive mode off.
    However, I would like to use CBC interactive mode and a nonlinear solver, too.

    Any help will be appreciated.

    Reply

    1. Thanks for that report; we will try to reproduce the issue here to diagnose the problem. Can you confirm that the previous version worked for you? Andrew

      Reply

  9. Hi,
    is it possible to setup, and solve an optimization problem via the google spreadsheets javascript interface?

    Basically, I have a bunch of optimization problems. Each one should be solved given an optimal solution to the previous.
    To my understanding, this setup allows me to solve a sequence of linear optimization problems (easy, fast, and reliable), instead of one big non-linear optimization problem (slow, not reliable).
    Since I believe opensolver doesn’t support more than a problem per page. Even it that is not true, probably doesn’t support ordering in the solving. So, I was hoping that I could write some code to wrap my objective (no pun intended).

    Thank you.

    Reply

    1. This is not currently possible with the way Google Sheets Addons work, they can’t expose Javascript/AppsScript functions like we can in Excel. This is a feature that has been asked about previously, so we would like to implement it, but it will require some pretty big changes to the way we store models. The aim would be to provide an AppsScript library that you can add a reference to and then use in your own project.

      For now, I would suggest that the easiest way to automate things is to copy all of our addon code into your spreadsheet’s code. You can then access all of the functions for changing the model and running a solve etc. The code is available here:
      https://github.com/OpenSolver/OpenSolverGoogle

      Don’t hesitate to get in touch if you have trouble figuring things out

      Reply

    1. Yes; the standard “install’ instructions are single use – they do not change anything permanently. Andrew

      Reply

  11. Is it possible to download a version of OpenSolver 2.6. I had to get a different computer and lost the downloaded version 2.6. Opensolver interfaces with other software I am using to run DEA models. The new version 2.6.2 doesn’t work with the DEA software. Thanks.

    Reply

    1. All past versions are on our SourceForge site. What dea software are you using? Nothing should have stopped working… if it has we may have broken simething accidentally that we need to fix. Andrew

      Reply

  12. Hi Jack, here is a summary of the proceedings so far:
    Last month I ran an optimization model (non-linear) in OpenSolver 2.6.1 32 windows bits using NEOS Bonmin (Non-linear solver) and I got an optimal solution into the spreadsheet. All the cells were empty except constraints.
    2 days ago, I did exactly the same: PC, OpenSolver version, NEOS Bonmin (Non-linear solver) and with the cells empty (solution deleted) and the result was “OpenSolver could not find an optimal solution, and reported: No feasible solution. No solution was available to load into the spreadsheet.” (today I ran the model with the previous solution and the result was the same msg).
    Today I ran the model again (same previous excel file) but with OpenSolver 2.6.2 32 windows bits using Bonmin (Non-linear solver) directly and the result was: ”OpenSolver 2.6.2 encountered an error. Object doesn`t support this property or method (at line 7468) (at line 7156) (at line 2816) Source=OpenSolver, ErrorNumber=438″ I tried it both with the cells empty and with the previous optimal solution.
    In this moment I can`t replicate the optimization problem which does not seem rational. Is it possible to send you the file to check? I would be grateful. Pedro

    Reply

  13. I update the OpenSolver to 2.6.2 version for windows 32 bits and then bonmin executable became available. What I really do not understand is the fact that I ran the model last month and I got a solution and now I can not do using the same file…

    Reply

    1. Is the solution currently in the spreadsheet the same as it was when you solved it previously? The non-linear solvers like Bonmin start solving from the current solution in the sheet, and can give different results depending on the starting solution. You may be able to get it working again by trying different starting solutions.

      Reply

  14. Now I find the bonmin executable file in the solvers folder but when I run the model, I get the following msg:”OpenSolver 2.6.2 encountered an error. Object doesn`t support this property or method (at line 7468) (at line 7156) (at line 2816) Source=OpenSolver, ErrorNumber=438″. What this error msg means?. Pedro

    Reply

    1. What did you change to find Bonmin? This error says you have used a formula we don’t understand; fixing this error is discussed in other comments on this site. Hope you can get it going. Andrew

      Reply

  15. Hi,
    First I want to thank you for this tool. Last month I ran an optimization model (non-linear) with NEOS using Bonmin (Non-linear solver) and I got an optimal solution. Last night, I ran exactly the same model in the exactly same conditions and I got the following message “OpenSolver could not find an optimal solution, and reported: No feasible solution. No solution was available to load into the spreadsheet.” What could be the problem?
    Thank you.

    Regards,
    Pedro

    Reply

    2. Thanks for the prompt reply. How can I do that? I have the bonmin aplication in the solvers folder but when I try to run the model, I got the msg”unable to find Bonmin (bonmin.exe) in the solver folder”.

      Reply

Leave a Reply

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