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,127 thoughts on “Help”

  1. Nice software. Incredibly powerful and much friendlier than what comes bundled with Excel. I have spreadsheets with multiple models saved on them by Frontline’s Solver. Is there any way to tell OpenSolver which model to use? Can OpenSolver save more than one model per worksheet? If it can be done, I haven’t figured it out yet. I assume that OpenSolver overwrites whatever model it’s using since there’s no choice where to save it.

    1. Thanks for the kind words. But we do not support multiple models per sheet. Instead, a model on one sheet can refer to cells on another sheet. Not many of our users want multiple models per sheet. Sorry we cannnot be more helpful. Andrew

  2. I ran my model in opensolver 2.9.3, linear, using CBC. After running it the following error message appears: “OpenSolver 2.9.3 encountered an error: overflow”.
    What does overflow mean and how do I fix this so my model runs? Thanks!

    1. This probably indicates very large numbers in your model. Try either upgrading CBC (see our help pages for how to do this), or reviewing your model. Andrew

  3. Hello,
    I have loved using your tool over the past year – it has been immensely useful.

    Unfortunately, today I have experienced many difficulties with it, all resulting in the same error, “Error 5: Access is denied”. I’ve tried different models, different files, different versions of opensolver (I tried 2.8.2, 2.8.3, 2.9.3, and 2.9.4), different folder locations, different permissions settings (ran as administrator), and even un-installed and re-installed Office, but none of it worked.

    Do you have any guidance as to what may be the issue, and how I can solve it? I last ran the exact same model without issue on January 27th. Digging back through your old help archives, I found a comment thread from 2016 with a person who had the same issue – have you learned any more since then about what drives the problem?

    Any insights you have would be very appreciated!

    1. I suspect this is Windows blocking the solver CBC.exe. Do you have an IT dept who can look at this? Exactly when do you get the error message? Andrew

      1. Hi Andrew,

        I’ve been working with our IT department, and we unblocked all the individual files and put them in trusted locations, but nothing had worked yet. I’ll continue to work with them.

        The error message pops up at the very end, after all of the variables have been run through in the bottom left corner of the screen (when normally the solution would pop out).

        1. My guess is the error happens cbc.exe is run by OpenSolver. I would check that you can this executable manually. You can also single step the VBA code to find the line that causes the error. What exactly is the full error messsage… does it give a function and a line number? Andrew

  4. Hello,
    I’m having a problem with the google sheets open solver. When I load the plugin, I select my objective cell and click on update. It correctly loads the objective cell. Then I select some of the variable cells (I have many in different columns) and click on the Add button in that area. It refreshes the side bar and adds the cells as variables but it removes the objective cell. If I try to update the objective cell again, the variable cells get deleted.

    What am I doing wrong?

    Here is a quicktime screen recording I made of the problem above. https://www.dropbox.com/s/mx43g94y4gixuln/Screen%20Recording%202021-03-03%20at%2010.20.00%20PM.mov?dl=0

    Thanks for your help!

    1. Sorry it is not working for you. Given this is not a widespread problem (as far as we know), do you mind trying a different browser to help narrow down the cause? Andrew

  5. OpenSolver 2.9.3 encountered an error:
    Unable to access the Couenne solver at “C:\Users\Ibrahim\Downloads\Compressed\OpenSolver2.9.3_LinearWin\Solvers\win64\couenne.exe”

    how I can solve this problem?

  6. Need some help with error. I cant get the solver to run at all. I keep getting this:

    Run-time error ’76’
    path not found

    degub points to line 2 in RethrowError
    2 Err.Raise OpenSolverErrorHandler.ErrNum, Description:=OpenSolverErrorHandler.ErrMsg

  7. Hello,
    When I open the OpenSolver sidebar (On google sheets) there is no sheet selected and I am not able to input any values. In fact, I cannot click a single thing within the sidebar except for the “Save” button, which is obviously useless as I cannot build the model. I downloaded and installed OpenSolver directly from the Google Workplace Market. Is there another step I need to perform or something I am missing to allow me to edit in the sidebar? My hunch is that since it is not automatically selecting a sheet for me when I open the program, I either need to find a way to manually select the sheet or missed a step when installing the add-on. Let me know.

    1. We have not seen that problem. Are you able to email screenshots to email hidden; JavaScript is required? Sorry it is not working the way it should. Andrew

  8. I am using VBA to set up and run OpenSolver. As part of this, I have included code that provides a brief summary that appears at the end of the run listing things such as the time from start to finish and Branch and Bound Tolerance. I would like to be able to capture and report the number of iterations that were used in order to find the solution. In looking at the OpenSolver API Reference, I see there are means to GetMaxIterations and SetMaxIterations; however, these only apply to the maximum number of iterations. Is there a method available to grab and record the actual number of iterations that were used to arrive at the solution?

    This is one AMAZING tool!

    1. You would need to double check the code to be sure, but I don’t think we have such a method, sorry. Feel free to modify the code to add this; it would be a useful addition. Andrew

  9. Having a problem running OpenSolver with Couenne on NEOS. I get a message back from NEOS that the calculation of the model exceeded the time limit. The model consistently times out after about 370 seconds (as reported during calculation). I tried to set the resource time out to 3600 seconds on the options menu, but the model still timed out in about 370 seconds. Any idea how I can address this issue. Thanks. (Enjoying using your tool very much!)

    1. Hi Jay,

      Thanks for your feedback. When OpenSolver sends a model to NEOS, it does not set the run-time priority based on the value entered in the resource window. If you look at line 3 of the function WrapMessageForNEOS in the VBA module SolverNeos (which you should be able to view in the VBA editor), we set the priority to ‘short’:
      WrapInTag("short", "priority") & _
      This is probably what is causing your model to time-out.

      In terms of a work-around, you could wait until the week after next when we release a new version of OpenSolver to deal with this issue and which will have the option of configuring run-time priority based on a user-entered run-time value. If you’d prefer to quickly fix this yourself, you could change
      WrapInTag("short", "priority") & _
      to
      WrapInTag("long", "priority") & _
      – this should hopefully be a good “rough-and-ready” fix for the problem you’re facing.

      Hope this helps,

      Taiji.

  10. Hello,

    This is an urgent request that I need answering. I have a client deadline and using OpenSolver to optimise their production schedule.

    I am using the Linear Programming version of OpenSolver. I do not wish to use the experimental non-linear solvers at this time.

    Is it possible to use various Excel functions within the constraints? In particularly, I would like to know whether IF statements and/or the MAX/MIN function can be used. These function generally product non-linearities in my model, therefore wondering whether OpenSolver can handle this.

    Thanks,

    Andrew

    1. No, you cannot use if or max or min. You can often mimic these in other ways often using binary 0/1 variables. Good luck, Andrew

      1. Thanks for the response. I’ve seen that OpenSolver CBC works with SUMIF within my model. Is this suppose to work?

        Also, would using a different solver allow these functions to be used? I understand that some of the solvers “use parsing” which appears to enable these Excel functions within their formula.

        Lastly, in relation to the above, I’m trying to use the non-linear CPLEX usine NEOS to cope with the non-linearity in my solver. Do I need to download the Advanced version and hence will this include experimental code? Generally, is there any way to generally speed up model solving time when using any particular solver option?

        Thanks,

        Andrew

      2. 09 Feb 21 21:23:54 [OpenSolver.xlam] CModelParsed.ExpandFormula: Line 94
        09 Feb 21 21:23:54 [OpenSolver.xlam] CModelParsed.ParseFormulae: Line 88
        09 Feb 21 21:23:54 [OpenSolver.xlam] CModelParsed.ProcessSolverModel: Line 8
        09 Feb 21 21:23:54 [OpenSolver.xlam] COpenSolver.BuildModelFromSolverData: Line 199
        09 Feb 21 21:23:54 [OpenSolver.xlam] OpenSolverAPI.RunOpenSolver: Line 13

        Error 1004: Method ‘Range’ of object ‘_Worksheet’ failed

        Solver: Couenne

        OpenSolver version 2.9.3 (2020.03.01); Distribution=Advanced
        Location: “\\lonfile300\redir$\k049105\Documents\OpenSolver2.9.3_AdvancedWin\OpenSolver.xlam”

        Very sorry for the constant questions, I do really appreciate all the help. Above is the error that I keep getting when using the Couenne solver. Do you have any experience of this? Thanks

        1. Hi there Andrew,
          Thanks for providing the error log with your query. It seems that your model is failing on a line involving a range object:
          Set rngBuildingRef = s.sheet.Range(BuildingRef)
          BuildingRef is a string which stores A1-style references.
          Unfortunately the error code 1004 is very generic and it is difficult to tell what exactly might be causing the error.
          Without looking at your spreadsheet, I can only suggest double-checking the ranges you have entered into the Model dialogue window.
          If you’d like, you could send us a copy of the spreadsheet at:
          help [at] opensolver.org
          for further exploration of this error.

          Hope this helps,
          Taiji.

  11. Hello
    I am running opensolver sequentially for several cases using a VBA script. For some cases the optimizer is not able to find a feasible solution and a dialog box pops-up alerting about this. Is there any way to programmatically close this pop-up or better yet to disable it?
    Thanks in advance for any help you can provide

    1. Have a look at our ‘Using OpenSolver‘ page and scroll down to ‘OpenSolver and Visual Basic for Applications’- this describes how you should be able to run OpenSolver with dialogs disabled by calling Result = RunOpenSolver(False, True).
      If you are calling OpenSolver using
      Application.Run("OpenSolver.xlam!RunOpenSolver")
      then changing this to
      Result = Application.Run("OpenSolver.xlam!RunOpenSolver",False, True)
      should prevent dialogs from appearing.
      Hope this helps,
      Taiji.

  12. Hello,

    I tried to run Solver by Application.Run method (to avoid compile error when OS not istalled). Everything works pretty fine (like ResetModel, SetObjective…) but when it comes to AddConstraint a get an error that macro is not available. What could be the problem?
    https://prnt.sc/xjxqc2

    1. Hi Michal,

      Thanks for your feedback. Screenshots and detailed descriptions of the problem are always helpful to us when we try to assist OpenSolver users with issues they encounter.

      When calling subs with the Application.Run method, normal VBA conventions about the scopes of subs and functions do not always apply. According to an article from wellsr and a StackOverflow question,
      Application.Run is able to run both public and private subs.

      In OpenSolver.xlam, the sub AddConstraint is defined in two places – in the OpenSolverAPI module and the SolverFileNL module. Its definition in the API module is Public and in SolverFileNL it is Private.

      However, if AddConstraint is called using Application.Run, it seems that these scopes are ignored and there are effectively conflicting or duplicate definitions for the sub – it is now ambiguous as to whether OpenSolverAPI.AddConstraint or SolverNLFile.AddConstraint should be run.

      This is why, if you want to build up a model using the OpenSolver API while using Application.Run, you should call

      Application.Run "OpenSolver.xlam!OpenSolverAPI.AddConstraint", EQrange.Offset(, -1), "RelationEQ", EQrange.Offset(,1)

      instead of

      Application.Run "OpenSolver.xlam!AddConstraint", EQrange.Offset(, -1), "RelationEQ", EQrange.Offset(,1)

      Hope this helps,
      Taiji.

  13. im using opensolver 2.9.4 and received error “Invalid procedure call or argument” “Non-linear parser failed while processing cell Solver_AR3.”
    i’m using only sumproduct formulae (with boolean logic). please help me on this, thanks a lot

    1. Sorry to hear that it isn’t working for your spreadsheet. It isn’t clear what might be raising this issue for you. Could you describe in more detail what formulae you have entered at cell AR3? Based on issues other users are having, Boolean logic functions like IF() etc. tend to cause errors in parsing for non-linear solvers.

  14. I have a new Macbook on which I am running Excel with macOS Big Sur version 11.0.1. I am not able to get OpenSolver to work. After reading some of your information, I assume that this set up will not function. Can you verify that this is true? Thanks.

Leave a Reply to Andrew Cancel reply

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