What method exactly is opensolver using? I noticed I get different results in R/MATLAB using their solve functions than I do with opensolver working with an overdetermined system. I believe R/MATLAB will use a least squares fit if appropriate for over-determined systems but what does opensolver do? The results are very much different.
Greg: OpenSolver only solves linear problems, for which it (and Solver) use the Simplex method with Branch and Bound. Newton’s method is for non-linear problems. Hope this helps, Andrew
Thanks for providing OpenSolver. It is a boon for students like me who like the UI of excel. I have a question. Is there a limit on the number of characters that can be entered in the “variable cells” dialog? It ceases to accept variable after reaching 266 characters. Is there any way to get around this?
Sashi: I think the RefEdit control we are using is setting that limit; we don’t impose any restrictions. Solver also seems to have a 256 character restriction. You can (possibly) get around this by defining your decision variables using a local Named Range with a name such as “Sheet1!solver_adj”. Create this using the Excel Name Manager, making sure the “scope” you choose is the name of the sheet, not the workbook. Don’t be surprised if other things then break, though; I have not tried this! Another approach is to create a contiguous block of new decision variables (which then won’t need many characters to define the range), and then put formulae in the old decision variables that reference the new decision variables. Note that OpenSolver does not require the decision variables and the constraints to be on the same sheet, but Solver does I believe. I look forward to hearing how you get on. Andrew
Hi Andrew,
I am using OpenSolver to solve some IP problems that are generated for student assessment. The VBA code is now as follows. Whenever I click a button, a macro will run and generate a random problem. I tried to use RunOpenSolver False and it returns error saying that the sub or routine is not found. I now use openSolver.RunOpenSolver False and it seems to work. However, an error returns (I have error catcher but do not know what it is) even though some solutions has been found. It turns out that that feasible solution is not optimal because I am able to click the OpenSolver button or solve through Solver and both give me the correct optimal solution, not the feasible solution I got from running the VBA code. Do you know why this is the case? Thanks,
Xuan, I’m pleased to hear you are using OpenSolver. I cannot think of any reason why you would be getting an error. Perhaps you’d like to email the file to me. I can have a look at it to see where the problem lies. My email is a dot mason at auckland dot ac dot nz. Cheers, Andrew
Xuan, If you want to use OpenSolver from VBA, you need to add a reference to “openSolver” in your project. I’ve added info on doing this at http://opensolver.org/using-opensolver. I hope this helps. Andrew
Dear OpenSolver team – great work!! I realize the next question is a leap but is there any plan to allow this wonderful tool to solve MINL or at least MIQP ? I realize there might be liminations to the CO-IN framework but I thought I’d ask…
Georgi: We have thought about an extension into the non-linear world, and there are some good COIN-OR tools for these problems, but it is not on our current to-do list, sorry. Andrew
Jadeson, Because OpenSolver works with standard Solver models, you can use all the standard Solver VBA functions such as SolverAdd to construct and modify the problem. Then, when you want to solve it, you should call OpenSolver using SolveModel(SolveRelaxation As Boolean). This does not currently return any result to tell you what happened. However, Mauricio suggested the function below for this. (Thanks, Mauricio, for sharing your code with us.) We are working on making OpenSolver more VBA friendly. Cheers, Andrew
Public Function statusReport(SolutionFilePathName As String) As Integer
‘ Read in the solution, status first
If Dir(SolutionFilePathName) = “” Then
MsgBox “The CBC solver did not create a solution file. No new solution is available.”, , “OpenSolver Error”
statusReport = -1
Else
Open SolutionFilePathName For Input As 1 ‘ supply path with filename
Dim cbcStatus As String
Line Input #1, cbcStatus ‘ Optimal – objective value 22
‘ Line Input #1, junk ‘ get rest of line
If cbcStatus Like “Optimal*” Then
statusReport = 0
ElseIf cbcStatus Like “Infeasible*” Then
statusReport = 1
ElseIf cbcStatus Like “Integer infeasible*” Then
statusReport = 2
ElseIf cbcStatus Like “Unbounded*” Then
statusReport = 3
ElseIf cbcStatus Like “Stopped*” Then ‘ Stopped on iterations or time
statusReport = 4
Else
MsgBox “The response from the solver is not recognised. The response was: ” & cbcStatus, , “OpenSolver Error”
statusReport = -1
End If
Close #1
End If
End Function
Mauricio, Unfortunately not at the moment. But you can change the code in “Function SolveModel(SolveRelaxation As Boolean)” so that OpenSolver returns an error message or result code instead of showing a dialog. This is on our to-do list, but if you do it first, we’d love to receive the changes. Andrew
I have a model that runs fine with Solver but when I try to run it with Open solver, it gives me an Error 1: Object variable or with variable not set.Am I doing something incorrectly?
You may have found a bug; sorry about that. Are you able to send us your spreadsheet? My email is a dot mason at auckland dot ac dot nz. Thanks, Andrew
Hallo,
When I click solve button, I always get Type mismatch error. I’m using Czech version of office 2007. I think it has something to do with Czech national environment, because the error occurs on lines of source code, where OpenSolver is reading values of convergence, tolerance and precision from excel solver.
If I change the source code and set the values of these attributes manually instead of using mid() function, The OpenSolver works and finds optimal solution.
Best Regards,
Karel Charvat
Vyanjana, It seems like you have problems with your model, not with OpenSolver…. is that correct? (In other words, does it work with Solver, but not with OpenSolver?) It is not surprising that changing the model can make it infeasible. I would suggest that you use OpenSolver’s Show Model and then very carefully check your constraints. Hope this helps, Andrew
Is it available for OpenOffice.org for use on Linux and/or Mac OS X? If this is a repeat question, my apologies (and please post to a link to the relevant info). Thanks.
Have never tried OpenSolver on a Mac. The VB code should mostly work, but it has some Windows specific code to run CBC that will need changing. You will also need a version of CBC compiled for the Mac. The CBC site http://www.coin-or.org/projects/Cbc.xml suggests a Mac version is available. Let me know how you get on; I’d love to make a Mac version available if you succeed. Have not tried OpenSolver on Linux, but the same comments apply. Andrew
The call Solver v. 0.982 (2010-08-17) leads to occurrence error 13. In the initial table of errors is not present. In the first variant for nonintegral numbers it was used «.», in the second variant «,». In both cases there is a message «Type mismatch Source = OpenSolver». What to do? Thanks, Alex.
If you like to send us your spreadsheet, we can add it to our suite of test problems to check our forthcoming release (now only days away). We expect this new version to fix all the problems we have found, and to be much faster for large problems. Andrew
I recently worked on project of mine,where I had to use Open Solver through VBA .I am very sure that you guys would be knowing the procedure.But as a user of this open source software,I found it pretty difficult to know the VBA code for running Open Solver.It would be really helpful to the users if you could post this code on your site. This is the code to run it:
Set NewRange = Range(“DecisionVariables”)
OpenSolver.InitializeQuickSolve
OpenSolver.RunQuickSolve
Even though it might be just 3 lines of code,I waste close to a week to find this out.I would really appreciate if you guys could post this info on your site.
Thank You.
Arjun Srinivasan
Arjun: Pleased to hear that you are using OpenSolver. However, your code for calling OpenSolver does not look right. I suggest you look at the code in OpenSolverMain, eg see OpenSolver_SolveClickHandler. You only need one line of VBA to solve a model: RunOpenSolver false. (Set the argument to be true to solve while ignoring integrality requirements.) The RunOpenSolver, InitializeQuickSolve and RunQuickSolve subroutines provide more detailed examples of using OpenSolver. Hope this helps, Andrew
PS: What are you solving with OpenSolver? We love to hear how this code is being used…
Maurice: Thanks for the feedback, and your subsequent help in tracking down the internationalisation issue causing this problem. The next version shoudl fix any “,” vs “.” issues that arise in non-English systems. Andrew
Thanks, Andrew! I’m using OpenSolver on a project I’m doing for a Major League Baseball team, and it’s exactly what I needed. It lets the team use their preferred platform, creates and solves their LPs very quickly, and doesn’t constrain them with any variable limits. Thanks again!
What method exactly is opensolver using? I noticed I get different results in R/MATLAB using their solve functions than I do with opensolver working with an overdetermined system. I believe R/MATLAB will use a least squares fit if appropriate for over-determined systems but what does opensolver do? The results are very much different.
It seems this uses Newton’s Method/steepest descent?
Greg: OpenSolver only solves linear problems, for which it (and Solver) use the Simplex method with Branch and Bound. Newton’s method is for non-linear problems. Hope this helps, Andrew
Hello,
Thanks for providing OpenSolver. It is a boon for students like me who like the UI of excel. I have a question. Is there a limit on the number of characters that can be entered in the “variable cells” dialog? It ceases to accept variable after reaching 266 characters. Is there any way to get around this?
Thanks,
Sashi
Sashi: I think the RefEdit control we are using is setting that limit; we don’t impose any restrictions. Solver also seems to have a 256 character restriction. You can (possibly) get around this by defining your decision variables using a local Named Range with a name such as “Sheet1!solver_adj”. Create this using the Excel Name Manager, making sure the “scope” you choose is the name of the sheet, not the workbook. Don’t be surprised if other things then break, though; I have not tried this! Another approach is to create a contiguous block of new decision variables (which then won’t need many characters to define the range), and then put formulae in the old decision variables that reference the new decision variables. Note that OpenSolver does not require the decision variables and the constraints to be on the same sheet, but Solver does I believe. I look forward to hearing how you get on. Andrew
Thanks Andrew. Named ranges was the way I handled it and it works like a charm. Thanks for the suggestion.
Sashi
Hi Andrew,
I am using OpenSolver to solve some IP problems that are generated for student assessment. The VBA code is now as follows. Whenever I click a button, a macro will run and generate a random problem. I tried to use RunOpenSolver False and it returns error saying that the sub or routine is not found. I now use openSolver.RunOpenSolver False and it seems to work. However, an error returns (I have error catcher but do not know what it is) even though some solutions has been found. It turns out that that feasible solution is not optimal because I am able to click the OpenSolver button or solve through Solver and both give me the correct optimal solution, not the feasible solution I got from running the VBA code. Do you know why this is the case? Thanks,
Xuan, I’m pleased to hear you are using OpenSolver. I cannot think of any reason why you would be getting an error. Perhaps you’d like to email the file to me. I can have a look at it to see where the problem lies. My email is a dot mason at auckland dot ac dot nz. Cheers, Andrew
Hi Andrew,
I sent you an example with the issue I described above. Thanks for your help,
Vinh
Xuan, If you want to use OpenSolver from VBA, you need to add a reference to “openSolver” in your project. I’ve added info on doing this at http://opensolver.org/using-opensolver. I hope this helps. Andrew
Hi Andrew,
Congratulations on the 2011 COIN-OR INFORMS Cup!
All the best,
Vinh
Dear OpenSolver team – great work!! I realize the next question is a leap but is there any plan to allow this wonderful tool to solve MINL or at least MIQP ? I realize there might be liminations to the CO-IN framework but I thought I’d ask…
Cheers,
Georgi
Georgi: We have thought about an extension into the non-linear world, and there are some good COIN-OR tools for these problems, but it is not on our current to-do list, sorry. Andrew
Hey,
I just wonder that is there similar VBA functions in opensolver like those in SOLVER.
e.g. SolverReset, SolverOptions, SolverOk, SolverAdd, SolverSolve.
I would like to run linear programming repeatly through VBA.
Thank you.
Jadeson
Jadeson, Because OpenSolver works with standard Solver models, you can use all the standard Solver VBA functions such as SolverAdd to construct and modify the problem. Then, when you want to solve it, you should call OpenSolver using SolveModel(SolveRelaxation As Boolean). This does not currently return any result to tell you what happened. However, Mauricio suggested the function below for this. (Thanks, Mauricio, for sharing your code with us.) We are working on making OpenSolver more VBA friendly. Cheers, Andrew
Public Function statusReport(SolutionFilePathName As String) As Integer
‘ Read in the solution, status first
If Dir(SolutionFilePathName) = “” Then
MsgBox “The CBC solver did not create a solution file. No new solution is available.”, , “OpenSolver Error”
statusReport = -1
Else
Open SolutionFilePathName For Input As 1 ‘ supply path with filename
Dim cbcStatus As String
Line Input #1, cbcStatus ‘ Optimal – objective value 22
‘ Line Input #1, junk ‘ get rest of line
If cbcStatus Like “Optimal*” Then
statusReport = 0
ElseIf cbcStatus Like “Infeasible*” Then
statusReport = 1
ElseIf cbcStatus Like “Integer infeasible*” Then
statusReport = 2
ElseIf cbcStatus Like “Unbounded*” Then
statusReport = 3
ElseIf cbcStatus Like “Stopped*” Then ‘ Stopped on iterations or time
statusReport = 4
Else
MsgBox “The response from the solver is not recognised. The response was: ” & cbcStatus, , “OpenSolver Error”
statusReport = -1
End If
Close #1
End If
End Function
Thanks a lot. It is a really good tool.
Is there any way to know the status of the solver after the method “RunOpenSolver” was executed on VBA?
Thanks,
Mauricio, Unfortunately not at the moment. But you can change the code in “Function SolveModel(SolveRelaxation As Boolean)” so that OpenSolver returns an error message or result code instead of showing a dialog. This is on our to-do list, but if you do it first, we’d love to receive the changes. Andrew
I have a model that runs fine with Solver but when I try to run it with Open solver, it gives me an Error 1: Object variable or with variable not set.Am I doing something incorrectly?
You may have found a bug; sorry about that. Are you able to send us your spreadsheet? My email is a dot mason at auckland dot ac dot nz. Thanks, Andrew
Hallo,
When I click solve button, I always get Type mismatch error. I’m using Czech version of office 2007. I think it has something to do with Czech national environment, because the error occurs on lines of source code, where OpenSolver is reading values of convergence, tolerance and precision from excel solver.
If I change the source code and set the values of these attributes manually instead of using mid() function, The OpenSolver works and finds optimal solution.
Best Regards,
Karel Charvat
Karel, Thanks for your detailed error report. We have fixed this in version 1.3 which I will email you for testing. Thanks, Andrew
Hi All… Trying to solve a IP which shows infeasible solution when integer values are greater than 10 … Any comments??
Vyanjana, It seems like you have problems with your model, not with OpenSolver…. is that correct? (In other words, does it work with Solver, but not with OpenSolver?) It is not surprising that changing the model can make it infeasible. I would suggest that you use OpenSolver’s Show Model and then very carefully check your constraints. Hope this helps, Andrew
Is it available for OpenOffice.org for use on Linux and/or Mac OS X? If this is a repeat question, my apologies (and please post to a link to the relevant info). Thanks.
Sid.
Have never tried OpenSolver on a Mac. The VB code should mostly work, but it has some Windows specific code to run CBC that will need changing. You will also need a version of CBC compiled for the Mac. The CBC site http://www.coin-or.org/projects/Cbc.xml suggests a Mac version is available. Let me know how you get on; I’d love to make a Mac version available if you succeed. Have not tried OpenSolver on Linux, but the same comments apply. Andrew
The call Solver v. 0.982 (2010-08-17) leads to occurrence error 13. In the initial table of errors is not present. In the first variant for nonintegral numbers it was used «.», in the second variant «,». In both cases there is a message «Type mismatch Source = OpenSolver». What to do? Thanks, Alex.
If you like to send us your spreadsheet, we can add it to our suite of test problems to check our forthcoming release (now only days away). We expect this new version to fix all the problems we have found, and to be much faster for large problems. Andrew
I recently worked on project of mine,where I had to use Open Solver through VBA .I am very sure that you guys would be knowing the procedure.But as a user of this open source software,I found it pretty difficult to know the VBA code for running Open Solver.It would be really helpful to the users if you could post this code on your site. This is the code to run it:
Set NewRange = Range(“DecisionVariables”)
OpenSolver.InitializeQuickSolve
OpenSolver.RunQuickSolve
Even though it might be just 3 lines of code,I waste close to a week to find this out.I would really appreciate if you guys could post this info on your site.
Thank You.
Arjun Srinivasan
Arjun: Pleased to hear that you are using OpenSolver. However, your code for calling OpenSolver does not look right. I suggest you look at the code in OpenSolverMain, eg see OpenSolver_SolveClickHandler. You only need one line of VBA to solve a model: RunOpenSolver false. (Set the argument to be true to solve while ignoring integrality requirements.) The RunOpenSolver, InitializeQuickSolve and RunQuickSolve subroutines provide more detailed examples of using OpenSolver. Hope this helps, Andrew
PS: What are you solving with OpenSolver? We love to hear how this code is being used…
The software is working great. But only use integers. If decimals are used, the results are unreliable.
Maurice: Thanks for the feedback, and your subsequent help in tracking down the internationalisation issue causing this problem. The next version shoudl fix any “,” vs “.” issues that arise in non-English systems. Andrew
Thanks, Andrew! I’m using OpenSolver on a project I’m doing for a Major League Baseball team, and it’s exactly what I needed. It lets the team use their preferred platform, creates and solves their LPs very quickly, and doesn’t constrain them with any variable limits. Thanks again!
The OpenSolver.zip file has now been uploaded; our first public beta release! Andrew