Welcome to the OpenSolver on-line help.
I 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.
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.
Common Problems
Here are some fixes for common problems.
1/ Unable to find the external solver ‘cbc.exe’ …
If OpenSolver reports this problem, then it could not find the “cbc.exe” file that comes with OpenSolver. (This file may just appear as “cbc”.) Please check that you have extracted (i.e. un-zipped or uncompressed) all the files from the OpenSolver download.
2/ The CBC solver did not create a solution file.
If this error is reported, then it is likely that OpenSolver was unable to run the “cbc.exe” file. (This file may just be named as “cbc” on some systems.) This may happen if the OpenSolver files (including cbc.exe) are in an ‘untrusted’ location, and so Windows will not let cbc.exe be run. To identify this problem, please find the “cbc.exe” file in the same folder as OpenSolver.xlam (which may appear as just OpenSolver on some systems), and double click on this ‘cbc.exe’ file. You should then see a command window open that starts with the text “Welcome to the CBC MILP Solver”, and gives 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, including OpenSolver and cbc) into a location such as your Documents, or even into Program Files, and try again.
Hello,
I am trying to run a similar model (same matrix each time, but with different constraint ‘b’ values and obj function coefficients) 20 times. I have around 522 x’s and 1000 constraints. Is there any way to speed up the time spent building the model/’setting up problem’, since the matrix is identical each time? Right now, it would take around 30 mins to run and I would like to improve the time if possible.
Thank you for producing this powerful and easy to use solver,
SVK
Quick Solve does exactly what you want, where the ‘parameters’ are the right hand sides. For me it turned an hour’s running into 1 minute! Let me know how you get on. Andrew
I am having problems with my solver solution. It works with excel solver, but as I need to run more than 200 variables, I am using open solver. It keeps giving me a negative number for one particular line item. Some of the decision variables are constrained as binary and integers values. The others I want between 0 and 1 and I have set up my constraints that way. I keep getting this one negative number and the message that it is unfeasible. When I take out some line items and run it in solver, it works.
Please use OpenSolver’s “View Last Model .lp File” to check the .lp file to see if the model is as you expect. If it is, then feel free to email me the spreadsheet; send it to a dot mason at auckland dot ac dot nz. Cheers,Andrew
Hello,
I am trying to use the solver but get an error: “The objective cell does not appear to contain a numeric value” ErrNumber=-2147220504, but the objective cell=target cell?? is a number… Hope you can help? can I send you the spreadsheet (how)??
Brgds Daniel
Daniel: This often happens when the objective/target cell has a non-linear formula that gives #DIV/0! or similar for some values of the decision cells. Such a model is non-linear, and so not suitable for OpenSolver because OpenSolver uses the CBC linear optimiser. If this is not the cause of your problem, feel free to email me the sheet at email hidden; JavaScript is required. Cheers, Andrew
To elaborate on my previous post, the error looks like this:
“OpenSolver encountered an error
The CBC solver did not create a solution file. No new solution is available. (at line 12300)(at line 19540)
Source = OpenSolver Model Solving. Error Number = -2147220503
The problem is almost certainly that CBC is crashing. You may wish to try the latest version of CBC; see the “CBC Downloads” link on http://opensolver.org. Just replace the cbc.exe file in the OpenSolver folder with a newer version you download. Hope this helps, Andrew
I am running open solver and am getting the “CBC solver did not create a solution file” error.
I was able to run it in the same file with a smaller size problem and I had no issues. this problem though has 60,000 variables. It successfully sets up the problem and writes problem to disk but when it starts to solve it lags Excel for a minute or 2 and then throws back the error.
I’ve tried moving the OpenSolver file from my desktop to my documents and that made no difference either. This is also my own personal laptop and OpenSolver has no issues with other models.
Any ideas?
Hi Andrew! I’m using Solver 2.1 and can’t find the way to set a variable as integer. Would you please let me know the procedure? Once again thanks a lot for this great solver of yours.
Best
Ralph
Same as in Solver, you add a constraint specifying that the decision variable (constraint left hand side) must be ‘int’ (using the < =, =, >=, … pop down list). Cheers, A>
Hi there,
upon trying to solve my model, I get the error message that “instance 2 [of an Excel constraint] does not depend on the decision variables and is not satisfied”. However the specified cells DO depend on the variables via a countif function. Hence I would assume that OpenSolver will go ahead and try to find a solution. Is the error message wrong or am I?
Thanks for your help!
Your model is non-linear; CBC cannot solve models with ‘countif’ in them. The next version of OpenSolver uses NOMAD which may be able to find a good (but not provably best) solution. Andrew
Thanks a lot for your quick response! I will try to figure out a way to revise the model to make it linear.
Is there a definition or list of functions that are allowed in a linear model?
Keep up the good work!
I am having trouble adding binary constraints to the model.
When I build a solver model without any binary constraints, it optimizes fine. When I add in the binary constraints (added either as integer variables min/max 0/1 OR as binary) the optimization gives “No feasible solution”
For some reason the optimizer is not changing the binary variables at all (just set =0).
See my comments below.
Hi,
I have been using solver to optimize a problem.
I’ve set up the same problem in OpenSolver and get the error “OpenSolver could not find an optimal solution, and reported: No Feasible Solution”
The reason for this is that all the binary variables are set to 0 and Opensolver doesn’t seem to try changing them at all.
Note that I have them set up as integer constraints, max/min 1/0. Could this be a problem?
Using integer variables should be fine. Not sure what youer problem is; check your constraints carefully. There is no way that you can see if OpenSolver is trying 1′s for your binary variables; it is all internal. Andrew
Thanks for your reply.
I don’t think the problem is with my constraints, as the model is entered exactly the same in the current solver (which optimizes fine to a feasible solution) as the OpenSolver.
Is there perhaps a problem if calculations are being done on other tabs within the same workbook? I am really stuck, which is unfortunate as I really think your work is great!
Would you mind please emailing your spreadsheet to email hidden; JavaScript is required so we can work out what’s going on? Thanks, Andrew
Hi,
First of all thank you for this wonderful tool.
I tried to solve a linear problem using the solver but I found that for some inputs the result is not optimal. When I also noticed that when I decrease the Branch & Bound %Tolerance the result is more accurate (but still for some inputs it makes mistakes). Can you explain a little bit on the roll of the % Tolerance?
I would be happy to send you my file to get your feedback.
Thanks,
Alon
Alon, the Branch and Bound tolerance needs to be 0% if you want optimal solutions and have integer or binary variables. Otherwise, you get some solution that is guaranteed to be within the tolerance specified of the true optimal solution. Hope this helps. Andrew
I would like to execute Open Solver from VBA rather than manually from Excel. I can’t seem to find how to do this.
Thanks
I have found what I need. Thanks anyway.
Looks like a fine system.
There are VBA instructions under http://opensolver.org/using-opensolver; hope this helps.
Hi,
First: Great Work!
I haven’t tied it yet, but is it possible to run two instances of excel and solve a problem in each instance simultaneously? Thanks, Axel
OpenSolver works via files, so it cannot run two optimisations at the same time because the two runs will overwrite each other’s files. Is this a feature people want? We could make it work if there is lots of demand. Andrew
I don’t know if other people could use it. It would have been a good feature but it isn’t very important.
I experienced something else:
The time opensolver needs to build its model, is very dependent on the file sizen even if there aren’t any references to sheets with much data. I think the problem is, that opensolver searches for any equations in the whole workbook which influence the objective cell. Is it possible to limit this area (e.g. one sheet)?
Thank you very much.
OpenSolver helps me so much in analysing different aspects in my thesis about district heating systems.
I’m pleased OpenSolver is working for you. OpenSolver does not analyse the spreadsheet as such, but simply does lots of repeated calculations of the spreadsheet with different values in the decision variables cells. If your spreadsheet is very big, then Excel can take a long time to do these calculations. Switching to SolverStudio (a free download from http://solverstudio.org) will give you a much faster system for big problems like yours, but will require you to learn a modelling language. Cheers, Andrew
Hi,
when cbc is trying to solve problem it’s givin an error. There is a printscreen in the link.
http://i48.tinypic.com/r04iz8.jpg
What should i do?
This indicates a bug in CBC (perhaps because it is running out of memory during a large branch and bound run). You can report this to the CBC team; see http://list.coin-or.org/mailman/listinfo/cbc. Please save the .lp file that OpenSolver creates as the CBC programmers may want to see this. I have found you can avoid some of these errors by using the 64 bit version of CBC; the next version of OpenSolver (which will be out within the next few weeks or so) includes a 64 bit version of OpenSolver. Sorry I cannot be of more help, but I am enroute to a conference at the moment. Cheers, Andrew
Good afternoon
I received an error message with the following information:
Unrecognized relationship for constraint(unknown) alldiff
Source=OpenSource Model Build, ErrNumber = -2147220504
Does OpenSolver solve linear models with a combination of different types of decision variables? As background, my model contains four decision variables. The model is a routing algorithm designed to determine a sequenced tour using two modes of transportation. The first two binary decision variables are associated with modes of travel, the third binary variable is utilized for the either-or condition associated with choosing a single mode, and the fourth is a sequencing integer decision variable used to order the tour.
OpenSolver does not work with AllDifferent constraints. I suggest you try an integer programming formulation. Good luck, Andrew
I can’t figure out how run OpenSolver 2.1 in Excel 2003. Double clicking on the extracted OpenSolver.xlam doesn’t work, which is not surprising. Must I convert it to xla (and how do I do that?) or is there another version for 2003 that I need to download? I read on the site that 2003 is supported. Please help?
I’m sorry, but you are on your own with Excel 2003 now. OpenSolver has code to support Excel 2003 in it, but we have stopped testing this now. It should still work, but you will have to find someone with a newer version of Excel to convert it to an older file format. Please let us know how you get on and the steps you follow; I’m sure others will want to know. Andrew
Somebody knows which is the sub that the button calls?
I need to use in a VBA sub, I try “RunOpenSolver False” but it didn´t solve integer problems.
Please explain in more detail what happened – was there an error message?
Thank you for your attention, I was using a previous version and I think that was the problem. I downloaded the new version and It worked!
Congratulations for your work, it helps to disseminate the culture of optimization!
Hello,
somehow my model doesnt work anymore. I get following error message:
OpenSolver encountered error 1004:
‘Calculation’ for Object ‘_Application’ failed(at line 19540)
Source = OpenSolver, ErrNumber=1004
I hope you understand the error message, because my Excel error message is german and i didnt know the proper translation for it
Can you help me with this problem? Many thanks in advance
Please email me your spreadsheet to email hidden; JavaScript is required, and I will have a look. Thanks, Andrew
hi. i also encountered the same error message… what are the possible causes of this kind of error? is it because the model is too large? by the way, what’s the maximum number of variables that open solver can handle? thanks
I’ve just installed open Solver and I can’t get it to work.
I’m trying to solver a MIP problem with 200 binary variables, but the solver doesen’t even run.
I’m using Windows 7 and Office 2010.
“Open Solver encountered an Error:
Unable to run the external program: C:\Users\Juan Pablo\Desktop\OpenSolver21\cbc.exe – directory
C:\Users\Juanpa\AppData\Local\Temp – import model.lp -ratioGap .01 -seconds 60 -solve -solution modelsolution.txt (at line 12230) (at line 19540)
Source=OpenSolver, ErrNumber=-2147220500
Any ideas? CBC.exe is not blocked …..
I don’t have Windows 7, sorry, but I gather it can block files on the desktop. Please try moving the OpenSolver folder to a new location (such as your documents, or even your Program Files folder), and then please let me know if that works so I can let others know. Thanks, Andrew
Hello Andrew! I Have a very large DEA Model (92 DMUs) that I wish to run with opensolver. I have used the following commands in VBA:
For unit = 1 To 50
Range(“H3″) = unit
SolverSolve UserFinish:=True
Range(“N” & 5 + unit) = Range(“H” & 5 + unit)
Next unit
End Sub
What command I should write to substitute “SolverSolve UserFinish:=True” ?
Best Regards!
Valdecy
Please see the “Using OpenSolver” pages at http://opensolver.org/using-opensolver, under the VBA section. Your model will run many times faster if you use the Quick Solve feature, described briefly on the same page. (See also the OpenSolver VBA source for code that uses Quick Solve.) Cheers, Andrew
Hello – is there a limitation to the number of variables? I am getting the following error: OpenSolver encountered error 1004: The specified value is out of range. (at line 23520) Source = OpenSolver
Thank you!
There is no limit. Are you able to send me your spreadsheet so I can see what the issue is? email hidden; JavaScript is required. Thanks. Andrew
This occurs because of a limitation in Excel limiting the size of the rectangles added to a sheet to highlight a cell range when showing a model. I will add fixing this to our to-do list. Thanks for the report, Andrew
Hi Andrew,
Thanks for your work. Dont want to be a pain but was this limitation issue fixed in the November release or is there an estimated date for it?
Thanks a lot
I’m not sure which issue you are referring to, sorry. Andrew
Hi Andrew,
I was referring to error 1004: The specified value is out of range. (at line 23520)
Thanks,
Esteban
I am trying to include more than one set of variable cells in the “Variable Cells” section of the model but unfortunately it does not allow me to utilise a comma “,” to separate the different cells. For example I would like to include:
$Z$166:$AH$182, $Z$169:$AH$182
However it ignores the comma when i try and select the cells. Is there a way around this?
Your ranges overlap in your example, which seems odd. Ignoring that, hold down the Control key to add a second range while dragging on cells in the sheet. Hope this helps. Andrew
Suspect the problem may be with running windows on a mac with VMware. The control key doesnt seem to work, but have typed the second range and is working. Many thanks for your assistance.
first thank the work they have done. is there a way to show me the number of iterations and the time it takes to find the solution of the model.
Thanks.
You can do this by giving the CBC solver specific logging instructions and pausing it after solving. See the CBC parameters info on the Using OpenSolver page. Andrew
First and foremost thank you for the awesome work. Is there a way that I can set up solver to provide me with the 3 best possible solutions instead of only the best? This could be as simple as changing the setup on my spreadsheet, which I can send to anyone who can help. Just curious if this is an option that needs to be selected, a constraint, or just how the data is set up on the spreadsheet.
Thanks
You can add what I call a “deja vu” cut which forces a new (2nd best) solution to be found. Have you got binary variables? If so, these cuts are easy. Eg, if you get an optimal solution with x1=1, x2=0, x3=1, then add a cut (x1-1)+x2+(1-x3)>=1, i.e. the number of changes from the old solution must be >= 1. Hope this helps, Andrew
Hello,
I have just downloaded OpenSolver 2.1 from SourceForge (OpenSolver21.zip), however if you open ‘OpenSolverMain’ in VBA the version number is 1.9 with a release date of 2011/12/5.
Is it possible to get hold of Version 2.1 from somewhere? I can’t see any obvious links on this page ‘http://opensolver.org/archives/410′
Many thanks,
Adam
Adam: I think you are running an old version of OpenSolver, not the new one you downloaded in the OpenSolver21.zip. Make sure you extract all the new files into a folder, and then open the OpenSolver file from this folder. Hope this helps, Andrew. PS: You may need to uninstall the 1.9 version from Excel; some versions of OpenSolver let you do this from their “About OpenSolver” dialog, or you can do this using Excel’s Add-in manager.
Hello Mr Mason
Is it possible that the variable cells be composed of many disjunct ranges, such as A1:C4;D7:G11 for example ?
Thanks
Yes. Andrew
OpenSolver is reporting that my model (1034 variables, 2400 constraints) has constraints that are not linear. However, they definitely are linear. I also verified this during a trial use of Premium Solver Pro which solved the exact same model perfectly without linearity issues. Morever, in OpenSolver, the model doesn’t seem to be adhering to all of my constraints regarding positive variables(ex: $O$97:$U$143>=0) and is producing negative values(I also selected, “make uncstrained variables non-negative). Any advice?
Have you tried the latest (v2.1) release? This is more robust in detecting non-linearity, and also uses a newer version of the solver CBCP. If this doesn’t work, then please email me your sheet so I can look at this in more detail. Thanks. Andrew. PS email address is email hidden; JavaScript is required.
Thanks for sending me your sheet. Your model depends on cells that contain random numbers, and so the model changes each time the spreadsheet is re-calculated. OpenSolver is behaving as expected in complaining that the model is non-linear. It works fine if you replace the cells containing formulae involving rand() with the values that were randomly generated. Hope this helps, Andrew.
I’ve created a simple linear model which solves easily with the excel ad-in solver, however, when I use open solver to solve the same model I receive an Open Solver Error;
Subscript out of range
SourceOpenSolver,ErrNumber=9.
I am a beginner at this. Have you any suggestions how I can correct this error? Doug
Please email your spreadsheet to email hidden; JavaScript is required so I can track down what sounds like a bug. Thanks for contacting us. Sorry you have hit this issue.Andrew
Hi Andrew, sorry for the delay in responding. I was not auto notified of your reply through my hotmail address though I thought I had selected that option, and was unaware that your response had come in. I’ll get a sheet to you. Thanks. Doug
Hi Andrew,
I’m using OpenSolver ver 1.9 with MS Excel 2010 and find that the search for a LP solution always starts with the variables set to zero. This means that the solver always ends up with a local optimum. In an example that I use to illustrate the benefits of OpenSolver, I can manually improve on the solution by changing values in the variable cells while satisfying all constraints. However, when I get OpenSolver to solve with this new set of cell values it reverts to the originl sub-optimal (local optimum) value in the goal cell. I note that Excel 2010 Solver does not have the initial “Guess” button any longer and wondered if this problem is related to this change in the Excel Solver. It would be preferable to find a local optimum but then entice the solver to search for a better or even global optimum by staring from a different set of initial variable values. Any advice on this?
Regards
Daniel
Daniel: OpenSolver only solves linear models, for which there is only one local optimum (being the globally best solution). So, OpenSolver correctly ignores the starting solution. Perhaps your model is non-linear? Andrew
Brilliant project! Thank you for your hard work and support. I have a seemingly simple question. Can I simply replace the bundled cbc.exe (2.7.1 version in OpenSolver 1.9) with something like the one in http://www.coin-or.org/download/binary/Cbc/Cbc-2.7.5-win32-cl15icl11.1.zip? My initial trial was unsuccessful, so I wonder if I am missing something.
Thanks for your feedback. Upgrading CBC should work if you just replace CBC.exe by the new one. I cannot imagine why it failed for you. TYou could try opening the CBC command line from OpenSolver, and see what version it reports. Good luck, Andrew
Thanks Andrew. It turned out to be the problem with my build. I built cbc.exe 2.7.7 from source using VS2010 on Windows 7 and tried to deploy it to Windows XP machine. The program does not work under the machine without VS2010 redistributable libraries. I was able to package all the required libraries into cbc.exe and fixed the problem, and OpenSolver does not complain about no solution file any more. By the way, the cbc.exe in my previous link should work fine on any machine since it seems to be the one containing all the required components.
Thanks for that update. The next OpenSolver version (that I’m still developing) will come with a newer version of CBC (and, hopefully, support for a 64-bit version of CBC). Andrew. PS: We always statically link the libraries when we compile CBC so we only need the one .exe file.
I have been working wonderfull with version 1.2,
I download the version 1.9 (last one) but when I run the model I get a compiling error in VBA Proyect..
Regards
Nicolas
I’m sorry to hear you are encountering an error. What is the error message? This code is being used by 1000′s of users, so the problem must be something arising from your particular software setup. It could be a problem with your RefEdit; the OpenSolver site has some info on this at http://opensolver.org/help. Please let me know how you get on. Andrew
You are rigth. The problem was with RedEdit Control
Great product and fantastic support
Nicolas
Pleased it worked, and thanks for the positive feedback. Hope you enjoy using OpenSolver. Andrew
Is it possible to retain quick solve model and parameter assumptions after closing/opening files? I have a large model that is time comsuming to re-initialize and establish quick solve after closing.
Thank you.
Sub Macro1()
‘
‘ Macro1 Macro
‘
‘
SolverOk SetCell:=”$C$159″, MaxMinVal:=1, ValueOf:=”0″, ByChange:= _
“$N$163:$AS$406″
SolverLoad LoadArea:=”$AK$56:$AK$96″
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _
:=True, StepThru:=True, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True
SolverOk SetCell:=”$C$159″, MaxMinVal:=1, ValueOf:=”0″, ByChange:= _
“$N$163:$AS$406″
runquicksolve
End Sub
Great that you are using Quick Solve. I’m sorry, but you cannot save a Quick Solve built model for use after re-opening a spreadsheet. For big models, I suggest you visit http://www.SolverStudio.org; it greatly speeds up the model build step by taking a different approach. Andrew
Hello,
I am developing an integer program for scheduling employees in a paper converting mill. I have it set up so the user can enter which shift an employee is on during a given week (1,2,3, or sick/vacation/training), and a table automatically populates which possible machines an employee can run and when he/she can run them by entering a 1 or 0 in the respective cell. I set up a table of decision variables as integers only, where the numbers determined by solver are multiplied by the binary table and a new table is the result of the product of the prior two tables. From here, I have set constraints such as which shifts production planning needs to run for a given week, and the number of full time shifts and overtime shifts for each employee is limited to 1. My problem is this: Each time I run the program, with a constraint that all decision variables are integer, OpenSolver reports that it could not find an optimal solution, “No Feasible Solution”, and it meets the planning requirements and shift requirements only by having some workers work 0.5 shifts during the week, violating the integer constraint. I thought my model may be off, however I entered he current schedule by hand into the decision variables, and it satisfies all constraints. Why is OpenSolver unable to satisfy constraints when I can?
I’m not sure why OpenSolver’s CBC solver is not finding the solution you can find manually. Maybe you could post me the spreadsheet so I can look into it; email hidden; JavaScript is required. Thanks, Andrew
I am running into a similar issue as MACarle (at least same error message). I can build a model with OpenSolver and all functions of OpenSolver work, however when I go to solve I get the following error message:
OpenSolver encounted an error:
Unable to run the exteral program: C:\Users\Micah\Desktop\OpenSolver\cbc.exe-directory
C:\Users\MICAH~1\AppDataq\Local\Temp\ -impportmodel.lp-ratioGap .05 -seconds 100 -solve -solution modelsolution.txt
Source=OperSolver,ErrNumber=2147220500
The model I’be built works with OpenSolver on other computers, just not on mine. Using Excel 2010 (32-bit) and Windows 7.
Thanks,
Micah
Micah: Two things come to mind.
1) It looks as though you have unzipped OpenSolver in a Temp directory. Perhaps cbc.exe does not have permissions to run if it is in this directory? Please try unzipping OpenSolver into another directory, such as your Documents directory, and the running OpenSolver.xlam from there.
2) Windows 7 may have marked cbc.exe as having been downloaded from the internet. To check this, please right click on cbc.exe, choose Properties, and ensure that there is no “unblock” button as shown on http://studio.opensolver.org/?page_id=8. Once you have used this button to unblock cbc.exe, it may fix the problem.
Please let me know how you get on.
Thanks, Andrew
Hi Andrew,
The unblock solution fixed the error I was encountering. Thanks for the help!
Micah
Pleased it worked; I’ll update my instructions to include this information. Andrew
Hello,
I built a rostering linear problem and so far opensolver did very well. But when I add a constraint that limits the solutions (sum of hours of all employees per month need to be less than 165) solver does not say “No feasible solution”, solver crashs after about 30 minutes (after setting up the problem for 90000 variable for 110 minuten). It doesnt create a solution file. I tested on multiple computer and it is always the same.
Any Idea ?
Can you be more specific about the crash – OpenSolver should never crash! Perhaps you could send me your spreadsheet by emailing it to email hidden; JavaScript is required. Your problem is a hard one. Maybe the COIN-OR CBC optimisation programme is running out of memory? Constraints like you have added can be very fractionating. Make sure the 165 is a multiple of the shift lengths or, even better, limit the number of shifts worked (or use column generation, which is our area of research). You can turn on the option to view the CBC output while CBC is running, which might help. You can also open the problem in CBC, and try solving it in CBC manually to see what’s happening; see http://opensolver.org/using-opensolver for more info. Hope this helps, Andrew
The problem indeed seemed to be a CBC issue. The crash always occured at the point an integer solution was found. At first I thought it was a problem in the feasibility pump, but the problem also came up when an integer solution was found in the branch and bound tree. We finally resolved the issue by replacing the 32bit cbc binary with a 64bit binary which can be downloaded from the coin or site.
Thanks for sending me the spreadsheet. I have confirmed that CBC is crashing, perhaps because it is running out of memory. (In my test, it crashed with 1.927G of memory used; the limit is 2G under 32 bit windows.) OpenSolver itself is not crashing, and is correctly reporting that CBC did not generate a solution. You may find the comment elsewhere on this page about using a 64 bit version of CBC useful (if you have a 64 bit version of Windows) as it will let you solve bigger problems. Cheers, Andrew
Hi,
I’ve found that OpenSolver works great on my own computer, but I’m having trouble getting it to run on a second machine where I don’t have administrative rights. I have included it as an add-in but when I run OpenSolver I get the message box:
“OpenSolver encountered an error
The CBC solver did not create a solution file.
No new solution is available
Error source = OpenSolver model solving errnumber = -2147220503″
I can’t include the spreadsheet, but the model is linear. The standard solver achieves result just fine on a sample problem with 20 variables. OpenSolver gives this error regardless of the number of variables. Any idea what triggers error -2147220503?
You need to ensure that CBC can write an output file into the temp directory. You can check which directory this is by choosing “Open Last Model in CBC”, and seeing the directory listed in the CBC arguments. FYI, we can run OpenSolver in student laboratories with no admin privileges. Hope this helps, Andrew
Hi,
I built my model using the standard Excel solver, then used OpenSolver to solve it. I get an error which says:
Source: OpenSolver, ErrNumber = -2147220500.
Can you help me find the signification of this error?
Thanks,
Marc-Andre
Marc-Andre: Would you mind sending your spreadsheet to me, so I can debug it? email hidden; JavaScript is required Thanks. Andrew
Hi,
Is there any example in c# that is using opensolver?
I could not find how to run open solver from my c# form?
Can you help me?
Hi!
Thanks for the project! It’s great!
But I still have one question: could you pls advise how to use the “alldiff” constraint.
So far, it doesn’t work for me
alldiff is not supported by OpenSolver, sorry, as it is not supported in the CBC solver OpenSolver uses. Andrew
Just a quick question (couldn’t find it on the site) –
What is the maximum amount of decisions and constraints that OpenSolver can handle?
There are no artificially imposed size limits, so it depends on the memory you have and the complexity of your problem. Hope this helps, Andrew
Hi Andrew,
I step up the model and when I clicked on Auto Model. There is an error which says ‘Couldn’t find objective and couldn’t find result’. When I clicked on Show/Hide Model, there is an error which says No solver model found on sheet.. My model is very big. It runs into 19586 rows and is not structured in LP format.What do you think could be the problem?
Thanks!
Preethi: AutoModel only works if the model is set up in a particular way. You should set up your model using the Model dialog. Hope this helps. Andrew
Hi
Thanks for a great product. It solved my ~1000 variable problem in a jiffy. The addon to Excel is easy and simple.
I want some help in the following: My output rows contain values from 0-5. I want to count the number of rows having non zero value and use it in a constraint. Can you advise me?
Thanks in advance
Ravi
Ravi, You will need to use 0/1 variables I think; I suggest you look up a book on mathematical modelling. Make sure you do NOT use an IF(); these do not work in Solver or OpenSolver (but can, I think, be used by Frontline’s Premium Solver, which presumably converts them to an 0/1 variable behind the scenes). Andrew
Hello Andrew
I was very excited to learn about OpenSolver and hoping to take an existing model currently being solved by Solver and use OpenSolver.
My issue is that the entire existing model was set up using standard Solver commands in VBA (e.g. SolverAdd Funcition) and so forth. I have made sure to reference OpenSolver, rather than Solver in the active module.
My questions are the following
1. Do the standard Solver commands now have to be OpenSolver commands (e.g. OpenSolverAdd)?
2. I am assuming the above is not true, in which case how do I call OpenSolver? I have removed the reference to Solver. Currently, my code is such:
‘Solve
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=True, Convergence:=0.0001, AssumeNonNeg:=True
I have tried placing RunOpenSolver False following this line, before and after the above line, and at the very beginning of the subroutine; but I always get the error “ Sub or Function not defined”. I’m missing something, but cannot figure out what. Should I be using the commands such as those listed in the RunOpenSolver code? If so, where should I place it? As a function, and call the function?
Thank you very much, any insight is much appreciated!
Thanks for your enthusiasm about OpenSolver. To answer your questions (1) the standard Solver commands work for building OpenSolver models (but not solving them). Then calling RunOpenSolver False should solve your problem. You should reference both Solver and OpenSolver, but that is unlikely to be your problem. I would double check you have OpenSolver loaded, and you have referenced it as detailed on http://opensolver.org/using-opensolver. Let me know how you get on. Andrew. PS: Why is AssumeLinear false? OpenSolver need this to be true.
I am currently working on a linear problem that contains 2940 variables (~1000 binary, rest free) and it is taking quite a long time (14 hours and counting). I wonder if there is a way to approximate the time it takes to finish. Having enabled the “show iterations” but not fulling understanding it, can someone explain to me what the nodes and tree represent? Thank you!
Problems like this can be very hard to solve. I suggest you Google “branch and bound” to get a better appreciation of what is going on. Hope this helps, Andrew
Thank you for the quick reply! I read on a website where a problem with 100 binary variables would take a computer that is running at 1000 nodes per second many life times to finish. Does this mean my solution will go on forever?
Benny: It all depends on how difficult your problem is. There is an art and science to building models that solve quickly. You can try increasing the integer solution branch and bound tolerance to see if it can find any sort of solution (not just an optimal one). Good luck, Andrew
Benny,
My program has approximately 6000+ binary constraints. I wanted to estimate the amount of time this program would take to run. Could you please tell me how long did yours take to complete? Did increasing the integer solution branch and bound tolerance help?
I have downloaded Open Solver and am able to create models, however when I try to solve them an error message pops up saying: “Unable to find an external server.” How can I fix this? Thanks. Sarah
I have not seen this error message before, sorry; it is not an error message that we have coded into OpenSolver. Are you perhaps saving your files on a network file server? Does this happen on other computers you try? Thanks, Andrew
Hi Andrew,
I’m dealing with a model whose objective function is nonlinear. If the option of ‘highlight the nonlinearities’ and ‘run a linearity check’ are selected, no optimum solution could be found. I’m wondering the nonlinear objective function is applicable for this software.
Thank you very much for your help.
Regards.
Lluvia
OpenSolver does not handle non-linear problems, sorry. Andrew
Hi,
I’m using Excel 2007 and I found OpenSolver very powerful. But I do have a question. For the project I’m working on, I have to set up the model and call RunOpenSolver(False, True) both from VBA. Even though the Assume Linear Model has already been turned on, the warning
“OpenSolver assumes the model is linear. However, Assume Linear Model is not turned on. Would you like to continue anyway (and solve the problem as if Assume Linear Model was turned on)?” appears every time. I think the reason probably lies in the BuildModelFromSolverData(). After is called, AssumeLinearModel is false. Is there a way to avoid such warning?
Thank you.
lin
Thanks for your report, and for having a look at the code. There are two checks that are made, being either that “Assume Linear Model” is turned on (as happens in Excel 2007 and earlier) or that the engine is set to the Simplex method (Excel 2010) and later. I have just checked the code for all this, and it looks fine. What version of (1) OpenSolver and (2) Excel are you using? Would you be happy to email your spreadsheet to me at email hidden; JavaScript is required so I can check how it works on my PC? Thanks, Andrew
Hi Andrew,
That problem disappeared. I must be very careless yesterday. Thank you for your attention and great software. lin
Hi Andrew,
Can you tell me your email address? I want to send my model to you and ask for your help to check it. The model runs and gives an “optimal” solution but actually there are some constraints it doen’t satisfy. The problem is serious to me, so your help is highly appreciated. I will clarify the model to make it easily understandable. Thanks.
I am trying to solve integer programming models. The size of models are normally 5000+ binary variables and 10000+ constraints. I met a few questions when running it.
First, I want to use your tool to find a feasible solution that satisfies all constraints but no objective needed. So I set the objective as minimizing a constant. When I run it, sometimes it can give me a feasible solution, but sometimes it says infeasible while I’m sure it has a feasible solution. So I want to know how opensolver works and what result Opensolver normally gives under this condition.
Second, I want to make sure whether Opensolver has limitations for variables and constraints in integer programming. Because for medium size model(about 1000 variables) Opensolver can give optimal solution in most cases, but for my models(5000+ variables) Opensolver always says no optimal solution can be found, but actually the problem is not infeasible. Even changing solving time and B&B tolerance doesn’t help.
Sue: Thanks for your questions. Firstly, OpenSolver has no size limits, and should be able to handle a constant objective. So, there are two remaining issues here. (1a) Is OpenSolver building the correct model for CBC to solve and (1b) correctly interpreting CBC’s output? (2) Is CBC able to solve the model? To address these, please use the “View last model .lp file” menu item to check that the model OpenSolver creates is as you expect, and also use “View last CBC solution file” to check that OpenSolver is correctly interpreting the output CBC produces. In terms of (2) — CBC’s ability to solve the model — if you can send me a spreadsheet that contains a feasible solution (that you have created) that OpenSolver cannot find, then I (and the CBC folks) would be most interested. Do be aware that integer models can be very hard to solve, and that you should not expect perfect integer values in the answers. To help your debuggin, you may wish to view the CBC output during the run; please see the Using OpenSolver page for info on this. Please let me knopw how you get on. Cheers, Andrew
Hi,
Can OpenSolver generate any reports especially sensitivity report as shown in Excel Solver?
OpenSolver can only generate one “report”, being a list of constraints and their dual prices. It cannot generate Solver’s reports. I have only ever used these reports in teaching, never in a real application. Are you wanting them for teaching? Andrew
hi, when i am trying to build model by clicking any button, says “Can’t find project or library”. What should i do?
Sorry to hear you are having problems. I’d try repairing Excel (using Excel application menu, then Excel Options, then Resources, then “run Microsoft Office Diagnostics” in Excel 2007), which often helps but is a bit of a stab in the dark. You might also want to look at http://support.microsoft.com/kb/166273, which talks about this. Please let me know if you find any missing references as per the last link. Cheers, Andrew
in tools > references saying “Missing: Ref Edit Control”. if I uncheck that line, solver working correctly, at least i did not see an error. But whenever i want to use solver i have to repeat those steps.
Using the VBA editor (which you access using Alt-F11), you should be able to save OpenSolver after un-checking the “Missing: RefEdit Control” line. This should fix your problem, which seems to be caused by some version differences with RefEdit. We need RefEdit, but it is known for being a difficult control because of these sorts of issues. I hope this helps, Andrew
problem solved. thank you so much for help.