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.
As of July 2025, there are two known issues caused by Microsoft code changes. These are a RefEdit issue that breaks the Model dialog (see #6 below; a user can easily fix this) and a DLL access change that impacts NOMAD (see #10 below; fixing this requires editting VBA code).
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:
- The online CBC TRAC bug reporting system (which you will have to register for), or
- 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’ and ‘Compile error: variable not defined’: Missing Reference & Blocked ActiveX RefEdit Errors (updated July 2025)
July 2026 update: OpenSolver uses the ActiveX “RefEdit” control. If this is not accessible, then you may get a “compile error: variable not defined” error (at a line containing “refObj”) when you open the Model dialog. I have also had a “Run-time error -2147352573 (800200003) Could not find the specified object” occur when clicking the “Model” button.
According to StackOverflow, the default setting in Excel has recently changed to a stricter level and this disables controls from external libraries like RefEdit. So, in recent Excel versions, permission to use ActiveX controls must be turned on in the Excel Trust center. To do this, use File…. Options… Trust Center… Trust Center Settings to select “Prompt me before enabling all controls with minimal restrictions” (with “Safe mode” turned on); see this screenshot. (Thanks to jkpieterse for replying on StackOverflow with this information, and for Ziad for sharing the settings that worked for them.) It also works for me selecting the slightly safer “Prompt me before enabling Unsafe for Initialization (UFI) controsl ith additional restritions and Safe for Initialzatrion (SFI) controls with minimal restrictions”. Note that this change also causes Frontline’s Solver to crash with the error “Solver: An unexpected internal error occurred or available memory was exhausted.”
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.
10. “About OpenSolver” menu crashes; NOMAD crashes (updated July 2025)
July 2025: A recent update to Excel has blocked access to the “OpenSolverNomad.dll” file that we use to execute the NOMAD solver. Symptoms of this include the “About OpenSolver” window crashing with an error: Run-time error: ’53’: File not found: OpenSolverNomad.dll
(This error occurs even though OpenSolverNomad.dll is located in the Opensolver folder under Solvers/win32 and Solvers/win64.) OpenSolver Errors also occur when trying to use the NOMAD solver.
These errors occur because Microsoft have restricted Excel’s access to DLL’s (which is what we use for Nomad). To fix this in OpenSolver you need to edit the code. (We will release a fix at some stage.) For more information, see this Microsoft Community post (also available locally). One of our users, Ed, says “I can confirm that adding the full path in the [NOMAD DLL] declare statement works”. See also this StackOverflow post.
Note: This has been confirmed for “Microsoft® Excel® for Microsoft 365 MSO (Version 2507 Build 16.0.19029.20136) 64-bit” installed as part of “Microsoft 365 Apps for Enterprise” on 12 Aug 2025.
Hello,
First of all, I understand this may very well not be an OpenSolver question, it may be a CBC question or even a general math programming question. But Andrew has been so helpful in the past that I am going to ask here.
I have a scheduling problem modeled with 800 variables and 1800 constraints (the real model will be 3 or 4 times bigger), all variables are binary except for 75 that are linear and bounded to a range or zero (they can take any value between a min and a max or be zero, I modeled this with an extra binary variable: var_i <= bin_i*max and bin_i*min <= var_i, min and max are constant, is this a problem?)
The problem I have is that the model is unfeasible, but I can make it feasible if I remove from the OF the sum of a set of binary variables multiplied by a constant, and it is also feasible if I make the coefficient of these binary variables negative. This made me believe that maybe CBC reported unfeasible and I was actually making it unbounded because of the change in the objective function but I just realized (after four days) that CBC actually reports unbounded as unbounded so my problem is really unfeasible.
How can this be?
I can also make it feasible if I relax the binary constraints, even with a positive coefficient in the OF.
I can also type a feasible solution in Excel and all constraints hold true (I only visually check this though, any better way to do this?).
I've tried OpenSolver 2.5.2 and 2.6 linear for Windows, I've tried Excel 2007 in Windows 7 and 2010 in Windows 8. I've tried many tolerance levels. I've tried CBC on the NEOS servers. All to no avail.
The model gets setup and built in a few seconds so I assume SolverStudio will not be my answer, and solves (with the zero coefficient and the binary variables) in a few minutes.
Hopefully I am doing something really dumb (easy to fix but hard to find).
Any help will be appreciated
Your results are indeed puzzling; changing the objective function should not make an infeasible problem become feasible. Your formulation sounds ok to me. Have you tried the latest version of CBC (available from http://ampl.com/products/solvers/open-source/)? Also, if you like to email me your model, I can try the Gurobi solver on it as a double check. Andrew. PS: We don’t always have time to support non-core (eg CBC) issues, but you are a valued customer!
Hello,
I thought the CBC I had (2.8.8) was the latest, but it is not… so I got 2.9, and the same results.
I will be sending you the model shortly to see what Gurobi does.
Thanks again and best regards
Hi, I have a problem with an optimization problem. Im trying to minimize the cost of assign buses to dispatch centers.My model has 645 binary variables, its like this:
A B C D
Cost Center Center Center Open/Close Cost-Openning Objective function: sumproduct(A,B) + sumproduct(C,D) . My problem is that I need that if you
Bus x y z Bus x y z x 1 50 don’t open a center then you cannot assign any bus to it, but I cannot use an (if) to make
1 10 10 20 1 1 0 1 y 0 40 the binary variables in C become 0 if sum(column x) is 0 and I cannot add other constraints
2 32 25 40 2 0 0 1 z 1 50 in calculated cells because it said that Im adding constraints to variables that aren’t
3 20 15 80 3 1 0 1 decision variables. Any suggestions of how can I solve this problem?
Hi Andrew,
I was just using latest version and found out that it doesn’t recognize the functions like Min, Max, Offset, abs.
Also it shows model to be Nonlinear with these functions.
I assume you are talking about the new solvers that parse the spreadsheet (i.e. look into the cell formulae) to create the model. Min(), Max(), and Abs() are indeed all non-linear, and so the linear solvers typically cannot handle them. You need to change the model to capture the effect of these in another way1. Offset() is not currently supported, but may be added if there is a lot of demand for this. Thanks for your feedback. Andrew
em caì solver nhưng không cài dc vì nó bảo thiếu file solver.xlam là sao ạ.ai có file đấy cho e với a.thanks m.ng ạ
Hello,
I am currently using open solver for optimization. Whenever I add more data, the solver does not give me an optimal solution. What could be the reason for it?
It also threw me an error while setting up the model for larger data set. The error is – While Constructing the model, Open Solver encountered error 1004: Method ‘Calculate’ of Object_Application failed (at line 33990)
Source: OpenSolver (frmModel::cmdBuild_click)
The sub-optimal solution may be because you have a non-zero branch and bound tolerance set. Tje error indicates Excel is failing to recalculate your spreadsheet. We have seen this happen but dont know why sorry. You can try anothrr solver (not CBC nor Gurobi) to get around this. Andrew
Hi,
I have a question regarding the feature “assume model is linear” and how the solver handles non-linear models.
I have created a non-linear binary optimization model. The non-linearity exists only in the objective function and the 3 relevant decision variables are related via multiplication (e.g. X*Y*Z).
As I already know the OpenSolver does not solve non-linear models and when I try to solve the model I’m getting a warning “The objective function is not linear”. But I can press continue and get a solution for my problem.
Why do I get a solution even when my problem is not linear? Has this something to do with the “assume model is linear”-feature?
Thanks for your help!
We should remove that “Continue” option; you are solving a version of your problem which was created by assuming the model was linear, and so this version is not correct (nor useful) for your non-linear model. Sorry about the confusion. Andrew
I’m trying to solve a mixed integer problem with approximately 130K binary variables, 70K continuous variables and 40K constraints. The problem takes about 4 hours to set up then once it begins to solve the problem excel show a not responding message and a cmd window appears. Previously we were able to solve a very similar version, however, now we have been unable to solve the problem. We have also tried adjusting the tolerances and maximum run time with no success. I’m using version 2.6 and the solver is cbc linear. What do you think we can do to fix our problem?
Big MIP models require careful formulation; you can dig out an OR textbook and see if you can strengthen the model. You can also try Gurobi (free for academic use) – it is a really good solver. I suggest you also solve the LP to understand how hard the LP is to solve, and (once you have solved the IP) work out how big the LP/IP gap is – this gives an indication of the problem difficulty.
Thank you I have taken your suggestion and started to solve the linear relaxation. The formulation itself is pretty flexible as we have introduced a number of deviation variables for the constraints. It’s just confusing why a slight variation of our model has resulted in our model going from working to not working.
Small changes can often make IP’s much harder to solve….
Hi Andrew, I just wanted to follow up and say we were able to find a very good solution after further adjusting the tolerance. Thank you for your product it provided a great solution for us since it is user-friendly which will aid our project sponsor.
Thanks for the feedback. I am pleased the project went well. Andrew
Hi Andrew,
I wasn’t able to find my comments.
I wish to ask:
Q1. Is there a way to use OpenSolver to explore different scenarios regarding people to jobs allocations(e.g., case 1= 2 people and 2 jobs, case 2= 3 people anbd 3 jobs, etc) within the same model such that I do not have to manually update each individual equation inside my OpenSolver model? I wish to have 1 big model of up to say 15 people and 15 jobs, then input values for different scenarios (with default values for non-existent people and jobs in relation to the scenario in question) and get optimal solutions.
Q2. How can conditional formatting work?
I wish to instruct Opensolver to recognize default values for non-existent people and jobs during its search for a relevant solution, whose output should show 0 (0=no allocation) for non-existent people and jobs. For example, if i solve for 5 people and 5 jobs by entering values for this scenario in a model defined for up to 15 people and 15 jobs (master model) and leave default values for person 6 to 15 and jobs 6 to 15, I wish to see 1s (1=allocation) only for the 5 by 5 matrix and zeros from 6 onwards (to indicate non-existent people and jobs).
Thank you in advanvce for your expertise.
You cannot “instruct” OpenSolver to ignore things; however you can build a model that gives useful results when some input cells are blank (which OpenSolver interprets as zero, or you can interpret as you wish using formulae that help specify the model). Good luck. Andrew
hello their,
I am using excel for mac and I downloaded the latest version of solver but the problem is that when I try to open the solver in order to add the objective function and the constraints, excel opens a window and it says “visual basic error”. Then the solver window appears partially, I can put the objective function cell but I can’t add the constraints.
Thanks in advance,
Khaled
Khaled: Sorry to hear of your problems. We have not seen this before. Can you send us a screenshot of the error message and the resulting OpenSolver window (with bits missing) to a dot mason at auckland dot ac dot nz. Thanks, Andrew
Hi Khaled,
Can you try clicking “End” when the error message comes up, then opening the Model window again. This seems to clear the error for me and the Model window works after it has been reopened. Let us know if it works for you.
I am trying to find the cause of the error, but hopefully this suffices as a workaround in the meantime.
Thanks,
Jack
Hi Andrew. Thank you for your prompt response. My model has both integer and non-integer variables and does not rely solely on rounding.
I have investigated 2 cases as below:
Case 1(6by6 matrix/solution space) – I built an OpenSolver model for assigning 6 people to 6 jobs as appropriate.
Case 2 (2by2 matrix/solution space): I modified the existing model above by clicking on Model, then manually updating all equations in relation to objective cell, variable cells and list of constraints to point to only the cells for the scenario in question (e.g., 2 people and 2 jobs), saved it as a different workbook.
I then used exactly the same input data and solved both models separately, to check whether I will get the same results (for case 1, I input data only for 2 people and 2 jobs and left default values for persons 3 to 6 and jobs 3 to 6). Having used exactly the same input data for both cases (except that there are default values in case 1 for the non-applicable/non-existent persons 3 to 6 and jobs 3 to 6), I obtained slightly different results (assignments and objective func value). I then explored further to ascertain whether the default values in case 1 are having an impact on the output, by using different tolerance values and observing/comparing opensolver outputs for the 2 cases. After finally changing the tolerance from 10% (the default value in OpenSolver) to 1% (99% solution precision), the outputs from the 2 cases investigated became exactly the same.
This leads me to conclude that the default values in case 1 have an impact on the output when the tolerance value is say 10%, 9%, etc but once it is narrowed down to 1%, there is no impact of the default values on the output (hence exactly the same outputs for both cases, using exactly the same input data).
Q1. I would greatly appreciate your deeper insights into this observation?
Q2. Is there a way to explore different scenarios(e.g., 2 people and 2 jobs, 3 people anbd 3 jobs, etc) within the same model without having to manually update each individual equation inside the OpenSolver interface as I have done for case 2 within the master model (case1)?
I look forward to your insights. That will be fantastic!
Solutions for different inputs will often be different for a non-zero tolerance. I suggest you set up one big model with constraints that get inactivated (eg by making the right hand side very large for a <= constraint).
Hi Andrew. Thank you for your prompt response. My model has both integer and non-integer variables and does not rely solely on rounding. When I change my OpenSolver parameters by clicking on Model, then manually updating all equations in relation to objective cell, variable cells and list of constraints to point to only the cells for the scenario in question (e.g. 2 people and 2 jobs), OpenSolver gives me an output for just that scenario (which is fine but cumbersome). If I do not manually update all my equations as above, OpenSolver gives me an output even in cells where there is no input data.
Is there a way to explore different scenarios within the same model without having to manually update each individual equation inside the OpenSolver interface? That will be a fantastic functionality to make this excellent software dynamic, especially for scaling up the size of the problem I am looking at (after testing that everything works with a small model).
I am also using VBA (and new to it) to communicate with the outputs from OpenSolver and also wish to avoid having to write lots of macros associated with making these individual changes/updates inside Opensolver model.
Thank you in advance for your cooperation and expertise as usual.
Hi,
I am trying to use the opensolver add in to excel to solve a simple knapsack continous! problem. I start by setting up the model (one constraint + upper bounds of 1 on each variable). I made sure to generate data that would not result in an integer LP solution. Then I use the Quick Automodel function and the Solve button. And then opensolver apparently solves a 0-1 knapsack problem- not the continuous (LP) relaxation. I have tried to monkey about with the model, but I cannot simply solve the LP problem. When inspecting the model with the show/hide model functionality, there does not seem to any binary requirement on the variables. What should I do if I just want to solve an LP model?
It is probably working fine but giving a naturally integer solution. Check the .lp file to convince yourself there are no integer requirements. Andrew
Hi Andrew. Thank you for your response.
Please elaborate on using conditional formatting to hide zeros?
Just to clarify, OpenSolver is giving an output (where 1 = assign) in my decision variable cells/changing cells, even when there is no data input in terms of exploring different situations for my assignment problem in relation to numbers of people and jobs. For example, if I have 10 people and 10 jobs as case 1 and input values for say 5 people and 5 jobs as case 2 (using the same model), I was not expecting to see any 1s for persons 6 to 10 and jobs 6 to 10 since there is no input data for persons 6 to 10 and jobs 6 to 10. I am looking at a mixed integer linear model where the output from openSolver (after considering the obj function and all constraints) is a result of rounding values (e.g.values less than 0.5 will become zeros to indicate no assignment and values greater than 0.5 will become 1 to indicate an assignment). The issue is that after clicking solve, there are 1s across all the rows (where rows are people) – places where I expect to see zeros since no input data was entered. I am not sure how conditional formatting can work.
Kindest regards,
P
Hi Andrew,
Please help me with the following 3 questions:
Q1. I have a big model to assign people to jobs using binary values as solver output (1=assign, 0=don’t assign). The objective function eqn is set for a specific number of people and jobs (and so are the constraints and variable/changing cells). For example, 10 people and 10 jobs. When I explore a different situation/problem in terms of numbers of people and jobs within the same big model (e.g. 4 people and 4 jobs), OpenSolver gives results even when no values (zero values/default values) are entered for person 5 to 10 and jobs 5 to 10. How can I get solver to display no results (or zeros ) in the variable/changing cells for which there are no values entered without setting different models?
Q2. I clicked on “View Last CBC Solution file” where there is a list of values in the 3rd column of my spreadsheet (2nd column shows a list of 1s and 0s). My question is: are the values in the 3rd column the objective values that CBC found before finding the optimal obj value shown at the top?
Q3 Under “View .lp file, it says “It took 7.983…E-02 seconds to build the model. Does this mean the solution time is say 0.078 sec from time of clicking solve? (this is pretty fast!). Thanks in advance for your expertise.
I suggest you look up conditional formatting to hide zeros. Please check the cbc documentation to understand all the outputs. And yes Opensolver builds small models fast. Thanks for your interest in OpenSolver.
Hi Andrew. Thank you for your response. Please elaborate on using conditional formatting to hide zeros? Just to clarify, OpenSolver is giving an output (where 1 = assign) in my decision variable cells/changing cells, even when there is no data input in terms of exploring different situations regarding numbers of people and jobs. For example, if I have 10 people and 10 jobs as case 1 and input values for say 5 people and 5 jobs (using the same model), I was expecting to see 1s only for the 5 people and 5 jobs and the rest should be zeros since there is no input data for persons 6 to 10 and jobs 6 to 10. I am looking at a mixed integer linear model where the output from openSolver (after considering the obj function and all constraints) is a result of rounding values (e.g.values less than 0.5 will become zeros to indicate no assignment and values greater than 0.5 will become 1 to indicate an assignment). The issue is that after clicking solve, there are 1s across all the rows (where rows are people) – places where I expect to see zeros since no input data was entered.
Empty cells are interpreted as zero; this may explain your observations. I hope your model has integer variables, and is not just relying on rounding. Andrew
Hi, I’m iteratively solving a problem using OpenSolver and I set a timeout limit of 20 seconds for each solution. Is there any way to suppress the dialog boxes? I attempted to do so with an “on error resume next” in VBA but that did not work. Any help is appreciated.
For my master’s project I have to solve a nurse scheduling problem using lagrangian relaxation. Opensolver is used to solve each iteration of the lagrangian dual problem. The problem has 800+ variables and 800+ constraints so I’d rather have a solution quickly (less than a minute) than a great solution in hours.
Thank you
We’ll have a look at this… what dialog is being shown? And how are you running the model… what OpenSolver method are you calling?
Nevermind I figured out my issue! I was using the command “RunOpenSolver False” but when I switched to “RunOpenSolver False, True” to minimize user interaction the dialog box went away.
Thanks Andrew