You mention that for larger problem it’s better to use SolverStudio as it’s faster. How large scale is called “large” generally? 10000 desicion variables?
I’d consider 10000 large, but if OpenSolver is fast enough, I would not change. it really depends on the complexity (and hence calculation speed) of your sheet. Andrew
Hi Andrew,
I been using the product (and it’s been great, btw) for a couple of months now. Have had no issues until I set up a recent problem and received the following message “OpenSolver encountered an error: Type mismatch (at line 9460) (at line 19530) Source – OpenSolver, ErrNumber=13”. I can’t find anything wrong with the way I’ve set up the problem. I am using VBA, but have used it previously with success.
Any insight is greatly appreciated.
Thanks,
Mike
I would like to thank you for publishing the plugin! It realy helped my research and showed that bigger problems could be solved by excel as well! Currently, the only question I have is about the algorythm which is being used during optimization. Do you use simplex or any kind of those algorythms?
Very nice, easy-to-use and impressive solver, thanks.
The only thing I can’t work out is how to set the QuickSolve parameter range using only VBA code. Which would be nice if possible.
Regards / Sigurd
Thank You!!!! Was struggling mightily with Excel version of Solver on a fantasy football problem- couldn’t come up with logical solution to problem of setting an optimum lineup with budget constraints and expected performance. Solved immediately with Open solver!! Made my Saturday!!
re Fantasy Premier League. How do you overcome the need to capture constraints (number in each position and max number per team)? Countifs is the obvious function. Do you have to add 20 team columns to count these and 4 position columns?
Hello and thank you so much for developing such an awesome piece of software!
I am hoping that you can help me with an issue I am having as I have exhausted my efforts in trying to get it resolved. I build process models for industrial facilities and typically analyze a year’s worth of hourly average data to determine lost opportunity pertaining to energy. This equates to almost 9000 rows of data and will take upwards of 12+ hours to run using the EXCEL solver. I am happy to say that OpenSolver cuts this computational time by around 30%, depending on the complexity of the model. AWESOME!
I use VB code to make calls to OpenSolver and automate the hour-by-hour analysis. However, the issue I am having is that if OpenSolver fails to converge, the routine stops running due to an Error/Message window that pops up asking the user for input. EXCEL solver does not do that and will run through all rows without stopping. I flag non-convergence etc., using VB code but as I mentioned earlier, it takes A LOT longer to complete an analysis with the EXCEL solver.
That said, I am actually not gaining an advantage using OpenSolver if I have to physically sit at my laptop and wait for one of these non-convergence messages to pop up so that I can answer how to handle it. Is there any way to:
1. Turn off the messaging and let me handle non-convergence situations like I normally would with EXCEL solver?
2. Use VB code in my routine that will allow me to move past the issue?
I have tried the 2nd option and cannot resolve the issue. I am sending this message as maybe someone else has encountered this in the past and there is a resolution or maybe it is new and your team can offer a solution that will allow me to continue working with and enjoying the benefits of OpenSolver.
I appreciate any insight you might have and look forward to hearing back.
THanks for your question, and positive OpenSolver comments. Are you calling OpenSolver using your own version of the RunOpenSolver code at http://opensolver.org/using-opensolver/#vba ? This is intended to give you full control, with no dialogs if that’s your preference. If you are using this, and are still getting dialogs, then I have a bug to fix! Please let me know, Andrew
First of all: thanks for a great tool! I’m currently using OpenSolver within a a larger model to solve a relatively small (a few hundred variables) LP multiple (a few thousand) times with different parameter settings. The quick solve function works perfectly well, however, the initialization takes ages to complete. This seems to be due to the “Application.CalculateFullRebuild” call OpenSolver makes when extracting the model. In my particular case, that triggers recalculation of about a gazillion complex array formulas on a hundred different sheets, which are not at all linked to the optimization model. I changed the call to a simple “Application.Calculate”, which solved the problem for me. Now I’m wondering, though, what is the reason you are using the CalculateFullRebuild call? Am I missing something here?
Patrick, Thanks for your feedback and for diving so deeply into the OpenSolver code. We have experimented with various re-calculation options, partly because of errors we sometimes se where Excel fails to recalculate. However, we should probably now change back to the simple Application.Calculate. I will add this change to our next release. Thanks, Andrew
Just for additional information: I’m also with a relatively simple model that has a lot of huge array formulas that takes some time to build (about 3 minutes in a core i5 – 1300 vars, 2681 rows) and I’ve tried to change all the ‘Application.CalculateFullRebuild’ to ‘Application.Calculate’ and also to just ‘calculate’. All the calculation methods worked (Excel 2013) but I have not noticed any significant difference among them.
One thing I did to disable the formulas that are not linked to the model was to use an if() function before the array formula which turns on and off the formulas, all the cells are linked to a single cell with the value TRUE or FALSE (My_Cell_With_T_or_F), such as:
=IF( My_Cell_With_T_or_F , ‘ArrayFormula’ , 0 )
When excel calculates the workbook the array formulas are not trigged because the if() is the first to be evaluated and returns 0 instead of calculate the array formula.
Once again thanks for the awesome tool, it is working really well for me!
Hi to everybody. For first thank you very much for your tool; it very very usefull and userfriendly.
I have a problem and I don’t know how to solve it; I’m not a good programmer and i need a help.
I have to create a Macro with the OpenSolver to scheduling, for some interation, my mathematics model with different input data iteration by iteration. I don’t know how to “integrate” the solver in my macro and how it solve for each interation the problem.
I hope to help me.
Hi, first of all I would like to say – great job!!!
Question: is there a way to write constraints faster? for example if i have a row of numbers that I want them to be larger than another row (every number in row a corresponds to a specific number in row b) – can I do it with just one constraint somehow?
Yes, it works just as the excel solver, for example if you have a range of ten cells in the column A that must be larger or equal to column B just use:
$A$1:$A$10 >= $B$1:$B$10
Both ranges must have the same size (same number of rows and columns)
I have set up a matrix and solved my model successfully with regular opensolve. But when I initialize and run quicksolve (in vba or with the GUI), a funny thing happens. If I initialize, then run quicksolve, everything is fine and the model solves as expected. However, when I run quicksolve a second time immediateily after running quicksolve the first time (having changed only the coefficients in the objective function), the model complains of non-linearities and sets all my x’s equal to zero. If I run it a third time, the model solves with no problem.
Any idea what could be going on here? I would like to rapid-fire solve my model in vba, but can’t due to this issue.
Sorry, but Quick Solve cannot handle changes in the objective function coefficients. It sounds as though that would be a useful feature! I will add it to my TODO list. Andrew
Hello:
A just downloaded Open Solver. I want to use it to run some moderately large models, developped in What’s Best to be used for student projects. (some 2000 vars and 1000 constraints) For better organization, each model is spread over several interelated sheets (WB allows that). Before converting WB to OS I would like to know if this spreading is supported in Open Model. Is it?
Thanks a lot
OpenSolver is great, thank you! I’m trying to call SolverDelete to make adjustments to the model in VBA, however I just get a “Function Not Defined” error. I can call RunOpenSolver just fine, but I can’t seem to call the other functions. I feel like it’s something simple…Any advice?
OpenSolver does not have any such functions. But the model is stored using the usual defined names, so you can still use the Solver functions to manipulate the model. Cheers, Andrew
Thanks for OpenSolver – it’s really great. But after some time of using I have a problem sometimes. It looks like:
OpenSolver: Calculation Error Occured…
Warning: The initial worksheet calculation did not complete, and so the model not be generated correctly. Would you like to try again?
Some other guys haven’t this problem on the same data – may it be because of my 2007 Office instead their 2010? Or the reason is the different?
We think this is an Excel issue. We first noticed on a 70,000 variable model where Excel would report the calculation had not completed. OpenSolver checks for this, and re-tries several times. If it still fails, you get the error message you report. Do you have a big model? If so, I’d suggest using our free companion product SolverStudio (www.solverstudio.org). Hope this helps, Andrew
is there a limit of decision variables that can be handled in the Open Solver?
Do you think a problem with 25’000 decision variables could be solved in an “appropriate” time?
The only limit is memory and time; it depends on your problem as to how hard it will be to solve. However, for a problem of this size, I’d recommend http://SolverStudio.org Cheers, Andrew
Hi~ Thank you so much~
Got a question to ask.
You mention that for larger problem it’s better to use SolverStudio as it’s faster. How large scale is called “large” generally? 10000 desicion variables?
Regards,
Xue Yuan
I’d consider 10000 large, but if OpenSolver is fast enough, I would not change. it really depends on the complexity (and hence calculation speed) of your sheet. Andrew
Hi Andrew,
I been using the product (and it’s been great, btw) for a couple of months now. Have had no issues until I set up a recent problem and received the following message “OpenSolver encountered an error: Type mismatch (at line 9460) (at line 19530) Source – OpenSolver, ErrNumber=13”. I can’t find anything wrong with the way I’ve set up the problem. I am using VBA, but have used it previously with success.
Any insight is greatly appreciated.
Thanks,
Mike
Any chance you could email me the spreadsheet? a.mason at auckland dot ac dot nz Thanks, Andrew
Hey,
I would like to thank you for publishing the plugin! It realy helped my research and showed that bigger problems could be solved by excel as well! Currently, the only question I have is about the algorythm which is being used during optimization. Do you use simplex or any kind of those algorythms?
Thanks in advance 🙂 André
Very nice, easy-to-use and impressive solver, thanks.
The only thing I can’t work out is how to set the QuickSolve parameter range using only VBA code. Which would be nice if possible.
Regards / Sigurd
It is just a hidden named range. Not at my computer system sorry but a quick source code search for quicksolve should fine it for you. Andrew
The QuickSolve parameters are defined by the range OpenSolverModelParameters. You can set it in VAB by assigning your own range to it by:
Names.Add Name:=”OpenSolverModelParameters”, RefersTo:=MyParameterRange
Thank You!!!! Was struggling mightily with Excel version of Solver on a fantasy football problem- couldn’t come up with logical solution to problem of setting an optimum lineup with budget constraints and expected performance. Solved immediately with Open solver!! Made my Saturday!!
Mind sharing? I’m working on the same problem now.
re Fantasy Premier League. How do you overcome the need to capture constraints (number in each position and max number per team)? Countifs is the obvious function. Do you have to add 20 team columns to count these and 4 position columns?
Any examples?
What laptop would you recommend for large problem solving?
Thanks
A big multi-core one! However, for big problems, I strongly suggest you look at the free SolverStudio as it will be much faster. Andrew
Hello and thank you so much for developing such an awesome piece of software!
I am hoping that you can help me with an issue I am having as I have exhausted my efforts in trying to get it resolved. I build process models for industrial facilities and typically analyze a year’s worth of hourly average data to determine lost opportunity pertaining to energy. This equates to almost 9000 rows of data and will take upwards of 12+ hours to run using the EXCEL solver. I am happy to say that OpenSolver cuts this computational time by around 30%, depending on the complexity of the model. AWESOME!
I use VB code to make calls to OpenSolver and automate the hour-by-hour analysis. However, the issue I am having is that if OpenSolver fails to converge, the routine stops running due to an Error/Message window that pops up asking the user for input. EXCEL solver does not do that and will run through all rows without stopping. I flag non-convergence etc., using VB code but as I mentioned earlier, it takes A LOT longer to complete an analysis with the EXCEL solver.
That said, I am actually not gaining an advantage using OpenSolver if I have to physically sit at my laptop and wait for one of these non-convergence messages to pop up so that I can answer how to handle it. Is there any way to:
1. Turn off the messaging and let me handle non-convergence situations like I normally would with EXCEL solver?
2. Use VB code in my routine that will allow me to move past the issue?
I have tried the 2nd option and cannot resolve the issue. I am sending this message as maybe someone else has encountered this in the past and there is a resolution or maybe it is new and your team can offer a solution that will allow me to continue working with and enjoying the benefits of OpenSolver.
I appreciate any insight you might have and look forward to hearing back.
THanks for your question, and positive OpenSolver comments. Are you calling OpenSolver using your own version of the RunOpenSolver code at http://opensolver.org/using-opensolver/#vba ? This is intended to give you full control, with no dialogs if that’s your preference. If you are using this, and are still getting dialogs, then I have a bug to fix! Please let me know, Andrew
First of all: thanks for a great tool! I’m currently using OpenSolver within a a larger model to solve a relatively small (a few hundred variables) LP multiple (a few thousand) times with different parameter settings. The quick solve function works perfectly well, however, the initialization takes ages to complete. This seems to be due to the “Application.CalculateFullRebuild” call OpenSolver makes when extracting the model. In my particular case, that triggers recalculation of about a gazillion complex array formulas on a hundred different sheets, which are not at all linked to the optimization model. I changed the call to a simple “Application.Calculate”, which solved the problem for me. Now I’m wondering, though, what is the reason you are using the CalculateFullRebuild call? Am I missing something here?
Patrick, Thanks for your feedback and for diving so deeply into the OpenSolver code. We have experimented with various re-calculation options, partly because of errors we sometimes se where Excel fails to recalculate. However, we should probably now change back to the simple Application.Calculate. I will add this change to our next release. Thanks, Andrew
Just for additional information: I’m also with a relatively simple model that has a lot of huge array formulas that takes some time to build (about 3 minutes in a core i5 – 1300 vars, 2681 rows) and I’ve tried to change all the ‘Application.CalculateFullRebuild’ to ‘Application.Calculate’ and also to just ‘calculate’. All the calculation methods worked (Excel 2013) but I have not noticed any significant difference among them.
One thing I did to disable the formulas that are not linked to the model was to use an if() function before the array formula which turns on and off the formulas, all the cells are linked to a single cell with the value TRUE or FALSE (My_Cell_With_T_or_F), such as:
=IF( My_Cell_With_T_or_F , ‘ArrayFormula’ , 0 )
When excel calculates the workbook the array formulas are not trigged because the if() is the first to be evaluated and returns 0 instead of calculate the array formula.
Once again thanks for the awesome tool, it is working really well for me!
Hi to everybody. For first thank you very much for your tool; it very very usefull and userfriendly.
I have a problem and I don’t know how to solve it; I’m not a good programmer and i need a help.
I have to create a Macro with the OpenSolver to scheduling, for some interation, my mathematics model with different input data iteration by iteration. I don’t know how to “integrate” the solver in my macro and how it solve for each interation the problem.
I hope to help me.
Hi, first of all I would like to say – great job!!!
Question: is there a way to write constraints faster? for example if i have a row of numbers that I want them to be larger than another row (every number in row a corresponds to a specific number in row b) – can I do it with just one constraint somehow?
Yes, it works just as the excel solver, for example if you have a range of ten cells in the column A that must be larger or equal to column B just use:
$A$1:$A$10 >= $B$1:$B$10
Both ranges must have the same size (same number of rows and columns)
Hello,
thanks again for this great tool.
I have set up a matrix and solved my model successfully with regular opensolve. But when I initialize and run quicksolve (in vba or with the GUI), a funny thing happens. If I initialize, then run quicksolve, everything is fine and the model solves as expected. However, when I run quicksolve a second time immediateily after running quicksolve the first time (having changed only the coefficients in the objective function), the model complains of non-linearities and sets all my x’s equal to zero. If I run it a third time, the model solves with no problem.
Any idea what could be going on here? I would like to rapid-fire solve my model in vba, but can’t due to this issue.
I don’t have any variables in my matrix.
Any help would be appreciated. Thanks!
Stefan
Sorry, but Quick Solve cannot handle changes in the objective function coefficients. It sounds as though that would be a useful feature! I will add it to my TODO list. Andrew
Hello:
A just downloaded Open Solver. I want to use it to run some moderately large models, developped in What’s Best to be used for student projects. (some 2000 vars and 1000 constraints) For better organization, each model is spread over several interelated sheets (WB allows that). Before converting WB to OS I would like to know if this spreading is supported in Open Model. Is it?
Thanks a lot
OpenSolver is great, thank you! I’m trying to call SolverDelete to make adjustments to the model in VBA, however I just get a “Function Not Defined” error. I can call RunOpenSolver just fine, but I can’t seem to call the other functions. I feel like it’s something simple…Any advice?
Many thanks,
-JC
OpenSolver does not have any such functions. But the model is stored using the usual defined names, so you can still use the Solver functions to manipulate the model. Cheers, Andrew
Thanks for OpenSolver – it’s really great. But after some time of using I have a problem sometimes. It looks like:
OpenSolver: Calculation Error Occured…
Warning: The initial worksheet calculation did not complete, and so the model not be generated correctly. Would you like to try again?
Some other guys haven’t this problem on the same data – may it be because of my 2007 Office instead their 2010? Or the reason is the different?
We think this is an Excel issue. We first noticed on a 70,000 variable model where Excel would report the calculation had not completed. OpenSolver checks for this, and re-tries several times. If it still fails, you get the error message you report. Do you have a big model? If so, I’d suggest using our free companion product SolverStudio (www.solverstudio.org). Hope this helps, Andrew
Hello everybody,
is there a limit of decision variables that can be handled in the Open Solver?
Do you think a problem with 25’000 decision variables could be solved in an “appropriate” time?
Regards,
Robert
The only limit is memory and time; it depends on your problem as to how hard it will be to solve. However, for a problem of this size, I’d recommend http://SolverStudio.org Cheers, Andrew