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.

1,789 thoughts on “Help”

  1. I am having trouble with using a for loop to add constraints to a problem.
    My code currently is:

    – Dim i As Integer
    – For i = 3 To 52
    – OpenSolver.AddConstraint Worksheet.Range(“Ai”), RelationLE, Worksheet.Range(“Ai+54”), _
    sheet:=Worksheet
    – Next i

    I am getting the Runtime Error 1004, Method ‘Range of Object’ _ worksheet failed.
    Are loops possible for adding constraints? I have tried this section of the code without a for loop and it works fine in the situation. I have also tried a “Do While” loop with the same results.
    Thank you in advance for any help!

  2. Hi Andrew!
    I created a model that would tell me the best routes to distribute our customer’s pallets to provinces in Peru. It worked but the model considered that the pallets for each province had to go in the same unit, so I couldn’t assign half to one unit, and the rest to another one.
    I designed another one, where the variables are the amount of pallets (integer) instead of a binary variable. However, it just simply runs for 5 minutes or so, and then tells me that it couldn’t find an optimal solution, but I cannot find a mistake in the logic.

    1. Why don’t you anually enter a solution you believe is correct, and check the constraints ae satisfied. Andrew

  3. How can I best configure Excel to run OpenSolver? Right now, it is using < 20% of the CPU and only 800 MG out of 32Gb of RAM.

    1. Nothing you can change, sorry. To build the model, OpenSolver asks Excel to re-calculate once for each decision variable; Excel decides how to do that. After the model is built, the solving is then single threaded using CBC. (You can try making this multi-threaded via CBC options; run CBC.exe from the command line to get this help.) Andrew

    1. You need a table of x_ij variables in Excel. Or even better, we have specialised rostering software using column generation; email me at email hidden; JavaScript is required for more info if you are interested. Andrew

  4. I use Solver, but I am in the process of learning to utilize Open Solver:

    My dilemma: I generate multiple results, i.e., lineups for sports, that require 50 to 100 results. I do not know any VBA, and the Solver only returns 1 lineup each time it is run. Is Open Solver capable of returning multiple iterations? If so, how do I set Open Solver up so I can do so? Much thanks for a gret resource.

    1. OpenSolver currently keeps only 1 solution; we are working on handling multiple solutions, but that’s down the track. Andrew

  5. Hi Andrew. I’m a huge fan of OpenSolver – many thanks.

    Is it possible to save a Quick Solve instance somewhere so that it doesn’t need to be reinitialized each time Excel is closed and then reopened?

    Thanks in advance

    1. Sorry, but the current code cannot save this. We’d welcome a code contribution to allow this! Andrew

  6. I have been Optimizing the output of a VBA macro with NOMAD. It is working, but at about 1 hour into the solve the time for each iteration gets longer and longer. This happens even when I have turned off Excel’s autosave function and closed all other applications when running the program. When I checked my Task Manager during the solve process, the Processes tab shows that Excel is using around 20-30% of the CPU and between solves (when it seems stalled) the Status shows it is Not Responding for a bit, then the Not Responding goes away when it accomplishes the next iteration and then comes back again. Is there a trick I can use to stop this and get it to iterate as fast as it does when I first start the Solve? From the symptoms I’m stating does this give you an idea of something I could try to stop the time for each iteration from increasing?

    1. Also, I was thrown a “Run-time error ‘1004’: The specified dimension is not valid for the current chart type” at iteration 1337. I’m currently trying to see where this is coming from. I do have an Excel plot that updates for each iteration (which I’m sure slows down each iteration but it is cool to see the results of each iteration visually matching the measured data<-the difference of which is what my SSE is based upon). I don't think my Plot is the issue here though. I've read that 1004 is typically a reference out of range issue, though I'm not sure why this would happen at iteration 1337.
      As an aside, when I was going through my code I noticed that when using the Callback feature, Opensolver re-imports all the constraints, which will not have changed between Callbacks. I'm sure this doesn't take up much computer time and I'm not sure if it is doing anything else that is critical while re-importing the constraints, but if it is not doing anything else critical, perhaps an If Callback = true then Constraints = Previous Constraints would save a bit of computational time. Thanks so much for looking into my issues. I really appreciate it!

      1. It appears the issue for the “Run-time error ‘1004’: The specified dimension is not valid for the current chart type” is happening within the importing constraints code since when debugging the issue, the error was thrown when running the code to the cursor when the cursor was at Next constraint (line 41 of the SolverNOMAD module) of the Function NOMAD_GetValues() As Variant. The following is just speculation: Would this be happening if Opensolver’s next guess was outside the constraint values? Would this be an indication that I either need to: 1) Enlarge my constraints somewhere and/or 2) Perhaps add another changing variable since the current changing variables I’m using (along with the constraint ranges I am using) cannot minimize my SSE to within the tolerance specified (I just used the default values). If this is the case, is there a way to have Opensolver stop the Solve and report which variable(s) it could not adjust further due the next guess being out of the specified constraint range? That way I would know which constraint range I should adjust.

        1. I suspect the line showing the error may be a false lead. Have you tried deleting the plot? Andrew

          1. Sorry for the late reply. I thought I’d get an e-mail notice (and if one was sent out it likely went to my spam folder). I did comment out the portion of my code that updated the graph and it is now much much faster. Thanks so much!
            Judd

      2. That chart error is most odd; I suspect this is caused by the chart on your spreadsheet. Please note that debugging complex VBA code can be confusing as Excel and the VBA editor can get confused, and so error messages can be somewhat strange. Can you try deleting the plot? Thanks for the idea to process the constraints just once; that’s a good idea. Andrew

        1. You’re welcome! Many thanks for creating OpenSolver! It is great! Also, do you happen to know where I can find literature on the NOMAD Solver so that I can describe the algorithm in my dissertation properly?
          Thanks again!
          Judd

    2. VBA has a habit of slowing down for reasons that no-one can really explain. Maybe this is the problem?

  7. Hii

    I wrote a code in VBA for Normal Excel Solver which ran successfully. However, due to its limitations for 200 observations, I downloaded and ran OpenSolver, which was also successful.

    Now while macros code for OpenSolver, the same code is not working. The code written for Normal Excel Solver is as under:

    Sub results()

    ‘ results Macro

    ‘ Keyboard Shortcut: Ctrl+Shift+H

    Dim i As Integer
    For i = 0 To 9
    Range(“T4”).Value = i + 1
    ActiveCell.FormulaR1C1 = i
    Range(“L2″).Select
    SolverOk SetCell:=”$L$2”, MaxMinVal:=1, ValueOf:=0, ByChange:= _
    “$K$2:$K$230,$L$2″, Engine:=2, EngineDesc:=”Simplex LP”
    SolverOk SetCell:=”$L$2″, MaxMinVal:=1, ValueOf:=0, ByChange:= _
    “$K$2:$K$230,$L$2″, Engine:=2, EngineDesc:=”Simplex LP”
    SolverSolve (True)
    SolverSolve userFinish:=True
    SolverFinish KeepFinal:=1

    Sheets(“Sheet1”).Range(“$I$2”).Offset(i, 0).Value = Sheets(“Sheet1”).Range(“$L$2”).Value

    Next i

    End Sub

    Please suggest the changes needed for the code to work in Opensolver. Thanks and Regards.

  8. I’m trying to run Excel with OpenSolver on a cloud-based server. It is very slow and continuously reaches out to another server. How do I solve this problem?

    1. I am not sure why it is slow, sorry. Regarding Excel reaching out to another server, OpenSolver uses NEOS, if you choose that as a Solver. It also does update checks, if you enable these; see https://opensolver.org/privacy/. Finally, Excel may be checking the certificate for OpenSolver that we used to sign the code. Sorry we don’t have any other ideas. Andrew

  9. I’m getting message that “full model needs to be cleaned up”. In some instances I am observing rather incoherent results, such as getting worse optimum when I relax a constraint somewhat. Any thoughts? What does the clean up message mean.

    1. I don’t know where that message comes from; the words “clean up” do not appear in the OpenSolver source code anywhere! Is this a message from a particular solver? If so, which solver are you using … CBC perhaps (the default)? Andrew

      1. Andrew, I believe the problem may be the solver engine (COIN CBC). For comparison I downloaded Frontline Systems analytic solver trial and ran my model using it for several scenarios. In each instance the frontline solver produced the more “correct” looking solution, and in each case had optimum that was 3-5% better than that produced by COIN CBC. Not sure what the issue is. I will say that my problem likely has some scaling issues (large variance between scales of some of the variables), but frontline solver seemed to handle it just fine. Thoughts? Is there some tuning of the CBC solver that I can do? Some parameters that need to be set. I know that the solver itself is not your product, but any suggestions would be appreciate before I go ahead and pop for $1,000 for the frontline solver.

        1. It could be a scaling issue. You can try updating to the latest version of CBC; see https://opensolver.org/help/ on how to do this. CBC may have more scaling options; please check their documentation (which is best accessed from the command line by running CBC.exe). Otherwise, you can try improving the scaling yourself. Andrew

  10. Hi

    I wanted to try out a simple resource allocation problem with OpenSolver.
    Essentially, assign individuals to a team, with constraints on team size and the objective is to maximize the sum of scores of each team’s composition based on individual skills.

    I found that OpenSolver consistently produces a solution that violates a team size constraint but says its a solution. I could not get it to work as a result. Please help. Here is the link to the file:

    https://docs.google.com/spreadsheets/d/1OFqrrLI4tkN0fAJGFHpxob5Xy0jG3ahaAg-l0lA27Cc/edit?usp=sharing

    Thanks in advance

    Joc

  11. Hi

    I am having problem with OpenSolver Google Sheets add-on. When I hit solve, nothing happens. I have older version in other sheets and it works fine. The basic functionality is same, but solver seems to crash. If I press reset button, it has only the warning, but no buttons to confirm reset. Any idea how to fix this?

    Thanks,

    Antti

  12. Hi,

    How do I add Variables from multiple range of cells.
    For eg; I want to set B5:S40 and A3:P4 as variables. I have tried using “,” and “;” after entering the first set of variables but it did not work.

      1. Hi,

        Unfortunately, there is an error when I’m trying to add new variables through control-clicking. What should I do to fix it?

          1. I tried the same and got the error message:
            Error: the cell range specified for the Variable Cells is invalid. This must be a valid Excel range that does not exceed Excel’s internal character count limit. Please correct this and try again.

            1. I have now figured out what to do: I need to hold the ctrl key while selecting the range of variables. Typing the range directly into the dialogue box does not seem to work. Please ignore my previous comment, unless it’s useful for others.

  13. Hello,
    Is there is a maximum model size that OpenSolver in Excel can process?
    I’ve got a setup that I’ve solved numerous times before at a smaller scale, but my most recent model was very large and was looking like it would take a few months to solve. (this model has 2212 constraints and 4 matrices each containing 4 million cells)
    I’m wondering if there are any ways to avoid such a long solve time, or whether the solver tops out at a certain sheet size.
    Thanks in advance!

    1. There are no size limits other than the memory limits on your PC. Do you really have 4 x 4,000,000 = 16,000,000 decision cells? If so, the spreadsheet has to be recalculated 16 million times to build the model before it can start solving! I would seek a better formulation (for which we can recommend a consulting guru who can help you), and/or try SolverStudio (http://solverstudio.org). Good luck, Andrew

Leave a Reply to Ken Schwab Cancel reply

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