I’ve spent weeks looking for a version of Solver that will work with Microsoft Access 2007 but without success. Has anyone ever come across a Solver addin for Access?
It won’t exist, as there is no “sheet” on which to build the model. Use SolverStudio with one of its modelling languages if you want a model that uses data from Access. Andrew
Something interesting for you.
As I loop opensolver (with Bonmin) over a similar problem, just with changing input values, the resolution time increases linearly with the number of iterations.
What takes less than 400 secs to solve at iteration 1 takes more than 1000 secs at the 100th. The RAM used by excel also increases a lot over time, although I reset the OpenSolver model at each iteration and the problem size remains the same.
It seems that the time spent with the message “OpenSolver: reading solution” just keeps on increasing, while building the .nl file and solving the problem itself takes a constant time.
That happens when I close the laptop lid and let the macro run.
However, when I actively look at the excel file where the solver is running, this problem does not happen: when “OpenSolver: reading solution” pops up, I just have to click the mouse somewhere on the worksheet where OpenSolver is running. Then the solution is immediately read and the macro goes to next iteration.
I work with Excel 2010 (64 bits) on Windows 64.
Can you please send us your code to email hidden; JavaScript is required? This looks like something we may be able to fix (or, it may be a quirk of VBA, probably around garbage collection which we don’t control). Thanks for letting us know about it – we’re definitely keen to have a look into what’s going on. Andrew
So again, this product is awesome. Thanks so much for the work.
One question: would it be possible to use parallelization for model (.nl file) creation?
What takes most time when solving nonlinear problems is to create the .nl file, more specifically writing linear constraints. This writing process time seems to grow quadratically with the number of variables.
Could that be parallelized using several cores/processors?
Good, so I have implemented a fake parallelization – using multiple Excel instances to build the JBlocks in order to use the full processing capacity when working on NL problems.
In order to make it I had to make some changes to the variable type: collections have been changed to dictionaries.
In particular what causes the extreeeeeemely long processing time to build the JBlocks is to go through LinearConstraints which is a Collection of Dictionaries.
Just by changing this to a Dictionary of Dictionaries makes the whole For loop EXTREMELY faster.
I save about 90% of the time on large models!!!
So I recommend for a future release that you consider dropping the Collection objects and change them for Dictionaries, when they are used in For loops… Might have something to do with how Excel allocates memory or deals with different object types?
Once this is done you do not need multithreading any longer to save time – although I now have the option available…
Thanks for your detailed analysis of our code, and well done on finding places we can make changes to speed up the code. We will be incorporating your suggestions in our next release. Thanks again… please send us more such feedback! Andrew
Hello, I am John and I write from Italy. I apologize for my English (I got help from Google Translate) and I hope I manage to make myself understood. I used opensolver 2.7.1 on an Excel spreadsheet that provides 5 variable cells with 2 type> variable = and <= for all cells (5). Opensolver starts and ends his work without generating any trouble but the result is not as expected. Iin the target cell indicated the maximum value but the result is not. In particular opensolver returns it, for all cells, the minimum value expected in the range. This happens with both NEOS using Bonmin and with NEOS using Couenne. Where am I wrong?
Thanks and congratulations for the excellent work.
This sounds like a mistake in your model. Please try to find a better solution manually. If you can then it might indicate a bug that we would like to know about. Andrew
In my workbook the objective (to be targeted) is calculated by calling a macro/subroutine. is it possible to use opensolver to solve the problem by asking the optimizer to call the macro everytime it tries to evaluate the objective? I cannot make a function to change the target cell automatically because I need to change many cells in the workbook and EXCEL does not allow function to change other cells in the workbook.
If you need a macro your problem is probably non linear. By not relying on formulae on the sheet you can only use the Nomad solver. This cannot run a macro yet, but earlier this week we added this as a feature request in our tracker. Check out the next version, or trigger a macro off changes in any of the decision variables. Hope this helps. Andrew
Thank you. Yes trigger a macro before making function evaluations. During iterations the optimizer may want to change multiple inputs and I would like to trigger a macro before getting outputs, but not trigger a macro when every single input changes.
I’m trying to install OpenSolver on OSX Yosemite (10.10.5) Excel 15.14, OpenSOlver 2.8.2 Advanced.
After I double-click OpenSolver.xlam It opens Excel, but:
– OpenSolver is under the ‘Add-Ins’, ribbon, but without any icons. It’s very difficult to navigate, as the buttons are very wide
– Going to Add-Ins->OpenSolver->About OpenSolver – after a few seconds (I”m trying to click on ‘Load OpenSolver when Excel Starts’), I get a spinning beach ball of death and Excel hangs. I have to force quit.
You are trying to run OpenSolver on a Mac on Excel 2016 (excel 15) which does not work, sorry. Microsoft have released a very crippled Excel… when they fix it up, things will hopefully start working. Andrew
I’m currently working on an optimization problem to determine optimal raw material procurement lot sizes for a food manufacturer. I wish to (just) use the OpenSolver add-in to solve the optimization problem. However, I noticed that OpenSolver (the default linear engine) does not like ’roundup(X;0)’ or ‘max(X;0)’ types of constraints since it will then tell me that the objective function is not linear. I wish to use fixed order batch sizes such that only integer purchasing sizes are generated that are divideable by the order batch size. Furthermore I also wish to incorporate shelf life constraints on the holding of inventory. Any idea how to solve these problems by just using OpenSolver, or should I use VBA or any other software package to deal with these specific constraints? I hope my case is clear.
You need to use integer variables that specify the number of batches to make. Also delete any rounding or max statements. Google modelling of max(). Good luck… sounds like a great project. Andrew
One more question…when Solver runs out of time when solving an integer programming problem, it is possible to ask Solver to continue solving the integer programming problem using additional time. Can this be done with OpenSolver? If so, how?
Thanks, as always, for your quick and useful responses…
I do not see my recent question posted, so I am writing to ask it again. When OpenSolver runs out of time solving a large integer programming problem, is there some way to instruct OpenSolver to display in the Worksheet the best integer feasible solution it found to date?
When OpenSolver runs out of time solving a large integer programming problem, is there anyway to get OpenSolver to return the best feasible integer solution it found?
27 I 16 09:31:59 [OpenSolver.xlam] SolverFileNL.NumberOfOperands: Line 8138
27 I 16 09:31:59 [OpenSolver.xlam] SolverFileNL.PopOperator: Line 8210
27 I 16 09:31:59 [OpenSolver.xlam] SolverFileNL.ConvertFormulaToExpressionTree: Line 8106
27 I 16 09:31:59 [OpenSolver.xlam] SolverFileNL.ProcessSingleFormula: Line 7796
27 I 16 09:31:59 [OpenSolver.xlam] SolverFileNL.ProcessFormulae: Line 7793
27 I 16 09:31:59 [OpenSolver.xlam] SolverFileNL.SolveModelParsed_NL: Line 7468
27 I 16 09:31:59 [OpenSolver.xlam] SolverCommon.WriteModelFile: Line 0
27 I 16 09:31:59 [OpenSolver.xlam] SolverCommon.SolveModel: Line 0
27 I 16 09:31:59 [OpenSolver.xlam] OpenSolverAPI.RunOpenSolver: Line 0
Error -2147220502: Unknown function countif
Please let us know about this at opensolver.org so we can fix it.
Solver: Bonmin
Version 2.7.1 (2015.06.28) running on 64-bit Windows 6.2 with VBA7 in 32-bit Excel 14.0
CBC v2.9.4 (64-bit) at “C:\Users\schusterz\AppData\Roaming\Microsoft\AddIns\Solvers\win64\cbc.exe”
Gurobi v6.5.0 (64-bit) at “D:\gurobi650\win64\bin\gurobi_cl.exe”
Bonmin v1.8.1 (64-bit) at “C:\Users\schusterz\AppData\Roaming\Microsoft\AddIns\Solvers\win64\bonmin.exe”
Couenne v0.5.3 (64-bit) at “C:\Users\schusterz\AppData\Roaming\Microsoft\AddIns\Solvers\win64\couenne.exe”
NOMAD v3.7.1 (32-bit) using OpenSolverNomad v1.2.2 at “C:\Users\schusterz\AppData\Roaming\Microsoft\AddIns\Solvers\win32\OpenSolverNomad.dll”
Thanks for the feedback. Sorry but we cannot handle countif in nonlinear models (except when using Nomad). Try a simpler linear model if possible. Andrew
Hi, I am having a serious problem running my large size model with Open Solver.
This is the output after 2 hours waiting for the result.
Error 6:Overflow
Solver: CBC
Version 2.7.1 (2015.06.28) running on 64-bit windows 6.1 with VBA7 in 64-bit excel 15.0
CBC v2.9.4(2015.06.28) running on 64-bit Windows 6.1 with VBA7 in 64 bit Excel 15.0.
Can someone help me with the problem?
Your response is very much appreciated.
Thanks,
Pooya
Sorry about that. Please try the 2.8.2 pre-release version. If this still gives you trouble, then please let us know. Andrew
Hello
I want to solve a non linear model and my sheet contains the formula “column”.
I receive the follow error message:
OpenSolver 2.7.1 encountered an error:
Unknown function column
Please let us know about this at opensolver.org so we can fix it.
Any solution?
We cannot handle that unless you are using the Nomad solver. Best if you can remove the column formula. Andrew
I removed the “Column” formula and I set as solver engine Nomad.
Now I have this error message:
OpenSolver 2.7.1 encountered an error:
NOMAD was unable to open the specified log file for writing:
E:\DOCUME~1\KONSTA~1\LOCALS~1\Temp\log1.tmp
Also the info from the error file is this:
03 Αpr 16 15:44:14 [OpenSolver.xlam] CSolverNomad.Solve: Line 0
03 Αpr 16 15:44:14 [OpenSolver.xlam] SolverCommon.SolveModel: Line 0
03 Αpr 16 15:44:14 [OpenSolver.xlam] OpenSolverAPI.RunOpenSolver: Line 0
Error -2147220489: NOMAD was unable to open the specified log file for writing:
E:\DOCUME~1\KONSTA~1\LOCALS~1\Temp\log1.tmp
Solver: NOMAD
Version 2.7.1 (2015.06.28) running on 32-bit Windows 5.1 with VBA6 in 32-bit Excel 11.0
NOMAD v3.7.1 (32-bit) using OpenSolverNomad v1.2.2 at “C:\1\Solvers\win32\OpenSolverNomad.dll”
Please try restarting your PC… this file has been left open by a previous crash. Andrew
Hi,
I’ve spent weeks looking for a version of Solver that will work with Microsoft Access 2007 but without success. Has anyone ever come across a Solver addin for Access?
It won’t exist, as there is no “sheet” on which to build the model. Use SolverStudio with one of its modelling languages if you want a model that uses data from Access. Andrew
Something interesting for you.
As I loop opensolver (with Bonmin) over a similar problem, just with changing input values, the resolution time increases linearly with the number of iterations.
What takes less than 400 secs to solve at iteration 1 takes more than 1000 secs at the 100th. The RAM used by excel also increases a lot over time, although I reset the OpenSolver model at each iteration and the problem size remains the same.
It seems that the time spent with the message “OpenSolver: reading solution” just keeps on increasing, while building the .nl file and solving the problem itself takes a constant time.
That happens when I close the laptop lid and let the macro run.
However, when I actively look at the excel file where the solver is running, this problem does not happen: when “OpenSolver: reading solution” pops up, I just have to click the mouse somewhere on the worksheet where OpenSolver is running. Then the solution is immediately read and the macro goes to next iteration.
I work with Excel 2010 (64 bits) on Windows 64.
Can you please send us your code to email hidden; JavaScript is required? This looks like something we may be able to fix (or, it may be a quirk of VBA, probably around garbage collection which we don’t control). Thanks for letting us know about it – we’re definitely keen to have a look into what’s going on. Andrew
So again, this product is awesome. Thanks so much for the work.
One question: would it be possible to use parallelization for model (.nl file) creation?
What takes most time when solving nonlinear problems is to create the .nl file, more specifically writing linear constraints. This writing process time seems to grow quadratically with the number of variables.
Could that be parallelized using several cores/processors?
Cheers
Parallelisation using VBA (which is what we use) is very hard. Maybe one day using C#? Any volunteers? Andrew
Good, so I have implemented a fake parallelization – using multiple Excel instances to build the JBlocks in order to use the full processing capacity when working on NL problems.
In order to make it I had to make some changes to the variable type: collections have been changed to dictionaries.
In particular what causes the extreeeeeemely long processing time to build the JBlocks is to go through LinearConstraints which is a Collection of Dictionaries.
Just by changing this to a Dictionary of Dictionaries makes the whole For loop EXTREMELY faster.
I save about 90% of the time on large models!!!
So I recommend for a future release that you consider dropping the Collection objects and change them for Dictionaries, when they are used in For loops… Might have something to do with how Excel allocates memory or deals with different object types?
Once this is done you do not need multithreading any longer to save time – although I now have the option available…
Cheers
Thanks for your detailed analysis of our code, and well done on finding places we can make changes to speed up the code. We will be incorporating your suggestions in our next release. Thanks again… please send us more such feedback! Andrew
Hello, I am John and I write from Italy. I apologize for my English (I got help from Google Translate) and I hope I manage to make myself understood. I used opensolver 2.7.1 on an Excel spreadsheet that provides 5 variable cells with 2 type> variable = and <= for all cells (5). Opensolver starts and ends his work without generating any trouble but the result is not as expected. Iin the target cell indicated the maximum value but the result is not. In particular opensolver returns it, for all cells, the minimum value expected in the range. This happens with both NEOS using Bonmin and with NEOS using Couenne. Where am I wrong?
Thanks and congratulations for the excellent work.
This sounds like a mistake in your model. Please try to find a better solution manually. If you can then it might indicate a bug that we would like to know about. Andrew
In my workbook the objective (to be targeted) is calculated by calling a macro/subroutine. is it possible to use opensolver to solve the problem by asking the optimizer to call the macro everytime it tries to evaluate the objective? I cannot make a function to change the target cell automatically because I need to change many cells in the workbook and EXCEL does not allow function to change other cells in the workbook.
If you need a macro your problem is probably non linear. By not relying on formulae on the sheet you can only use the Nomad solver. This cannot run a macro yet, but earlier this week we added this as a feature request in our tracker. Check out the next version, or trigger a macro off changes in any of the decision variables. Hope this helps. Andrew
Thank you. Yes trigger a macro before making function evaluations. During iterations the optimizer may want to change multiple inputs and I would like to trigger a macro before getting outputs, but not trigger a macro when every single input changes.
Great; that was our thinking too. Andrew
Hi,
I’m trying to install OpenSolver on OSX Yosemite (10.10.5) Excel 15.14, OpenSOlver 2.8.2 Advanced.
After I double-click OpenSolver.xlam It opens Excel, but:
– OpenSolver is under the ‘Add-Ins’, ribbon, but without any icons. It’s very difficult to navigate, as the buttons are very wide
– Going to Add-Ins->OpenSolver->About OpenSolver – after a few seconds (I”m trying to click on ‘Load OpenSolver when Excel Starts’), I get a spinning beach ball of death and Excel hangs. I have to force quit.
The rest of it might work – I’ll let you know.
You are trying to run OpenSolver on a Mac on Excel 2016 (excel 15) which does not work, sorry. Microsoft have released a very crippled Excel… when they fix it up, things will hopefully start working. Andrew
Hello there,
I’m currently working on an optimization problem to determine optimal raw material procurement lot sizes for a food manufacturer. I wish to (just) use the OpenSolver add-in to solve the optimization problem. However, I noticed that OpenSolver (the default linear engine) does not like ’roundup(X;0)’ or ‘max(X;0)’ types of constraints since it will then tell me that the objective function is not linear. I wish to use fixed order batch sizes such that only integer purchasing sizes are generated that are divideable by the order batch size. Furthermore I also wish to incorporate shelf life constraints on the holding of inventory. Any idea how to solve these problems by just using OpenSolver, or should I use VBA or any other software package to deal with these specific constraints? I hope my case is clear.
Thanks.
You need to use integer variables that specify the number of batches to make. Also delete any rounding or max statements. Google modelling of max(). Good luck… sounds like a great project. Andrew
Andrew,
One more question…when Solver runs out of time when solving an integer programming problem, it is possible to ask Solver to continue solving the integer programming problem using additional time. Can this be done with OpenSolver? If so, how?
Thanks, as always, for your quick and useful responses…
Opensolver does not support that sorry because the solvers we use don’t. Andrew
Thanks for all your help! I am still very impressed with what you and your group have accomplished…
Thanks for the feedback. Andrew
Andrew,
I do not see my recent question posted, so I am writing to ask it again. When OpenSolver runs out of time solving a large integer programming problem, is there some way to instruct OpenSolver to display in the Worksheet the best integer feasible solution it found to date?
Thanks…
Danny
It will do so automatically (if it has found any solutions). Andrew
Andrew,
When OpenSolver runs out of time solving a large integer programming problem, is there anyway to get OpenSolver to return the best feasible integer solution it found?
Thanks for your help with this…
Hi, I would like to know which relation I should have as AddConstraint argument when I to define an inequality?
Please see the OpenSolverConstants file in the source code. (We will add this to the documentation sometime soon; sorry for not having it.) Andrew
27 I 16 09:31:59 [OpenSolver.xlam] SolverFileNL.NumberOfOperands: Line 8138
27 I 16 09:31:59 [OpenSolver.xlam] SolverFileNL.PopOperator: Line 8210
27 I 16 09:31:59 [OpenSolver.xlam] SolverFileNL.ConvertFormulaToExpressionTree: Line 8106
27 I 16 09:31:59 [OpenSolver.xlam] SolverFileNL.ProcessSingleFormula: Line 7796
27 I 16 09:31:59 [OpenSolver.xlam] SolverFileNL.ProcessFormulae: Line 7793
27 I 16 09:31:59 [OpenSolver.xlam] SolverFileNL.SolveModelParsed_NL: Line 7468
27 I 16 09:31:59 [OpenSolver.xlam] SolverCommon.WriteModelFile: Line 0
27 I 16 09:31:59 [OpenSolver.xlam] SolverCommon.SolveModel: Line 0
27 I 16 09:31:59 [OpenSolver.xlam] OpenSolverAPI.RunOpenSolver: Line 0
Error -2147220502: Unknown function countif
Please let us know about this at opensolver.org so we can fix it.
Solver: Bonmin
Version 2.7.1 (2015.06.28) running on 64-bit Windows 6.2 with VBA7 in 32-bit Excel 14.0
CBC v2.9.4 (64-bit) at “C:\Users\schusterz\AppData\Roaming\Microsoft\AddIns\Solvers\win64\cbc.exe”
Gurobi v6.5.0 (64-bit) at “D:\gurobi650\win64\bin\gurobi_cl.exe”
Bonmin v1.8.1 (64-bit) at “C:\Users\schusterz\AppData\Roaming\Microsoft\AddIns\Solvers\win64\bonmin.exe”
Couenne v0.5.3 (64-bit) at “C:\Users\schusterz\AppData\Roaming\Microsoft\AddIns\Solvers\win64\couenne.exe”
NOMAD v3.7.1 (32-bit) using OpenSolverNomad v1.2.2 at “C:\Users\schusterz\AppData\Roaming\Microsoft\AddIns\Solvers\win32\OpenSolverNomad.dll”
Thanks for the feedback. Sorry but we cannot handle countif in nonlinear models (except when using Nomad). Try a simpler linear model if possible. Andrew
This work cites opensolver: http://www.hindawi.com/journals/aor/2016/7597062/