Do I need to install official solver add-in to use OpenSolver?
I’m asking because one of my developed macro which use OpenSolver occur a problem of missing solver reference and can not be executed.
Thank you.
Thank you for this amazing tool.
However, there’s a problem occur while using excel 2007 but it works perfectly in excel 2010, please refer to the hyperlink below
I see you are getting an error “bad file name or number (at line 102320) (at line 19540)”. Can you please tell me the full path of the directory which contains OpenSolver? Thanks, Andrew
Julian: Thanks for investigating this “bad file name or number” error, and finding that it was fixed by ensuring OpenSolver is in a directory with an English name (ie no Chinese characters in the full path to the OpenSolver file). I’m sure others will appreciate knowing this. Andrew
I have a compatibility issue between OpenSolver 2.1 and Excel 2007 which doesn’t exist with Excel 2010.
When I run my model using Excel 2010, I have no issues (the solution is created in Excel 2010), however when I run the same model using the same file using Excel 2007 on the same PC, I get the following message and a far from optimal solution).
As our organisation uses Excel 2007, I’d like to find a solution other than upgrading Excel.
Warning/Error Message: ”
OpenSolver could not find an optimal solution, and reported:
No Feasible solution
The solution generated has been loaded into the spreadsheet.
CBC Solver reported: Infeasible – object value -462.19378370
“/b>
OpenSolver should be fine with integer and binary variables, as long as there are no vlookup(), if() or similar functions in your model that depend on the decision variables. Please feel free to email me your spreadsheet if you’d like me to look at it: a.mason at auckland.ac.nz. Cheers, Andrew
The tool is already great and I thank the developer to have it available to everyone for free, but if I pick one major feature to see in the tool (if I am not missing anything): It will be great if we can terminate cbc solver prematurely through an excel dialog box. For example, the solve button brings up a dialog box that allows the interruption (and potentially restart if cbc allows it).
Is it possible to model a semi-continuous variable with OpenSolver?
I’m trying to do something like x = {y OR 0}, where x and y are both variables.
My first attempt was introducing a binary flag variable like this:
x = y * flag
but that doesn’t work because then we’ve got a non-linear constraint (two vars multiplied together).
I suggest you check out an integer programming textbook. The form you will need will depend on how this interacts with the objective function. There are some good examples at this INFORMS Transactions on Education article. Hope this helps, Andrew
Second, I thought you’d like to know that on the ‘Using OpenSolver’ page (cannot enter Comment there), the link to the online textbook by Robert Vanderbei now shows the following:
> Linear Programming: Foundations and Extensions
> At the publisher’s request, the online version is currently unavailable.
Finally, I’m running OpenSolver for Excel in the Excel 2013 Preview version with absolutely no issues so far … so it looks like the ‘forward compatibility’ is working!
Thanks for the link info; the other information on thatr page is still useful, so I will leave it up. I was pleased to read your comments that OpenSolver works in Excel 2013. Much appreciated. Cheers, Andrew
My team at work are now using OpenSolver to optimize staffing and production. The runtime for our model (build + write to disk) is now at about 45min with 7700ish variables. We’d like to use the Quick Solve to cut that down but haven’t been able to find any documentation for how to set it up for our model. Is there some that we’ve overlooked? Thank you!
Sorry, there’s no documentation apart from what’s on the web site. For big models, SolverStudio (http://solverstudio.org) will dramatically reduce your run times as it allows you to build the model in a proper modelling language such as AMPL or GMPL or PULP. Your 45 minutes will then reduce to 1 or 2 minutes. Hope this helps, Andrew
We have created an Excel-VBA based application that uses OpenSolver as backend to model and solve optimization problems. Using a brilliant front end, our application can be used to model, geographically visualize data on builit-in maps, select solving options and then solve different types of Supply chain network optimization models. OpenSolver is one of the backend apps used by this project. For visualization and other purposes, we use Google and Yahoo APIs and Maps. The front end of this application is developed from scratch which takes visualization of model and solved results to a whole new level. We would like to submit this as a candidate for the Innovation in Analytics Informs competition. Would you be kind enough to guide me towards some sample submission abstracts which would help me to write our case neatly?
Thank you for the good work to develop OpenSolver and make it free for users to download. Opensolver has been very helpful and I just would like to let you know that at least one student from UC Berkeley has benefitted from your development.
Andrew,
I would like to report an error when running OpenSolver in Excel (Visual Basic).
In my application, OpenSolver is called in a loop that has to be executed a number of times. When running the application, after 3 or 4 loops depending upon the case, a message appears:
Model Solve Failed: Unable to delete file C:Documents and SettingsDanielLocal filesTempmodel.lp.
After clicking the error message, the calculation of the current step is skipped and the program continues with the calculation of the next 3 or 4 loops and then the error message appears again.
The error appreared in the earlier version 1.4 and still appears now with the latest version 1.9.
Kind regards,
Daniel.
Daniel. Thanks for the error report. I am away for Christmas, but will have a look when I get back in the new year. Thanks for trying this with the different versions. Did you try anything before version 1.4, and if so, did it also cause an error? Please note that there is better error handling in the latest version in that errors are thrown right back to the caller, so you can trap this error yourself in your VBA code and try again. See the page on this site under Using OpenSolver for more details. Merry Christmas, Andrew
Daniel: If you search the code, you will find a “Close 1” which should probably be “Close #1”. However, there is another “Close #1” that also should close this file, so I’m not sure what’s going on. Maybe you could email me a spreadsheet I could look at to: a dot mason at auckland dot ac dot nz. Thanks. Andrew
Andrew,I did not try earlier versions than the 1.4.
The error occurs in Excel 2003 and in Excel 2007.
Without the proposed correction and with “ScreenUpdating false” it occurs every 3 to 4 loops; with the correction (indeed in de code there was one statement “Close 1” instead of
“Close #1”) the error occurs every 8 to 9 loops. With “ScreenUpdating True” it occurs every second loop.
I am using another (commercial) tool in parallel; the Simplex method does not work but more sophisticated methods do the job. If a calculation is executed by OpenSolver, then the result is correct. Don’t let your holidays be spoiled by this; Happy New Year!
Daniel.
Daniel: I have now tried to reproduce the problem; it doesn’t fail for me even after trying 1000 solves in succession. Could you perhaps send me your example? a dot mason at auckland dot ac dot nz. Thanks, Andrew
Hallo,
I have encountered error in OpenSolver version 1.7 , when I was trying to pass more parameters to CBC through OpenSolver_CBCParameters range. The error was caused by lines
ParamName = CBCParametersRange.Cells(i, 1).Trim
and
ParamName = CBCParametersRange.Cells(i, 1).Trim
This modification works for me:
ParamName = Trim(CBCParametersRange.Cells(i, 1))
ParamValue = Trim(CBCParametersRange.Cells(i, 2))
I’m using Czech version of MS Excel 2007
Best regards,
Karel Charvát
Karel: Thanks for your bug report and fix; bugs like this are what happens if you program in C#, Python and VBA! Getting a software fix is great – and what I love about Open Source! I will make sure this gets incorporated into the next release. By the way, what setting are you changing? You are probably the first person to use this – I have never needed it. Andrew
Andrew: I’ was only doing some experiments with parameters settings. 🙂 It was curiosity more than real necessity. As I know, CBC usually does well with its default settings. Only parameter, I sometimes change, when I work with CBC, is integer optimality tolerance, and in OpenSolver case, it is possible to change this parameter through the settings form.
I’m also sometimes using CBC with PuLP, and I think, It’s good open source solver. It was great Idea to use it with VBA add-in and you have done really good work 🙂
I will probably do some experiments with your OpenSolver studio, where you putting together OpenSolver and PuLP 🙂
Btw: I’m Ph.d students of program Econometrics and Operations research at University of Economics, Prague. As Ph.D students, I’m teaching practical lectures of Software for Mathematical Modelling.
In these lectures, students are using Excel Solver, LINGO and MPL with CPLEX. I’m trying to find some good tools, which may help to students, when they graduate and start work as analysts.
In most cases their company wouldn’t like to spend 10000$ on professional modeling tools with professional solvers like CPLEX or GUROBI.
I think, OpenSolver is great tool for models with not very complicated structure, if the number of variables and number of constraints exceed limits of standard excel solver, or if the computation time in Excel solver is too long.
Karel Charvát
I installed OpenSolver in Excel 2010. But when I click on the Model button, I get an error “Run-time error ‘459’: Object or class does not support the set of events”. Clicking on the “Debug” button then takes me to
Sub OpenSolver_ModelClick(Optional Control)
‘frmAutoModel.Show
‘frmAutoModel2.Show
If Not CheckWorkSheetAvailable Then Exit Sub
=> frmModel.Show ‘ Where the error occurred)
DoEvents
End Sub
Any idea what I might be missing? The addin is installed in “Profiles[user]Application DataMicrosoftAddInsOpenSolver17”
Thanks for the detailed error message. I have just tried this in Excel 2010. (I normally test in 2007.) Clicking the Model button works for me (at least on an empty spreadsheet, and then after creating a model, closing, and clicking Model again). Could you please try this in your version? If you still have a problem, I will send you a special debugging version to help track the problem. Thanks, Andrew. PS: It could possibly be a problem with the RefEdit control… please try the Excel Repair link given at http://opensolver.org/help
* Clicking the Model button in a new empty spreadsheet produced the same error
* Clicking on “Set Quick Solve Parameters” brings up an input box (“Please select the ‘parameter’ cells …)
* Clicking on the “Option…” under Model dropdown successfully brings up the “Open Solver – Solve Options” dialog.
Since “Set Quick Solve Parameters” dialog works, do you still think I should use the Temp File Deleter? Many thanks.
I would guess that your ListBox control is corrupted or missing, so please try to repair your Excel, and then, launch the Visual Basic editor (Alt-F11), and check Tools… References… for any ‘missing’ items. Good luck, Andrew
Hi, I noticed that it is required to ‘turn on both the “Assume Linear Model” and “Assume Non Negative” Solver options’. But I tried to run a model with the value of a variable that could be negative without turning on the non-negative option, and it worked well. Just want to know if this is also a right way. Thanks.
Sorry, that is a mistake in our documentation. OpenSolver no longer needs “assume non negative” to be turned on; we have updated the web pages. Thanks for alerting us to this. Andrew
Do I need to install official solver add-in to use OpenSolver?
I’m asking because one of my developed macro which use OpenSolver occur a problem of missing solver reference and can not be executed.
Thank you.
Thank you for this amazing tool.
However, there’s a problem occur while using excel 2007 but it works perfectly in excel 2010, please refer to the hyperlink below
http://ppt.cc/jGx1
Greatly appreciate your awesome work!
Regards,
Julian Chu
I see you are getting an error “bad file name or number (at line 102320) (at line 19540)”. Can you please tell me the full path of the directory which contains OpenSolver? Thanks, Andrew
Spreadsheet has been sent, thanks.
Julian: Thanks for investigating this “bad file name or number” error, and finding that it was fixed by ensuring OpenSolver is in a directory with an English name (ie no Chinese characters in the full path to the OpenSolver file). I’m sure others will appreciate knowing this. Andrew
Hi, Did you solver your problem? I can not do this. Thank you very much!
I have a compatibility issue between OpenSolver 2.1 and Excel 2007 which doesn’t exist with Excel 2010.
When I run my model using Excel 2010, I have no issues (the solution is created in Excel 2010), however when I run the same model using the same file using Excel 2007 on the same PC, I get the following message and a far from optimal solution).
As our organisation uses Excel 2007, I’d like to find a solution other than upgrading Excel.
Warning/Error Message:
”
OpenSolver could not find an optimal solution, and reported:
No Feasible solution
The solution generated has been loaded into the spreadsheet.
CBC Solver reported: Infeasible – object value -462.19378370
“/b>
Is this because your model is perhaps non-linear? The results in such cases will be rather random! Andrew
The model isn’t non-linear but does involve one discrete variable. By removing this from the solver, the solver works.
OpenSolver should be fine with integer and binary variables, as long as there are no vlookup(), if() or similar functions in your model that depend on the decision variables. Please feel free to email me your spreadsheet if you’d like me to look at it: a.mason at auckland.ac.nz. Cheers, Andrew
The tool is already great and I thank the developer to have it available to everyone for free, but if I pick one major feature to see in the tool (if I am not missing anything): It will be great if we can terminate cbc solver prematurely through an excel dialog box. For example, the solve button brings up a dialog box that allows the interruption (and potentially restart if cbc allows it).
Is it possible to model a semi-continuous variable with OpenSolver?
I’m trying to do something like x = {y OR 0}, where x and y are both variables.
My first attempt was introducing a binary flag variable like this:
x = y * flag
but that doesn’t work because then we’ve got a non-linear constraint (two vars multiplied together).
Any suggestions? thanks!
I suggest you check out an integer programming textbook. The form you will need will depend on how this interacts with the objective function. There are some good examples at this INFORMS Transactions on Education article. Hope this helps, Andrew
Is there a way to localize (translate) OpenSolver?
OpenSolver will need to be improved to allow localisation. We would welcome contributions to this Andrew
First, thank you for a very nice tool!
Second, I thought you’d like to know that on the ‘Using OpenSolver’ page (cannot enter Comment there), the link to the online textbook by Robert Vanderbei now shows the following:
> Linear Programming: Foundations and Extensions
> At the publisher’s request, the online version is currently unavailable.
Finally, I’m running OpenSolver for Excel in the Excel 2013 Preview version with absolutely no issues so far … so it looks like the ‘forward compatibility’ is working!
Thanks for the link info; the other information on thatr page is still useful, so I will leave it up. I was pleased to read your comments that OpenSolver works in Excel 2013. Much appreciated. Cheers, Andrew
Hello!
I love OpenSolver. Thank y’all so much 🙂
My team at work are now using OpenSolver to optimize staffing and production. The runtime for our model (build + write to disk) is now at about 45min with 7700ish variables. We’d like to use the Quick Solve to cut that down but haven’t been able to find any documentation for how to set it up for our model. Is there some that we’ve overlooked? Thank you!
Sorry, there’s no documentation apart from what’s on the web site. For big models, SolverStudio (http://solverstudio.org) will dramatically reduce your run times as it allows you to build the model in a proper modelling language such as AMPL or GMPL or PULP. Your 45 minutes will then reduce to 1 or 2 minutes. Hope this helps, Andrew
I’m getting “model cannot be solved as it has not yet been built. What am I missing?
Dear Andrew,
We have created an Excel-VBA based application that uses OpenSolver as backend to model and solve optimization problems. Using a brilliant front end, our application can be used to model, geographically visualize data on builit-in maps, select solving options and then solve different types of Supply chain network optimization models. OpenSolver is one of the backend apps used by this project. For visualization and other purposes, we use Google and Yahoo APIs and Maps. The front end of this application is developed from scratch which takes visualization of model and solved results to a whole new level. We would like to submit this as a candidate for the Innovation in Analytics Informs competition. Would you be kind enough to guide me towards some sample submission abstracts which would help me to write our case neatly?
Dear OpenSolver Developers:
Thank you for the good work to develop OpenSolver and make it free for users to download. Opensolver has been very helpful and I just would like to let you know that at least one student from UC Berkeley has benefitted from your development.
Thank you!
I’m pleased that it has been useful. If you have a chance, we’d love to hear more about what you were doing with OpenSolver. Cheers, Andrew
Andrew,
I would like to report an error when running OpenSolver in Excel (Visual Basic).
In my application, OpenSolver is called in a loop that has to be executed a number of times. When running the application, after 3 or 4 loops depending upon the case, a message appears:
Model Solve Failed: Unable to delete file C:Documents and SettingsDanielLocal filesTempmodel.lp.
After clicking the error message, the calculation of the current step is skipped and the program continues with the calculation of the next 3 or 4 loops and then the error message appears again.
The error appreared in the earlier version 1.4 and still appears now with the latest version 1.9.
Kind regards,
Daniel.
Daniel. Thanks for the error report. I am away for Christmas, but will have a look when I get back in the new year. Thanks for trying this with the different versions. Did you try anything before version 1.4, and if so, did it also cause an error? Please note that there is better error handling in the latest version in that errors are thrown right back to the caller, so you can trap this error yourself in your VBA code and try again. See the page on this site under Using OpenSolver for more details. Merry Christmas, Andrew
Daniel: If you search the code, you will find a “Close 1” which should probably be “Close #1”. However, there is another “Close #1” that also should close this file, so I’m not sure what’s going on. Maybe you could email me a spreadsheet I could look at to: a dot mason at auckland dot ac dot nz. Thanks. Andrew
Andrew,I did not try earlier versions than the 1.4.
The error occurs in Excel 2003 and in Excel 2007.
Without the proposed correction and with “ScreenUpdating false” it occurs every 3 to 4 loops; with the correction (indeed in de code there was one statement “Close 1” instead of
“Close #1”) the error occurs every 8 to 9 loops. With “ScreenUpdating True” it occurs every second loop.
I am using another (commercial) tool in parallel; the Simplex method does not work but more sophisticated methods do the job. If a calculation is executed by OpenSolver, then the result is correct. Don’t let your holidays be spoiled by this; Happy New Year!
Daniel.
Daniel: I have now tried to reproduce the problem; it doesn’t fail for me even after trying 1000 solves in succession. Could you perhaps send me your example? a dot mason at auckland dot ac dot nz. Thanks, Andrew
Hallo,
I have encountered error in OpenSolver version 1.7 , when I was trying to pass more parameters to CBC through OpenSolver_CBCParameters range. The error was caused by lines
ParamName = CBCParametersRange.Cells(i, 1).Trim
and
ParamName = CBCParametersRange.Cells(i, 1).Trim
This modification works for me:
ParamName = Trim(CBCParametersRange.Cells(i, 1))
ParamValue = Trim(CBCParametersRange.Cells(i, 2))
I’m using Czech version of MS Excel 2007
Best regards,
Karel Charvát
Karel: Thanks for your bug report and fix; bugs like this are what happens if you program in C#, Python and VBA! Getting a software fix is great – and what I love about Open Source! I will make sure this gets incorporated into the next release. By the way, what setting are you changing? You are probably the first person to use this – I have never needed it. Andrew
Andrew: I’ was only doing some experiments with parameters settings. 🙂 It was curiosity more than real necessity. As I know, CBC usually does well with its default settings. Only parameter, I sometimes change, when I work with CBC, is integer optimality tolerance, and in OpenSolver case, it is possible to change this parameter through the settings form.
I’m also sometimes using CBC with PuLP, and I think, It’s good open source solver. It was great Idea to use it with VBA add-in and you have done really good work 🙂
I will probably do some experiments with your OpenSolver studio, where you putting together OpenSolver and PuLP 🙂
Btw: I’m Ph.d students of program Econometrics and Operations research at University of Economics, Prague. As Ph.D students, I’m teaching practical lectures of Software for Mathematical Modelling.
In these lectures, students are using Excel Solver, LINGO and MPL with CPLEX. I’m trying to find some good tools, which may help to students, when they graduate and start work as analysts.
In most cases their company wouldn’t like to spend 10000$ on professional modeling tools with professional solvers like CPLEX or GUROBI.
I think, OpenSolver is great tool for models with not very complicated structure, if the number of variables and number of constraints exceed limits of standard excel solver, or if the computation time in Excel solver is too long.
Karel Charvát
I installed OpenSolver in Excel 2010. But when I click on the Model button, I get an error “Run-time error ‘459’: Object or class does not support the set of events”. Clicking on the “Debug” button then takes me to
Sub OpenSolver_ModelClick(Optional Control)
‘frmAutoModel.Show
‘frmAutoModel2.Show
If Not CheckWorkSheetAvailable Then Exit Sub
=> frmModel.Show ‘ Where the error occurred)
DoEvents
End Sub
Any idea what I might be missing? The addin is installed in “Profiles[user]Application DataMicrosoftAddInsOpenSolver17”
Many thanks.
Thanks for the detailed error message. I have just tried this in Excel 2010. (I normally test in 2007.) Clicking the Model button works for me (at least on an empty spreadsheet, and then after creating a model, closing, and clicking Model again). Could you please try this in your version? If you still have a problem, I will send you a special debugging version to help track the problem. Thanks, Andrew. PS: It could possibly be a problem with the RefEdit control… please try the Excel Repair link given at http://opensolver.org/help
Andrew,
Thank you for responding. I tried the following:
* Clicking the Model button in a new empty spreadsheet produced the same error
* Clicking on “Set Quick Solve Parameters” brings up an input box (“Please select the ‘parameter’ cells …)
* Clicking on the “Option…” under Model dropdown successfully brings up the “Open Solver – Solve Options” dialog.
Since “Set Quick Solve Parameters” dialog works, do you still think I should use the Temp File Deleter? Many thanks.
I went ahead and ran the Temp File Deleter. But that did not help either.
I would guess that your ListBox control is corrupted or missing, so please try to repair your Excel, and then, launch the Visual Basic editor (Alt-F11), and check Tools… References… for any ‘missing’ items. Good luck, Andrew
Can you also please try “Set Quick Solve Parameters” under the OpenSolver menu; this will test the RefEdit on a simple form. Thanks, Andrew
Thanks for letting me know that repairing Excel fixed your problem, as described at http://www.sobolsoft.com/question/#Run-time_error_459:_Object_or_class_does_not_support_the_set_of_events. Cheers,
Andrew
Hi, I noticed that it is required to ‘turn on both the “Assume Linear Model” and “Assume Non Negative” Solver options’. But I tried to run a model with the value of a variable that could be negative without turning on the non-negative option, and it worked well. Just want to know if this is also a right way. Thanks.
Sorry, that is a mistake in our documentation. OpenSolver no longer needs “assume non negative” to be turned on; we have updated the web pages. Thanks for alerting us to this. Andrew