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. I am encountering a problem when i try to run the model in excel 2010. A range of cells are specified at bin or int that are not decision variables. Open colver does not support this yet

    Source = Open Solver Model Build, ErrNumber=-2147220504

    When i tried to select all the decision vairables (332 in total) it would not let me select them. I then used a named range for all my decision varaibles to see if this worked. They are all showing in the solver engine now but when i build the model and it shows me what the model looks like not all the variables are shown.

    Is there any way to fix this or is the model too large for Open solver?

    If it is what is the limit?

    Reply

    1. OpenSolver has no limits, and so you can have as many decision variables as you can enter. (Excel sets a limit on the number of characters used to enter these decision variables, not OpenSolver.) As you have seen, you cannot specify non-decision variables to be int or bin. If you checked the .lp file to see what decision variables were being used, and only some of your variables are listed, then there’s a problem somewhere; if you send me your sheet I can have a look. However, you might also try moving the decision variables so they are all next to eachother, so you can then enter them easily into OpenSolver. Hope this helps, Andrew

      Reply

        1. I have just moved the decision varables so they are all next to each other, this sorted the problem out first time.

          Thanks for the help

          Reply

  2. Trying to get a 346-variable model to work, it maximizes for thousands of results that are based on vlookuping the variables. Keep getting the same error after it sets up the problem: “No Solution found (Unbounded)”, and all the variables stay at zero.

    Reply

    1. If the VLOOKUP formulae depend on your decision variables, you have a non-linear model that OpenSolver cannot handle. Furthermore, have you turned on assume non-negative? Andrew

      Reply

  3. Is there anywhere a detailed definition, how opensolver tries to identify
    target function, constraints and variables on a spreadsheet.
    For the target function it works quite well for me, but it never identifies any constraints.

    Thanks,
    Michael

    Reply

    1. Michael: Sorry, documentation is still coming. However, you need ‘min’ or ‘max’ on the row containing the objective, and a less-than-or-equals, an equals (=), or a greater-than-or-equals in the cell between each constraint’s left hand side and right hand side. Please let us know if it works for you. Andrew

      Reply

  4. I’m looking to use solver on a model which uses VBA to perform most of the calculations, variables and results are tracked on the worksheet. Unfortunatly Excel’s built-in solver cannot help with this… can opensolver?

    Reply

    1. Neither Solver or OpenSolver care how the values appear in the spreadsheet, as long as you can specify decision variables etc on the sheet. So, having calculations performed in VBA is fine as long as you are using User Defined Functions for these. If your calculations are run by a maco, then neither Solver nor OpenSolver will work. Further, if your calculations are complicated, they are probably not linear, and so the CBC solver used by OpenSolver will not work. If you are using VBA as a modelling lanaguage, then I suggest you look at SolverStudio (http://solverstudio.org) which handles modelling languages (currently PuLP, with others coming soon). Hope this helps, Andrew

      Reply

    2. Re: Calculations run by a macro.

      My workaround is to use the Change event of the worksheet that contains the cell(s) that Solver is using.
      It runs every time a cell value on the worksheet (“Main_sheet”) changes. If the cell is one of Solver’s change cells, the calculations macro runs (Sub MainA)

      The key part is that while the calculations macro runs, Events are turned off. After the calcs are printed in the worksheet by MainA, Events are turned back on and Solver will react for its next iteration.

      Hope this is useful. I’ve only used this with built-in Frontline Solver.
      Mike

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim adjustCells As String

      On Error Resume Next
      Application.EnableEvents = False

      adjustCells = SolverGet(typeNum:=4, sheetname:=”Main_sheet”) ‘Solver changing cells

      If InStr(1, adjustCells, Target.Address, vbTextCompare) 0 Then
      Call MainA ‘MainA does the calcs. Only runs if change is in Solver change cells
      End If

      Application.EnableEvents = True ‘now Solver will fire

      End Sub

      Reply

      1. Mike, Thanks for that tip, which is a good way to do something complicated in a (presumably non-linear) model. I would not have thought that disabling events was crucial, but I have not tried it. Another way would be a user-defined function which also allows VBA code to execute during a Solver run if the input variables to the function include the model variables. (Readers should note that OpenSolver only does linear models, for which these tricks are not so useful.)
        Cheers, Andrew.
        PS: Now we know that Solver is the reason why your boats are so fast…

        Reply

        1. Solver: couldn’t live without it! I’ve only just seen your project and will give it a go.

          I notice there is a typo or maybe Web display problem in the above code, the correct line is:
          If InStr(1, adjustCells, Target.Address, vbTextCompare) 0 Then
          in case it’s a display thing:
          If Instr(….) not_equal_to 0 Then

          It’s about a decade since I figured this out, and I’m pretty sure turning off Events while the calc macro runs is important because Events are recursive, so the first result printed by the macro on the worksheet will fire Solver (I think); which will run before the calc macro finishes printing the full results.
          At least that’s how I remember the problem.

          My main use of this is to solve for geometry features by running a NURBS macro.

          Mike

          Mike

          Reply

  5. Need help!

    I am trying out to find out the minimum possible manpower for the required work load.

    I am selecting the decision variables directly using mouse but the variables are gone when I reached the 34th variable. What happened? I’m using Excel 2007.

    Anyone?

    Reply

    1. There is a maximum limit set by Microsoft on the number of characters allowed in the decision variables RefEdit text box. You may be encountering this? We have increased the size of this text box to allow more decision variables to be entered and displayed. I would suggest you group your decision variables together so they can be entered in one go, or create new decsion variables in one group, and refer to these in your current decision variable cells. Hope this helps. Please let us know how you get on. Andrew

      Reply

  6. Hi, I would like to use quicksolve, but I always run into errors. My model works fine with the regular “Solve” function. When trying quicksolve, I can successfully define the parameters, initialise quicksolve and run quicksolve once. When I change some parameter and run quicksolve again, it stops and I get an error message “The following contraints are nonlinear …”. I can then run a linearity check that tells me “There have been no instances of nonlinearity found in this model”. Any idea how to resolve this? Many thanks in advance.

    Reply

  7. I have the same problem with the guy above:

    “Compile error:
    The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PrtSafe attribute.”

    Win 7 with MS office 2010 64bit.
    Can you help me, too?
    Thank you.

    Reply

  9. The installation instructions refer to the Excel add-in directoryand note that this is typically: C:Documents and Settings”user name”Application DataMicrosoftAddins.

    I have just been upgraded to Windows7 and Office 2010 for which there is no such directory. For an simple user :-), can you advise the appropriate directory for OpenSolver?

    Thanks,

    Alasdair

    Reply

    1. I don’t have Windows 7, so cannot help, sorry. But you can alternatively use the About OpenSolver menu within OpenSolver to configure OpenSolver to load automatically from its current location. Hope this helps, Andrew

      Reply

      1. I have since found that the answer to my question is simple. With Office 2010 the addins can be placed anywhere on the pc. After Excel is opened, go File -> Options -> Addins -> Browse and then select the folder and addin required.

        Reply

  10. Dear Andrew,

    I’ve used open solver many times with excel 2007, and never experienced any problems. Last week I updated to excel 2010 (64 bit) and – as I just found out – to VBA7. Unfortunately, OpenSolver doesn’t start at my laptop (windows 7 ultimate, 64 bit). When I open OpenSolver.xlam, I get the following message:

    Compile error:
    The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PrtSafe attribute.

    I’ve spend a whole day on the internet to find a solution for this problem, but unfortunately I didn’t found one. I did find some information at this website (http://msdn.microsoft.com/en-us/library/ee691831(office.14).aspx), concerning the update to office 64bit and VBA7 and I’ve downloaded this file: http://www.microsoft.com/download/en/confirmation.aspx?displaylang=en&id=9970. But this did’t solve my problem and I don’t know what to do next. I’ve read your site that OpenSolver is compatibele with Office 2010 64bit, so hopefully you are familiar with my problem and do you know how I can make OpenSolver work at my system.

    Thanks in advance for your reaction!
    Regards,
    Rutger

    Reply

    2. Rutger: Thanks for your comment, and the subsequent emails. The next version of OpenSolver will fix the 64 bit incompatibility in version 1.7. Andrew

      Reply

  11. Thanks for the tips. I managed to get it going without a workaround. With Excel 2003, you need a .xla or .xls addin to reference OpenSolver from another workbook (just like the way you reference normal Solver). Renaming the .xlam addin to .xla or .xls wont work (msg “The file is not in a recognisable format”). The trick is to save the .xlam addin as a .xls workbook (by setting the “IsAddin” to False) and re-saving the .xls workbook as a .xla addin (by setting the “IsAddin” to True). Now I have a OpenSolver.xla addin which works perfectly with Excel 2003.

    Might be an idea to include this .xla addin in your distribution pack for Excel 2003 users (there is still a lot of them around). I can email it to you if you dont have Excel 2003 to generate it.

    Thanks again for a wonderful product !!!

    Reply

    1. Please send me the OpenSolver.xla file. I tried following the above steps, but, I could not get the .xla file from the .xls file.

      Thank You!!!

      Reply

    2. Same as Harsha, I have excel 2003 and cannot open xlam (msg “The file is not in a recognisable format”).

      Please, post the xla version for 2003

      thanks a lot

      Reply

  12. I have already coded my model for the normal solver in excel. I was wondering if there is a SMALL amount of code (rather than the entire Open Solver file) that can just allow OpenSolver to pull the info that i programmed into solver and Run the model through Open Solver.

    Please help 🙂

    Reply

    1. We don’t have anything ready to go, but you can either strip out the code in OpenSolver that you don’t want (start by deleting the dialogs you don’t use), or you can turn OpenSolver back into an ordinary spreadsheet, and put your model into that. (See my comment elsewhere on how to turn off the IsAddin setting.) Or you can go back to an earlier, smaller version. What is your goal with doing this? Andrew

      Reply

  13. Downloaded OpenSolver and tried to use with Excel 2003. Good news and bad news.

    The good news is:
    Double click on OpenSolver.xlam – opens Excel (after a file conversion process)
    Opened workbook with solver problem and loaded problem on the standard Excel solver
    Inserted a sub with command “RunOpenSolver False” in the OpenSolver.xlam “OpenSolverMain” module and executed the sub
    OpenSolver works fine and I get the same result as the standard Excel solver in less than HALF THE TIME. Beautiful!!

    Now the bad news:
    I cannot create a reference to OpenSolver.xlam in my VBA project. When I try it, it looks for a .xla file and renaming the .xlam file to a .xla file wont work either
    Because of this, I cannot run OpenSolver from my VBA project code.

    Any solution or a workaround?

    Reply

    1. It is a long time since I have used Excel 2003! From memory, the .xlam extension is new, and so OpenSolver will need to be renamed with a .xls (or .xla) extension. (I’m not sure why you say .xla will not work?) You may wish to go with the .xls extension, and simply add your VBA code into OpenSolver (which is really just a workbook with some VBA code). In this case, you may want to open OpenSolver, open VBA, and set the “IsAddin” setting to false for OpenSolver’s “ThisWorkbook” so the worksheet will open normally. Hope this helps, Andrew

      Reply

  14. I’m encountering a confounding problem with my open solver code. I am able to solve my problem in one sheet. But in another, where I have some VBA code for modifying content of certain cells, it is building the model completely but when I hit the “solve” button, it’s throwing an error like “Initial worksheet calculations are not complete and so the so the model may not be generated correctly” Why so?? It’s the same problem I got a solution for in another sheet!!!
    Pls help
    Regards

    Reply

    1. OpenSolver re-calculates the worksheet many times to deduce the model’s equations. We have seen this re-calculation fail very occasionally in Excel 2010 (which I suspect is an Excel bug), and so OpenSolver contains code to try again, which it does a number of times. If Excel still reports the worksheet as not having calculated, it lets you know. We have never seen a spreadsheet where this re-try strategy did not work, so yours is a first! If you like to send me your sheet, I can have a quick look at it. Do you perhaps have VBA worksheet functions in your workbook? Andrew. My email is a dot mason at auckland dot ac dot nz

      Reply

Leave a Reply

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