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

    Please help me to take a look at this log and see what would be the issue(s). I can run cbc.exe from the command line in Windows.

    29 Nov 23 10:02:38 [OpenSolver.xlam] OpenSolverExternalCommand.RunCommand: Line 12
    29 Nov 23 10:02:38 [OpenSolver.xlam] OpenSolverExternalCommand.ExecCapture: Line 24
    29 Nov 23 10:02:38 [OpenSolver.xlam] SolverCommon.RunLocalSolver: Line 4
    29 Nov 23 10:02:38 [OpenSolver.xlam] SolverCommon.SolveModel: Line 44
    29 Nov 23 10:02:38 [OpenSolver.xlam] OpenSolverExternalCommand.RunCommand: Line 12
    29 Nov 23 10:02:38 [OpenSolver.xlam] OpenSolverExternalCommand.ExecCapture: Line 24
    29 Nov 23 10:02:38 [OpenSolver.xlam] CSolverGurobi.Version: Line 8
    29 Nov 23 10:02:38 [OpenSolver.xlam] OpenSolverAPI.RunOpenSolver: Line 15

    Error -2147220504: Unable to run the external program: “C:\OpenSolver\Solvers\win64\cbc.exe” -directory “C:\Users\tngo\AppData\Local\Temp\OpenSolver-4169” -import “C:\Users\tngo\AppData\Local\Temp\OpenSolver-4169\model.lp” -ratioGap 0.05 -seconds 100 -maxIterations 1000 -solve -solution “C:\Users\tngo\AppData\Local\Temp\OpenSolver-4169\model.sol”

    Error 5: Access is denied.

    Solver: CBC

    OpenSolver version 2.9.3 (2020.03.01); Distribution=Advanced
    Location: “C:\OpenSolver\OpenSolver.xlam”

    Excel 16.0; build 16827; 64-bit; VBA7
    Excel product code = {90160000-000F-0000-1000-0000000FF1CE}
    Excel language: English – US
    OS: Windows 10.0; 64-bit

  2. I am attempting to use OpenSolver 2.9.3 under Windows 10, MS Office 2021. I am observing very erratic operation: sometimes none of the menu options anywhere in excel fail to operate, and sometimes the maximize, minimize and close options in the upper right corner of the spreadsheet do not work. The only to close excel is to use the task manager. I would like to use open solver for a class I am developing but right now it is very problematic. Also, is there any way to have two or more non contigious groups of variables? Thanks

    1. Sorry to hear of your issues. We have not changed OpenSolver. but sometimes Microsoft make changes that break things (which they then tend to fix fairly quickly). Can you provide more details of what is causing you problems, i.e. what you have been doing that leads to the problems? Also, please send the contents of the About OpenSolver box to email hidden; JavaScript is required; this gives us useful information. Finally, are you running in a student laboratory? These can do unusual things, and are increasingly relying on OneDrive which introduces its own challenges. Hope this helps, Andrew

  3. Hi,
    OpenSolver for Sheets was working fine, until just now i got a message asking me to do a linearity check. I checked yes, it started running again, then froze. Now every time i click solve it does nothing and is just a blue circle (loading) spinning indefinitely.

  4. Hello!
    The other day while I was using OpenSolver in Google Sheets to solve problems the program stopped and it never started again.

    It worked perfectly for the first 7 days, but now when I click on “Solve problem,” the program doesn’t do anything. The only change I made from one day to another was adding a couple of variables.

  5. I have a non-linear optimization problem I am working on that is related o sports power rankings. The first 212 formulas had no issue, but then when I went to do game 213, I get the following error “OpenSolver 2.9.4 encountered an error: NEOS was unable to solve the model because there was an error while running AMPL. Please let us know and send us a copy of your spreadsheet so that we can try to fix this error.”

    I have also tried the COIN-OR Bonmin and Couenne solvers and get “OpenSolver 2.9.4 encountered an error: Out of stack space. Non-linear parser failed while processing cell Sheet1_P1.”

    This is really throwing me thru a loop on why everything was fine for the first 212 games I processed, and then randonly it changed. The formulas are all munally inputted to be direct (no ifs, index, vloopup, etc.) just a straight cell reference.

    1. I would suggest you use “View all OpenSolver files” to look at the AMPL file that is being created (or you can send two spreadsheets, one that works and one that fails, to email hidden; JavaScript is required). Cheers, Andrew

  6. There are cells in my objective function which call VBA code. The solver seems to have a problem with this. Is this a known issue and if so, is there a workaround? The VBA code is extensive and cannot be written up in the spreadsheet itself. Here is the error I’m getting; “ZCubic” is my VBA function.

    01 Sep 23 15:49:33 [OpenSolver.xlam] SolverFileNL.NumberOfOperands: Line 12
    01 Sep 23 15:49:33 [OpenSolver.xlam] SolverFileNL.PopOperator: Line 5
    01 Sep 23 15:49:33 [OpenSolver.xlam] SolverFileNL.ConvertFormulaToExpressionTree: Line 65
    01 Sep 23 15:49:33 [OpenSolver.xlam] SolverFileNL.ProcessSingleFormula: Line 3
    01 Sep 23 15:49:33 [OpenSolver.xlam] SolverFileNL.ProcessFormulae: Line 20: Non-linear parser failed while processing cell Ar150.687_K6.
    01 Sep 23 15:49:33 [OpenSolver.xlam] SolverFileNL.SolveModelParsed_NL: Line 13
    01 Sep 23 15:49:33 [OpenSolver.xlam] SolverCommon.WriteModelFile: Line 16
    01 Sep 23 15:49:33 [OpenSolver.xlam] SolverCommon.SolveModel: Line 42
    01 Sep 23 15:49:33 [OpenSolver.xlam] OpenSolverAPI.RunOpenSolver: Line 11

    Error -2147220504: Unknown function zcubic
    Please re-design your spreadsheet it does not use this function as part of the model and try again.
    Non-linear parser failed while processing cell Ar150.687_K6.

    Solver: Bonmin

    OpenSolver version 2.9.0 (2017.11.10); Distribution=Advanced
    Location: “C:\OpenSourceSolver\OpenSolver.xlam”

    Excel 16.0; build 16626; 64-bit; VBA7
    Excel product code = {90160000-000F-0000-1000-0000000FF1CE}
    Excel language: English – US
    OS: Windows 10.0; 64-bit
    Username: vanpeursemd

    CBC v2.9.4 (64-bit) at “C:\OpenSourceSolver\Solvers\win64\cbc.exe”

    No Gurobi installation was detected. The value of %GUROBI_HOME% was not set.

    Bonmin v1.8.4 (64-bit) at “C:\OpenSourceSolver\Solvers\win64\bonmin.exe”

    Couenne v0.5.6 (64-bit) at “C:\OpenSourceSolver\Solvers\win64\couenne.exe”

    NOMAD v3.7.1 (64-bit) using OpenSolverNomad v1.3.1 at “C:\OpenSourceSolver\Solvers\win64\OpenSolverNomad.dll”

    1. You can try Nomad; that uses the sheet directly without creating an equivalent set of equations. Andrew

  7. Thank you, Andrew. I have checked many times. OpenSolver simply ignores the binary constraints when called from the macro and return the LP relaxation. But it works fine when used outside the macro.

    1. Can you show us the VBA you are calling it with? The first argument controls solving the relaxation… Andrew

  8. Hello,
    A trivial question: is it possible to disable the OpenSolver status bar in Excel please?
    I am running a multi-year analysis, for each year the OpenSolver performs optimisation for c. 1 minute. I have set up the Status bar to display the year, but the OpenSolver shows its own status like “Setting up Problem”, “Setting up Constraints”, etc. Is there a way to disable those message and keep the user defined status bar only?
    Thank you.

    1. Hi Nadia!
      You may have already found the solution but I just saw your question… in the event that it is still needed, try this. Make sure the ‘Developer’ tab is available in EXCEL (if not currently visible, do a quick Google search for adding it). Within the ‘Developer’ tab, click on ‘View Code’, and on the far-left of the screen in the find the file named ‘OpenSolver (OpenSolver.xlam)’. Double click on that file and you should then see four sub categories (‘Microsoft Excel Objects’, ‘Forms’, ‘Modules’, and ‘Class Modules’). Double click on ‘Modules’ and then double click on any of the modules that appear. Locate any blank line and place your cursor there – next, select ‘Edit’ on the menu bar and then chose ‘Find’. Type in what phrase you are looking for (i.e., “Setting Up Problem”) and select the ‘Current Project’ radio button. Press ‘Find Next’ and it will show all instances of the phrase within the code. I found two instances of ‘Setting Up Problem’ – you can then change the line of code to what you would like to see instead. For example and as a test, the first occurrence I saw was in the ‘CModelDiff’ class model, line 22 and it appeared like this:

      UpdateStatusBar “OpenSolver: Setting Up Problem… ” & var & “/” & s.NumVars & ” vars, ” & s.NumRows & ” rows.”

      As a test add a phrase like “Testing 09-20-23” right after the first word (‘UpdateStatusBar’) but leave the rest of the original code in place (just in case this is not the instance that needs adjustment and you need to easily restore it). Add a single ‘ mark after the new phrase which tells VBA to ignore the code – your modified line should look like this:

      UpdateStatusBar “Testing 09-20-23″ ‘”OpenSolver: Setting Up Problem… ” & var & “/” & s.NumVars & ” vars, ” & s.NumRows & ” rows.”

      Now, select the ‘X’ in the upper right-hand corner to close VBA and now run your test with your data. Hopefully, instead of seeing the “OpenSolver: Setting Up Problem…” message in the statusbar you will see “Testing 09-20-23” (or whatever you typed). If that doesn’t solve your issue, go back into VBA, undo, that change, and search for the next instance of the phrase you are trying to replace (note: I had to change it in both locations and it worked for me). I hope this may help you!

  9. Good day! I must address this issue as i cannot succeed using open solver on my macbook. It appears that OpenSolver 2.9.4 encountered an error:
    Unable to access the CBC solver at
    “/Library/OpenSolver/Solvers /osx/cbc”.

    Is there any way to solve this?

    1. Something must be corrupted, or you have blocked macros. Try reopening OpenSolver (I.e. remove OpenSolver from the addins, if you have installed it rather than just opening it when needed, quit Excel, open up OpenSolver in Explorer) and be sure to allow macros to run. If this fails, try repairing Excel. Hope this helps, Andrew

  10. I’ve set up variable cells as follows to optimise a maximum value
    100 cells (25 rows x 4 columns) as binary and 3 cells to take an integer value between 4 and 6 inclusive and a sum of 15. I tried using all different but it produced an error message. The sum of 15 is a workaround.

    Whenever I run the solver it manages the binary from what I can see correctly but the 3 cells don’t appear to work. It picks the values 6, 5 and 4 in that order but when I manually reverse it I get a bigger solution. When I press solve again it reverts back to 6, 5, 4.

    As a test I removed the lower limit and it gave me 6, 6 and 3 in that order. I’m guessing it’s just putting the largest values possible in from left to right and not trying alternatives.

    Any ideas?

  11. I am running a binary LP in OpenSolver and it works. But when I run a VBA macro that calls the OpenSolver, it runs but ignores the binary constraints and returns the LP relaxation.

    Any advice?

    Thanks!

    1. I would check you arguments very carefully; you might have made a mistake in your call. Andrew

  12. First, thanks for making this great tool available.

    Second, I’ve noticed that sometimes OpenSolver randomly changes constraints or variable cells I’ve entered. It will globally shift the cells by an arbitrary amount — for example, every block of variables/constraints might be shifted down two rows, or forward three columns, etc. While I don’t know what causes this glitch, it appears to be related to how Excel and OpenSolver process cursor inputs, especially when the inputs are made from the mouse or arrow keys. It’s as if OpenSolver is processing changes in the cursor position as updates to the model and shifting every constraint according to how you’ve moved the cursor, even if you are not actively updating the model. This problem seems to come and go, and I’m guessing it’s related to some kind of incompatibility with Excel rather than something with OpenSolver itself.

    Any workaround for this glitch? I am running MS Office Home and Business 2019 (latest version through May 23, 2023) for Windows 10 Build 19045.

    Thanks!

    1. OpenSolver stores the model as named ranges. Excel will update these as cells are moved on the sheet; this is by design, and happens when OpenSolver is not running. But, you may be describing something that happens while OpenSolver is running? If so, we’d welcome more details. Andrew

  13. This is what it says when i click on solve:
    A range of cells are specified as bin or int that are not decision variables. OpenSolver does not support this.

    Please help me out here

    1. You can only specify INT or BIN for cells that are shown in the model window as being “variable cells”. Hope this helps, Andrew

  14. Hi, I’ve used OpenSolver to try and solve a problem in excel. It does run and provide a solution BUT, not all constraints are followed. It looks to me that the solution provided is close enough to some cutoff point and then it stops. When I narrow down the constraints it did run to completion in Excel Solver. I’m running an optimiztion that is just too large for Solver. Any suggestions on what might be happening or what might need adjustment?

    160 Variables and 360 Constraints, using CBC Linear solver. I’m not exactly sure if this is a linear or non-linear problem.

    1. What constraints are not satisfied? EG If you 4.999 and you want 5, then that is satisfied within the accuracy limits of the solver. Andrew

      1. Can you give more complete information about the accuracy limits of the solver? For the CBC solver, under Solve Options, the precision is locked at 1E-06. I assumed that this was the accuracy to which OpenSolver would return solutions. However, in my model, the solution that OpenSolver is returning is feasible only to the 3rd decimal place. Should we expect precision only to the 3rd decimal place? Is there something else going on?

Leave a Reply

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