OpenSolver for Excel
The Open Source Optimization Solver for Excel
We welcome your comments on OpenSolver.
Dear developers, good afternoon.
I have the following question, I am working with a simple model for the development of a study topic, but how can I make it so that every time the message “OpenSolver Could not find an optimal solution, and reported: No feasible Integer Solution. The solution generated has been loaded into the spreadsheet.” appears, it runs the model using VBA in a loop so that the process finishes running the model once it completes all the data in the database.
Can this step be done using VBA?
I am aware that OpenSolver, when it does not find the optimal solution “in a timely manner”, still delivers a solution.
Thank you for your help.
You can do anything with VBA 🙂
como activo en VBA esta linea, no lo he logrado configurar.
OpenSolver.RunOpenSolver , False
‘A: MinimiseUserInteraction If True, all dialogs and messages will be suppressed.
‘Use this when automating a lot of solves so that there are no interruptions. Defaults to False
Al generar una de forma automática la idea es que los cuadros de diálogos no interrumpan la acción.
Se agradece su ayuda.
omitir… era tan simple, solo se debe cambiar a True.
I am trying to download Open Solver but receive the following warning when I follow the links to SourceForge:
This site has been reported as unsafe
Hosted by sourceforge.net
Microsoft recommends you don’t continue to this site. It has been reported to Microsoft for containing harmful programs that may try to steal personal or financial information.
I ignored it and tried to download anyway, however McAfee Anti-Virus also blocked both OpenSolver2.9.4_Beta_LinearWin.zip & OpenSolver2.9.3_LinearWin.zip
We have not changed anything, and I am not aware that SourceForge has either. I suggest you try Virus Total to get peace of mind that OpenSolver is not a virus. Cheers, Andrew
I have used Excel Solver to perform successful reconciliations in the past however when I try to perform the same type of reconciliation in Open Solver it does not give me the correct answer. I am not sure if there are variations in the model but it looks nearly identical to Excel Solver so I would have expected it to work. Below is a video of someone doing a reconciliation in Excel Solver. I am using the same setup as the video but open solver seems to handle negatives differently. For example I had tried to find an amount for -22,697 but open solver instead found me 22,697. I had no issues in Excel Solver. Please let me know what I am doing wrong or why the video below would not work in open solver.
Paul, Thanks for your message. You have indeed found a problem that we are now working to fix in the next release. Thanks for alerting us to this. Note that if you set up the model so that the target value in B2 is set to 110.01 directly in the Solver Model dialog then it works fine. Cheers, Andrew
How do I save different solver models and the recall them when needed? Where are they stored?
They are stored under ‘named ranges’… you can see everything under Name Manager. Andrew
Consulta, estoy resolviendo una macro en VBA y quiero dejar como opción el que se pueda llamar desde la macro el % branch AND bound tolerance
Que sentencia debo ocupar para llamarla desde mi macro a la macro de ustedes?, Esto me ayudaría demasiado en automatizar un proceso.
Please see the VBA documentation at https://opensolver.org/opensolver-api-reference/
Out of stack space errors appear when running both Couenne and Bonmin solvers.
Nomad works ok, but irrespectively of the precision, every time it provides a different solution.
It is only finding a local minimum… there are typically lots of these minima. Andrew
I have the following error:
26 Sep 22 21:48:06 [OpenSolver.xlam] SolverFileNL.SolveModelParsed_NL: Line 13
26 Sep 22 21:48:06 [OpenSolver.xlam] SolverCommon.WriteModelFile: Line 16
26 Sep 22 21:48:06 [OpenSolver.xlam] SolverCommon.SolveModel: Line 42
26 Sep 22 21:48:06 [OpenSolver.xlam] OpenSolverAPI.RunOpenSolver: Line 11
Error 9: Index not included in the range
using Excel non-linear solver i get a good solution, so i don’t think the problem is my model.
If necessary I can forward the file to you.
Our non linear support requires parsing your formulae. There must be a formula we don’t understand… perhaps try simplifying things. Or add constraints one by one. Or use Nomad which avoids this issue. Hope this helps, Andrew
I am getting error 13 type mismatch whenever I am using non liner solver Bonmin on Couenne but solver of excel is working fine. Please suggest some solution
Our non-linear solvers are experimental. NOMAD is more robust as it does not have to parse the Excel formulae. Andrew
Andrew, I love OpenSolver. It has helped to solve several problems in last years. Thank you and your team for creating and maintaining this great tool!
Thanks for the kind words; I appreciate you taking the time to contact us. I will let the team know. Andrew
I am running a model but some of my constraints are not being respected.
I am running it on google sheets.
Can someone advise please?
Any advice please?
I would be grateful if someone could kindly assist me in solving this issue.
Perhaps you could give the specific details… what are the values of the left hand side and right hand side if the equation? Have you tried the same model in OpenSolver for Excel? Do all the solvers give you the same problem?
I keep running the model i have entered and nothing happens. it just gives me the spinning wheel of death. I uninstalled the add on, re-installed it, i closed and re-opened sheets, i narrowed my variable cells and parameters, and still nothing happens. every time i try to reset the model, a popup asks me if i really want to reset it, but there is no “okay” or “yes” button, just an “x” to close the popup.
Sorry to hear of your hassles. Is this Windows or Mac Excel or Google Sheets?
OpenSolver for Google Sheets has completely stopped working for me as of one or two months ago.
Is there any way I can contribute to fixing the problem?
Hi. When I run OpenSolver, my constraints are not maintained in the manner in which I set them up. Wondering if anyone would be willing to look at my spreadsheet. When I run the same problem in regular Solver, I have no issues. (The reason I’m using OpenSolver is due to the extra variables, but the overall framework of my problem has not changed.)
Can you explain what is changing? Is it the order of constraints?
When I run the model in regular Solver, my constraints are maintained. However, when I replicate that setup in OpenSolver, the constraints aren’t maintained.
For instance, I’m calculating offensive and defensive ratings for football teams, and the constraints are that the average of all offensive ratings must equal the average number of points scored in each game (and the same thing for defense).
When I run the model, however, even if the average is 23.1 points allowed, each team’s rating goes to zero and thus the average rating goes to zero. And as a result, 0 does not equal 23.1, so the “must equal” constraint is simply not maintained.
Your model almost certainly has a non-linearity in it. Andrew
Dear Andrew Mason
I have written a paper that contains the development of a mathematical model which is then solved by opensolver. After the paper was submitted to an international journal, I received several comments related to the use of opensolver. Among them is about the quality of the solutions produced by OpenSolver. Can the resulting solution be guaranteed to be globally optimal? What kind of algorithm (heuristic method) does opensolver use? Can you help me to answer it?
I am pleased you found OpenSolver useful. The solution quality depends on what solver you used. If CBC, then I suggest you read up on Branch and Bound (assuming you have integer variables). If you have no integer variables, then Googling Simplex Algorithm will help you. Hope this helps, Andrew
How to use the latest version of cba, now the version of cbc is long. cba.exe is version 2.9.4. But https://github.com/coin-or/Cbc The cbc version here is already 2.10.7. How to use the new version 2.10.7?
You can simply replace your cbc.exe file with the latest one from the COIN-OR repository https://projects.coin-or.org/Cbc Note that OpenSolver has different CBC versions in ‘Solvers/win32’ and ‘Solvers/win64’, with the solver being chosen to match the 32 or 64 bit status of your operating system. Use the OpenSolver/About OpenSolver menu to see if you are running 32 or 64 bit; eg
“Version 2.9.3 (2020.03.01) running on 64-bit Windows 10.0 with VBA7 in 32-bit Excel 16.0”.
In this case, we are using the 64-bit solver in “Solver/win64”, so this is the one you should replace. You can also see this info under:
CBC v2.9.4 (64-bit) at “D:\Documents\Projects\OpenSolver\OpenSolver2.9.3_Beta_AdvancedWin\Solvers\win64\cbc.exe”
Hello the team,
Thanks for your work
Unfortunately, i try to use the open solver with my class this morning, i have no problems on my 2 laptop who running windows 10 and 11, but no one of my student got the message for activate the macro and no one was able to use the open solver
I have no idea why, i ask them to check the authorisation on excel parameters for allow the macros, but that still doesn’t worked
If you have any suggestions for fix this problems i would be gratefull, the open solver is very cool and i would like to use it with others students
Have a good day, Bests regards
We have OpenSolver running happily on Windows 10 in our student labs, so it is possible! I would ask your local IT team; they are probably blocking something somewhere or not “trusting” some locations. Please let me know how you get on. Andrew
Your email address will not be published. Required fields are marked *