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”
Cheers,
Andrew
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
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
I am using Open Solver Advanced 2.9.0 with my students for both Mac and Windows. My students running Windows 11 are having issue with it remaining installed on the Data tab in Excel. It runs fine but they need to reinstall it each time they open Excel. Any advice?
That is a new issue we have not heard of before. These glitches are often caused by, and then fixed by, Microsoft making code changes; let’s wait a bit and see if it goes away. For the record, could you post (or email if you are worried about privacy) the text of the About OpenSolver window so we get a record of the exact Excel version.
By the way, I do not install OpenSolver, but instead just ‘pin’ it in the Open File list so I can open it when I need it; this is good advice for lots of add-ins as it means Excel starts faster. it would also address your issue as a work-around.
This little message to tell you how I love your tool. Your solver solution is far better than the main other alternative, since it works as well and models don’t have size limits.
Many thanks for creating and maintaining such a fantastic tool.
Thank you for continuing to update this fantastic tool, I use it daily.
One question I have, and maybe this already exists I just haven’t found it… is there a way to set up a Macro for OpenSolver Model options? I have a couple spreadsheets where the data changes, but I run the same OpenSolver model on them, sometimes multiple times a day. Is there a way that I could set up a Macro to fill in the Model options since the objective cell, variable cells, and constraints are always the same? I tried doing this with the Macro feature within Excel, but as anticipated the options selected for the OpenSolver Model did not save and carry over in the Excel macro.
Any help or direction is greatly appreciated, or if this does not exist yet considering it for a future version would be awesome.
Thanks!
You can do this with VBA, but no, we do not have support for recording macros. Can you just duplicate the sheet instead; this will copy the model settings with it. Andrew
Jason,
One possible solution that works for me is to have two workbooks – one with the data and the other running OpenSolver. Using VBA, as Andrew suggested, simply ‘feed’ the new data to the workbook that launches OpenSolver by either copying the relevant cells or copying the whole worksheet. With a click of a button, the new data is transferred and then you can either manually start OpenSolver or do it programmatically. Mine, unlike yours, has variable data where the constraints, variable cells, and objective cells changes from one run to the next and VBA can be set up to perform this as well.
In regards to filling in the model options, these can be controlled via VBA using the ‘OpenSolver API References’ listed under the ‘Using OpenSolver’ tab. For instance, changing the Branch & Bound Tolerance from the default 5% to another value, like 20%, can be made by simply adding a line like “SetToleranceAsPercentage (20)” to your VBA code.
I love this software!!!
I am using Open Solver dialog box to build the model manually, but this a daunting task to do repetitively when I have to build and run model 6 times by changing objective, variable and constraints.
I am able to change objective and variables using VBA, but unable to add, set or update constraints using VBA code. I tried this code :
Dim TestSheet As Worksheet
Set TestSheet = Sheets(“Dispatch Planning Model”)
I have not found an answer to a question that seems simple to me. Server Neos uses the “Short priority: Send to higher priority queue with maximum CPU time of 5 minutes” setting in the job settings on its website. I can disable this option on the site because tasks are usually done so quickly. But OpenSolver when specifying, for example, “NEOS using CPLEX” explicitly uses “Short Priority”, and the complex task crashes after 5 minutes.
Can this be changed somewhere in the model settings?
A quick check of the code suggests OpenSolver does not yet have the option for long runs on NEOS. I will add it to the ToDo list. Thanks for raising this, Andrew
Good day, dear developers!
Thanks a lot for the great tool! I was amazed at the efficiency of solving integer programming problems for 20-30 thousand variables.
I enjoy teaching my OpenSolver students on my MBA course.
I recently came across an integer linear optimization problem that only has 161 binary variables and cannot be solved with OpenSolver (or it takes more than 12 hours to solve).
Excel file link: https://1drv.ms/u/s!ArFRI2i3V3yXl_ddltR-08_O9pumjQ?e=yfe1ze
The task schedules delivery days for 7 days of the week and 23 stores (binary variables E2: K24). I want to schedule a given number of deliveries D2: D24 for each store. The goal is to achieve a minimum difference in the loading of the central warehouse on different days of the week.
(The real problem is a little more complicated, but that’s enough.)
Surprisingly, the Calc Solver add-in (LibreOffice) solves the problem in a few minutes and gives the solution shown in the file (for example).
Can you please tell me what is the problem? The task looks very simple.
I was able to figure out how to overcome the described complexity.
In my opinion, a problem occurs when a problem has a large number of equally good solutions.
Tracking the optimization process shows that the algorithm quickly achieves excellent objective function values, and then continues to look for even better options.
From the point of view of practice, it is necessary to limit the objective function to some reasonable value. For minimization, this is the lower bound.
After that, the practical problem is solved quickly enough.
Sergei: This is the behaviour we expect. Proving we have the best solution is often much harder than finding the best solution. Try to change your equations to remove equivalent equally-good solutions by, for example, giving staff small random preferences (i.e. slightly lower costs) for shifts (or whatever makes sense for your problem). Setting a large optimality tolerance can also help reduce run times. Andrew
You should add circular references as constraints. I have not tried this, but it should work. You then need to manually break the cycle yourself; the OpenSolver constraint ‘completes’ the cycle. Let us know if it works, Andrew
I am just thinking about using OpenSolver, and I would like to know if it is possible to define the problem in Excel and then save it as mps or gurobi file or in another syntax. Thank you.
Milan
Yes; if you open the working files directory (using the OpenSolver menu), then you can see the .lp file. But, don’t forget that OpenSolver can run Gurobi directly for you. Cheers, Andrew
OpenSolver for Google Sheets is currently unavailable because of issues with changes made by Google that we are working through. Sorry about this, Andrew
I tried every free solver and minimizer available free of cost.
This is by far my favorite because it’s simple. Personally prefer this over SolverStudio because it can do what I want with minimum programming work on my side.
My problem.
-nonlinear
-set constraints
-minimizing one cell calculated in a spreadsheet
-larger than 200 variables. (So standard solver couldn’t handle it.)
This works. My suggestions follow
-Nomad solver
-On installation, it seems to work better with OpenSolver extracted into the MS Excel Addin’s folder. Specifically, if one clicks: file->options(at the bottom left)->Add-Ins-> Go (after manage in the middle with excel add-ins selected)-> browse (on analysis toolpak) -> it will take you to the addins folder. Copy OpenSolver to there.
-when selecting constraints and variables sometimes it seems to mess up and write the same cells multiple times. Sheet1!$A$1:$C$8 is how it should look as shown in the examples. Where ‘Sheet1!” = sheet name. $A$1:$C$8 = rows and columns from A1 to C8.
Very grateful for this OpenSolver. Well done Andrew Mason.
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”
Cheers,
Andrew
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
I am using Open Solver Advanced 2.9.0 with my students for both Mac and Windows. My students running Windows 11 are having issue with it remaining installed on the Data tab in Excel. It runs fine but they need to reinstall it each time they open Excel. Any advice?
That is a new issue we have not heard of before. These glitches are often caused by, and then fixed by, Microsoft making code changes; let’s wait a bit and see if it goes away. For the record, could you post (or email if you are worried about privacy) the text of the About OpenSolver window so we get a record of the exact Excel version.
By the way, I do not install OpenSolver, but instead just ‘pin’ it in the Open File list so I can open it when I need it; this is good advice for lots of add-ins as it means Excel starts faster. it would also address your issue as a work-around.
Cheers, Andrew
copy files to likethis “C:\Program Files (x86)\Microsoft Office\root\Office16\Library\SOLVER”。
Hello,
This little message to tell you how I love your tool. Your solver solution is far better than the main other alternative, since it works as well and models don’t have size limits.
Many thanks for creating and maintaining such a fantastic tool.
Alexandre
Thanks for your kind words. Andrew
Dear OpenSolver Team,
Thank you for continuing to update this fantastic tool, I use it daily.
One question I have, and maybe this already exists I just haven’t found it… is there a way to set up a Macro for OpenSolver Model options? I have a couple spreadsheets where the data changes, but I run the same OpenSolver model on them, sometimes multiple times a day. Is there a way that I could set up a Macro to fill in the Model options since the objective cell, variable cells, and constraints are always the same? I tried doing this with the Macro feature within Excel, but as anticipated the options selected for the OpenSolver Model did not save and carry over in the Excel macro.
Any help or direction is greatly appreciated, or if this does not exist yet considering it for a future version would be awesome.
Thanks!
You can do this with VBA, but no, we do not have support for recording macros. Can you just duplicate the sheet instead; this will copy the model settings with it. Andrew
Jason,
One possible solution that works for me is to have two workbooks – one with the data and the other running OpenSolver. Using VBA, as Andrew suggested, simply ‘feed’ the new data to the workbook that launches OpenSolver by either copying the relevant cells or copying the whole worksheet. With a click of a button, the new data is transferred and then you can either manually start OpenSolver or do it programmatically. Mine, unlike yours, has variable data where the constraints, variable cells, and objective cells changes from one run to the next and VBA can be set up to perform this as well.
In regards to filling in the model options, these can be controlled via VBA using the ‘OpenSolver API References’ listed under the ‘Using OpenSolver’ tab. For instance, changing the Branch & Bound Tolerance from the default 5% to another value, like 20%, can be made by simply adding a line like “SetToleranceAsPercentage (20)” to your VBA code.
I love this software!!!
Dear Open Solver Team,
I am using Open Solver dialog box to build the model manually, but this a daunting task to do repetitively when I have to build and run model 6 times by changing objective, variable and constraints.
I am able to change objective and variables using VBA, but unable to add, set or update constraints using VBA code. I tried this code :
Dim TestSheet As Worksheet
Set TestSheet = Sheets(“Dispatch Planning Model”)
Application.Run “OpenSolver.xlam!OpenSolverAPI.ResetModel”, TestSheet
Application.Run “OpenSolver.xlam!OpenSolverAPI.SetObjectiveFunctionCell”, TestSheet.Range(“Z1”), TestSheet
Application.Run “OpenSolver.xlam!OpenSolverAPI.SetDecisionVariables”, TestSheet.Range(“y4:y22”), TestSheet
Application.Run “OpenSolver.xlam!OpenSolverAPI.AddConstraint”, 1, TestSheet.Range(“y4:y22”), RelationINT, TestSheet – this line of my code gets error.
Please help.
Hey!
Thanks again for your tool!
I have not found an answer to a question that seems simple to me. Server Neos uses the “Short priority: Send to higher priority queue with maximum CPU time of 5 minutes” setting in the job settings on its website. I can disable this option on the site because tasks are usually done so quickly. But OpenSolver when specifying, for example, “NEOS using CPLEX” explicitly uses “Short Priority”, and the complex task crashes after 5 minutes.
Can this be changed somewhere in the model settings?
With deep respect.
A quick check of the code suggests OpenSolver does not yet have the option for long runs on NEOS. I will add it to the ToDo list. Thanks for raising this, Andrew
Good day, dear developers!
Thanks a lot for the great tool! I was amazed at the efficiency of solving integer programming problems for 20-30 thousand variables.
I enjoy teaching my OpenSolver students on my MBA course.
I recently came across an integer linear optimization problem that only has 161 binary variables and cannot be solved with OpenSolver (or it takes more than 12 hours to solve).
Excel file link: https://1drv.ms/u/s!ArFRI2i3V3yXl_ddltR-08_O9pumjQ?e=yfe1ze
The task schedules delivery days for 7 days of the week and 23 stores (binary variables E2: K24). I want to schedule a given number of deliveries D2: D24 for each store. The goal is to achieve a minimum difference in the loading of the central warehouse on different days of the week.
(The real problem is a little more complicated, but that’s enough.)
Surprisingly, the Calc Solver add-in (LibreOffice) solves the problem in a few minutes and gives the solution shown in the file (for example).
Can you please tell me what is the problem? The task looks very simple.
Best regards, Sergei.
Good day!
I was able to figure out how to overcome the described complexity.
In my opinion, a problem occurs when a problem has a large number of equally good solutions.
Tracking the optimization process shows that the algorithm quickly achieves excellent objective function values, and then continues to look for even better options.
From the point of view of practice, it is necessary to limit the objective function to some reasonable value. For minimization, this is the lower bound.
After that, the practical problem is solved quickly enough.
Sincerely yours, Sergei.
Sergei: This is the behaviour we expect. Proving we have the best solution is often much harder than finding the best solution. Try to change your equations to remove equivalent equally-good solutions by, for example, giving staff small random preferences (i.e. slightly lower costs) for shifts (or whatever makes sense for your problem). Setting a large optimality tolerance can also help reduce run times. Andrew
Dear OpenSolver developers:
I am a huge fan of using Excel when modelling Process Engineering simulations.
I have been using circular references, scaling and GRG, and find it quite powerful.
However, I sometimes miss a more powerful non linear solver, such as the ones you have implemented.
I have been trying to use OpenSolver, but seems to get into trouble when dealing with circular references.
May I ask you to state if this is the case? If so, are you planning on embedding OpenSolver with native circular references?
Thank you in advance.
You should add circular references as constraints. I have not tried this, but it should work. You then need to manually break the cycle yourself; the OpenSolver constraint ‘completes’ the cycle. Let us know if it works, Andrew
Error in loading DLL (Error 48)，Automation error (Error 440)
Excel will report errors when running on the computer. No loss is found in the reference, but it works normally on another computer
I am just thinking about using OpenSolver, and I would like to know if it is possible to define the problem in Excel and then save it as mps or gurobi file or in another syntax. Thank you.
Milan
Yes; if you open the working files directory (using the OpenSolver menu), then you can see the .lp file. But, don’t forget that OpenSolver can run Gurobi directly for you. Cheers, Andrew
On the webpage: https://opensolver.org/opensolver-for-google-sheets/
“You can install the OpenSolver Add-on by clicking on the Chrome Web Store link below” but I couldn’t find any such link.
“When you start OpenSolver” but the webpage does not contain a description of starting OpenSolver within Google Sheets.
OpenSolver for Google Sheets is currently unavailable because of issues with changes made by Google that we are working through. Sorry about this, Andrew
I tried every free solver and minimizer available free of cost.
This is by far my favorite because it’s simple. Personally prefer this over SolverStudio because it can do what I want with minimum programming work on my side.
My problem.
-nonlinear
-set constraints
-minimizing one cell calculated in a spreadsheet
-larger than 200 variables. (So standard solver couldn’t handle it.)
This works. My suggestions follow
-Nomad solver
-On installation, it seems to work better with OpenSolver extracted into the MS Excel Addin’s folder. Specifically, if one clicks: file->options(at the bottom left)->Add-Ins-> Go (after manage in the middle with excel add-ins selected)-> browse (on analysis toolpak) -> it will take you to the addins folder. Copy OpenSolver to there.
-when selecting constraints and variables sometimes it seems to mess up and write the same cells multiple times. Sheet1!$A$1:$C$8 is how it should look as shown in the examples. Where ‘Sheet1!” = sheet name. $A$1:$C$8 = rows and columns from A1 to C8.
Very grateful for this OpenSolver. Well done Andrew Mason.